|Version 5 (modified by 2 years ago) ( diff ),|
Functions for Linear Referencing
These functions augment the built-in ones in PostGIS.
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 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
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 CROSS JOIN ST_LineLocateSegment( data.line, 'POINT(15 15.1)'::geometry ) ) SELECT id, ST_AsText( ST_AddPoint( line, pt, index ) ) FROM loc;