ST_Transform NAD27 --> WGS84 Inconsistencies

[judsoncrouch]

Hello PostGIS Team,

I've encountered an issue when transforming NAD27 coordinates to WGS84. The resulting WGS84 coordinates are off by quite a bit (10-12 feet in most cases) and it lead me to digging into why that is so. As a control, I converted the same point from NAD27 to WGS84 in ArcMap and hold that to be the truth.

select st_transform(st_setsrid(ST_POINT(-99.723991251, 35.954241741), 4267), 4326)

-99.7243815258533 35.95428699761289 -- ESRI Transformed (Control)
-99.7243847871355 35.954284680650595 -- Result of ST_TRANSFORM on CentOS7

Thinking it may be a difference in configuration or PROJ grids, I compared/contrasted a fresh install of Postgresql 13 and Postgis 3.2.2 on CentOS 7 with the official ​Postgis Docker image for the same versions.

CentOS 7

select postgis_full_version()

POSTGIS="3.2.2 628da50" [EXTENSION] PGSQL="130" GEOS="3.10.3-CAPI-1.16.1" SFCGAL="1.3.7" PROJ="8.2.1" GDAL="GDAL 3.4.3, released 2022/04/22" LIBXML="2.9.7" LIBJSON="0.13.1" LIBPROTOBUF="1.3.0" WAGYU="0.5.0 (Internal)" TOPOLOGY RASTER

Docker

select postgis_full_version() 

POSTGIS="3.2.2 628da50" [EXTENSION] PGSQL="130" GEOS="3.9.0-CAPI-1.16.2" PROJ="7.2.1" LIBXML="2.9.10" LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" TOPOLOGY

Now here is where I get confused. When I run the same ST_TRANSFORM as above on the Docker image, I get exactly what I need.

select st_transform(st_setsrid(ST_POINT(-99.723991251, 35.954241741), 4267), 4326)

-99.7243815258533 35.95428699761289 -- ESRI Transformed (Control)
-99.7243815258398 35.95428699761243 -- Official PostGIS Docker Image Transformed - proj 7.2.1

-99.7243847871355 35.954284680650595 -- Result of ST_TRANSFORM on CentOS7

I have done the following:

• Ensured proj-data grids are installed. Didn't help.
• Removed proj-data grids and copied grids from Docker to CentOS7 proj location. Still no luck.
• Altered spatial_ref_sys entries for 4267 and 4326 (NAD27 and WGS84, respectively) with no luck either.

Can someone on the PostGIS team please help me understand the differences? My company relies on ST_TRANSFORM from NAD27 to WGS84 and right now we are stuck. Unfortunately, we cannot deploy the Docker container for production use, we must use CentOS7 install.

Thanks for your time and your help! Judson Crouch

[pramsey]

This is not a PostGIS problem but almost certainly a packaging problem in wherever you are getting the Centos7 builds from. Where is that?

[robe]

Okay my answers deviate everywhere, kind of troubling.

SELECT ST_AsText(st_transform(st_setsrid(ST_POINT(-99.723991251, 35.954241741), 4267), 4326));

On my Centos: {{ POSTGIS="3.1.6 cde4b6b" [EXTENSION] PGSQL="120" GEOS="3.9.2-CAPI-1.14.3" SFCGAL="1.3.1" PROJ="7.2.1" LIBXML="2.9.1" LIBJSON="0.11"}} from yum.postgresql.org

 POINT(-99.72437633585803 35.95430452419984)

On my Ubuntu Jammy {{ POSTGIS="3.2.2 628da50" [EXTENSION] PGSQL="140" GEOS="3.10.2-CAPI-1.16.0" PROJ="8.2.1" GDAL="GDAL 3.4.1, released 2021/12/27" LIBXML="2.9.13" LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" RASTER}} from apt.postgresql.org # this agrees with docker and is most right I guess

POINT(-99.72438152583983 35.95428699761243)

and on my windows: POSTGIS="3.2.2 3.2.2" [EXTENSION] PGSQL="140" GEOS="3.10.3-CAPI-1.16.1" PROJ="7.2.1" LIBXML="2.9.9" LIBJSON="0.12" LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)" — this is way different, but agreets with the CentOS PROJ 7.2.1 install, so might be nature of PROJ 7.2.1

POINT(-99.72437633585803 35.95430452419984)

I'll try to upgrade my centos 7 to latest to see if I get same answer as reporter. The fact that both my current CentOS and my windows are running 7.2.1 I assume is the issue with those.

But anyrate, this is clearly not a PostGIS issue, so reset to PostGIS Packaging.

Side note examples are not 10-12 feet apart. Even in my worsed case that is 6 ft (that of PROJ 7.2.1), and the one the reporter is using is 1.4 ft.

[robe]

I tried with a fresh centos 7 install and installing PG13 and PostGIS 3.2.2 from yum.postgresql.org.

I still get PROJ 7.2.1 with answers that match my CentOS 12 and Windows install.

Where did you get your PROJ from? Did you install yourself?

I also misread your docker output and thought that was running PROJ 8, but I see it too is running PROJ 7.2.1 and not 8.2.1 like my Jammy which agrees with it.

I guess whatever is being left out in the Yum proj packaging, I'm probably missing in my windows packaging as well since our answers agree.

I know that I had built my windows packaging with ​https://download.osgeo.org/proj/proj-datumgrid-north-america-1.2.zip, but there is a newer one ​https://download.osgeo.org/proj/proj-datumgrid-north-america-1.4.zip.

I'm going to give that a try to see if it fixes the answers on my windows setup.

[robe]

Upgrading with new world files didn't seem to help. However I did find out that if I copy these files (in the attached zip) which are missing in my install for some reason (I have to troubleshoot why), then my Windows install starts giving answers more along the lines of what you were hoping.

select ST_AsText(st_transform(st_setsrid(ST_POINT(-99.723991251, 35.954241741), 4267), 4326))

now return:

POINT(-99.72438152583983 35.95428699761243)

So definitely one of the files there. before it was returning:

POINT(-99.72437633585803 35.95430452419984)

So try copying the attached files to your /usr/share/proj folder and see if that fixes your issue.

[robe]

missing files