[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:  closed
  Priority:  high     |  Milestone:  PostGIS 3.1.9
 Component:  postgis  |    Version:  3.1.x
Resolution:  wontfix  |   Keywords:
----------------------+---------------------------
Comment (by robe):

 Replying to [comment:4 jbkoch]:
 > @robe Thank you for the information as I was not aware of the precedence
 used. This is somewhat of a special case as this is happening in the
 official Amazon RDS Postgresql instances of which I have no control over
 packaging. It would appear that something is going wrong in their
 dependency stack to not have the correct projection info in proj.db for
 PROJ 8.0.1. I am not sure who manages that project or who would be the
 correct person to contact about this issue?


 At jbkoch, are you saying the 8.0.1 on Amazon RDS is wrong?  I thought you
 were complaining about the proj 5.2.0 on your 3.1.5 install which would be
 wrong, because we ship a different proj4, and your 3.1.5 is using proj
 5.2.0, which does use the proj4text in spatial_ref_sys.

 If there is an issue with what AMZ is shipping, I can talk with the AMZ
 packaging group about that.

 When you said

 > I was able to fix the issue by using the PROJ.4 string defined in PROJ
 8.0.1

 Copy this where?  If you updated your spatial_ref_sys on your AMZ RDS
 (running PROJ 8.0.1) with this proj4text, it shouldn't have made a
 difference as it would still be using the proj.db shipped with PROJ, and
 would only fall back on spatial_ref_sys, if that entry was entirely
 missing in the proj.db that Amazon ships.

 On my Amazon RDS database running PROJ 8.0.1:

 {{{
 POSTGIS="3.3.2 4975da8" [EXTENSION] PGSQL="150" 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)" TOPOLOGY
 RASTER
 }}}


 I get:


 {{{
 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.2894197142 2051852.3578379445,145979.74635016784
 2051853.2334285255,145980.8230537888 2051823.3784043754,145954.36613142194
 2051822.5028163139,145953.2894197142 2051852.3578379445))
 }}}

 Which corresponds to your second answer.

 If I run:


 {{{
     UPDATE spatial_ref_sys SET proj4text = '+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'
     WHERE srid=5070;
 }}}

 and check the results, as I would expect, I get the same answer:


 {{{
 POLYGON((145953.2894197142 2051852.3578379445,145979.74635016784
 2051853.2334285255,145980.8230537888 2051823.3784043754,145954.36613142194
 2051822.5028163139,145953.2894197142 2051852.3578379445))
 }}}


 This should only make a difference if you are updating your 3.1.5 instance
 which ships with PROJ 5.2.0 (predates the existence of the new proj api,
 so uses the spatial_ref_sys.proj4text)
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5353#comment:7>
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.