[postgis-users] Postgis Simple Feature silently giving us wrong results

[Lars Aksel Opsahl]

Hi


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.


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 https://gitlab.com/nibioopensource/resolve-overlap-and-gap/-/blob/develop/src/test/pictures/difference_test_01_POSTGIS_3_3_3.png (output from ST_Difference, the green rectangles).


The ST_Difference test and data used can be found here https://gitlab.com/nibioopensource/resolve-overlap-and-gap/-/blob/develop/src/test/sql/regress/rog_sf_difference.sql .


If we use Postgis Topology we get a result that seems to be correct. https://gitlab.com/nibioopensource/resolve-overlap-and-gap/-/blob/develop/src/test/pictures/topo_difference_test_01_POSTGIS_3_3_3.png .


The Postgis Topology test is here https://gitlab.com/nibioopensource/resolve-overlap-and-gap/-/blob/develop/src/test/sql/regress/rog_topology_difference.sql .


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).


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.


Is that possible today?


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.


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.


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 (https://postgis.net/docs/ValidateTopology.html ) that can check the database consistency.


NIBIO will have both a workshop and talk at Foss4g 2023 in Prizren. Here we will talk about Postgis Topology related used cases https://gitlab.com/nibioopensource/topology_update_app and https://gitlab.com/nibioopensource/resolve-overlap-and-gap.



Thanks.


Lars


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20230614/d3469b6b/attachment.htm>