[postgis-devel] Much slower processing on GEOS 3.9.0 versus 3.8.0 for geodesic area calculation - Upgrade to PROJ 7.2.1 causes issue

Tue Apr 27 23:51:26 PDT 2021

Hi Paul and others,

After the first update of GEOS only, see below, that showed *normal* 
speed, I now selected:

* proj-bin

from the available updatable packages in the UbuntuGIS repository as a 
first test to see if this would replicate the performance issues. 
According to Synaptic, the installed version was 6.3.1 (which correspond 
with the result of 'SELECT postgis_full_version()'), and the available 
update 7.2.1.

Once selected, the additional dependencies:

* libproj19

* libproj-dev

* proj-data

were also automatically marked for update / installation by Synaptic, so 
also included in the update.

I then did a full reboot / restart of the VM, and restarted the 
PostgreSQL service. I subsequently started the processing again, and 
yes(!), I *do* see the *slow* processing again specifically with the 
same step of 'ST_Area(way::geography,true)' where the cast to geography 
is done.

One thing that is really strange though, is that:

'SELECT postgis_full_version()'

still returns:

"PROJ=6.3.1"

instead of the exptected "PROJ=7.2.1" after updating the 'proj-bin' 
package via Synaptic, even though Synaptic clearly showing the packages 
were succesfully updated to 7.2.1 and no error was thrown. I have this 
code line in my Python code, and also ran it from pgAdmin4, both 
reporting the same result for 'SELECT postgis_full_version()', so 6.3.1.

Anyway, what was initially suspected, that PROJ is involved, now seems 
to be quite clear. However, contrary to an update of PROJ 5.x TO PROJ 
6.x, it seems the upgrade of any of the related associated packages to 
PROJ 7.x is causing the issue.

Note though that in the PostGIS ticket:

"#4890: ST_Transform large slow down from upgrade to 3.1.1 from 2.4"

according to the original user reporting it, both PostGIS versions 
2.4/3.1.1 were supposed to be on PROJ 7.2.1 already. However, given the 
fact that I see my installation return '6.3.1' even after the upgrade to 
'7.2.1' and doing a full reboot of the VM and restart of the PostgreSQL 
service, there is a possibility the 'SELECT postgis_full_version()' 
result for the 2.4 version of the original poster, might be wrong, and 
he/she might still be using PROJ 5.x on that installation (allthough 
this would not fit my issue with *lower* than installed version reported).

Marco

Op 28-4-2021 om 00:20 schreef Marco Boeringa:
> Hi Paul,
>
> Maybe I can put a nail into that GEOS coffin myself. I now attempted 
> to reproduce this issue a third time after another restore of the VM 
> from backup, but now was really careful to only select the bare 
> minimum in Synaptic Package Manager.
>
> As a bare minimum, these were updated:
>
> * libgeos-3.9.0
> * libgeos-c1v5
> * libgeos-dev
>
> to 3.9.0.
>
> This now ran at *normal* speed, so no slow down compared to 3.8.0.
>
> I want to investigate this further, and see if installing any of the 
> other possible upgrades from the UbuntuGIS repository, that Synaptic 
> offers once I refresh it, potentially are involved. Will take some 
> time to find out though.
>
> Marco
>
> Op 27-4-2021 om 22:46 schreef Paul Ramsey:
>> Just to stick a nail in the GEOS-version-affects-geography-area 
>> conversation, I took the time to set up two builds (GEOS 3.8 and GEOS 
>> 3.9) and swapped between them without changing anything else about 
>> the system.
>>
>> postgis=# select postgis_full_version();
>> postgis_full_version
>> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
>>
>>   POSTGIS="3.2.0dev 3.1.0rc1-162-g46efb9f2d" [EXTENSION] PGSQL="130" 
>> GEOS="3.8.3-CAPI-1.13.4" PROJ="8.0.0" GDAL="GDAL 3.2.0, released 
>> 2020/10/26" LIBXML="2.9.4" LIBJSON="0.15" LIBPROTOBUF="1.3.3" 
>> WAGYU="0.5.0 (Internal)"
>>
>> postgis=# select sum(st_area(geom::geography)) from va;
>>          sum
>> --------------------
>>   1070124793748.3967
>> (1 row)
>>
>> Time: 1449.551 ms (00:01.450)
>>
>>
>> Then stop the server, change the LD_LIBRARY_PATH to the 3.9 install 
>> location and restart.
>>
>> postgis=# select postgis_full_version();
>> postgis_full_version
>> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
>>
>>   POSTGIS="3.2.0dev 3.1.0rc1-162-g46efb9f2d" [EXTENSION] PGSQL="130" 
>> GEOS="3.9.2dev-CAPI-1.14.2" PROJ="8.0.0" GDAL="GDAL 3.2.0, released 
>> 2020/10/26" LIBXML="2.9.4" LIBJSON="0.15" LIBPROTOBUF="1.3.3" 
>> WAGYU="0.5.0 (Internal)"
>>
>> postgis=# select sum(st_area(geom::geography)) from va;
>>          sum
>> --------------------
>>   1070124793748.3959
>> (1 row)
>>
>> Time: 1439.969 ms (00:01.440)
>>
>>
>>
>> There's no effect to be seen when GEOS version is the only variable 
>> in play.
>>
>> P
>> _______________________________________________
>> postgis-devel mailing list
>> postgis-devel at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-devel
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-devel