Functions for Linear Referencing

These functions augment the ​built-in ones in PostGIS.

ST_LineLocateSegment - Locate Segment for Point

This can be useful for inserting a line vertex at the location closest to a given point.

Also see ​ST_Snap for doing this in a single step, and with the ability to add multiple points.

--- 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
---   geom: the closest point on the line

CREATE OR REPLACE FUNCTION ST_LineLocateSegment( line geometry, pt geometry )
RETURNS table(index integer, dist double precision, geom geometry)
AS $$
  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
      LIMIT 1
  )
  SELECT i - 1, dist, geom FROM dist;
$$
LANGUAGE sql STABLE STRICT;

Example 1: show the segment record returned

SELECT ST_LineLocateSegment( 'LINESTRING (0 0, 10 10, 20 20, 30 30)'::geometry,
  'POINT(15 15.1)'::geometry);

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;