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)?
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;