Greg's notes are great on the relative scale of the discrepancy compared to the expected accuracy of the transform. That said, all things being equal I'd usually expect NAD83>WGS84 to be a null transform, and the actual reprojection step to not have a large difference. You do have different Proj versions, and you could independently compare those versions to see if you can replicate the difference yourself. You also have a proj.db in both cases that you do not know the contents of. In particular, though the grid download feature of Proj is turned off, you don't know what grids your vendor has loaded for RDS vs Aurora. You'll have to ask them if their Proj setup differs between the two products (it very well might, there's no guarantee that RDS and Aurora use the same build or packaging chain).
ATB, P On Mon, Feb 17, 2025 at 12:53 PM Pat Blair <pbl...@geocomm.com> wrote: > > 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
