Changes between Version 1 and Version 2 of UsersWikiLinearRefFunctions

Timestamp:

Aug 5, 2020, 10:12:16 AM (4 years ago)

Author:

mdavis

Comment:

—

UsersWikiLinearRefFunctions

@@ -7 +7 @@
 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.
+Also see [https://postgis.net/docs/manual-3.0/ST_Snap.html ST_Snap] for doing this in a single step, 
+and with the ability to add multiple points.
 
 {{{
@@ -14 +15 @@
 ---   index: the segment index of the closest point on the line
 ---   dist: the distance to the given point
+---   geom: the closest point on the line
 
 CREATE OR REPLACE FUNCTION ST_LineLocateSegment( line geometry, pt geometry )
-RETURNS table(index integer, dist double precision)
+RETURNS table(index integer, dist double precision, geom geometry)
 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)
+  WITH segs AS (
+    SELECT s.i, ST_MakeLine( ST_PointN( line, s.i ), ST_PointN( line, s.i+1 ) ) AS seg
+        FROM generate_series(1, ST_NumPoints( line )-1) AS s(i)
+  ),
+  dist AS (
+    SELECT i, ST_Distance(seg, pt) AS dist, ST_ClosestPoint(seg, pt) AS geom
+      FROM segs
       ORDER BY dist
-    ) AS t LIMIT 1;
+      LIMIT 1
+  )
+  SELECT i - 1, dist, geom FROM dist;
 $$
 LANGUAGE sql STABLE STRICT;
 }}}
 
-Example
+**Example 1: show the segment record returned**
 
 {{{
@@ -36 +42 @@
 }}}
 
+**Example 2: Add the closest point to the line**
+{{{
+WITH data(id, line) AS (VALUES
+    ( 1, 'LINESTRING (0 0, 10 10, 20 20, 30 30)'::geometry )
+),
+loc AS (
+  SELECT id, line, index, geom AS pt
+    FROM data
+    JOIN LATERAL ST_LineLocateSegment( data.line, 'POINT(15 15.1)'::geometry ) AS lls ON true
+)
+SELECT id, ST_AsText( ST_AddPoint( line, pt, index ) )
+  FROM loc; 
+}}}
+