Changes between Version 1 and Version 2 of UsersWikiLinearRefFunctions


Ignore:
Timestamp:
Aug 5, 2020, 10:12:16 AM (16 months ago)
Author:
mdavis
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • UsersWikiLinearRefFunctions

    v1 v2  
    77This can be useful for inserting a line vertex at the location closest to a given point.
    88
    9 Also see [https://postgis.net/docs/manual-3.0/ST_Snap.html ST_Snap] for doing this.
     9Also see [https://postgis.net/docs/manual-3.0/ST_Snap.html ST_Snap] for doing this in a single step,
     10and with the ability to add multiple points.
    1011
    1112{{{
     
    1415---   index: the segment index of the closest point on the line
    1516---   dist: the distance to the given point
     17---   geom: the closest point on the line
    1618
    1719CREATE OR REPLACE FUNCTION ST_LineLocateSegment( line geometry, pt geometry )
    18 RETURNS table(index integer, dist double precision)
     20RETURNS table(index integer, dist double precision, geom geometry)
    1921AS $$
    20     SELECT i - 1, dist FROM (
    21     SELECT i, ST_Distance(
    22         ST_MakeLine( ST_PointN( line, s.i ), ST_PointN( line, s.i+1 ) ),
    23         pt) AS dist
    24       FROM generate_series(1, ST_NumPoints( line )-1) AS s(i)
     22  WITH segs AS (
     23    SELECT s.i, ST_MakeLine( ST_PointN( line, s.i ), ST_PointN( line, s.i+1 ) ) AS seg
     24        FROM generate_series(1, ST_NumPoints( line )-1) AS s(i)
     25  ),
     26  dist AS (
     27    SELECT i, ST_Distance(seg, pt) AS dist, ST_ClosestPoint(seg, pt) AS geom
     28      FROM segs
    2529      ORDER BY dist
    26     ) AS t LIMIT 1;
     30      LIMIT 1
     31  )
     32  SELECT i - 1, dist, geom FROM dist;
    2733$$
    2834LANGUAGE sql STABLE STRICT;
    2935}}}
    3036
    31 Example
     37**Example 1: show the segment record returned**
    3238
    3339{{{
     
    3642}}}
    3743
     44**Example 2: Add the closest point to the line**
     45{{{
     46WITH data(id, line) AS (VALUES
     47    ( 1, 'LINESTRING (0 0, 10 10, 20 20, 30 30)'::geometry )
     48),
     49loc AS (
     50  SELECT id, line, index, geom AS pt
     51    FROM data
     52    JOIN LATERAL ST_LineLocateSegment( data.line, 'POINT(15 15.1)'::geometry ) AS lls ON true
     53)
     54SELECT id, ST_AsText( ST_AddPoint( line, pt, index ) )
     55  FROM loc;
     56}}}
     57