Reprojecting all geometries in PostGIS table?
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);
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.
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...