ST_Transform broken with PROJ 9.2.0

[tjay]

Hi,

if PostGIS 3.x is compiled with PROJ 9.2.0 some Transformations are broken:

postgis 3.1.8 with PROJ 9.1.1

 select postgis_full_version();
                                                                        postgis_full_version
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 POSTGIS="3.1.8 c5ebc1f" [EXTENSION] PGSQL="120" GEOS="3.11.2-CAPI-1.17.2" PROJ="9.1.1" LIBXML="2.9.7" LIBJSON="0.13.1" LIBPROTOBUF="1.3.0" WAGYU="0.5.0 (Internal)"
(1 row)

postgres=# select ST_AsText(ST_Transform(ST_GeomFromText('POINT(343806.17 5676555.69)',25832),5676),4);
            st_astext
----------------------------------
 POINT(2553378.3214 5676286.9204)
(1 row)

postgis 3.1.8 with PROJ 9.2.0

postgres=# select postgis_full_version();
                                                                        postgis_full_version
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 POSTGIS="3.1.8 c5ebc1f" [EXTENSION] PGSQL="120" GEOS="3.11.2-CAPI-1.17.2" PROJ="9.2.0" LIBXML="2.9.7" LIBJSON="0.13.1" LIBPROTOBUF="1.3.0" WAGYU="0.5.0 (Internal)"
(1 row)

postgres=# select ST_AsText(ST_Transform(ST_GeomFromText('POINT(343806.17 5676555.69)',25832),5676),4);
            st_astext
----------------------------------
 POINT(2553376.7567 5676287.3596)
(1 row)

cs2cs PROJ 9.2.0

[root@d5929135c288 /]# cat proj_utm.csv
343806.17       5676555.69 0.00


[root@d5929135c288 /]# cs2cs +init=epsg:25832 +to +init=epsg:5676 proj_utm.csv
2553378.32      5676286.92 0.00

[tjay]

auditing the DEBUG-Logs it seems some missing (and not needed) Datumgrids are differently handled in PostGIS vs cs2cs:

cs2cs 9.1.1

pj_open_lib(proj.ini): call fopen(/share/proj/proj.ini) - succeeded
pj_open_lib(proj.db): call fopen(/share/proj/proj.db) - succeeded
pj_open_lib(epsg): call fopen(epsg) - failed
pj_open_lib(proj.db): call fopen(/share/proj/proj.db) - succeeded
pj_open_lib(epsg): call fopen(epsg) - failed
pj_open_lib(proj.db): call fopen(/share/proj/proj.db) - succeeded
pj_open_lib(epsg): call fopen(epsg) - failed
pj_open_lib(proj.db): call fopen(/share/proj/proj.db) - succeeded
pj_open_lib(epsg): call fopen(epsg) - failed
pj_open_lib(proj.db): call fopen(/share/proj/proj.db) - succeeded
pj_open_lib(de_adv_BETA2007.tif): call fopen(/share/proj/de_adv_BETA2007.tif) - succeeded
pj_open_lib(de_lgvl_saarland_SeTa2016.tif): call fopen(de_lgvl_saarland_SeTa2016.tif) - failed
pj_open_lib(SeTa2016.gsb): call fopen(SeTa2016.gsb) - failed
Using coordinate operation Inverse of UTM zone 32N + Inverse of DHDN to ETRS89 (8) + 3-degree Gauss-Kruger zone 2
pj_open_lib(de_adv_BETA2007.tif): call fopen(/share/proj/de_adv_BETA2007.tif) - succeeded

postgis + proj 9.1.1

pj_open_lib(proj.ini): call fopen(/share/proj/proj.ini) - succeeded
pj_open_lib(proj.db): call fopen(/share/proj/proj.db) - succeeded
pj_open_lib(de_adv_BETA2007.tif): call fopen(/share/proj/de_adv_BETA2007.tif) - succeeded
pj_open_lib(de_lgvl_saarland_SeTa2016.tif): call fopen(de_lgvl_saarland_SeTa2016.tif) - failed
pj_open_lib(SeTa2016.gsb): call fopen(SeTa2016.gsb) - failed
Using coordinate operation Inverse of UTM zone 32N + Inverse of DHDN to ETRS89 (8) + 3-degree Gauss-Kruger zone 2
pj_open_lib(de_adv_BETA2007.tif): call fopen(/share/proj/de_adv_BETA2007.tif) - succeeded

