= 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 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; }}}