<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Aptos;
panose-1:2 11 0 4 2 2 2 2 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
font-size:12.0pt;
font-family:"Aptos",sans-serif;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;
mso-ligatures:none;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style>
</head>
<body lang="EN-US" link="#0563C1" vlink="#954F72" style="word-wrap:break-word">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size:11.0pt">Here’s another data point. We have two test environments with the versions of PROJ that we see in the AWS environments. When we perform a projection using cs2cs we get the same results from PROJ 8.0.1 and
9.1.1:<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt">PROJ Rel. 8.0.1, March 5th, 2021<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt">$ echo 2399319.7374562174 611365.0841263086 | cs2cs -f "%.13f" +init=epsg:26850 +to +init=epsg:4326<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt">-95.1331365261898 45.7734068103073 0.0000000000000<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt">PROJ Rel. 9.1.1, December 1st, 2022</span><span style="font-size:11.0pt"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt">$ echo 2399319.7374562174 611365.0841263086 | cs2cs -f "%.13f" +init=epsg:26850 +to +init=epsg:4326<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt">-95.1331365261898 45.7734068103073 0.0000000000000<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt">Would it be correct to think that this result suggests that the discrepancy is more likely to be in the configuration of the two AWS services?<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt">For context I should also mention (perhaps with just a bit of embarrassment) that the concern over the shifts has less to do with accuracy than with a consuming system that doesn’t properly understand the
nature of GIS data and is just considering a decimal difference in coordinate values to be suggestive of an error in processing. Greg’s point about the topological meaning of a 12 cm displacement is, of course, spot on: as a matter of topology, it isn’t
significant.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt">Please let me know if I can provide more information, especially anything that may be of help or interest to the community.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt">Many, many thanks once again for all your attention!<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt">Pat<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif"><o:p> </o:p></span></p>
<div id="mail-editor-reference-message-container">
<div>
<div>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal" style="margin-bottom:12.0pt"><b><span style="color:black">From:
</span></b><span style="color:black">Paul Ramsey <pramsey@cleverelephant.ca><br>
<b>Date: </b>Monday, February 17, 2025 at 7:09</span><span style="font-family:"Arial",sans-serif;color:black"> </span><span style="color:black">PM<br>
<b>To: </b>Pat Blair <pblair@geocomm.com><br>
<b>Cc: </b>postgis-users@lists.osgeo.org <postgis-users@lists.osgeo.org>, Ryan Thomas <rthomas@geocomm.com>, Forest Carter <fcarter@geocomm.com>, Neil Erickson <nerickson@geo-comm.com>, Kellsey Schilly <kschilly@geocomm.com>, Colin Kelly <ckelly@geocomm.com><br>
<b>Subject: </b>Re: Different Projection Results in AWS Managed Services<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt">ALERT: This message originated outside of GeoComm. Use CAUTION when opening links and attachments.<br>
<br>
Greg's notes are great on the relative scale of the discrepancy<br>
compared to the expected accuracy of the transform. That said, all<br>
things being equal I'd usually expect NAD83>WGS84 to be a null<br>
transform, and the actual reprojection step to not have a large<br>
difference. You do have different Proj versions, and you could<br>
independently compare those versions to see if you can replicate the<br>
difference yourself. You also have a proj.db in both cases that you do<br>
not know the contents of. In particular, though the grid download<br>
feature of Proj is turned off, you don't know what grids your vendor<br>
has loaded for RDS vs Aurora. You'll have to ask them if their Proj<br>
setup differs between the two products (it very well might, there's no<br>
guarantee that RDS and Aurora use the same build or packaging chain).<br>
<br>
ATB,<br>
P<br>
<br>
On Mon, Feb 17, 2025 at 12:53</span><span style="font-size:11.0pt;font-family:"Arial",sans-serif"> </span><span style="font-size:11.0pt">PM Pat Blair <pblair@geocomm.com> wrote:<br>
><br>
> 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.<br>
><br>
><br>
><br>
> Is there a clever way to take greater control over the results of projections to get more predictable results between environments?<br>
><br>
><br>
><br>
> 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.<br>
><br>
><br>
><br>
> 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.<br>
><br>
><br>
><br>
> /**<br>
><br>
> AWS RDS<br>
><br>
> */<br>
><br>
> SELECT postgis_full_version()<br>
><br>
> FROM pg_extension;<br>
><br>
> -- POSTGIS="3.4.2 c19ce56" [EXTENSION]<br>
><br>
> -- PGSQL="150"<br>
><br>
> -- GEOS="3.11.2-CAPI-1.17.2"<br>
><br>
> -- PROJ="8.0.1<br>
><br>
> -- NETWORK_ENABLED=OFF<br>
><br>
> -- URL_ENDPOINT=https://cdn.proj.org<br>
><br>
> -- USER_WRITABLE_DIRECTORY=/tmp/proj<br>
><br>
> -- DATABASE_PATH=/rdsdbbin/postgres-15.7.R3/share/proj/proj.db"<br>
><br>
> -- LIBXML="2.9.1"<br>
><br>
> -- LIBJSON="0.15"<br>
><br>
> -- LIBPROTOBUF="1.3.2"<br>
><br>
> -- WAGYU="0.5.0 (Internal)"<br>
><br>
> SELECT proj4text FROM spatial_ref_sys WHERE srid=26850;<br>
><br>
> -- "+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 "<br>
><br>
> SELECT<br>
><br>
> ST_AsText(<br>
><br>
> ST_Transform(<br>
><br>
> ST_SetSRID(<br>
><br>
> 'POINT(2399319.7374562174 611365.0841263086)'::geometry,<br>
><br>
> 26850<br>
><br>
> ),<br>
><br>
> 4326<br>
><br>
> )<br>
><br>
> ) AS transformed<br>
><br>
> ;<br>
><br>
> -- POINT(-95.1331380928918 45.773407072930254)<br>
><br>
><br>
><br>
><br>
><br>
> /**<br>
><br>
> AWS Aurora.<br>
><br>
> */<br>
><br>
> SELECT postgis_full_version()<br>
><br>
> FROM pg_extension;<br>
><br>
> -- POSTGIS="3.4.2 0" [EXTENSION]<br>
><br>
> -- PGSQL="150"<br>
><br>
> -- GEOS="3.12.0-CAPI-1.18.0"<br>
><br>
> -- PROJ="9.1.0 NETWORK_ENABLED=OFF<br>
><br>
> -- URL_ENDPOINT=https://cdn.proj.org<br>
><br>
> -- USER_WRITABLE_DIRECTORY=/tmp/proj<br>
><br>
> -- DATABASE_PATH=/rdsdbbin/aurora/bin/../share/postgresql/proj/proj.db"<br>
><br>
> -- LIBXML="2.12.5"<br>
><br>
> -- LIBJSON="0.12.99"<br>
><br>
> -- LIBPROTOBUF="1.3.0"<br>
><br>
> -- WAGYU="0.5.0 (Internal)" (core procs from "3.3.2 4975da8" need upgrade)<br>
><br>
> SELECT proj4text FROM spatial_ref_sys WHERE srid=26850;<br>
><br>
> -- "+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 "<br>
><br>
> SELECT<br>
><br>
> ST_AsText(<br>
><br>
> ST_Transform(<br>
><br>
> ST_SetSRID(<br>
><br>
> 'POINT(2399319.7374562174 611365.0841263086)'::geometry,<br>
><br>
> 26850<br>
><br>
> ),<br>
><br>
> 4326<br>
><br>
> )<br>
><br>
> ) AS transformed<br>
><br>
> ;<br>
><br>
> -- POINT(-95.13313652618976 45.77340681030725)<br>
><br>
><br>
><br>
><br>
><br>
> /**<br>
><br>
> Calculate the displacement.<br>
><br>
> */<br>
><br>
> SELECT ST_Distance(<br>
><br>
> 'POINT(-95.1331380928918 45.773407072930254)'::geography,<br>
><br>
> 'POINT(-95.13313652618976 45.77340681030725)'::geography<br>
><br>
> ) AS shift;<br>
><br>
> -- 0.12530368<o:p></o:p></span></p>
</div>
</div>
</div>
</div>
</div>
</body>
</html>