<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=Windows-1252">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
</head>
<body dir="ltr">
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof">
Hi</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof">
Sure <br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof">
On my local mac I have this</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof ContentPasted0">
postgis_full_version | 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<br class="ContentPasted0">
version | PostgreSQL 14.5 on x86_64-apple-darwin21.5.0, compiled by Apple clang version 13.1.6 (clang-1316.0.21.2.5), 64-bit</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof ContentPasted0">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof ContentPasted0">
In test environment where we first did see the problem</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof ContentPasted0">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof ContentPasted0 ContentPasted1">
postgis_full_version | POSTGIS="3.2.0 c3e3cc0" [EXTENSION] PGSQL="120" GEOS="3.10.1-CAPI-1.16.0" SFCGAL="1.3.7" PROJ="8.2.0" GDAL="GDAL 3.4.0, released 2021/11/04" LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" TOPOLOGY RASTERversion
| PostgreSQL 12.12 (Ubuntu 12.12-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof">
<br>
</div>
<div id="appendonsend"></div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof">
In our CI system</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof">
<pre class="code highlight" lang="sql"><span data-testid="content" class="line" lang="sql"><span class="hljs-comment ContentPasted2">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</span><span></span></span>
<span data-testid="content" class="line" lang="sql"><span></span><span class="hljs-comment ContentPasted2">--ON Postgresql 12, 13 and 14</span></span>
</pre>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof ContentPasted3">
There are more details here around the results and what's tested here also in <a href="https://gitlab.com/nibioopensource/resolve-overlap-and-gap/-/blob/develop/src/test/sql/regress/rog_sf_difference.sql" id="LPNoLPOWALinkPreview">
https://gitlab.com/nibioopensource/resolve-overlap-and-gap/-/blob/develop/src/test/sql/regress/rog_sf_difference.sql</a><br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof ContentPasted3">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof ContentPasted3">
Thanks.</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof ContentPasted3">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof ContentPasted3">
Lars<br>
<div class="_Entity _EType_OWALinkPreview _EId_OWALinkPreview _EReadonly_1"></div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
</div>
<hr tabindex="-1" style="display:inline-block; width:98%">
<div id="divRplyFwdMsg" dir="ltr"><font style="font-size: 11pt; color: rgb(0, 0, 0);" face="Calibri, sans-serif"><b>From:</b> postgis-users <postgis-users-bounces@lists.osgeo.org> on behalf of Regina Obe <lr@pcorp.us><br>
<b>Sent:</b> Wednesday, June 14, 2023 6:43 AM<br>
<b>To:</b> 'PostGIS Users Discussion' <postgis-users@lists.osgeo.org><br>
<b>Subject:</b> Re: [postgis-users] Postgis Simple Feature silently giving us wrong results</font>
<div> </div>
</div>
<div lang="EN-US">
<div class="x_WordSection1">
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size: 11pt; font-family: "Calibri", sans-serif; color: rgb(31, 73, 125);">I haven’t looked thru your examples, but it would be good to know which version of GEOS you are running since ST_Difference ultimately depends on GEOS.</span></p>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size: 11pt; font-family: "Calibri", sans-serif; color: rgb(31, 73, 125);"> </span></p>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size: 11pt; font-family: "Calibri", sans-serif; color: rgb(31, 73, 125);">What does:</span></p>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size: 11pt; font-family: "Calibri", sans-serif; color: rgb(31, 73, 125);"> </span></p>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size: 11pt; font-family: "Calibri", sans-serif; color: rgb(31, 73, 125);">SELECT postgis_full_version(), version();</span></p>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size: 11pt; font-family: "Calibri", sans-serif; color: rgb(31, 73, 125);"> </span></p>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size: 11pt; font-family: "Calibri", sans-serif; color: rgb(31, 73, 125);">Return on your system?</span></p>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size: 11pt; font-family: "Calibri", sans-serif; color: rgb(31, 73, 125);"> </span></p>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size: 11pt; font-family: "Calibri", sans-serif; color: rgb(31, 73, 125);">Thanks,</span></p>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size: 11pt; font-family: "Calibri", sans-serif; color: rgb(31, 73, 125);">Regina</span></p>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size: 11pt; font-family: "Calibri", sans-serif; color: rgb(31, 73, 125);"> </span></p>
<div style="border:none; border-left:solid blue 1.5pt; padding:0in 0in 0in 4.0pt">
<div>
<div style="border:none; border-top:solid #E1E1E1 1.0pt; padding:3.0pt 0in 0in 0in">
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<b><span style="font-size:11.0pt; font-family:"Calibri",sans-serif">From:</span></b><span style="font-size:11.0pt; font-family:"Calibri",sans-serif"> postgis-users [mailto:postgis-users-bounces@lists.osgeo.org]
<b>On Behalf Of </b>Lars Aksel Opsahl<br>
<b>Sent:</b> Tuesday, June 13, 2023 11:34 PM<br>
<b>To:</b> 'PostGIS Users Discussion' <postgis-users@lists.osgeo.org><br>
<b>Subject:</b> [postgis-users] Postgis Simple Feature silently giving us wrong results</span></p>
</div>
</div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
</p>
<div>
<p class="x_contentpasted0" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family: "Calibri", sans-serif; color: black;">Hi</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family: "Calibri", sans-serif; color: black;"> </span></p>
<p class="x_contentpasted0" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family: "Calibri", sans-serif; color: black;">In Postgis we now have problems where we silently get wrong result from ST_Difference. This bug was only found because a tester did inspect the result manually by actually checking the maps generated
on the screen. </span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family: "Calibri", sans-serif; color: black;"> </span></p>
<p class="x_contentpasted0" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family: "Calibri", sans-serif; color: black;">In this case, we have a fixed restricted area (blue), where users can select an area to use that are not already used by another user (red area), so we need to remove the area taken. The result
the user saw is here <a href="https://gitlab.com/nibioopensource/resolve-overlap-and-gap/-/blob/develop/src/test/pictures/difference_test_01_POSTGIS_3_3_3.png" data-auth="NotApplicable">
https://gitlab.com/nibioopensource/resolve-overlap-and-gap/-/blob/develop/src/test/pictures/difference_test_01_POSTGIS_3_3_3.png</a> (output from ST_Difference, the green rectangles).
</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family: "Calibri", sans-serif; color: black;"> </span></p>
<p class="x_contentpasted0" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family: "Calibri", sans-serif; color: black;">The ST_Difference test and data used can be found here
<a href="https://gitlab.com/nibioopensource/resolve-overlap-and-gap/-/blob/develop/src/test/sql/regress/rog_sf_difference.sql" data-auth="NotApplicable">
https://gitlab.com/nibioopensource/resolve-overlap-and-gap/-/blob/develop/src/test/sql/regress/rog_sf_difference.sql</a> .</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family: "Calibri", sans-serif; color: black;"> </span></p>
<p class="x_contentpasted0" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family: "Calibri", sans-serif; color: black;">If we use Postgis Topology we get a result that seems to be correct.
<a href="https://gitlab.com/nibioopensource/resolve-overlap-and-gap/-/blob/develop/src/test/pictures/topo_difference_test_01_POSTGIS_3_3_3.png" data-auth="NotApplicable">
https://gitlab.com/nibioopensource/resolve-overlap-and-gap/-/blob/develop/src/test/pictures/topo_difference_test_01_POSTGIS_3_3_3.png</a> .
</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family: "Calibri", sans-serif; color: black;"> </span></p>
<p class="x_contentpasted0" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family: "Calibri", sans-serif; color: black;">The Postgis Topology test is here
<a href="https://gitlab.com/nibioopensource/resolve-overlap-and-gap/-/blob/develop/src/test/sql/regress/rog_topology_difference.sql" data-auth="NotApplicable">
https://gitlab.com/nibioopensource/resolve-overlap-and-gap/-/blob/develop/src/test/sql/regress/rog_topology_difference.sql</a> .</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family: "Calibri", sans-serif; color: black;"> </span></p>
<p class="x_contentpasted0" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family: "Calibri", sans-serif; color: black;">We use the same input data and run on the same Postgis version (POSTGIS="3.3.3 2355e8e") and server for this tests. (If a I use latest source compiled by Sandro a couple weeks ago on a server locally,
this test case works, but then another test case fails).</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family: "Calibri", sans-serif; color: black;"> </span></p>
<p class="x_contentpasted0" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family: "Calibri", sans-serif; color: black;">We have reported errors like before so the path that Simple Feature seems to be heading now is where it silently generate very wrong results and then silently corrupts your spatial database, is
very problematic for us. It would be much better for us if we got with a Topology exception that could tell us what and where the problem was and then can decide what to do.
</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family: "Calibri", sans-serif; color: black;"> </span></p>
<p class="x_contentpasted0" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family: "Calibri", sans-serif; color: black;">Is that possible today?</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family: "Calibri", sans-serif; color: black;"> </span></p>
<p class="x_contentpasted0" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family: "Calibri", sans-serif; color: black;">The default behavior of Postgis Simple Feature (not only ST_Difference) today makes it problematic for NIBIO to use it in systems where users can apply for subsidies related to areas of land for
instance. It’s also difficult to find new errors, because they are silently hidden and when doing billions of operation, manual inspection is out off range. We can maybe start to make tests to check if any the selected from from ST_Difference intersects with
area already used, but I have no idea if that will find all problems. Another problem is producing spatial change logs because with Postgis bugs like this, you get new changes just because of a new Postgis version and not because data have changed.</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family: "Calibri", sans-serif; color: black;"> </span></p>
<p class="x_contentpasted0" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family: "Calibri", sans-serif; color: black;">It would have been better throwing an error in cases like this and then user can decide if he wants to report this as error maybe modify the input data, I think, actually one of the reasons why
NIBIO started use Postgis was that others system did cover up operations like Postgis are doing now.</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family: "Calibri", sans-serif; color: black;"> </span></p>
<p class="x_contentpasted0" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family: "Calibri", sans-serif; color: black;">Another option is to use Postgis Topology and the way that Sandro is doing it here seem much better related to data corruption, because here we get an error if the system are not able to handle
a new input line. In Postgis Topology we also have methods (<a href="https://postgis.net/docs/ValidateTopology.html" data-auth="NotApplicable">https://postgis.net/docs/ValidateTopology.html</a> ) that can check the database consistency.</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family: "Calibri", sans-serif; color: black;"> </span></p>
<p class="x_contentpasted0" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family: "Calibri", sans-serif; color: black;">NIBIO will have both a workshop and talk at Foss4g 2023 in Prizren. Here we will talk about Postgis Topology related used cases
<a href="https://gitlab.com/nibioopensource/topology_update_app" data-auth="NotApplicable">
https://gitlab.com/nibioopensource/topology_update_app</a> and <a href="https://gitlab.com/nibioopensource/resolve-overlap-and-gap" data-auth="NotApplicable">
https://gitlab.com/nibioopensource/resolve-overlap-and-gap</a>. </span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family: "Calibri", sans-serif; color: black;"> </span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family: "Calibri", sans-serif; color: black;"> </span></p>
<p class="x_contentpasted0" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family: "Calibri", sans-serif; color: black;">Thanks.</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family: "Calibri", sans-serif; color: black;"> </span></p>
<p class="x_contentpasted0" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family: "Calibri", sans-serif; color: black;">Lars </span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family: "Calibri", sans-serif; color: black;"> </span></p>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family: "Calibri", sans-serif; color: black;"> </span></p>
</div>
</div>
</div>
</div>
</body>
</html>