Add an ESRI projected coordinate system to PostGIS

Question

I was trying to add to PostGIS a new projected coordinate system, the ESRI World_Cylindrical_Equal_Area, 54034. I have searched for it in epsg.io but it is not available there. I would have liked to use epsg.io to generate a copy/paste code I would have uses to enable the projection in PostGIS. So, I have imported in a PostGIS db a file gdb containing a feature class projected in 54034 and this coordinate system has been automatically added into the spatial_ref_sys table, with the srid=900914, auth_name=Esri, auth_srdid=54034. Did I still a achieved my goal? Can I use now ST_Transform (geom, 900914) to project all the data in my db to the newly added coordinate system?

Answer 1

Can I use now ST_Transform (geom, 900914) to project all the data in my db to the newly added coordinate system?

1)
Yes you can. And this was because the importer you used (presumably shape2sql or ogr2ogr?) had UPDATEd (i.e., overwritten) the original srid=900913 entry in the spatial_ref_sys table with the source data's WKT and PROJ entries. The importer did this because there is no EPSG coordinate system code equivalent to the ESRI WKID 54034. (ogr2ogr version tested was GDAL 3.0.4, released 2020/01/28.)

Interestingly, if you now import into PostgreSQL/PostGIS another data set with yet another coordinate system that has no equivalent EPSG coordinate system code, the importer will INSERT a new entry with srid=900915, and so on.

2)
However, in my testing, when I "create extension postgis" in another database, PostGIS quickly restored the srid=900913 entry in the original spatial_ref_sys table, thereby losing the source data's WKT and PROJ values. Hence, if you plan to keep the data around for some time, it might be a good idea to change srid=900913 in spatial_ref_sys to an arbitrary value (that doesn't clash with existing srids) and then doing UpdateGeometrySRID on the imported table.

3)
Having said all the above, if your intention is just merely to be able to project your data to WKID 54034, then the below should do :-

select your_column, st_transform(geometry_column, '+proj=cea +lat_ts=0 +lon_0=0 +x_0=0 +y_0=0 +datum=WGS84 +units=m +no_defs') geometry_column from your_table;