I have two linestrings that I am trying to merge and determine their total length:
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:
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.
