= Functions for Linear Referencing = These functions augment the [https://postgis.net/docs/manual-3.0/reference.html#Linear_Referencing built-in] ones in PostGIS. == ST_LineLocateSegment == Locates the segment on a line containing the location closest to a given 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. {{{ --- ST_LineLocateSegment: 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** * Note that the ST_LineLocateSegment function in the JOIN can reference the preceding table in the join (functions cause an implicit LATERAL join). {{{ WITH data(id, line) AS (VALUES ( 1, 'LINESTRING (0 0, 10 10, 20 20, 30 30)'::geometry ) ) SELECT id, ST_AddPoint( data.line, geom, index ) FROM data CROSS JOIN ST_LineLocateSegment( data.line, 'POINT(15 15.1)'::geometry ); }}}