2

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;          
2
  • I would suggest changing max to min in SELECT DISTINCT max(ST_DISTANCE(points1.geom, points2.geom)). You could also use ST_ClosestPoint and you might find that using ST_DWithin might speed things up (as it uses a spatial index), assuming you know a minimum search distance to use. Apr 20, 2016 at 11:08
  • Can you post sample data and/or column descriptions of your tables? I'm going to post a sketch answer but I'm not 100% sure what you're trying to achieve between your explanation and the code you posted.
    – raphael
    May 1, 2016 at 19:19

1 Answer 1

2

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
2
  • That is not what I want. That would tell me the distance between the end of one line to the opposite end of another line if they were near parallel. May 2, 2016 at 17:42
  • My bad, I updated the question in response to your feedback
    – raphael
    May 2, 2016 at 18:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.