cs2cs 9.2.0

pj_open_lib(proj.ini): call fopen(/share/proj/proj.ini) - succeeded
pj_open_lib(proj.db): call fopen(/share/proj/proj.db) - succeeded
pj_open_lib(epsg): call fopen(epsg) - failed
pj_open_lib(proj.db): call fopen(/share/proj/proj.db) - succeeded
pj_open_lib(epsg): call fopen(epsg) - failed
pj_open_lib(proj.db): call fopen(/share/proj/proj.db) - succeeded
pj_open_lib(epsg): call fopen(epsg) - failed
pj_open_lib(proj.db): call fopen(/share/proj/proj.db) - succeeded
pj_open_lib(epsg): call fopen(epsg) - failed
pj_open_lib(proj.db): call fopen(/share/proj/proj.db) - succeeded
pj_open_lib(de_adv_BETA2007.tif): call fopen(/share/proj/de_adv_BETA2007.tif) - succeeded
pj_open_lib(de_lgvl_saarland_SeTa2016.tif): call fopen(de_lgvl_saarland_SeTa2016.tif) - failed
pj_open_lib(SeTa2016.gsb): call fopen(SeTa2016.gsb) - failed
pj_open_lib(de_adv_BETA2007.tif): call fopen(/share/proj/de_adv_BETA2007.tif) - succeeded
Using coordinate operation Inverse of UTM zone 32N + Inverse of DHDN to ETRS89 (8) + 3-degree Gauss-Kruger zone 2
pj_open_lib(de_adv_BETA2007.tif): call fopen(/share/proj/de_adv_BETA2007.tif) - succeeded

postgis + proj 9.2.0

pj_open_lib(proj.ini): call fopen(/share/proj/proj.ini) - succeeded
pj_open_lib(proj.db): call fopen(/share/proj/proj.db) - succeeded
pj_open_lib(de_adv_BETA2007.tif): call fopen(/share/proj/de_adv_BETA2007.tif) - succeeded
pj_open_lib(de_lgvl_saarland_SeTa2016.tif): call fopen(de_lgvl_saarland_SeTa2016.tif) - failed
pj_open_lib(SeTa2016.gsb): call fopen(SeTa2016.gsb) - failed
pj_open_lib(de_adv_BETA2007.tif): call fopen(/share/proj/de_adv_BETA2007.tif) - succeeded

Transformation works correctly by adding the missing datumgrid (de_lgvl_saarland_SeTa2016.tif) — short fix for now However, it would be important to know why this is happening…

[robe]

This might be the same issue we are running into with our tests failing for 9.2.0 when certain grid shift files aren't available as discussed in #5360 and I'm pretty sure we had same issue with our GHA latest that runs 9.2.0, but can't find that ticket.

[tjay]

with PROJ="9.2.1" the PosGIS-Test above, does not fail with wrong coordinates but with an Exception:

ERROR:  transform: File not found or invalid (1029)

So basicly better but still not comprehensible to the results of cs2cs that does not fail and transforms correctly.

[pramsey]

I am running the head of the 9.2 branch. I made sure I started my PostgreSQL server with the PROJ_NETWORK environment variable set to ON. And things work for me?

SELEcT ST_AsText(
  ST_Transform(ST_GeomFromText('POINT(343806.17 5676555.69)', 25832), 5676), 4);

            st_astext             
----------------------------------
 POINT(2553378.3214 5676286.9204)


SELECT postgis_full_version();

POSTGIS="3.4.0dev 3.3.0rc2-970-g3f57ca3d2" 
PGSQL="150" 
GEOS="3.12.0dev-CAPI-1.18.0" 
PROJ="9.2.1" 
LIBXML="2.9.13" 
LIBJSON="0.16" 
LIBPROTOBUF="1.4.1" 
WAGYU="0.5.0 (Internal)"

[pramsey]

Interesting note: until I turned PROJ_NETWORK to ON my regression was failing on a couple other proj tests, and now that I flipped it, regression is good. This is going to be a huge packaging problem, packagers are going to have no idea how to handle this well.

[robe]

Isn't this kind of a dupe with #5316