Spatialite SQL query to find the nearest node for the given lat/lon

Question

I want to find the nearest node to a given coordinate. I checked this https://gis.stackexchange.com/questions/186783/how-to-get-the-long-lat-information-from-a-nodeid-and-vice-versa But, his answer doesn't work.

My query:

SELECT PID, 
    min (DISTANCE(Startpoint(geometry), MakePoint(-79.417589, 43.266571)))
    FROM test1f 

There is no synax error, but no matter what the point is, it always returns the first record in the database and the distance to it.

I also tried this query

SELECT PID, 
      DISTANCE(Startpoint(geometry), MakePoint(-79.917589, 43.266571))
    FROM test1f  ORDER BY DISTANCE(Startpoint(geometry), MakePoint(-79.917589, 43.266571))

I got 50000 results, the first one is what I want. How can I only get the first one?

Answer 1

Your first query looks correct. (It is essentially identical with the first example here.)

Anyway, to return only the first result row, add LIMIT 1 to the query.

Since SpatiaLite 4.4.0, you can use the KNN virtual table to do the search more efficiently:

SELECT test1f.PID,
       knn.distance
FROM test1f
JOIN knn ON test1f.PID = knn.fid  -- assuming PID is the INTEGER PRIMARY KEY
WHERE f_table_name = 'test1f'
  AND ref_geometry = MakePoint(-79.417589, 43.266571)
  AND max_items = 1;