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?


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

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.

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.

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;
  • I have used ogr2ogr. Thank you for the very useful recommendations, I certenaly want to keep the ESRI 54034 coordinate system, so I will cange 900914 srid to 54034 (it is not assigned). In fact I don't need to work on the imported data, it was just a try to add the 54034 to PostGIS and it worked. I need to reproject WGS84 datasets – Luigi Falco May 22 '20 at 8:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.