Skip to content
October 8, 2007 / Steven Pousty

Updating Projections and PostGIS

I imported some data from the MAGIC server at UCONN into PostGIS. Unfortunately it did not recognize the projection in the prj file or shp2pgsql does use the projection information associated with the shapefile. [UPDATE: bitner just helped me see what was right in front of my eyes the whole time – shp2pgsql has a -s flag to specify the SRID. Live and learn] When I imported the data it has a SRID (projection identifier) of -1. I hopped on the IRC channel and the good folks helped me figure out all the places I need to update the information and now for my own benefit I am going to write it out here. My table name is leach (from the leachate data) and the geometry column is titled: the_geom.

There are 3 places that store the SRID for the dataset.
1. In the geometry column:
First place

2. As a constraint on the table for the geometry column:
Second place

3. In the geometry_columns system table:
third place

So we need to update all of these to the correct projection SRID (in this case CT State Plane NAD 83 Feet) which for PostGIS is SRID 2234. I will defer to another day how you figure out which SRID you need.
Here are the statements to carry it out:

1. Drop the constraint otherwise you can’t do any changes on the leach table:
ALTER TABLE leach DROP CONSTRAINT “enforce_srid_the_geom” RESTRICT;

2. Alter the geometry column to incorporate the new SRID:
UPDATE leach SET the_geom = ST_SetSRID(the_geom, 2234);

3. Reapply the contstraint on the geometry:
ALTER TABLE leach ADD CONSTRAINT “enforce_srid_the_geom” CHECK (SRID(the_geom)=2234);

4. Update the geometry column table to have the right SRID:
UPDATE geometry_columns SET SRID=2234 WHERE f_table_name=’leach’;

And now you should be good to go. Thanks again to bitner and hobu in the IRC channel and this discussion post from the PostGIS-user forum.

As a feature request – perhaps someone could write the pgsql to do all these steps at once and bundle it in the product. [UPDATE 2 – of course after writing this post I come across this method: UpdateGeometrySRID([<schema_name>], <table_name>, <column_name>, <srid>) – which Updates the SRID of all features in a geometry column updating constraints and reference in geometry_columns. /me tries to remove egg from face]  Or perhaps the shp2pgsql needs to do a better job importing the projection info if there is a .prj file present – though I suspect this would be a much trickier problem.


One Comment

Leave a Comment
  1. sfletche / Feb 12 2015 12:05 pm

    Thanks Steve! Working on an old version of PostGIS and your solution was just what I was looking for. 🙂

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: