I have a table of line/lane ids in a table called matched_lanes
, and I am trying to create a lateral query that on each id in the table to find the maximumum minimum distance between each point
.
I.e. for every lane:
Loop through each point on the lane, and find the minimum distance
to its first_left_boundary
(this is just another lane stored in the matched_lanes
table.
Return the MAXIMUM of these minimum distance and append this to our table as max_left_lane
Essentially if you consider these lanes as roads, at any point along the road you could calculate the smallest distance to your (the roads) left neighbour (another road). For each point along the lane this smallest distance can be calculated. I want to find the max of these min distances value for each lane.
I have the below, but I'm not sure it is actually calculating the minimum, but rather the max
Refernces:
How to calculate the average distance among set of points as measure of closeness
Get the lines between all points of a polygon in PostGis : avoid nested loop?
SELECT
lane_id,
first_left_boundary
sub.max_lane as max_left_lane
FROM
matched_lanes,
LATERAL(
WITH
points1 AS(
SELECT
(ST_DumpPoints(lane_info.geometry)).*
FROM
lane lane_info
WHERE
lane_info.id = lane_id
),
points2 AS(
SELECT
(ST_DumpPoints(first_left_lane.geometry)).*
FROM
lane first_left_lane
WHERE
first_left_lane.id = first_left_boundary
)
SELECT DISTINCT
max(ST_DISTANCE(points1.geom, points2.geom))
FROM
points1,
points2
)
AS sub(max_lane)
ORDER BY
lane_id;
No comments:
Post a Comment