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

Tue Jun 13 21:59:30 PDT 2023

Hi

Sure

On my local mac I have this

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

In test environment where we first did see the problem

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

In our CI system

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
--ON Postgresql 12, 13 and 14

There are more details here around the results and what's tested here also in https://gitlab.com/nibioopensource/resolve-overlap-and-gap/-/blob/develop/src/test/sql/regress/rog_sf_difference.sql

Thanks.

Lars

________________________________
From: postgis-users <postgis-users-bounces at lists.osgeo.org> on behalf of Regina Obe <lr at pcorp.us>
Sent: Wednesday, June 14, 2023 6:43 AM
To: 'PostGIS Users Discussion' <postgis-users at lists.osgeo.org>
Subject: Re: [postgis-users] Postgis Simple Feature silently giving us wrong results

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.

What does:

SELECT postgis_full_version(), version();

Return on your system?

Thanks,

Regina

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Lars Aksel Opsahl
Sent: Tuesday, June 13, 2023 11:34 PM
To: 'PostGIS Users Discussion' <postgis-users at lists.osgeo.org>
Subject: [postgis-users] Postgis Simple Feature silently giving us wrong results

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/d65881e3/attachment.htm>