How to reproject all geometries in a 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.

Answer 1

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

Answer 2

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.

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

Answer 4

Taking a look at a very old (2005) posting from the Postgis User mailing list, if you want to reproject your existing table in-place, I think you would do the following:

SELECT UpdateGeometrySRID('<table>', '<geom>', 32644);
UPDATE <table> SET <geom> = ST_TRANSFORM(ST_SetSRID(<geom>, 2249), 26986);

Is a better way to do this in more current versions of PostGIS though? I'm not sure...