How to change the SRID of exisisting data in PostGIS?

Question

While importing my shapefile data to PostGIS, I did not select the proper Projection.

How do I now change the SRID of the data, without transforming the Coordinates?

Answer 1

This is a four stage process:

1. In the geometry_columns table, update the SRID to the required value.

2. Drop the contraint on the table, by using the following SQL statement

   ALTER TABLE mytable DROP CONSTRAINT enforce_srid_the_geom;

3. Update the SRID'd of the geometry by using the following SQL statement

   UPDATE mytable SET the_geom = ST_SetSRID(the_geom, newSRID);

4. Add the contraint back by using the following SQL statement

   ALTER TABLE mytable

   ADD CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = (newSRID));

Or if you want to get it done in one line, you can use the following function:

 select UpdateGeometrySRID('Schema Name', 'mytable', 'the_geom', newSRID) ;