47

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

3 Answers 3

84
+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);
4
  • Noting of course that you should replace "Point" with the actual geometry type of your geometry. Nov 6, 2012 at 18:15
  • Is there a simple way to replace Point with The same geometry type as it was?
    – Mohayemin
    Jan 11, 2015 at 4:01
  • 1
    No, afraid not. Jan 13, 2015 at 2:23
  • Does this play well with existing indexes? I.e. if I have an index on column geom whilst it was in 4326, and then I transform to 32644 with this method, does my existing index need to be recreated?
    – Him
    Dec 8, 2023 at 15:15
21
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, 2012 at 10:58
  • What is your exact problem?
    – Vladimir
    Nov 2, 2012 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, 2012 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, 2012 at 8:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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