[postgis-tickets] [PostGIS] #5401: ST_Difference silently giving us wrong results

[PostGIS]

#5401: ST_Difference silently giving us wrong results
--------------------------------+---------------------------
  Reporter:  Lars Aksel Opsahl  |      Owner:  pramsey
      Type:  defect             |     Status:  new
  Priority:  medium             |  Milestone:  PostGIS 3.3.4
 Component:  postgis            |    Version:  3.3.x
Resolution:                     |   Keywords:
--------------------------------+---------------------------
Comment (by Lars Aksel Opsahl):

 On this CI job failed [https://gitlab.com/nibioopensource/resolve-overlap-
 and-gap/-/pipelines/893035719]

 formula to test.
 {{{
 difference_test_01 sum|9|12374747|72
 }}}
 Versions
 {{{
 POSTGIS="3.3.3 2355e8e" [EXTENSION] PGSQL="120" GEOS="3.10.2-CAPI-1.16.0"
 PROJ="8.2.1" LIBXML="2.9.13" LIBJSON="0.15" LIBPROTOBUF="1.3.3"
 WAGYU="0.5.0 (Internal)" TOPOLOGY
 }}}

 But on my local pipeline we see this

 formula to test.
 {{{
 difference_test_01 sum|8|11848089|67
 }}}
 Versions
 {{{
 POSTGIS="3.2.3 2f97b6c" [EXTENSION] PGSQL="140" GEOS="3.11.0-CAPI-1.17.0"
 PROJ="9.0.1" LIBXML="2.9.14" LIBJSON="0.16" LIBPROTOBUF="1.4.1"
 WAGYU="0.5.0 (Internal)" TOPOLOGY
 }}}

 Using Sandro code

 {{{
 CREATE TABLE difference_test_01 AS
 SELECT
   ST_Difference(
     a.geo,
     ( SELECT ST_Union(u.geo) FROM used_area u )
   )
 AS geom
 FROM all_area a
 }}}

 formula to test.

 {{{
 difference_test_01 sum |  1719 | 15799744 | 442752
 }}}
 Versions
 {{{
 POSTGIS="3.2.3 2f97b6c" [EXTENSION] PGSQL="140" GEOS="3.11.0-CAPI-1.17.0"
 PROJ="9.0.1" LIBXML="2.9.14" LIBJSON="0.16" LIBPROTOBUF="1.4.1"
 WAGYU="0.5.0 (Internal)" TOPOLOGY
 }}}

 Another thing with the Sandro code the time used on my local mac

 {{{
 Time: 1173.537 ms (00:01.174)
 }}}

 But with original code it was much slower (and giving the wrong result the
 systems I tested on)

 {{{
 Time: 48452.738 ms (00:48.453)
 }}}

 At your test you get 3874 in the count column so something has changed
 yes, because with  the Sandro code count number is 1719 but area is almost
 the same.

 Tests that behave this differently on system that are almost the same is
 not a good thing, since seems difficult to exactly pin point exact code on
 where this problem originates from (works on older geos
 GEOS="3.9.1-CAPI-1.14.2").

 With exceptions and maybe a hint on how to try rewrite the SQL in some way
 and where in your data the problems is, gives us a nice option to fix
 "bad" data like we have here.

 The result from using Postgis Topology is below and thats almost the same
 as from the Sandro way code.

 formula to test.
 {{{
 topo_difference_test_01 sum|1734|15799747|440021
 }}}
 Versions
 {{{
 POSTGIS="3.2.3 2f97b6c" [EXTENSION] PGSQL="140" GEOS="3.11.0-CAPI-1.17.0"
 PROJ="9.0.1" LIBXML="2.9.14" LIBJSON="0.16" LIBPROTOBUF="1.4.1"
 WAGYU="0.5.0 (Internal)" TOPOLOGY
 }}}
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5401#comment:13>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.