Changes between Initial Version and Version 1 of UsersWikiLinearRefFunctions

Timestamp:

Aug 5, 2020, 9:35:57 AM (4 years ago)

Author:

mdavis

Comment:

—

UsersWikiLinearRefFunctions

@@ +1 @@
+= Functions for Linear Referencing =
+
+These functions augment the [https://postgis.net/docs/manual-3.0/reference.html#Linear_Referencing built-in] ones in PostGIS.
+
+== Locate Segment Index for Point ==
+
+This can be useful for inserting a line vertex at the location closest to a given point.
+
+Also see [https://postgis.net/docs/manual-3.0/ST_Snap.html ST_Snap] for doing this.
+
+{{{
+--- Locates the segment on a LineString containing the location closest to a given point. 
+--- Returns a record containing:
+---   index: the segment index of the closest point on the line
+---   dist: the distance to the given point
+
+CREATE OR REPLACE FUNCTION ST_LineLocateSegment( line geometry, pt geometry )
+RETURNS table(index integer, dist double precision)
+AS $$
+    SELECT i - 1, dist 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_LineLocateSegment( 'LINESTRING (0 0, 10 10, 20 20, 30 30)'::geometry,
+  'POINT(15 15.1)'::geometry);
+}}}
+