For Loop Maximum Minimum Distance Between Two Lines

Question

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;          

Answer 1

I think you're overcomplicating things here when you could use ST_MaxDistance, this will return the maximum distance between two points within two point clouds.

WITH points AS(
    SELECT
        id,
        (ST_DumpPoints(lane.geometry)).geom as geom  
    FROM lane
    GROUP BY id
)
, min_distances AS(
/*Finds the smallest distance for each distinct tuple of lane_id, point geom
and first_left_boundary*/
    SELECT DISTINCT ON (lane_info.id, lane_info.geom, first_left_boundary)
        lane_info.id as lane_id,
        lane_info.geom,
        first_left_boundary,
        ST_Distance( lane_info.geom, first_left_lane.geom) AS min_point_dist
    FROM points lane_info
    INNER JOIN matched_lanes ON lane_id = lane_info.id
    INNER JOIN points first_left_lane ON first_left_boundary = first_left_lane.id
    ORDER BY lane_info.id,
             lane_info.geom,
             first_left_boundary,
             ST_Distance( lane_info.geom, first_left_lane.geom)
)
SELECT 
    lane_id,
    first_left_boundary,
    MAX(min_point_dist) AS max_left_lane
FROM min_distances
GROUP BY lane_id, first_left_boundary