[postgis-users] changing a non spatial table to a spatial talble

[Stephen Woodbridge]

Kirk R. Wythers wrote:
> I am new to postgis. I am running GRASS GIS with a a postgresql  
> attribute table which contains x,y coordinates and point data. I  would 
> like to see if I could get this table spatially indexed. I have  postgis 
> installed, but no geometry columns inserted yet.
> 
> Can I alter a column that holds at UTM coordinate from double  precision 
> to geometry, or should I add a new spatial columns for the  x and y's 
> and then copy them into the new column?
> 
> This is one of those, "I want to change a non spatial table to a  
> spatial table" questions? Can I alter this table in any way, or  should 
> I start over?

You should do something like:

select 
addgeometrycolumn('your_schema','your_table','the_geom',<srid_for_your_utm_zone>,2);
update your_table set the_geom=setsrid(makepoint(<x_column>, 
<y_column>), <srid_for_your_utm_zone>);
CREATE INDEX <your_index_name> ON your_table USING gist (the_geom);
vacuum analyze your_table;

-Steve W.