Different results for same transformation on different PostgreSQL/Postgis installations

Question

While migratring web services from one server to another, we noticed different transformation results for the same query. In our case, a Polygon is supplied in WKT with SRID=4326 and a query is sent to PostgresSQL to transform it to 31467. It was suspected that the different servers have different proj4 definitions, but the spatial_ref_sys information for srid 31467 is identical.

Example polygon in WKT, SRID=4326:

POLYGON((6.765692822761539 51.00597872375141,6.767598964757953 51.00509342952401,6.771910476416512 51.006635544034566,6.7707304837520645 51.00806338213016,6.765692822761539 51.00597872375141))

Server A

SELECT ST_AsText(ST_Transform(ST_GeomFromText('POLYGON((6.765692822761539 51.00597872375141,6.767598964757953 51.00509342952401,6.771910476416512 51.006635544034566,6.7707304837520645 51.00806338213016,6.765692822761539 51.00597872375141))',4326),31467))

yields:

POLYGON((3343258.68567984 5654686.62282287,3343389.47157136 5654584.09718158,3343697.23474346 5654746.47217529,3343619.23068104 5654907.79612667,3343258.68567984 5654686.62282287))

Additional version information:

SELECT PostGIS_Full_Version();  
SELECT version();

yields

POSTGIS="2.2.5 r15298" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.2, released 2012/10/08" LIBXML="2.7.6" LIBJSON="0.11" RASTER
PostgreSQL 9.4.13 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit

and

SELECT proj4text FROM spatial_ref_sys WHERE srid=31467;

yields

+proj=tmerc +lat_0=0 +lon_0=9 +k=1 +x_0=3500000 +y_0=0 +datum=potsdam +units=m +no_defs

When we execute the transformation on Server B,

SELECT ST_AsText(ST_Transform(ST_GeomFromText('POLYGON((6.765692822761539 51.00597872375141,6.767598964757953 51.00509342952401,6.771910476416512 51.006635544034566,6.7707304837520645 51.00806338213016,6.765692822761539 51.00597872375141))',4326),31467))

we receive

POLYGON((3343204.36380305 5654547.24689407,3343335.13281662 5654444.73197122,3343642.85963301 5654607.08595063,3343564.86647133 5654768.39203429,3343204.36380305 5654547.24689407))

A notably different result when compared to the returned polygon of Server A.

Additional version information:

SELECT PostGIS_Full_Version();
SELECT version();

yields

POSTGIS="2.5.3 r17699" [EXTENSION] PGSQL="100" GEOS="3.7.2-CAPI-1.11.2 b55d2125" PROJ="Rel. 6.2.0, September 1st, 2019" GDAL="GDAL 3.0.1, released 2019/06/28 GDAL_DATA not found" LIBXML="2.9.1" LIBJSON="0.11" LIBPROTOBUF="1.0.2" RASTER
PostgreSQL 10.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit

and

SELECT proj4text FROM spatial_ref_sys WHERE srid=31467;

yields

+proj=tmerc +lat_0=0 +lon_0=9 +k=1 +x_0=3500000 +y_0=0 +datum=potsdam +units=m +no_defs

When both polygons are exported to geojson and rendered in QGis, the difference becomes obvious:

We would not have expected a newer version of PostGIS and associated libraries to have such an effect on the results, especially since the proj4 definition for 31467 on both installations is identical. Is there something we missed here?

Answer 1

The PROJ library has had three different definitions for the potsdam datum.

In version 4.8.0, it was changed from a three-parameter form to a more precise seven-parameter form. This seven-parameter form is what server A is using, and what is shown on epsg.io.

In version 5.0.1, it was changed to use the BeTA2007 grid. This is what server B is using.