Version 5 (modified by mdavis, 3 years ago) ( diff )

Functions for Linear Referencing

These functions augment the 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 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;
Note: See TracWiki for help on using the wiki.