<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">
<p style="line-height: 100%; margin-bottom: 0in; background: transparent;" class="ContentPasted0">
Hi</p>
<p style="line-height: 100%; margin-bottom: 0in; background: transparent;"><br class="ContentPasted0">
</p>
<p style="line-height: 100%; margin-bottom: 0in; background: transparent;" class="ContentPasted0">
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.
</p>
<p style="line-height: 100%; margin-bottom: 0in; background: transparent;"><br class="ContentPasted0">
</p>
<p style="line-height: 100%; margin-bottom: 0in; background: transparent;" class="ContentPasted0">
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" class="ContentPasted0">
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).
</p>
<p style="line-height: 100%; margin-bottom: 0in; background: transparent;"><br class="ContentPasted0">
</p>
<p style="line-height: 100%; margin-bottom: 0in; background: transparent;" class="ContentPasted0">
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" class="ContentPasted0">
https://gitlab.com/nibioopensource/resolve-overlap-and-gap/-/blob/develop/src/test/sql/regress/rog_sf_difference.sql</a> .</p>
<p style="line-height: 100%; margin-bottom: 0in; background: transparent;"><br class="ContentPasted0">
</p>
<p style="line-height: 100%; margin-bottom: 0in; background: transparent;" class="ContentPasted0">
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" class="ContentPasted0">
https://gitlab.com/nibioopensource/resolve-overlap-and-gap/-/blob/develop/src/test/pictures/topo_difference_test_01_POSTGIS_3_3_3.png</a> .
</p>
<p style="line-height: 100%; margin-bottom: 0in; background: transparent;"><br class="ContentPasted0">
</p>
<p style="line-height: 100%; margin-bottom: 0in; background: transparent;" class="ContentPasted0">
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" class="ContentPasted0">
https://gitlab.com/nibioopensource/resolve-overlap-and-gap/-/blob/develop/src/test/sql/regress/rog_topology_difference.sql</a> .</p>
<p style="line-height: 100%; margin-bottom: 0in; background: transparent;"><br class="ContentPasted0">
</p>
<p style="line-height: 100%; margin-bottom: 0in; background: transparent;" class="ContentPasted0">
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).</p>
<p style="line-height: 100%; margin-bottom: 0in; background: transparent;"><br class="ContentPasted0">
</p>
<p style="line-height: 100%; margin-bottom: 0in; background: transparent;" class="ContentPasted0">
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.
</p>
<p style="line-height: 100%; margin-bottom: 0in; background: transparent;"><br class="ContentPasted0">
</p>
<p style="line-height: 100%; margin-bottom: 0in; background: transparent;" class="ContentPasted0">
Is that possible today?</p>
<p style="line-height: 100%; margin-bottom: 0in; background: transparent;"><br class="ContentPasted0">
</p>
<p style="line-height: 100%; margin-bottom: 0in; background: transparent;" class="ContentPasted0">
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.</p>
<p style="line-height: 100%; margin-bottom: 0in; background: transparent;"><br class="ContentPasted0">
</p>
<p style="line-height: 100%; margin-bottom: 0in; background: transparent;" class="ContentPasted0">
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.</p>
<p style="line-height: 100%; margin-bottom: 0in; background: transparent;"><br class="ContentPasted0">
</p>
<p style="line-height: 100%; margin-bottom: 0in; background: transparent;" class="ContentPasted0">
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" class="ContentPasted0">https://postgis.net/docs/ValidateTopology.html</a>
) that can check the database consistency.</p>
<p style="line-height: 100%; margin-bottom: 0in; background: transparent;"><br class="ContentPasted0">
</p>
<p style="line-height: 100%; margin-bottom: 0in; background: transparent;" class="ContentPasted0">
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" class="ContentPasted0">
https://gitlab.com/nibioopensource/topology_update_app</a> and <a href="https://gitlab.com/nibioopensource/resolve-overlap-and-gap" class="ContentPasted0">
https://gitlab.com/nibioopensource/resolve-overlap-and-gap</a>. </p>
<p style="line-height: 100%; margin-bottom: 0in; background: transparent;"><br class="ContentPasted0">
</p>
<p style="line-height: 100%; margin-bottom: 0in; background: transparent;"><br class="ContentPasted0">
</p>
<p style="line-height: 100%; margin-bottom: 0in; background: transparent;" class="ContentPasted0">
Thanks.</p>
<p style="line-height: 100%; margin-bottom: 0in; background: transparent;"><br class="ContentPasted0">
</p>
<p style="line-height: 100%; margin-bottom: 0in; background: transparent;" class="ContentPasted0">
Lars </p>
<p style="line-height: 100%; margin-bottom: 0in; background: transparent;"><br class="ContentPasted0">
</p>
<br>
</div>
</body>
</html>