ST_LineLocatePoint — Liefert die gebrochene Position des Punktes auf einer Linie, der einem Punkt am nächsten liegt.
float8 ST_LineLocatePoint(geometry a_linestring, geometry a_point);
float8 ST_LineLocatePoint(geography a_linestring, geography a_point, boolean use_spheroid = true);
Gibt eine Gleitpunktzahl zwischen 0 und 1 zurück, welche die Lage des Punktes auf einer Linie angibt, der zu einem gegebenen Punkt am nächsten liegt. Die Lage wird als Anteil an der Gesamtlänge der 2D Linie angegeben.
Sie können die zurückgegebene Lage nutzen, um einen Punkt (ST_LineInterpolatePoint) oder eine Teilzeichenfolge (ST_LineSubstring) zu extrahieren.
Nützlich, um die Hausnummern von Adressen anzunähern
Verfügbarkeit: 1.1.0
Änderung: 2.1.0. Bis zu 2.0.x wurde diese Funktion mit ST_Line_Locate_Point bezeichnet.
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 house_loc As as_text_house_loc,
start_house_num +
CAST( (end_house_num - start_house_num)
* ST_LineLocatePoint(street_line, house_loc) As integer) As house_num
FROM
(SELECT 'LINESTRING(1 2,3 4)'::geometry As street_line,
ST_Point(x*1.01, y*1.03) As house_loc, 10 As start_house_num,
20 As end_house_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 | house_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_LineInterpolatePoint(
foo.the_line,
ST_LineLocatePoint(foo.the_line, 'POINT(4 3)'::geometry))
FROM (SELECT 'LINESTRING(1 2,4 5,6 7)'::geometry 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_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)
ST_ClosestPoint, ST_DWithin, ST_Length2D, ST_LineInterpolatePoint, ST_LineSubstring