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.

  • 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" Nov 2 '12 at 8:31
  • 1
    drop that constraint. and its fixed Nov 6 '12 at 8:49

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

ALTER TABLE mytable 
  TYPE Geometry(Point, 32644) 
  USING ST_Transform(geom, 32644);
  • Noting of course that you should replace "Point" with the actual geometry type of your geometry. 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 '15 at 4:01
  • 1
    No, afraid not. Jan 13 '15 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.

  • I tried as above and succeeded, but I'm unable to export the resultant table into qgis/udig even it's appearing in database? Nov 2 '12 at 10:58
  • What is your exact problem?
    – Vladimir
    Nov 2 '12 at 11:10
  • 2
    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. 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...

  • 4
    This solution not reproject geometries. If geometries are stored using other SRID, data will be inconsistence after change constrains. You need use st_trasnform. Nov 12 '12 at 8:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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