[postgis-users] Help updating SRID values...

[Paul Ramsey]

For the last step, do not 'select into..'.
Instead, 'insert into yourtable select * from tmptable'

Bill Wheaton wrote:
> PostGISers:
> 
> I have to change (update) the SRID in a geometry column. I've searched 
> for methods of doing this and came across some posts from about a year 
> ago...The procedure suggested by John Reid and Dave Blasby below seems 
> to work EXCEPT for the last step...SELECT INTO only seems to allow you 
> to create a NEW table not move records from one table to another. This 
> is probably a simple postgreSQL question, but I'm a stumped newbie. The 
> procedure I'm trying to follow is below. Again, I'm just trying to 
> update the SRID for a geometry column (had a dumb typo when loading the 
> data). If there is an easier way, I'd love to hear about it.
> 
> Any help is greatly appreciated...
> 
> Bill
> ---------------------------------------------------------------------------- 
> 
> 
> Alternatively, here's another method that should work for you:
> 
> 1. Make a copy of your table (this will have no contraints on it):
> create table tmp_table as select * from <table>;
> 2. DropGeometryColumn() on your old table to de-register the geometry in
> the geometry_columns table
> 3. drop the old table
> 4. create the table again
> 5. AddGeometryColumn() to create & register the geometry column. Make
> sure you set the parameters correctly.
> 6. If you're changing SRIDs you'll have to:
> update table tmp_table set <geometry column> = setSRID(<geometry
> column>,<srid>)
> 7. use "select into..." to put the rows in tmp_table into the new table
> 

-- 
       __
      /
      | Paul Ramsey
      | Refractions Research
      | Email: pramsey at refractions.net
      | Phone: (250) 885-0632
      \_