[GIS] Find the closest line vertex from the linestring using PostGIS

Question

How to find the closest line vertex from a linestring? ST_ClosestPoint will give a point which is not from the linestring.

Answer 1

ST_ClosestPoint will find the closest point on a linestring. For example, locating a point on a road that is closest to a point-of-interest off to one side:

SELECT ST_AsText(ST_ClosestPoint(road, poi))
FROM (SELECT
  'LINESTRING (10 10, 20 20)'::geometry AS road,
  'POINT (14 10)'::geometry AS poi
) AS f;
  st_astext
--------------
 POINT(12 12)
(1 row)

There are theoretically an infinite number of points that are on a line, and this location is exactly on the road linestring:

SELECT ST_Intersects('LINESTRING (10 10, 20 20)'::geometry, 'POINT (12 12)'::geometry);
 st_intersects
---------------
 t
(1 row)

---

If the objective is to get the closest vertex or coordinate from the linestring, the road needs to be converted to a MultiPoint geometry. Here's a function that does that:

CREATE OR REPLACE FUNCTION ST_AsMultiPoint(geometry) RETURNS geometry AS
'SELECT ST_Union((d).geom) FROM ST_DumpPoints($1) AS d;'
LANGUAGE sql IMMUTABLE STRICT COST 10;

Then use it similar to before:

SELECT ST_AsText(ST_ClosestPoint(ST_AsMultiPoint(road), poi))
FROM (SELECT
  'LINESTRING (10 10, 20 20)'::geometry AS road,
  'POINT (14 10)'::geometry AS poi
) AS f;
  st_astext
--------------
 POINT(10 10)
(1 row)