Using PostGIS 2.0.0 I'd like to find the point on a LINESTRING that is closest to a given point. The LINESTRING represents a great circle line (ie. geography type). ST_ClosestPoint appears to do exactly what I want, however, I find that the returned point does not lie on the great circle line:
WITH points AS ( SELECT ST_GeomFromEWKT('SRID=4326;POINT(41 12)') AS point, ST_GeomFromEWKT('SRID=4326;LINESTRING(40 5,41 15)') AS border ) SELECT ST_AsText(ST_ClosestPoint(border, point)), -- POINT(40.7029702970297 12.029702970297) ST_DWithin(ST_ClosestPoint(border, point)::geography, border::geography, 700) -- false FROM points;
It's actually over 700 metres from the line. I believe this happens because ST_ClosestPoint operates on a plane. How would I do the same thing on the spheroid (or at least on the sphere)?
Edit: I tried MerseyViking's function (ported to plpgsql) and it mostly works very well, but in some cases returns a point that is not on the line. A simple example:
WITH points AS ( SELECT ST_GeomFromEWKT('SRID=4326;POINT(10 3)') AS point, ST_GeomFromEWKT('SRID=4326;LINESTRING(10 5,10 15)') AS border ), calc AS ( SELECT ST_ClosestPoint(border, point)::geography AS plane_closest, closest_point_to_line(border, point) AS sphere_closest FROM points ) SELECT ST_AsText(plane_closest) AS plane_closest, -- POINT(10 5) ST_DWithin(plane_closest, border::geography, 1), -- true ST_AsText(sphere_closest) AS sphere_closest, -- POINT(10 3) ST_DWithin(sphere_closest, border::geography, 1), -- false FROM points, calc;