Thank you Etienne. That may be the way to go.<br><br>The problem is that one table has the necessary attributes and another table has the x,y coordinates. They need to be separate tables. Also, I'm using EPSG:26918 (UTM Z18 meters). Basic structures of both tables are:<br>
<br>Table1: StationID, easting, northing, elevation, owner, date (our complete inventory table)<br>
Table2: StationID, value1, value2, value3, etc... (a data table with observations)<br><br><br>Table2 gets updated on a more frequent basis, daily to weekly. Table1 only a few times per year. <br><br>Could I add a geom column to Table1 and then create a view that brings in the data values I want to map? I am not sure if packages such as QGIS, MapServer, etc.., can read the view as a spatial class. Or maybe create a third table that brings in fields from Table1 and Table2, and then adds a geom column? <br>
<br>- John<br><br clear="all">**************************************************<br>John Callahan, Research Scientist<br>Delaware Geological Survey, University of Delaware<br>URL: <a href="http://www.dgs.udel.edu" target="_blank">http://www.dgs.udel.edu</a><br>
**************************************************<br>
<br><br><div class="gmail_quote">On Wed, Jan 19, 2011 at 10:28 AM, Etienne Bellemare <span dir="ltr"><<a href="mailto:etiennebr@gmail.com">etiennebr@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
You should probably use something like this (let's say your coordinates are in the x and y columns [also replace <table> and <srid>]):<br><br>SELECT AddGeometryColumn('<table> ','the_geom', <srid>,'POINT', 2);<br>
UPDATE <table> SET the_geom = ST_SetSRID(ST_MakePoint(x,y), <srid>); --replace x and y if it's not the good columns<br><br>For the view (you haven't provided much information), it depends of your table structure, if they are the same, if when you update one you're likely to update the other and if they aren't too big, then maybe it should be merged in a single table. Otherwise, a view could be usefull if you need to view these simultaneously.<br>
<font color="#888888">
<br>Etienne</font><div><div></div><div class="h5"><br><br><div class="gmail_quote">On Wed, Jan 19, 2011 at 9:30 AM, John Callahan <span dir="ltr"><<a href="mailto:john.callahan@udel.edu" target="_blank">john.callahan@udel.edu</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
Thanks Mark. This gives me a direction to go. I have the correct SRS, and your other points are valuable. ST_GeomFromText is really what I was missing. Thanks.<br><br>The fact that I have two data tables to combine, would a View help here? Or somehow modify the SELECT statements that would go into the ST_GeomFromText function? (the examples only show hard-coded coordinates but I'm hoping a SELECT statement can provide input.)<br>
<br>- John<br> <br><br><br><div class="gmail_quote">On Tue, Jan 18, 2011 at 4:20 PM, MarkW <span dir="ltr"><<a href="mailto:mark.wimer@gmail.com" target="_blank">mark.wimer@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
Now that you have data in columns in Postgresql, you can use SQL statements to create your spatial data. Here are the steps:<br>1) identify your SRID/ spatial reference system<br>2) create geometries by passing your x and y with a spatial ref to the right function, and <br>
3) add a row to the geometry_columns table so that other applications can more easily see the spatial data. <br><br>1) It's much easier if you can match your coordinate system to the right UTM srids in the spatial_ref_sys table; see <a href="http://spatialreference.org" target="_blank">spatialreference.org</a> for help.<br>
2) See this function:<br><a href="http://www.postgis.org/docs/ST_GeomFromText.html" target="_blank">http://www.postgis.org/docs/ST_GeomFromText.html</a><br>(and to prove it works, the reverse is ST_X or ST_Y) to get X,Y back out )<br>
3) <a href="http://postgis.refractions.net/docs/ch04.html#Manual_Register_Spatial_Column" target="_blank">http://postgis.refractions.net/docs/ch04.html#Manual_Register_Spatial_Column</a><br>
(but also see the help under 'AddGeometryColumn (<a href="http://postgis.refractions.net/docs/AddGeometryColumn.html" target="_blank">http://postgis.refractions.net/docs/AddGeometryColumn.html</a>)<br><br>Hope this helps. <br>
<br>Mark<br>
<br><br><div class="gmail_quote"><div><div></div><div>On Tue, Jan 18, 2011 at 3:37 PM, John Callahan <span dir="ltr"><<a href="mailto:john.callahan@udel.edu" target="_blank">john.callahan@udel.edu</a>></span> wrote:<br>
</div></div><blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;"><div><div></div><div>
I apologize for asking what seems like an easy question. I'm really just getting started with PostGIS and not sure which way to go here. I have a two tables in MS Access format. They are:<br><br>Table1: StationID, easting, northing, elevation, etc...<br>
Table2: StationID, data values...<br><br>Table1 is basically an inventory of all our stations. Table2 is a subset that includes only stations we have certain data for. How would I convert these into a point data set (of Table2 stations) in PostGIS? I was able to copy the tables from Access into Postgres. Where would I go from here? Maybe OGR would help going directly from Access (or text exports of Access) into PostGIS? <br>
<br>I am using Postgres 9.0.2/PostGIS 2.0.0 on Windows, and using Quantum GIS for viewing. Thanks for any guidance.<br><br>- John<br><br clear="all">**************************************************<br><font color="#888888">John Callahan, Research Scientist<br>
Delaware Geological Survey, University of Delaware<br>URL: <a href="http://www.dgs.udel.edu" target="_blank">http://www.dgs.udel.edu</a><br>**************************************************<br>
</font><br></div></div></blockquote></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br>