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

[Stephen Woodbridge]

Kirk R. Wythers wrote:
> 
> 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)?

the_geom is just the name of the geometry column. It can be anything but 
I always call it the same so it is obvious where it is.

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

 From the proj epsg file it is likely one of these. the SRID would be 
the first number in <>, ie: 2027 for the first entry below. You should 
probably verify these with the values int the spatial reference system 
table.

# NAD27(76) / UTM zone 15N
<2027> +proj=utm +zone=15 +ellps=clrk66 +units=m +no_defs  <>

# Pulkovo 1995 / Gauss-Kruger 15N
<20075> +proj=tmerc +lat_0=0 +lon_0=87 +k=1.000000 +x_0=500000 +y_0=0 
+ellps=krass +units=m +no_defs  <>

# Beijing 1954 / Gauss-Kruger 15N
<21475> +proj=tmerc +lat_0=0 +lon_0=87 +k=1.000000 +x_0=500000 +y_0=0 
+ellps=krass +units=m +no_defs  <>

# NAD27 / UTM zone 15N
<26715> +proj=utm +zone=15 +ellps=clrk66 +datum=NAD27 +units=m +no_defs  <>

# NAD83 / UTM zone 15N
<26915> +proj=utm +zone=15 +ellps=GRS80 +datum=NAD83 +units=m +no_defs  <>

# NAD27 / BLM 15N (ftUS)
<32065> +proj=tmerc +lat_0=0 +lon_0=-93 +k=0.999600 
+x_0=500000.001016002 +y_0=0 +ellps=clrk66 +datum=NAD27 
+to_meter=0.3048006096012192 +no_defs  <>

# NAD27 / BLM 15N (feet)
<32075> +proj=tmerc +lat_0=0 +lon_0=-93 +k=0.999600 
+x_0=500000.001016002 +y_0=0 +ellps=clrk66 +datum=NAD27 
+to_meter=0.3048006096012192 +no_defs  <>

# WGS 72 / UTM zone 15N
<32215> +proj=utm +zone=15 +ellps=WGS72 +units=m +no_defs  <>

# WGS 72BE / UTM zone 15N
<32415> +proj=utm +zone=15 +ellps=WGS72 +towgs84=0,0,1.9,0,0,0.814,-0.38 
+units=m +no_defs  <>

# WGS 84 / UTM zone 15N
<32615> +proj=utm +zone=15 +ellps=WGS84 +datum=WGS84 +units=m +no_defs  <>

> 3. ok... 3 questions, what does the 2 at the end of the select statement 
> refer to? Is that x and y?

The 2 means dimension = 2, ie X,Y. This is all documented in the postGIS 
reference manual which can be found on the postGIS site.

> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users