Changes between Initial Version and Version 1 of UsersWikiLinearRefFunctions


Ignore:
Timestamp:
Aug 5, 2020, 9:35:57 AM (16 months ago)
Author:
mdavis
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • UsersWikiLinearRefFunctions

    v1 v1  
     1= Functions for Linear Referencing =
     2
     3These functions augment the [https://postgis.net/docs/manual-3.0/reference.html#Linear_Referencing built-in] ones in PostGIS.
     4
     5== Locate Segment Index for Point ==
     6
     7This can be useful for inserting a line vertex at the location closest to a given point.
     8
     9Also see [https://postgis.net/docs/manual-3.0/ST_Snap.html ST_Snap] for doing this.
     10
     11{{{
     12--- Locates the segment on a LineString containing the location closest to a given point.
     13--- Returns a record containing:
     14---   index: the segment index of the closest point on the line
     15---   dist: the distance to the given point
     16
     17CREATE OR REPLACE FUNCTION ST_LineLocateSegment( line geometry, pt geometry )
     18RETURNS table(index integer, dist double precision)
     19AS $$
     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)
     25      ORDER BY dist
     26    ) AS t LIMIT 1;
     27$$
     28LANGUAGE sql STABLE STRICT;
     29}}}
     30
     31Example
     32
     33{{{
     34SELECT ST_LineLocateSegment( 'LINESTRING (0 0, 10 10, 20 20, 30 30)'::geometry,
     35  'POINT(15 15.1)'::geometry);
     36}}}
     37