1

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?

kevin
  • 309
  • 2
  • 12
  • I think you need to Take a look on [this](https://stackoverflow.com/questions/2234204/latitude-longitude-find-nearest-latitude-longitude-complex-sql-or-complex-calc) hope that help – Mario Nassef Jun 11 '17 at 03:37
  • I think you need to Take a look on [this](https://stackoverflow.com/questions/2234204/latitude-longitude-find-nearest-latitude-longitude-complex-sql-or-complex-calc) hope that help – Mario Nassef Jun 11 '17 at 03:38
  • @MarioNassef That has nothing to do with SpatiaLite. – CL. Jun 11 '17 at 08:04
  • Did you try using a `LIMIT`? – Yunnosch Jun 11 '17 at 08:07
  • Please make a [mcve], e.g. by posting the result of `.dump` from SQLite command line tool of a suitably tailored toy database. – Yunnosch Jun 11 '17 at 08:08

1 Answers1

0

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;
CL.
  • 173,858
  • 17
  • 217
  • 259