[postgis-users] Reprojecting a postgis layer

[Stephen Woodbridge]

Marcus C. England wrote:
> Hi all,
> 
> I have spent the last three hours trying to find a plain-English 
> description of how you change the projection of a PostGIS layer, and I 
> am now giving up. I have found quite a few questions about this on the 
> internet, but not a single answer that allows for a newbie to SQL. Every 
> answers states something like "transform (arguments)"... what arguments?
> 
> So, here it goes. I have a layer called "california_counties". The 
> layer's current SRID is correctly set at 3309. I want to transform the 
> layer into SRID 26911. How do I do that? It seems it should be so simple.

There are a copy of different concepts and issues here which are 
probably tripping you up.

o you have a table and it has column of data in some project, in your 
case SRID 3309

Typically you do not change the projection of a column. It is possible 
by can be tricky and complicated. But that should not stop you, you have 
a couple of choices:

1) create a new column and load it from the old column but in the new 
projection

select addgeometrycolumn('schema', 'table', 'new_geom', 26911, 
'MULTIPOLYGON', 2);

update "schema"."table" set new_geom = transform(the_geom, 26911);

now you have the column loaded with in the new projection. You might 
want to index it, and probably vaccum analyze "schema"."table";


2) just use the original column but transform it on the fly to the 
projection you want.

In the example UPDATE statement we did on the fly transform of the data. 
You can do the same thing in SELECT statements also.

Hope this helps some.

-Steve