1

I have two linestrings that I am trying to merge and determine their total length:

intersection of linestrings A and B

This is a contrived example: the green linestring B is fully-contained within the red linestring A (but this will not always be the case), and the length of the merged geometry should be the same as the length of linestring A.

In PostGIS, if I calculate the length of the red linestring A with

select st_length(st_geomfromtext('(A geom)'), false)

I get a result of 0.6102 (arbitrary units).

If I then calculate the length of the union of the red linestring A and green linestring B, via:

select st_length(

st_union(

st_geomfromtext('(A geom)'),

st_geomfromtext('(B geom)')

), false)

I then get a result of 0.6328, even though the green linestring B completely overlaps with the red linestring A.

I've tried many variations of merging these two linestrings involving st_unaryunion, st_linemerge, st_collect, st_dump, st_removerepeatedpoints and no luck.

How can I reconcile this? There are situations where the linestrings to be merged will not overlap 100% and I'll need to measure the length of the total union geometry, but I have trouble trusting that number when it seems to fail in this scenario.

For what it's worth, linestring A is:

MULTILINESTRING((-84.3381536299124 33.7648637741265,-84.337082 33.76486,-84.3370269 33.7648551,-84.3367653 33.7648604,-84.336237 33.764861,-84.3358725 33.7648538,-84.335818 33.764857,-84.335192 33.764848,-84.3350945 33.7648474,-84.335009 33.764844,-84.334164 33.764846,-84.333928 33.764846,-84.3337994 33.7648477,-84.332997 33.764858,-84.332176 33.76486,-84.331834 33.764852,-84.330605 33.764845,-84.330119 33.764848,-84.329495 33.76486,-84.328663 33.764858,-84.327896 33.764867,-84.3275311259472 33.764859190368))

linestring B is:

MULTILINESTRING((-84.3372103956974 33.7648604521912,-84.337082 33.76486,-84.3370269 33.7648551,-84.3367653 33.7648604,-84.3365005784092 33.7648607006492))

EDIT: an added wrinkle: The length of linestring B is 0.04079. If I measure the intersection between both linestrings:

select st_length(st_intersection(st_geomfromtext('(geom A')),st_geomfromtext('(geom B)'), false)

I get a result of 0.01821, which implies that there is (0.04079 - 0.01821) = 0.02258 of its length that doesn't overlap, which is the difference measured between st_length(A) and st_length(union(A,B)).

EDIT 2: this is getting weirder! I ran ST_SymDifference to find the area where these lines didn't intersect/overlap. It looks like this: Green dots are linestring B, black line is where B doesn't intersect with A

So the point where the line doesn't cross the green dots is where both linestrings overlap, and that coincides to all of the points in linestring B that have identical matches in linestring A, which is every point except the first and last points of B.

So if I take the first point of linestring B (we'll call it P): POINT(-84.3372103956974 33.7648604521912) and see if it intersects with linestring A:

select st_intersection(st_geomfromtext('(geom A)'), st_geomfromtext('POINT(-84.3372103956974 33.7648604521912)'))

I get "GEOMETRYCOLLECTION EMPTY" as a result.

If I then try to find the point on A that is closest to P, it is identical to P:

select st_astext(st_closestpoint(st_geomfromtext('(geom A)'), st_geomfromtext('POINT(-84.3372103956974 33.7648604521912)')))

The result is "POINT(-84.3372103956974 33.7648604521912)"!

On top of that, this also fails:

select st_intersection(st_geomfromtext('(geom A)'), st_closestpoint(st_geomfromtext('(geom A)'), st_geomfromtext('POINT(-84.3372103956974 33.7648604521912)')))

The result is "GEOMETRYCOLLECTION EMPTY", which seems quite broken since I'm requesting the intersection of a linestring with a point explicitly generated as being on that linestring. This query fails with the last point of B as well, but works with points 2, 3, and 4. The order of arguments (geom first, point second or vice versa) does not affect anything.

1

The result of the query is as WKT

MULTILINESTRING (( -84.3381536299124 33.7648637741265, -84.337082 33.76486 ), ( -84.337082 33.76486, -84.3370269 33.7648551 ), ( -84.3370269 33.7648551, -84.3367653 33.7648604 ), ( -84.3367653 33.7648604, -84.336237 33.764861, -84.3358725 33.7648538, -84.335818 33.764857, -84.335192 33.764848, -84.3350945 33.7648474, -84.335009 33.764844, -84.334164 33.764846, -84.333928 33.764846, -84.3337994 33.7648477, -84.332997 33.764858, -84.332176 33.76486, -84.331834 33.764852, -84.330605 33.764845, -84.330119 33.764848, -84.329495 33.76486, -84.328663 33.764858, -84.327896 33.764867, -84.3275311259472 33.764859190368 ), ( -84.3372103956974 33.7648604521912, -84.337082 33.76486 ), ( -84.3367653 33.7648604, -84.3365005784092 33.7648607006492 ))

If you study that geometry with for example OpenJUMP you will see that the parts of the MultiLineString have an overlapping section at coordinates POINT (-84.3365005784092 33.7648607006492). Because of the duplicate part the length is longer. I believe that your linestring B is not totally fully contained by A if you look at every vertex.

  • I don't understand how that could be the case -- both linestring A and B were generated by ST_LineSubstring(), so they should both lie exactly on the same line, right? Separately, is there a way to merge two linestrings such that duplicate parts aren't counted twice? – durkie Oct 18 '17 at 20:56
  • Addind ST_SnapToGrid usually helps. – user30184 Oct 19 '17 at 4:55
  • Lifesaver! For some reason having two points identical to 13 decimal places isn't enough to get them to overlap, but snapping to a grid of 0.00001 works. Might be mixing units there, but still: thanks a ton! – durkie Oct 19 '17 at 14:39

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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