Aligning line with closest line segment in PostGIS

Question

I've got a road network dataset and a dataset that consist of multiple disconnected lines of varying lengths in PostGIS.

Most of these lines are (largely) parallel to the road network, but the distance between these lines and the road network can differ. Since these lines tell something about the condition of the road segments that are parallel to these lines, I would like to create a new line segment that is aligned with the closest road segment. The blue lines in the example below is what I eventually would like to achieve.

Does anyone have an idea how to solve this in PostGIS?

Answer 1

This is a classic road network conflation problem. It has the following challenges:

• determining a set of matched road segments to a path line in spite of them being very different lengths
• clipping the matched road segments to the path line (this is best done after merging the matched road segments

(The "path lines" are the red lines in the diagram)

For the matching a sketch of an approach is:

• For each path line, select the road segments which are within a distance tolerance d
• For each matched road segment, "clip" the path line to it (clipping explained below)
• Discard any clipped segments which have length 0 (these are segments which are roughly perpendicular to the path, e.g. cross streets)
• Compute the Hausdorff distance between the road segment and the clipped path, and keep only segments with a distance below the tolerance d (this discards road segments where only one end is near the path)
• Clip each road segment to the path line (to discard pieces of the road segment which extend beyond the path)
• Merge the clipped road segments together

It's easiest and clearer to create Postgres functions for some of the processing above. There is some code which does this at https://github.com/dr-jts/pg-util/blob/master/geom/pathmatch.sql

"Clipping" a segment to a clip line involves extracting the substring of the segment between the two points which lie closest to the start and end points of the clip line. This is done by the function ST_LineSubstringLine in the code linked above.

Answer 2

So, let's summarize for one piece of geodata.

1) Input data are axial lines of roads of one Amsterdam tablet;

2) The length of red lines is not uniform, it is present either on one side or on two sides of quarters and in some places has the bent form;

3) Distances between the red lines and the green lines are different;

4) When trimming lines at intersections, it is possible to lose short green lines (not perpendicular) during filtering.

Conclusion:

1) The script works correctly only for special cases, if it is set correctly;

2) You can remove perpendicular artifacts automatically, only using green lines or a smaller buffer border;

3) It is reasonable to simply trim the green lines by setting the correct trim buffer.

I would specify whether bent parts of red lines can be removed by shortening it to straight parts, to connect the lines at the breakup points, as small breakups in the lines are not clear, etc.,

In general, you're in the field of geotechnical processing...

try to go that way:

create table blue_lines AS                                                                                                            
WITH 
tbla AS (SELECT DISTINCT ST_Buffer(geom, 20, 'endcap=flat join=round') geom FROM red_lines)
(SELECT DISTINCT ST_Intersection(a.geom, b.geom) geom FROM green_lines a JOIN tbla b ON ST_Intersects(a.geom, b.geom))

or

create table blue_lines AS
WITH 
tbla AS (SELECT DISTINCT ST_Buffer(geom, 15, 'side=right') geom FROM red_lines),
tblb AS (SELECT DISTINCT ST_Intersection(a.geom, b.geom) geom FROM green_lines a JOIN tbla b ON ST_Intersects(a.geom, b.geom)),
tblc AS (SELECT DISTINCT ST_Buffer(geom, 15, 'side=left join=mitre') geom FROM red_lines),
tbld AS (SELECT DISTINCT ST_Intersection(a.geom, b.geom) geom FROM green_lines a JOIN tbla b ON ST_Intersects(a.geom, b.geom))
SELECT * FROM tblb UNION SELECT * FROM tbld

make sure you set this script correctly for the actual road width...

You have a complicated case, and a universal script will be a bit difficult to write, it's easier to solve the problem selectively, based on the specific situation, selecting objects by their id and then combine the results of processing (but it's a long process), you can connect filtering by length, for example, ST_Length(geom)>10, but then it's possible to lose short fragments at intersections, somehow so...