I have a dataset osmMajor containing road data (from OSM; 670,780 features) and another dataset points containing some points of interest (500,000 points). For each feature in my point layer, I want to find the coordinate of the closest point located on the road network.

I have two options to do this: either ArcMap's near tool (Analysis tools/Proximity/Near), or Spatialite. I tried using ArcMap, but it seems a very long task to perform (I had to stop the process after 3 hours). For this reason, I was thinking about using Spatialite to do this, but it seems that the process is less straightforward than what I thought.

My question is thus: how should I write my Spatialite query to get what I want?

For Spatialite, I think that I should follow these steps:

1. For each point feature, calculate the distance between the point and the closest road
2. Around each point, create a buffer using the distance to the closest feature
3. Get the intersection between the buffer and the road data (it should be a point)
4. Get the coordinates (x,y) of the resulting point

Trying to implement these 4 steps on a sample dataset with only 10 points, and I encountered several issues.

For point (1.), I can find the overall smallest distance between the points and the roads, but I cannot have it for each point:

SELECT test.ID, min(Distance(test.Geometry, osmMajor.Geometry)) AS distance FROM test, osmMajor WHERE osmMajor.ROWID IN ( SELECT ROWID FROM SpatialIndex WHERE f_table_name = 'osmMajor' AND search_frame = test.Geometry) GROUP BY test.ID 

I get a distance for only three points instead of the 10 that are in the test table. I suspect it has something to do with the way the spatial index works, because the query only returns the results for points 2, 3 and 4, which are the closest to the road network.

Can anyone give me advice on the best way to get the coordinate of the point on a feature which is closest to another point?

Thank you!

I have a dataset osmMajor containing road data (from OSM; 670,780 features) and another dataset points containing some points of interest (500,000 points). For each feature in my point layer, I want to find the coordinate of the closest point located on the road network.

I have two options to do this: either ArcMap's near tool (Analysis tools/Proximity/Near), or Spatialite. I tried using ArcMap, but it seems a very long task to perform (I had to stop the process after 3 hours). For this reason, I was thinking about using Spatialite to do this, but it seems that the process is less straightforward than what I thought.

How should I write my Spatialite query to get what I want?

For Spatialite, I think that I should follow these steps:

1. For each point feature, calculate the distance between the point and the closest road
2. Around each point, create a buffer using the distance to the closest feature
3. Get the intersection between the buffer and the road data (it should be a point)
4. Get the coordinates (x,y) of the resulting point

Trying to implement these 4 steps on a sample dataset with only 10 points, and I encountered several issues.

For point (1.), I can find the overall smallest distance between the points and the roads, but I cannot have it for each point:

SELECT test.ID, min(Distance(test.Geometry, osmMajor.Geometry)) AS distance FROM test, osmMajor WHERE osmMajor.ROWID IN ( SELECT ROWID FROM SpatialIndex WHERE f_table_name = 'osmMajor' AND search_frame = test.Geometry) GROUP BY test.ID 

I get a distance for only three points instead of the 10 that are in the test table. I suspect it has something to do with the way the spatial index works, because the query only returns the results for points 2, 3 and 4, which are the closest to the road network.

Can anyone give me advice on the best way to get the coordinate of the point on a feature which is closest to another point?

I have a dataset osmMajor containing road data (from OSM; 670,780 features) and another dataset points containing some points of interest (500,000 points). For each feature in my point layer, I want to find the coordinate of the closest point located on the road network.

I have two options to do this: either ArcMap's near tool (Analysis tools/Proximity/Near), or Spatialite. I tried using ArcMap, but it seems a very long task to perform (I had to stop the process after 3 hours). For this reason, I was thinking about using Spatialite to do this, but it seems that the process is less straightforward than what I thought.

My first question is thus: is ArcMap's tool the best mean to obtain the coordinates I'm looking for?

If yes, is such a long processing time normal? Are there ways to speed it up?

If not, how should I write my Spatialite query to get what I want?

For Spatialite, I think that I should follow these steps:

1. For each point feature, calculate the distance between the point and the closest road
2. Around each point, create a buffer using the distance to the closest feature
3. Get the intersection between the buffer and the road data (it should be a point)
4. Get the coordinates (x,y) of the resulting point

Trying to implement these 4 steps on a sample dataset with only 10 points, and I encountered several issues.

For point (1.), I can find the overall smallest distance between the points and the roads, but I cannot have it for each point:

SELECT test.ID, min(Distance(test.Geometry, osmMajor.Geometry)) AS distance FROM test, osmMajor WHERE osmMajor.ROWID IN ( SELECT ROWID FROM SpatialIndex WHERE f_table_name = 'osmMajor' AND search_frame = test.Geometry) GROUP BY test.ID 

I get a distance for only three points instead of the 10 that are in the test table. I suspect it has something to do with the way the spatial index works, because the query only returns the results for points 2, 3 and 4, which are the closest to the road network.

Can anyone give me advice on the best way to get the coordinate of the point on a feature which is closest to another point?

Thank you!

I have a dataset osmMajor containing road data (from OSM; 670,780 features) and another dataset points containing some points of interest (500,000 points). For each feature in my point layer, I want to find the coordinate of the closest point located on the road network.

I have two options to do this: either ArcMap's near tool (Analysis tools/Proximity/Near), or Spatialite. I tried using ArcMap, but it seems a very long task to perform (I had to stop the process after 3 hours). For this reason, I was thinking about using Spatialite to do this, but it seems that the process is less straightforward than what I thought.

My question is thus: how should I write my Spatialite query to get what I want?

For Spatialite, I think that I should follow these steps:

1. For each point feature, calculate the distance between the point and the closest road
2. Around each point, create a buffer using the distance to the closest feature
3. Get the intersection between the buffer and the road data (it should be a point)
4. Get the coordinates (x,y) of the resulting point

Trying to implement these 4 steps on a sample dataset with only 10 points, and I encountered several issues.

For point (1.), I can find the overall smallest distance between the points and the roads, but I cannot have it for each point:

SELECT test.ID, min(Distance(test.Geometry, osmMajor.Geometry)) AS distance FROM test, osmMajor WHERE osmMajor.ROWID IN ( SELECT ROWID FROM SpatialIndex WHERE f_table_name = 'osmMajor' AND search_frame = test.Geometry) GROUP BY test.ID 

I get a distance for only three points instead of the 10 that are in the test table. I suspect it has something to do with the way the spatial index works, because the query only returns the results for points 2, 3 and 4, which are the closest to the road network.

Can anyone give me advice on the best way to get the coordinate of the point on a feature which is closest to another point?

Thank you!