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