Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question
    
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" –  satya chandra Nov 2 '12 at 8:31
1  
drop that constraint. and its fixed –  simplexio Nov 6 '12 at 8:49
2  
@satyachandra You might want to improve your accept rate btw. Otherwise people might think twice before answering your questions next time. –  R.K. Dec 4 '12 at 12:17

4 Answers 4

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);
share|improve this answer
    
Noting of course that you should replace "Point" with the actual geometry type of your geometry. –  Paul Ramsey Nov 6 '12 at 18:15
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.

share|improve this answer
    
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 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. –  HeyOverThere Nov 2 '12 at 14:57

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

share|improve this answer
1  
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 8:52

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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