ST_Union creates wrong result

[Lars Aksel Opsahl]

I have a table with a set off linestrings (found in test_input_snap.zip) (image found in test_input_snap.png)

If i run the SQL below I get a very strange results , because the lines seems to snap to a grid off some kind, then the lines are totally changed.

(image of result found in test_output_union_fail.png)

create table test_output_union_fail as (
select (ST_Dump(ST_Union(i.geom))).geom from 
( select geom from test_input_snap ) as i
where st_isValid(geom) = true 
);

If I pick out some of the failing and just run ST_Union on this lines the result is ok. (image of result found in test_output_union_ok.png)

create table test_output_union_ok as (
select (ST_Dump(ST_Union(i.geom))).geom from 
( select geom from test_input_snap where id in (760,310,760,1511,792) ) as i
where st_isValid(geom) = true 
);

I am testing on

POSTGIS="3.1.0dev 3.1.0alpha1-144-g50796cc" [EXTENSION] PGSQL="120" GEOS="3.8.1-CAPI-1.13.3" PROJ="7.0.1" LIBXML="2.9.1" LIBJSON="0.11" TOPOLOGY (1 row)

[Lars Aksel Opsahl]

Dump of the input table

[Lars Aksel Opsahl]

Image of input

[Lars Aksel Opsahl]

Image of ok

[Lars Aksel Opsahl]

Image of failed result

[Lars Aksel Opsahl]

I have testing some more and I see this behaviour

If test all lines with id less than 500 it seem to be OK.

select (ST_Dump(ST_Union(i.geom))).geom from 
( select geom from test_input_snap where id < 500 ) as i

It also seem ok with all between 200 and 600.

select (ST_Dump(ST_Union(i.geom))).geom from 
( select geom from test_input_snap where id > 200 and id < 600 ) as i

But if I try to out lines between 100 and 600 it starts to snap.

select (ST_Dump(ST_Union(i.geom))).geom from 
( select geom from test_input_snap where id > 100 and id < 600 ) as i

I now tested on POSTGIS="3.0.0 r17983" [EXTENSION] PGSQL="120" GEOS="3.8.0-CAPI-1.13.1 " PROJ="6.3.0" LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.2" WAGYU="0.4.3 (Internal)" TOPOLOGY

[Lars Aksel Opsahl]

If you look at the last query and change that to use ST_AsText, the lines seems not to collapse. And in addition to get a more correct result it's also much faster with ST_AsText.

[local] lop@sl=# select count(*) as ST_AsBinary from (
select (ST_Dump(ST_Union(i.geom))).geom from 
( select ST_AsBinary(geom) as geom from test_input_snap where id > 100 and id < 600 ) as i
) as r;
 st_asbinary 
-------------
         343
(1 row)

Time: 2808.473 ms (00:02.808)
[local] lop@sl=# select count(*) as ST_AsText from (
select (ST_Dump(ST_Union(i.geom))).geom from 
( select ST_AsText(geom) as geom from test_input_snap where id > 100 and id < 600 ) as i
) as r;
 st_astext 
-----------
     12091
(1 row)

Time: 336.900 ms

[strk]

This is most likely entering the precision reduction heuristic in GEOS and reducing precision too much. I guess using the new (GEOS-3.9) fixed-precision overlay would fix this case, it's to be tested.

[mdavis]

This works with JTS OverlayNG. Hopefully will with GEOS as well. Have added it as a test case.