0

I have a linestring and a point, I want to find the closest point on the linestring to that point.

Below is a screenshot of a test I created to confirm that the various POSTGIS functions for finding the closest point between two objects will interpolate a point that isn't in the original linestring.

I need to return the nearest point that is an actually point that exists in the linestring source. In the test it should be the first square-red marker i want returned. But POSTGIS returns a point in-between points 1 & 2 that it generates.

[test screenshot] http://i.imgur.com/XWfJPZV.jpg

|improve this question
1

It's working this way but there may be a better function/query:

SELECT ST_AsText((r.gdump).geom) AS wkt
FROM
  (SELECT ST_DumpPoints(track) AS gdump,
          id
   FROM rides) AS r
WHERE id = 25
ORDER BY ST_Distance(ST_GeomFromText('POINT( 49.336533 -122.98797422 )', 4326), (r.gdump).geom) LIMIT 1
|improve this answer
  • Thank you! This works perfectly, seems so obvious now, i'm not to postgis so I don't know all the functions yet. This also is great because the ST_DumpPoints() function can also return the INDEX of the point, which is what I ultimately wanted. I just added (gdump).path[1] AS key – Canadaka Jul 19 '13 at 19:58

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.