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
Return the MAXIMUM of these minimum distance and append this to our table as
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
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;