Functions for Linear Referencing

These functions augment the ​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 ​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);