Sign up ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I created a spatial table with SRID:4326. Now I want to change total projection to SRID:32644 into a new table. The old table should remain unchanged.

share|improve this question
I'm adding this as a comment instead of an answer because there should be a more elegant method. But you could copy the table, and then run: UPDATE <table> SET the_geom=ST_Transform(the_geom,32644); (Assuming you have the complete entry in your spatial_ref_sys for 32644.) –  L_Holcombe Nov 2 '12 at 8:05
i tried but igot this error update abc SET geom=ST_Transform(geom,32644); new row for relation "abc" violates check constraint "enforce_srid_geom" –  satya chandra Nov 2 '12 at 8:31
drop that constraint. and its fixed –  simplexio Nov 6 '12 at 8:49

3 Answers 3

up vote 35 down vote accepted

If you're on PostGIS 2.0+, you can go:

 ALTER TABLE mytable 
   TYPE Geometry(Point, 32644) 
   USING ST_Transform(geom, 32644);
share|improve this answer
Noting of course that you should replace "Point" with the actual geometry type of your geometry. –  Paul Ramsey Nov 6 '12 at 18:15
Is there a simple way to replace Point with The same geometry type as it was? –  Mohayemin Jan 11 at 4:01
No, afraid not. –  Paul Ramsey Jan 13 at 2:23
CREATE TABLE new_table AS 
  SELECT ST_Transform(the_geom,32644) AS the_geom 
  FROM original_table;

There should be an integer ID field in your spatial table in order to add it to QGIS.

share|improve this answer
I tried as above and succeeded, but I'm unable to export the resultant table into qgis/udig even it's appearing in database? –  satya chandra Nov 2 '12 at 10:58
What is your exact problem? –  Vladimir Nov 2 '12 at 11:10
If you are using a version of PostGIS older than version 2.0 you'll need to add a record to your Geometry_Columns table pointing to your new table. –  HeyOverThere Nov 2 '12 at 14:57

follow this way:

  1. CREATE TABLE 'new_table' AS SELECT * FROM 'old_table';
  2. ALTER TABLE new_table DROP CONSTRAINT enforce_srid_the_geom;
  3. ALTER TABLE new_table DROP CONSTRAINT enforce_geotype_the_geom;
  4. UPDATE new_table SET the_geom = ST_SetSRID(the_geom, new_srid);
  5. ALTER TABLE new_table ADD CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = (new_srid));
  6. ALTER TABLE new_table ADD CONSTRAINT enforce_geotype_geom CHECK ((geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
  7. That is it!

if you cannot create new table in first line pls try 2. and 3. first then create your table with number 1.

i hope it helps you...

share|improve this answer
This solution not reproject geometries. If geometries are stored using other SRID, data will be inconsistence after change constrains. You need use st_trasnform. –  angelcervera Nov 12 '12 at 8:52

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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