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);