# For Loop Maximum Minimum Distance Between Two Lines

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;
``````
• 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. May 1, 2016 at 19:19

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
``````
• 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 May 2, 2016 at 18:50