1

Is there an easy way to retrieve the line segment of a point on a linestring using PostGIS?

To map a point onto a linestring, I use ST_(3D)ClosestPoint. Now I would like to find the index of the line segment on that linestring, the closestpoint is on.

Turf.js is returning that inline with its nearestpointonline function. That returns the line segment index, the distance between point and line, the distance along the linestring together with the closest point on the linestring in one call.

1

This probably has to be done by iterating over the line segments and finding the index of the one with minimum distance to the point.

This would be a nice addition to the ST_(3D)ClosestPoint functions. Or perhaps more appropriately it could be an extension to one or more of the Linear Referencing functions

If you want the segment index in order to add a vertex to the LineString at the closest point, then ST_Snap can be used to add the point in one step:

SELECT ST_AsText( ST_Snap('LINESTRING (0 0, 9 9)', 'POINT(1 1.1)', 0.2));
1
  • Yes, that is what I feared. In the end, PG is doing the exact same thing already in the background (but most likely much more efficient ;-) +1 on the addition. Even though, I don't know, how to return objects/several values in SQL statements... – ASI Jul 28 '20 at 7:54
0

Here is SQL to find the index of the segment which is closest to a point:

WITH data(id, geom) AS (VALUES
    ( 1, 'LINESTRING (0 0, 10 10, 20 20, 30 30)'::geometry )
)
SELECT i FROM (
SELECT i, ST_Distance(
   ST_MakeLine( ST_PointN( data.geom, s.i ), ST_PointN( data.geom, s.i+1 ) ),
   'POINT(15 15.1)'::geometry ) AS dist
   FROM data JOIN LATERAL (
        SELECT i FROM generate_series(1, ST_NumPoints( data.geom )-1) AS gs(i)
     ) AS s(i) ON true
   ORDER BY dist
) AS t LIMIT 1;

Not the simplest, but it could be wrapped up in a function.

1
  • Thanks a lot, @dr_jts! – ASI Sep 15 '20 at 11:23
0

Here's a SQL function to compute the segment index of the LineString segment closest to a given point:

CREATE OR REPLACE FUNCTION ST_LineLocateN( line geometry, pt geometry )
RETURNS integer
AS $$
    SELECT i FROM (
    SELECT i, ST_Distance(
        ST_MakeLine( ST_PointN( line, s.i ), ST_PointN( line, s.i+1 ) ),
        pt) AS dist
      FROM generate_series(1, ST_NumPoints( line )-1) AS s(i)
      ORDER BY dist
    ) AS t LIMIT 1;
$$
LANGUAGE sql STABLE STRICT;

Example:

SELECT ST_LineLocateN( 'LINESTRING (0 0, 10 10, 20 20, 30 30)'::geometry, 
  'POINT(15 15.1)'::geometry);
==> 2

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.