Hello. We started noticing some coordinate shifting when we moved our PostGIS data from an AWS RDS database to AWS Aurora. In many cases the displacement isn’t dramatic and can likely be explained by differences in the versions of GEOS and PROJ for each service. That said, it is large enough to attract the attention of some of the people we work with, and we would be interested in learning any more we can about how we might manage the discrepancies.
Is there a clever way to take greater control over the results of projections to get more predictable results between environments? Below I am pasting some SQL that I hope will illustrate what we are seeing. When we transform a coordinate from EPSG:26860 (NAD83 / Minnesota Central (ftUS)) to WGS84 in two different environments (“AWS RDS” and “AWS Aurora”) we get a different result with a displacement of about 0.12 meters. The proj4 definitions for the projected coordinate system look to be the same on both systems, though since these are managed database services we don’t have direct access to any of the projection databases, grids, etc. Many thanks in advance if you can provide some advice! We would certainly be happy to provide any more information that might be helpful in describing what we are seeing. /** AWS RDS */ SELECT postgis_full_version() FROM pg_extension; -- POSTGIS="3.4.2 c19ce56" [EXTENSION] -- PGSQL="150" -- GEOS="3.11.2-CAPI-1.17.2" -- PROJ="8.0.1 -- NETWORK_ENABLED=OFF -- URL_ENDPOINT=https://cdn.proj.org -- USER_WRITABLE_DIRECTORY=/tmp/proj -- DATABASE_PATH=/rdsdbbin/postgres-15.7.R3/share/proj/proj.db" -- LIBXML="2.9.1" -- LIBJSON="0.15" -- LIBPROTOBUF="1.3.2" -- WAGYU="0.5.0 (Internal)" SELECT proj4text FROM spatial_ref_sys WHERE srid=26850; -- "+proj=lcc +lat_1=47.05 +lat_2=45.61666666666667 +lat_0=45 +lon_0=-94.25 +x_0=800000.0000101599 +y_0=99999.99998983997 +datum=NAD83 +units=us-ft +no_defs " SELECT ST_AsText( ST_Transform( ST_SetSRID( 'POINT(2399319.7374562174 611365.0841263086)'::geometry, 26850 ), 4326 ) ) AS transformed ; -- POINT(-95.1331380928918 45.773407072930254) /** AWS Aurora. */ SELECT postgis_full_version() FROM pg_extension; -- POSTGIS="3.4.2 0" [EXTENSION] -- PGSQL="150" -- GEOS="3.12.0-CAPI-1.18.0" -- PROJ="9.1.0 NETWORK_ENABLED=OFF -- URL_ENDPOINT=https://cdn.proj.org -- USER_WRITABLE_DIRECTORY=/tmp/proj -- DATABASE_PATH=/rdsdbbin/aurora/bin/../share/postgresql/proj/proj.db" -- LIBXML="2.12.5" -- LIBJSON="0.12.99" -- LIBPROTOBUF="1.3.0" -- WAGYU="0.5.0 (Internal)" (core procs from "3.3.2 4975da8" need upgrade) SELECT proj4text FROM spatial_ref_sys WHERE srid=26850; -- "+proj=lcc +lat_1=47.05 +lat_2=45.61666666666667 +lat_0=45 +lon_0=-94.25 +x_0=800000.0000101599 +y_0=99999.99998983997 +datum=NAD83 +units=us-ft +no_defs " SELECT ST_AsText( ST_Transform( ST_SetSRID( 'POINT(2399319.7374562174 611365.0841263086)'::geometry, 26850 ), 4326 ) ) AS transformed ; -- POINT(-95.13313652618976 45.77340681030725) /** Calculate the displacement. */ SELECT ST_Distance( 'POINT(-95.1331380928918 45.773407072930254)'::geography, 'POINT(-95.13313652618976 45.77340681030725)'::geography ) AS shift; -- 0.12530368
