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

[Kirk R. Wythers]

On Feb 3, 2006, at 12:36 PM, Stephen Woodbridge wrote:

> 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;
Thanks for the reply Steve. Two questions:

1. What is 'the_geometry'? Is this just a column name to hold the  
geometry or is it something specific. For the table in question, I  
have x,y coordinates. So are you talking about something like  
wtk_geom (for text bases geometery)?

2. where do I find srids? I am in utm zone 15. Looks like those are  
in spatial_ref_sys?

3. ok... 3 questions, what does the 2 at the end of the select  
statement refer to? Is that x and y?
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20060203/c6e79a86/attachment.html>