PostGIS Projection Project

PostGIS is a wonderful suite of tools, which really anchors the open source web mapping toolkit.  However, it doesn’t always integrate well with an ArcGIS desktop environment.  One obvious source of conflict relates to projections – most of ArcGIS’s projections are infinitesimally different than their real world counterparts.  As an example, consider the Washington North State Plane coordinate system.  Although the official unit of the State Plane system is the meter, the projection has also been defined using the foot.  The Spatial Reference ID (SRID) of the meter version is 2855, while the foot version is 2926.  However, the version of the coordinate system that ArcGIS uses is SRID 102748.  Unfortunately, this projection, which almost all Washington government agencies use, is undefined in PostGIS.

The good news is that you can add new projections to PostGIS.  It’s neither simple nor intuitive, but it is quite possible.  The following is a set of instructions for adding projection 102748.  I’m assuming you have a default installation of PostGIS and total access to your server.

Adding a projection is accomplished by modifying the spatial_ref_sys table, which is part of the template_postgis database.  But by default, that database doesn’t allow connections.  So you’ll need to connect to template1, as the user postgres:

psql -d template1

Modify template_postgis so that it allows connections:

UPDATE pg_database SET datallowconn = TRUE WHERE datname = ‘template_postgis’;

Now you can connect (switch) to template_postgis:

\c template_postgis

The code to actually add the projection is long and complicated.  Fortunately, spatialreference.org supplies us with the perfect code.  Just click ‘PostGIS spatial_ref_sys INSERT statement’ and copy the code:

INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) values ( 9102748, ‘esri’, 102748, ‘+proj=lcc +lat_1=47.5 +lat_2=48.73333333333333 +lat_0=47 +lon_0=-120.8333333333333 +x_0=500000.0000000002 +y_0=0 +ellps=GRS80 +datum=NAD83 +to_meter=0.3048006096012192 +no_defs ‘, ‘PROJCS[“NAD_1983_StatePlane_Washington_North_FIPS_4601_Feet”,
GEOGCS[“GCS_North_American_1983”,DATUM[“North_American_Datum_1983”,
SPHEROID[“GRS_1980”,6378137,298.257222101]],PRIMEM[“Greenwich”,0],
UNIT[“Degree”,0.017453292519943295]],PROJECTION[“Lambert_Conformal_Conic_2SP”],
PARAMETER[“False_Easting”,1640416.666666667],PARAMETER[“False_Northing”,0],
PARAMETER[“Central_Meridian”,-120.8333333333333],PARAMETER[“Standard_Parallel_1”,47.5],
PARAMETER[“Standard_Parallel_2”,48.73333333333333],PARAMETER[“Latitude_Of_Origin”,47],
UNIT[“Foot_US”,0.30480060960121924],AUTHORITY[“EPSG”,”102748″]]’);

All done, let’s switch back to template1 and reset template_postgis:

\c template1
UPDATE pg_database SET datallowconn = FALSE WHERE datname = ‘template_postgis’;

These changes should take effect once the server is restarted.

So is it worth all the work? Sorta. Using one of the more standard SRIDs is plenty accurate – I think the only real difference is a few decimal places. But I think it’s important to be vigilant, especially when dealing with projections. Precision, accuracy, and internal consistency are the cornerstones of science and repeatability.

Comments are closed.