<HTML><BODY style="word-wrap: break-word; -khtml-nbsp-mode: space; -khtml-line-break: after-white-space; "><BR><DIV><DIV>On Feb 3, 2006, at 12:36 PM, Stephen Woodbridge wrote:</DIV><BR class="Apple-interchange-newline"><BLOCKQUOTE type="cite"><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Kirk R. Wythers wrote:</DIV> <BLOCKQUOTE type="cite"><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">I am new to postgis. I am running GRASS GIS with a a postgresql<SPAN class="Apple-converted-space"> </SPAN>attribute table which contains x,y coordinates and point data. I<SPAN class="Apple-converted-space"> </SPAN>would like to see if I could get this table spatially indexed. I have<SPAN class="Apple-converted-space"> </SPAN>postgis installed, but no geometry columns inserted yet.</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Can I alter a column that holds at UTM coordinate from double<SPAN class="Apple-converted-space"> </SPAN>precision to geometry, or should I add a new spatial columns for the<SPAN class="Apple-converted-space"> </SPAN>x and y's and then copy them into the new column?</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">This is one of those, "I want to change a non spatial table to a<SPAN class="Apple-converted-space"> </SPAN>spatial table" questions? Can I alter this table in any way, or<SPAN class="Apple-converted-space"> </SPAN>should I start over?</DIV> </BLOCKQUOTE><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><BR></DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">You should do something like:</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><BR></DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">select addgeometrycolumn('your_schema','your_table','the_geom',<srid_for_your_utm_zone>,2);</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">update your_table set the_geom=setsrid(makepoint(<x_column>, <y_column>), <srid_for_your_utm_zone>);</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">CREATE INDEX <your_index_name> ON your_table USING gist (the_geom);</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">vacuum analyze your_table;</DIV></BLOCKQUOTE></DIV><DIV>Thanks for the reply Steve. Two questions:</DIV><DIV><BR class="khtml-block-placeholder"></DIV><DIV>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)?</DIV><DIV><BR class="khtml-block-placeholder"></DIV><DIV>2. where do I find srids? I am in utm zone 15. Looks like those are in spatial_ref_sys? </DIV><DIV><BR class="khtml-block-placeholder"></DIV><DIV>3. ok... 3 questions, what does the 2 at the end of the select statement refer to? Is that x and y?</DIV><FONT class="Apple-style-span" face="Verdana" size="4"><SPAN class="Apple-style-span" style="font-size: 13.3px;"></SPAN></FONT></BODY></HTML>