[postgis-users] Find LineString segment index containing closest point?

[Martin Davis]

And here's a SQL function to do it:

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

On Tue, Aug 4, 2020 at 3:56 PM Martin Davis <mtnclimb at gmail.com> wrote:

> Here's. SQL to do this:
>
> 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;
>
> It would be simpler to wrap this up in a function.
>
> On Tue, Aug 4, 2020 at 1:43 PM Bruce Rindahl <bruce.rindahl at gmail.com>
> wrote:
>
>> I found this post from a few years back by Paul Ramsey:
>>
>> http://blog.cleverelephant.ca/2015/02/breaking-linestring-into-segments.html
>>
>> One of the examples gives a collection of each segment of the line along
>> with what you can use as an index.  Could you then query for the closest
>> geometry from that collection and get the index number?
>> Sorry, I can't test right now as my pgadmin is not working.
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200804/64373ff7/attachment.html>