Reprojecting all geometries in PostGIS table?

Question

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 SET the_geom=ST_Transform(the_geom,32644); (Assuming you have the complete entry in your spatial_ref_sys for 32644.) — L_Holcombe, Nov 02 '12 at 08: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 02 '12 at 08:31

score 84 · Accepted Answer · edited Jan 18 '19 at 06:21

84

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);

edited Jan 18 '19 at 06:21

Evan Carroll

7,071
2
32
58

answered Nov 05 '12 at 17:23

Paul Ramsey

19,865
1
47
57

Noting of course that you should replace "Point" with the actual geometry type of your geometry. – Paul Ramsey Nov 06 '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 04:01
1

No, afraid not. – Paul Ramsey Jan 13 '15 at 02: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 08 '23 at 15:15

score 21 · Answer 2 · edited Nov 05 '12 at 20:38

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.

edited Nov 05 '12 at 20:38

underdark

84,148
21
231
413

answered Nov 02 '12 at 09:10

Vladimir

1,569
7
15

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 02 '12 at 10:58
What is your exact problem? – Vladimir Nov 02 '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 02 '12 at 14:57

score 3 · Answer 3 · answered Nov 05 '12 at 17:42

follow this way:

CREATE TABLE 'new_table' AS SELECT * FROM 'old_table';
ALTER TABLE new_table DROP CONSTRAINT enforce_srid_the_geom;
ALTER TABLE new_table DROP CONSTRAINT enforce_geotype_the_geom;
UPDATE new_table SET the_geom = ST_SetSRID(the_geom, new_srid);
ALTER TABLE new_table ADD CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = (new_srid));
ALTER TABLE new_table ADD CONSTRAINT enforce_geotype_geom CHECK ((geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
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...

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 08:52

Reprojecting all geometries in PostGIS table?

3 Answers3

Linked

Related