I posted this question originally Using nearest neighbor query in SpatiaLite?

Using 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) AND
(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);
  • Can you give a reasonable maximum distance from biketaxi stations to nearest roads? Could it be 100 meters or 1 km? – user30184 Jul 26 '15 at 16:21
  • Yes I definitely could but where do I apply that distance? – kflaw Jul 26 '15 at 16:25
  • I would build a search_frame around biketaxe stations by using the BuildCircleMBR function gaia-gis.it/gaia-sins/spatialite-sql-latest.html and radius of the max distance. ST_x and ST_y should be used for getting x and y coordinates for BuildCircleMBR. For easier reading I would put AND p.ROWID IN (SELECT ROWID FROM SpatialIndex WHERE f_table_name= "ugandabiketaxis_test" AND search_frame=BuildCircleMBR...) last in the query. – user30184 Jul 26 '15 at 16:31
  • So you mean just replace search_frame=l.Geometry with search_frame=BuildCircleMBR...? I don't quite understand. I am new with these queries. – kflaw Jul 26 '15 at 16:39
  • Well, your query looks a bit odd to me but without your data it would take me some time to make test layers to play with and I feel a bit lazy right now. Anyway, you query as it stands now is using the geometries of linestrings (actually envelopes of the geometries) as search_frame. That may lead to no hit at all if linestring is very short or to all too many hits if linestring is very long. So I would first use fixed distance from biketaxi stations as a search_frame for having a better control on what happens. – user30184 Jul 26 '15 at 16:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.