[PostGIS] #5788: ERROR: XX000: GEOSIntersects: TopologyException: side location conflict at

[PostGIS]

#5788: ERROR:  XX000: GEOSIntersects: TopologyException: side location conflict at
-------------------------------+---------------------------
 Reporter:  Lars Aksel Opsahl  |      Owner:  pramsey
     Type:  defect             |     Status:  new
 Priority:  medium             |  Milestone:  PostGIS 3.4.4
Component:  postgis            |    Version:  3.4.x
 Keywords:                     |
-------------------------------+---------------------------
 On


 {{{
 POSTGIS="3.4.2 c19ce56" [EXTENSION] PGSQL="160" GEOS="3.12.1-CAPI-1.18.1"
 (compiled against GEOS 3.10.2) SFCGAL="SFCGAL 1.4.1, CGAL 5.3.1, BOOST
 1.74.0" PROJ="8.2.1 NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org
 USER_WRITABLE_DIRECTORY=/tmp/proj DATABASE_PATH=/usr/share/proj/proj.db"
 GDAL="GDAL 3.4.1, released 2021/12/27" LIBXML="2.9.13" LIBJSON="0.15"
 LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" TOPOLOGY RASTER
 }}}

 When uploading files and run this below sql


 {{{
 SELECT ST_area(ST_Union(ST_Intersection(mt.geo,f.geo)),true)
           FROM
           free_area_geo_before_must_overlap f,
           must_overlap_area_used_list_soknad_and_tiltak mt
           WHERE mt.geo && f.geo AND ST_Intersects(mt.geo,f.geo)
           AND ST_IsValid(mt.geo) AND ST_IsValid(f.geo);


 }}}

 I get this error

 {{{
 ERROR:  XX000: GEOSIntersects: TopologyException: side location conflict
 at 5.993730927467122 59.273001457529752. This can occur if the input
 geometry is invalid.
 LOCATION:  pg_error, lwgeom_pg.c:332
 }}}


 With a the new SQL below it works where I use st_dump it works

 {{{
 SELECT ST_area(ST_Union(ST_Intersection(mt.geo,f.geo)),true)
           FROM
           (SELECT (ST_Dump(ff.geo)).geom geo FROM
 free_area_geo_before_must_overlap ff) f,
           (SELECT (ST_Dump(mt1.geo)).geom geo FROM
 must_overlap_area_used_list_soknad_and_tiltak mt1) mt
           WHERE mt.geo && f.geo AND ST_Intersects(mt.geo,f.geo)
           AND ST_IsValid(mt.geo) AND ST_IsValid(f.geo);
      st_area
 -----------------
  0.1568622817327
 (1 row)

 }}}

 And the sql below also works but gives a different result as you see


 {{{
 SELECT ST_area(ST_Union(ST_Intersection(mt.geo,f.geo)),true)
           FROM
           free_area_geo_before_must_overlap f,
           (SELECT ST_collect(mt1.geo) geo FROM
 must_overlap_area_used_list_soknad_and_tiltak mt1) mt
           WHERE mt.geo && f.geo AND ST_Intersects(mt.geo,f.geo)
           AND ST_IsValid(mt.geo) AND ST_IsValid(f.geo);
  st_area
 ---------
        0
 (1 row)

 }}}


 On latest postgis all 3 sql's works but they give different result

 {{{
 POSTGIS="3.6.0dev 3.5.0-13-g290fffd11" [EXTENSION] PGSQL="160"
 GEOS="3.13.0beta2-CAPI-1.19.0" PROJ="9.3.0 NETWORK_ENABLED=OFF
 URL_ENDPOINT=https://cdn.proj.org USER_WRITABLE_DIRECTORY=/tmp/proj
 DATABASE_PATH=/usr/local/share/proj/proj.db" (compiled against PROJ
 9.13.0) LIBXML="2.9.13" LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0
 (Internal)" TOPOLOGY
 }}}

 As you see here

 {{{

            SELECT ST_area(ST_Union(ST_Intersection(mt.geo,f.geo)),true)
           FROM
           free_area_geo_before_must_overlap f,
           must_overlap_area_used_list_soknad_and_tiltak mt
           WHERE mt.geo && f.geo AND ST_Intersects(mt.geo,f.geo)
           AND ST_IsValid(mt.geo) AND ST_IsValid(f.geo);

           SELECT ST_area(ST_Union(ST_Intersection(mt.geo,f.geo)),true)
           FROM
           (SELECT (ST_Dump(ff.geo)).geom geo FROM
 free_area_geo_before_must_overlap ff) f,
           (SELECT (ST_Dump(mt1.geo)).geom geo FROM
 must_overlap_area_used_list_soknad_and_tiltak mt1) mt
           WHERE mt.geo && f.geo AND ST_Intersects(mt.geo,f.geo)
           AND ST_IsValid(mt.geo) AND ST_IsValid(f.geo);


           SELECT ST_area(ST_Union(ST_Intersection(mt.geo,f.geo)),true)
           FROM
           free_area_geo_before_must_overlap f,
           (SELECT ST_collect(mt1.geo) geo FROM
 must_overlap_area_used_list_soknad_and_tiltak mt1) mt
           WHERE mt.geo && f.geo AND ST_Intersects(mt.geo,f.geo)
           AND ST_IsValid(mt.geo) AND ST_IsValid(f.geo);
        st_area
 ---------------------
  0.12751095705231963
 (1 row)

       st_area
 --------------------
  0.1569281251764355
 (1 row)

  st_area
 ---------
        0

 }}}
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5788>
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.