4

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

enter image description here

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.

enter image description here

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

New contributor
winecity is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • 1
    Welcome to GIS SE! Can you include the definition of the tables? – Gabriel De Luca Jan 29 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. – jbalk Jan 29 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? – winecity Jan 31 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... – Cyril M yesterday
2

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 – winecity Jan 30 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. – dr_jts Jan 30 at 16:50
  • 1
    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. – dr_jts Jan 30 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... – dr_jts Jan 30 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. – dr_jts Jan 30 at 17:05
1

If I got the translation right,

try to go that way:

WITH 
tbla AS (SELECT DISTINCT ST_Buffer(geom, 0.0001, 'endcap=flat join=round') geom FROM <line_red> GROUP BY geom),
tblb AS (SELECT DISTINCT ST_Intersection(a.geom, b.geom) geom FROM <line_green> a JOIN tbla b ON ST_Intersects(a.geom, b.geom) GROUP BY a.geom, b.geom)
SELECT * FROM tblb WHERE ST_Length(geom::geography) > 3

It's gotta work,

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

  • 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 – winecity 16 hours ago
  • send, maybe I can explain the reason for this behavior... – Cyril M 16 hours ago
  • tell me which EPSG your data is in? I tested the script in EPSG: 4326... – Cyril M 16 hours ago

Your Answer

winecity is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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