Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

share|improve this question
    
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 at 8:17

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.