46

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.

3
  • 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
79
+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);
3
  • 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
19
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.

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

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

1
  • 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

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .