<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p
{mso-style-priority:99;
margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
p.contentpasted0, li.contentpasted0, div.contentpasted0
{mso-style-name:contentpasted0;
margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
span.EmailStyle19
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>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.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>What does:<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>SELECT postgis_full_version(), version();<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Return on your system?<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Thanks,<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Regina<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></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=MsoNormal><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<o:p></o:p></span></p></div></div><p class=MsoNormal><o:p> </o:p></p><div><p class=contentpasted0><span style='font-family:"Calibri",sans-serif;color:black'>Hi<o:p></o:p></span></p><p><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p><p class=contentpasted0><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. <o:p></o:p></span></p><p><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p><p class=contentpasted0><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">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). <o:p></o:p></span></p><p><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p><p class=contentpasted0><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">https://gitlab.com/nibioopensource/resolve-overlap-and-gap/-/blob/develop/src/test/sql/regress/rog_sf_difference.sql</a> .<o:p></o:p></span></p><p><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p><p class=contentpasted0><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">https://gitlab.com/nibioopensource/resolve-overlap-and-gap/-/blob/develop/src/test/pictures/topo_difference_test_01_POSTGIS_3_3_3.png</a> . <o:p></o:p></span></p><p><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p><p class=contentpasted0><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">https://gitlab.com/nibioopensource/resolve-overlap-and-gap/-/blob/develop/src/test/sql/regress/rog_topology_difference.sql</a> .<o:p></o:p></span></p><p><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p><p class=contentpasted0><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).<o:p></o:p></span></p><p><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p><p class=contentpasted0><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. <o:p></o:p></span></p><p><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p><p class=contentpasted0><span style='font-family:"Calibri",sans-serif;color:black'>Is that possible today?<o:p></o:p></span></p><p><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p><p class=contentpasted0><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.<o:p></o:p></span></p><p><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p><p class=contentpasted0><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.<o:p></o:p></span></p><p><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p><p class=contentpasted0><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">https://postgis.net/docs/ValidateTopology.html</a> ) that can check the database consistency.<o:p></o:p></span></p><p><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p><p class=contentpasted0><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">https://gitlab.com/nibioopensource/topology_update_app</a> and <a href="https://gitlab.com/nibioopensource/resolve-overlap-and-gap">https://gitlab.com/nibioopensource/resolve-overlap-and-gap</a>. <o:p></o:p></span></p><p><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p><p><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p><p class=contentpasted0><span style='font-family:"Calibri",sans-serif;color:black'>Thanks.<o:p></o:p></span></p><p><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p><p class=contentpasted0><span style='font-family:"Calibri",sans-serif;color:black'>Lars <o:p></o:p></span></p><p><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div></div></div></body></html>