Postgis st_transform error: SQL Error [XX000]: ERROR: could not form projection (LWPROJ) from 'srid=7801' to 'srid=4326'

[iknikolov]

I have 4 servers with the same versions of OS (Ubuntu 20.04), Postgis and Postgre:

POSTGIS="3.2.1 5fae8e5" [EXTENSION] PGSQL="130" GEOS="3.10.1-CAPI-1.16.0" PROJ="6.3.1" LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)"

PostgreSQL 13.6 (Ubuntu 13.6-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit

The query below works on any server except one:

SELECT 
    st_asgeojson(st_transform("table-name".geom, 4326)) AS way
    FROM "table-name"

The server with the issue responds whiy this error:

SQL Error [XX000]: ERROR: could not form projection (LWPROJ) from 'srid=7801' to 'srid=4326'

I upgraded the PostGIS server to 3.3.2 but the result is the same.

Can you help me?

[robe]

You are several micro versions behind the latest stable 3.2. I'd suggest upgrading just to rule out any issues already fixed.

I'm running below so a bit newer than your setup.

POSTGIS="3.3.2 4975da8" [EXTENSION] PGSQL="150" GEOS="3.10.2-CAPI-1.16.0" SFCGAL="SFCGAL 1.4.1, CGAL 5.3.1, BOOST 1.74.0" PROJ="8.2.1" GDAL="GDAL 3.4.1, released 2021/12/27" LIBXML="2.9.13" LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)"  TOPOLOGY RASTER PostgreSQL 15.1 (Ubuntu 15.1-1.pgdg22.04+1) on aarch64-unknown-linux-gnu, compiled by gcc (Ubuntu 11.3.0-1ubuntu1~22.04) 11.3.0, 64-bit

What does this output in the database that is not working and in your others?

in psql

\x
SELECT *
FROM spatial_ref_sys where srid = 7801;

mine outputs:

id      | 7801
auth_name | EPSG
auth_srid | 7801
srtext    | PROJCS["BGS2005 / CCS2005",GEOGCS["BGS2005",DATUM["Bulgaria_Geodetic_System_2005",SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]],AUTHORITY["EPSG
","1167"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","7798"]],PROJECTION["Lambert_Conformal_Coni
c_2SP"],PARAMETER["standard_parallel_1",42],PARAMETER["standard_parallel_2",43.33333333333334],PARAMETER["latitude_of_origin",42.66787568333333],PARAMETER["central_meridian",25.
5],PARAMETER["false_easting",500000],PARAMETER["false_northing",4725824.3591],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AUTHORITY["EPSG","7801"]]
proj4text | +proj=lcc +lat_1=42 +lat_2=43.33333333333334 +lat_0=42.66787568333333 +lon_0=25.5 +x_0=500000 +y_0=4725824.3591 +ellps=GRS80 +units=m +no_defs

also can you check what this outputs, in particular the one erroring does it fail on this?

SELECT ST_AsText(ST_Transform(ST_Point(500000, 4725824, 7801), 4326));

mine outputs: POINT(25.5 42.667872450492496)

The issue sounds like possibly missing proj grid files so something off with your PROJ installation.

[robe]

oops sorry I missed the part about you having upgraded to 3.3.2.

Did you run:

SELECT postgis_extensions_upgrade();

after? that should fix issue of spatial_ref_sys if it is your spatial_ref_sys at fault.