# Aligning line with closest line segment in PostGIS

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?

• Welcome to GIS SE! Can you include the definition of the tables? Commented Jan 29, 2020 at 22:19
• you can use the linear referencing functions with the start/end points. The trick is finding the closest road, since the side streets are the same distance as the road you're trying to match. I would add a column to both tables to hold the line bearing. You can calculate that using the start/end points and the azimuth function. They in your query to find the closest road, you'd also select the road with azimuth closest to the azimuth of your line. Commented Jan 29, 2020 at 22:51
• Thank you for your answer. I have added a column to both tables to hold the line bearing like you said. Now I have calculated the azimuth of each line segment, Im unsure how to exactly select the road with azimuth closest to the azimuth of your line. The query should specify a percentage difference (let's say 5 or 10%) and probably search within a certain distance. Any advice on how to do this in PostGIS or QGIS? Commented Jan 31, 2020 at 14:45
• try to find simple and correct steps, 1) create square buffer zones on the red lines, 2) cut the green lines with them, 3) get rid of the artifacts and you will get your blue lines... Commented Feb 2, 2020 at 14:29

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.

• Thank you for your elaborate answer. Since the distance of the side streets is in some cases smaller than the distance of the road segment I’m trying to match, I get stuck on how to select the road segments that are within a distance tolerance d... how would you suggest to select all the road segments that are closest parallel to the red lines. I have updated my post with a new diagram example Commented Jan 30, 2020 at 11:24
• Yes, that's a tricky part. I think one approach is to clip the path line to the segment (using `ST_LineSubstringLine`), and then clip the result to the path line. For perpendicular segments this should cause them to become zero-length. Commented Jan 30, 2020 at 16:50
• I'm starting to think `ST_ClipLine` might be a better name for `ST_LineSubstringLine` - given that's how I always refer to it. So don't be surprised if that changes in the repo. Commented Jan 30, 2020 at 16:52
• The other possibility for discarding non-parallel segments is to match and clip all segments as per above. This will then form a small subnetwork of segments. Then find the shortest path through that network, which should discard the non-parallel segments. BUT - I don't have PostGIS code to do that, and it's complicated... Commented Jan 30, 2020 at 16:55
• When you say "the distance of the side streets is closer...", note that using `ST_Hausdorff` distance between the segment and the clipped path will allow filtering out segments which have some portion which is far from the (clipped) path. Commented Jan 30, 2020 at 17:05

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...

• Thanks for sharing your code. Unfortunately I get an error `SQL Error [22023]: ERROR: Only lon/lat coordinate systems are supported in geography.`. Besides, the actual road width is not of relevance for this case. I only need to create a new (in the example blue) line segment that is aligned with the nearest, green road segment and parallel to the red line. If you'd be interested, I could send you the data sample I'm using Commented Feb 3, 2020 at 16:22
• My data is in EPSG 28992. You can find a sample of the data here: (drive.google.com/open?id=1fF0Hjl3cJOVCX0X6wEH9uyQyybdsGNMz) Commented Feb 4, 2020 at 11:16
• That would be great, thanks a lot Commented Feb 4, 2020 at 11:28
• I see. If I do that I still get an `SQL Error [42601]: ERROR: syntax error at end of input`... Commented Feb 4, 2020 at 13:02