I am trying to find the nearest line to each point in a layer, and I have over 1 million points. I want to be able to associated the closest unique line ID with the point unique IDs.

I have tried to use the solution given in this post Finding nearest neighbors between two tables with point locations in SpatiaLite? on a small subset, and I do get a result table but the distance value is null, and the IDs from the lines that are associated with each point are not the IDs for the closest lines.

I am new with SpatialLite and running spatial queries in SQL. I don't really understand the purpose of this statement: WHERE g1.OGC_FID <> g2.OGC_FID.

I have also played around with this line: AND ST_Contains(ST_Expand(g1.geometry,50),g2.geometry) as well as removed it and still get no distance values, even though I am getting an ID.

I was wondering if it might be a coordinate system issue but both the points and the lines are in the same system. I am using the QSpatiaLite plugin in QGIS. Can anyone provide some guidance?


Using the answer below I was able to get the query working on some test data. However I am having trouble applying the spatial index. I've tried the query below, but it runs slower than the query statement without the spatial index. What am I doing wrong?

UPDATE UgandaBikeTaxis_test SET dist=(SELECT 
    ST_Distance(p.geometry, l.geometry) AND
p.ROWID IN
(SELECT ROWID FROM SpatialIndex WHERE f_table_name= "ugandabiketaxis_test"  AND search_frame=l.Geometry)
    FROM osm_filtered_prj_test AS l, ugandabiketaxis_test AS p
    WHERE p.uniqueID = ugandabiketaxis_test.uniqueID
    ORDER BY ST_Distance(p.geometry, l.geometry) LIMIT 1);
  • Yes that's the answer I tried to use but I am having the problems I mention above – kflaw Jul 24 '15 at 16:28
  • Since this thread already has an accepted answer, your follow-up question on performance would get more attention if it would be posted separately. – underdark Jul 25 '15 at 22:17
up vote 5 down vote accepted

You can do this with two UPDATE statements, one for the distance, and the second for the line ID, with a subquery to get the values from the line table. And use the

ORDER BY ST_Distance(...) LIMIT 1

construct to get only the closest line.

I have a cities point layer, and a hiways line layer. Each has a primary key column 'pk'. I added to the cities two columns: 'dist' and 'line_id' to hold the values for the closest line. Here are the queries:

UPDATE cities_il SET line_id=(SELECT l.pk
    FROM hiways_il AS l, cities_il AS p
    WHERE p.pk=cities_il.pk
    ORDER BY ST_Distance(p.geometry, l.geometry) LIMIT 1);
UPDATE cities_il SET dist=(SELECT 
    ST_Distance(p.geometry, l.geometry)
    FROM hiways_il AS l, cities_il AS p
    WHERE p.pk=cities_il.pk
    ORDER BY ST_Distance(p.geometry, l.geometry) LIMIT 1);

Note the form of the WHERE clause in the subquery. Here you set the primary key in the subquery equal to the primary key in the outer update statement, to be sure to update each row separately.

In my example I had only 100 or so cities and 10,000 hiways. And each UPDATE took a minute or so. In your case, with a million points, you will definitely need to add a spatial index to the lines (maybe also the points), and use it in your query. Otherwise it will probably take hours. You'll find plenty of examples on spatial indexing with spatialite, such as: https://www.gaia-gis.it/fossil/libspatialite/wiki?name=SpatialIndex

  • Thanks very much for your answer. I think I generally follow. I have added spatial indexes to my layers, and based on the link you provided, it would seem that I would include the line FROM SpatialIndex before the where clauses? – kflaw Jul 25 '15 at 14:50
  • I ran the first update query and it ran without error but gave me no result? – kflaw Jul 25 '15 at 16:22
  • I am wondering if this has to do with the coordinate systems, I haven't explicitly defined the SRID I am trying to figure out how to do that – kflaw Jul 25 '15 at 16:38
  • ok I think I finally got it! – kflaw Jul 25 '15 at 16:51
  • I am having trouble getting the spatial index in the query to work I pasted the query I am trying to run above. – kflaw Jul 25 '15 at 21:46

Since version 4.4.0 SpatiaLite supports a KNN virtual table index for nearest neighbor problems. Here is a query the finds the nearest line in a linestring table to each point in a point table.

SELECT k.* FROM knn k, points p
WHERE f_table_name = 'linestrings' 
AND ref_geometry = p.geometry
AND max_items = 1;

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.