Different Projection Results in AWS Managed Services

[Paul Ramsey]

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 <pblair at 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