4

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: Difference between geojson from Server A and B

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?

  • Your proj4text is different from epsg.io/31467. I have no idea why. gdalsrsinfo epsg:31467 reports proj string also as PROJ.4 : +proj=tmerc +lat_0=0 +lon_0=9 +k=1 +x_0=3500000 +y_0=0 +ellps=bessel +towgs84=598.1,73.7,418.2,0.202,0.045,-2.455,6.7 +units=m +no_defs. – user30184 Aug 21 at 9:45
  • I get the 2nd answer with my local 2.5 version – Ian Turton Aug 21 at 9:54
  • A correct transformation on Server B requires BETA2007.gsb, which can be provided with proj-datumgrid for PROJ 6. – Mike T Aug 21 at 10:23
4

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.