I am trying to process a road network file in which certain road links have near-identical duplicates/neighbours.

By "near identical" i mean 2 Linestrings may share the same centroid but have start/end nodes which differ by a few millimeters. In other cases, a certain Linestring may have a near-parallel neighbour which does not share a common centroid; again, this "duplicated" line is typically within a few millimeters of its near-parallel neighbour.

My goal is to remove these near-identical Linestrings to facilitate accurate length calculations.

I have browsed other threads exploring problems similar to this but have yet to find the PostGIS solution that works on this particular task. - St_SnapToGrid maintains the relationships of the near-identical pairings. - ST_DWithin test can result in deleting consecutive links rather than coinciding ones. - ST_Union maintains the original geometry - ST_Linemerge fails to merge lines if they are too far apart.
This image shows the centroids of each link as well as the selection of two near-identical links, one of which also overlaps another link at its higher end. Centroids of each link and selection of two near-identical links

Zooming to a scale of 1:1 and measuring shows the two links are 8.5mm apart. near-parallel links

PostGreSQL version: 9.6.5 PostGIS version 2.4.0 GEOS 3.6.2 GDAL 2.2.2 LibJson 0.12

  • "even though they coincide in space to a VERY high degree" the problem here is that you have quantified a very high degree. Also a lot of words. Can we have a simplified example instead. – Evan Carroll Nov 26 '17 at 3:58
  • HI @EvanCarroll, i heavily edited the question to better explain what the problem is, hopefully this version is better. When you say i have "quantified to a very high degree" do you mean the precision of my coordinates? i have noticed that my UTM coordinates (should be in meters) have 9 decimal places and have suspected that this level of precision is contributing to my issues, but i have yet to cross anything online or any tool from the postGIS library meant to deal with that. Thanks for commenting! – Marshall Dec 20 '17 at 4:37
  • actally, st_snaptogrid does not preserve anything. it's possible though (somewhat unlikely at that scale but possible) that your line pairs are snapped to different grid cells. for distance < 1cm try gridsize > 2cm maybe and perform a linemerge on the result. as I said, robustness of that approach is limited, I always ended up tediously defining rules to select those linepairs or use plugins like pg-trajectory...there is no tool in PostGIS as of yet (AFAIK) – ThingumaBob Dec 20 '17 at 10:42
  • Thanks @ThingumaBob, i did some more experimenting with the SnapToGrid function and it seems my first conclusion is correct: Snaptogrid maintains the distances between neighbouring lines, and just moves both of them to a newly defined grid. When i look at the original lines and the snapped ones, the same "quasi-identical" lines are preserved, just moved over. (am i missing something about this tool?) For STG to help me out it would have to take one of the lines and move it directly on top of its neighbour. I fear you are correct when you say there's no easy way of doing this in PostGIS ATM. – Marshall Jan 8 '18 at 22:54

Your Answer

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

Browse other questions tagged or ask your own question.