Sunday, 13 October 2019

postgis - 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;


No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...