Adding the US Contiguous Albers USGS (or another) CRS to PostGIS

Post date: Nov 26, 2014 1:58:30 AM

PostGIS databases contain a table in the public schema called spatial_ref_sys. Only data that have coordinate reference systems that are also in that table can be added to the database. Unfortunately, PostGIS does not support the CRS we use for our data, the US Contiguous Albers Equal Area Conic USGS (WKID: 102039).

Fortunately, with a simple SQL insert, we can add a CRS to the table. Specifically, for this CRS, the steps are as follows:

• 1. Open a command prompt as an administrator

  2. If the PostgreSQL bin folder is not in the system path, cd the bin folder (on Atlas: C:\Program File\PostgreSQL\9.0\bin)

  3. Run the command "psql -U postgres [name_of_postgis_database]" and enter the password at the prompt. This will connect to your PostGIS database as the postgres admin user.

  4. Now, insert the USGS CRS record via

  5. INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) values ( 102039, 'ESRI', 102039, '+proj=aea +lat_1=29.5 +lat_2=45.5 +lat_0=23 +lon_0=-96 +x_0=0 +y_0=0 +datum=NAD83 +units=m +no_defs ', 'PROJCS["USA_Contiguous_Albers_Equal_Area_Conic_USGS_version",GEOGCS["GCS_North_American_1983",DATUM["D_North_American_1983",SPHEROID["GRS_1980",6378137.0,298.257222101]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]],PROJECTION["Albers"],PARAMETER["False_Easting",0.0],PARAMETER["False_Northing",0.0],PARAMETER["Central_Meridian",-96.0],PARAMETER["Standard_Parallel_1",29.5],PARAMETER["Standard_Parallel_2",45.5],PARAMETER["Latitude_Of_Origin",23.0],UNIT["Meter",1.0],AUTHORITY["ESRI",102039]]');

If another CRS is needed, ArcGIS can export the CRS to a text file, the contents of which can be used for the srtext value. To do so, right-click the CRS in any Coordinate System window (such as the tab in the data frame properties) and choose "Save As". Getting the proj4text from this file is easy, assuming GDAL is available. Running the command "gdalsrsinfo [path_to_.prj_file_from_arc]" will output properties of the CRS in the .prj text file exported from Arc, including the proj4 string.