5

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

  • Could you draw a sketch about your case with the linestrings and mark which point you would like to find. Attach also the SQL query you have tried. – user30184 Aug 26 '14 at 8:17
3

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)
  • POINT(12 12) is not exact point want (10 10) or(20,20) – user32876 Aug 26 '14 at 8:55
  • i want exact point which is from linestring is there some function..?? – user32876 Aug 26 '14 at 8:57
  • @user32876 updated answer to convert to MultiPoint – Mike T Aug 26 '14 at 9:26
  • Could you edit your question and call closest point as closest vertex? – user30184 Aug 26 '14 at 13:35

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.