Description

Returns a float between 0 and 1 representing the location of the closest point on a LineString to the given Point, as a fraction of 2d line length.

You can use the returned location to extract a Point (ST_LineInterpolatePoint) or a substring (ST_LineSubstring).

This is useful for approximating numbers of addresses

Availability: 1.1.0

Changed: 2.1.0. Up to 2.0.x this was called ST_Line_Locate_Point.

Examples

This example approximates the street number of a house by locating the closest point on a street line. The subquery generates sample house and street data, and ST_DWithin excludes houses that are too far from the street.

SELECT ST_AsText(house_loc) As as_text_house_loc,
	startstreet_num +
		CAST( (endstreet_num - startstreet_num)
			* ST_LineLocatePoint(street_line, house_loc) As integer) As street_num
FROM
(SELECT ST_GeomFromText('LINESTRING(1 2, 3 4)') As street_line,
	ST_Point(x*1.01,y*1.03) As house_loc, 10 As startstreet_num,
		20 As endstreet_num
FROM generate_series(1,3) x CROSS JOIN generate_series(2,4) As y)
As foo
WHERE ST_DWithin(street_line, house_loc, 0.2);

 as_text_house_loc | street_num
-------------------+------------
 POINT(1.01 2.06)  |         10
 POINT(2.02 3.09)  |         15
 POINT(3.03 4.12)  |         20

This example finds the closest point on a line to a point or other geometry.

SELECT ST_AsText(ST_LineInterpolatePoint(
    foo.the_line,
    ST_LineLocatePoint(foo.the_line, ST_GeomFromText('POINT(4 3)'))))
FROM (SELECT ST_GeomFromText('LINESTRING(1 2, 4 5, 6 7)') As the_line) As foo;

POINT(3 4)

This example locates the closest segment and point on a LineString.

WITH data AS (
  SELECT
    'LINESTRING (0 0, 10 10, 20 20, 30 30)'::geometry AS line,
    'POINT(15 15.1)'::geometry AS pt
),
segments AS (
  SELECT
    s.i,
    d.pt,
    ST_MakeLine(ST_PointN(d.line, s.i), ST_PointN(d.line, s.i + 1)) AS segment
  FROM data AS d
  CROSS JOIN LATERAL generate_series(1, ST_NumPoints(d.line) - 1) AS s(i)
)
SELECT
  i AS segment_number,
  round(ST_Distance(segment, pt)::numeric, 3) AS distance,
  ST_AsText(ST_ClosestPoint(segment, pt)) AS closest_point
FROM segments
ORDER BY ST_Distance(segment, pt)
LIMIT 1;

 segment_number | distance |   closest_point
----------------+----------+--------------------
              2 |    0.071 | POINT(15.05 15.05)

See Also

ST_ClosestPoint, ST_DWithin, ST_Length2D, ST_LineInterpolatePoint, ST_LineSubstring