[postgis-tickets] [PostGIS] #5353: EPSG:5070 mismatch between spatial_ref_sys and PROJ

[PostGIS]

#5353: EPSG:5070 mismatch between spatial_ref_sys and PROJ
---------------------+--------------------------
 Reporter:  jbkoch   |      Owner:  pramsey
     Type:  defect   |     Status:  new
 Priority:  medium   |  Milestone:  PostGIS GDAL
Component:  postgis  |    Version:  3.1.x
 Keywords:           |
---------------------+--------------------------
 I discovered a coordinate transformation issue with projection EPSG:5070
 when upgrading an Amazon RDS instance from/to:
 {{{
 POSTGIS="3.1.5 c60e4e3" [EXTENSION] PGSQL="130" GEOS="3.9.1-CAPI-1.14.2"
 PROJ="Rel. 5.2.0, September 15th, 2018" GDAL="GDAL 2.4.4, released
 2020/01/08" LIBXML="2.9.1" LIBJSON="0.13.1" LIBPROTOBUF="1.3.2"
 WAGYU="0.5.0 (Internal)" RASTER
 }}}
 {{{
 POSTGIS="3.1.7 aafe1ff" [EXTENSION] PGSQL="140" GEOS="3.9.1-CAPI-1.14.2"
 PROJ="8.0.1" GDAL="GDAL 3.4.3, released 2022/04/22" LIBXML="2.9.1"
 LIBJSON="0.15" LIBPROTOBUF="1.3.2" WAGYU="0.5.0 (Internal)" RASTER
 }}}

 Running the following query on both systems produces mismatched results:
 {{{#!sql
 --
 select
   st_astext( geom_5070 )
 from st_geomfromtext(
   'POLYGON((
     -94.23933949466884 41.46010784655825,
     -94.23902022173033 41.46011128632304,
     -94.23901390824655 41.45984441537449,
     -94.23933317987697 41.45984097564519,
     -94.23933949466884 41.46010784655825))', 4326 ) geom_4326,
     st_transform( geom_4326, 5070 ) geom_5070;
 }}}
 {{{
 POLYGON((145953.8164326792 2051852.6976375678,145980.27310072441
 2051853.5729657637,145981.34988068073
 2051823.7175862629,145954.89322071325 2051822.842260323,145953.8164326792
 2051852.6976375678))
 }}}
 {{{
 POLYGON((145953.2894197142 2051852.3578379445,145979.74635016784
 2051853.2334285255,145980.8230537888 2051823.3784043754,145954.36613142194
 2051822.5028163139,145953.2894197142 2051852.3578379445))
 }}}

 I was able to fix the issue by using the PROJ.4 string defined in PROJ
 8.0.1:
 {{{
 projinfo EPSG:5070

 +proj=aea +lat_0=23 +lon_0=-96 +lat_1=29.5 +lat_2=45.5 +x_0=0 +y_0=0
 +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +units=m +no_defs +type=crs
 }}}

 I'm not sure if there are other projections that need updated as well,
 e.g., ESRI:102039 is the same as EPSG:5070?
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5353>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.