Find closest NON interpolated point on a LINESTRING

Question

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

Answer 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