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

[Martin Davis]

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/ae476999/attachment.html>