TransWikia.com

Finding line segment of point on linestring using PostGIS

Geographic Information Systems Asked on August 25, 2021

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.

3 Answers

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

Answered by dr_jts on August 25, 2021

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.

Answered by dr_jts on August 25, 2021

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

Answered by dr_jts on August 25, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP