Finding line segment of point on linestring using PostGIS

Question

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.

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

• ASI: 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...

Answer 2

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.

• ASI: Thanks a lot, @dr_jts!

Answer 3

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