ST_LineLocatePoint — Returns the fractional location of the closest point on a line to a point.
float8 ST_LineLocatePoint(geometry a_linestring, geometry a_point);
float8 ST_LineLocatePoint(geography a_linestring, geography a_point, boolean use_spheroid = true);
입력 포인트에 가장 가까운 위치에 있는 라인스트링 상의 포인트를 나타내는 2차원 라인 전체 길이의 비율을 0에서 1 사이의 부동소수점 데이터형(float)으로 반환합니다.
반환된 위치를 이용해서 포인트(ST_LineInterpolatePoint) 또는 부분 스트링(ST_LineSubstring)을 추출할 수 있습니다.
이 함수는 주소의 개수의 근사치를 구하는 데 유용합니다.
1.1.0 버전부터 사용할 수 있습니다.
변경 사항: 2.1.0 미만 버전, 즉 2.0.x 버전까지 이 함수의 명칭은 ST_Line_Locate_Point였습니다.
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,
startstreet_num +
CAST( (endstreet_num - startstreet_num)
* ST_LineLocatePoint(street_line, house_loc) As integer) As street_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 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_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)