38

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

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

ALTER TABLE mytable 
  ALTER COLUMN geom 
  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. – 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 '15 at 4:01
  • No, afraid not. – Paul Ramsey Jan 13 '15 at 2:23
17
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? – Hyma 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. – HeyOverThere Nov 2 '12 at 14:57
4

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...

  • 3
    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 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.