[PostGIS] #6023: Silent toplogy error in the result (face has multiple shells,mixed face labeling in ring)

[PostGIS]

#6023: Silent toplogy error in the result (face has multiple shells,mixed face
labeling in ring)
--------------------------------+-------------------
  Reporter:  Lars Aksel Opsahl  |      Owner:  strk
      Type:  defect             |     Status:  new
  Priority:  medium             |  Milestone:
 Component:  topology           |    Version:  3.6.x
Resolution:                     |   Keywords:
--------------------------------+-------------------
Description changed by Lars Aksel Opsahl:

Old description:

> From a set of topology jobs we got a simple feature result table with
> 115,930,754 rows. But in this result table we did find both gaps and
> overlaps in 6677 quite small areas.
>
> We always run ValidateTopology so we did know that we had invalid
> topology i this areas also.
>
> So I created a simple test [https://gitlab.com/nibioopensource/resolve-
> overlap-and-gap/-/blob/118-topology-error-cause-overlap-in-result-mixed-
> face-labeling-in-ring-54-face-has-multiple-
> shells/src/test/sql/regress/rog_overlay_test_25.sql?ref_type=heads] for
> small problem area
>
> {{{
> face has multiple shells    |   3 |    -25
> mixed face labeling in ring |   1 | [NULL]
> }}}
>
> So from this test I created single sql file attached no_tolerance.sql to
> reproduce the error.
>
> If create the Topology with a tolerance almost all errors seems to go
> away, see file with_tolerance.sql.
>
> This is tested in
>

> {{{
> PostgreSQL 16.10 (Homebrew) on aarch64-apple-darwin23.6.0, compiled by
> Apple clang version 16.0.0 (clang-1600.0.26.6), 64-bit
>
> POSTGIS="3.7.0dev 3.6.0rc2-152-g19e25a659" [EXTENSION] PGSQL="160"
> GEOS="3.14.0-CAPI-1.20.4" PROJ="9.7.0 NETWORK_ENABLED=OFF
> URL_ENDPOINT=https://cdn.proj.org
> USER_WRITABLE_DIRECTORY=/Users/lop/Library/Application Support/proj
> DATABASE_PATH=/opt/homebrew/Cellar/proj/9.7.0/share/proj/proj.db"
> (compiled against PROJ 9.7.0) LIBXML="2.13.0" LIBJSON="0.18" TOPOLOGY
>
> }}}
>
> and
>

> {{{
> PostgreSQL 16.4 (Ubuntu 16.4-1.pgdg20.04+1) on x86_64-pc-linux-gnu,
> compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.2) 9.4.0, 64-bit
>
> POSTGIS="3.7.0dev 3.6.0rc2-152-g19e25a659" [EXTENSION] PGSQL="160"
> GEOS="3.14.0-CAPI-1.20.4" (compiled against GEOS 3.13.1) PROJ="8.2.1
> NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org
> USER_WRITABLE_DIRECTORY=/var/lib/postgresql/.local/share/proj
> DATABASE_PATH=/usr/share/proj/proj.db" (compiled against PROJ 8.2.1)
> LIBXML="2.9.13" LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0
> (Internal)" TOPOLOGY
>
> }}}

New description:

 From a set of topology jobs we got a simple feature result table with
 115,930,754 rows. But in this result table we did find both gaps and
 overlaps in 6677 quite small areas.

 We always run ValidateTopology so we did know that we had invalid topology
 i this areas also.

 So I created a simple test [https://gitlab.com/nibioopensource/resolve-
 overlap-and-
 gap/-/blob/1c57b1cfb26c498a6032bf4ecefb73334f7f26c3/src/test/sql/regress/rog_overlay_test_25.sql]
 for small problem area

 {{{
 face has multiple shells    |   3 |    -25
 mixed face labeling in ring |   1 | [NULL]
 }}}

 So from this test I created single sql file attached no_tolerance.sql to
 reproduce the error.

 If create the Topology with a tolerance almost all errors seems to go
 away, see file with_tolerance.sql.

 This is tested in


 {{{
 PostgreSQL 16.10 (Homebrew) on aarch64-apple-darwin23.6.0, compiled by
 Apple clang version 16.0.0 (clang-1600.0.26.6), 64-bit

 POSTGIS="3.7.0dev 3.6.0rc2-152-g19e25a659" [EXTENSION] PGSQL="160"
 GEOS="3.14.0-CAPI-1.20.4" PROJ="9.7.0 NETWORK_ENABLED=OFF
 URL_ENDPOINT=https://cdn.proj.org
 USER_WRITABLE_DIRECTORY=/Users/lop/Library/Application Support/proj
 DATABASE_PATH=/opt/homebrew/Cellar/proj/9.7.0/share/proj/proj.db"
 (compiled against PROJ 9.7.0) LIBXML="2.13.0" LIBJSON="0.18" TOPOLOGY

 }}}

 and


 {{{
 PostgreSQL 16.4 (Ubuntu 16.4-1.pgdg20.04+1) on x86_64-pc-linux-gnu,
 compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.2) 9.4.0, 64-bit

 POSTGIS="3.7.0dev 3.6.0rc2-152-g19e25a659" [EXTENSION] PGSQL="160"
 GEOS="3.14.0-CAPI-1.20.4" (compiled against GEOS 3.13.1) PROJ="8.2.1
 NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org
 USER_WRITABLE_DIRECTORY=/var/lib/postgresql/.local/share/proj
 DATABASE_PATH=/usr/share/proj/proj.db" (compiled against PROJ 8.2.1)
 LIBXML="2.9.13" LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0
 (Internal)" TOPOLOGY

 }}}

--
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/6023#comment:5>
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.