I posted this question originally nearest neighbor query in SpatiaLite. Thanks to the answer given I was able to get the query working on some test data. However I am having trouble applying a spatial index (which is a must as I have over 1 million points and I am trying to find the closest line in a layer that has over 300,000 lines). I've tried the query below on a test data set, 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) ANDp.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);
أكثر...
UPDATE UgandaBikeTaxis_test SET dist=(SELECT ST_Distance(p.geometry, l.geometry) ANDp.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);
أكثر...