Thanks for your response Rob. Looks good. I'll give this a try.<br><br>I cannot merge everything into one table. Our station inventory table contains only the basics/metadata about each station. As other projects arise, they each have their own tables containing data observations for that project only. There are many project tables with all types of data.<br>
<br>As an aside, for future questions, what kind of table information would you (the list) need in order to provide support? Would you need column types (string, numeric), or some sample data? Thanks.<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:56 AM, <span dir="ltr"><<a href="mailto:Robert_Clift@doh.state.fl.us" target="_blank">Robert_Clift@doh.state.fl.us</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;">
<div>
<div dir="ltr" align="left"><span><font face="Arial" size="2">Hi John:</font></span></div>
<div dir="ltr" align="left"><span><font face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span><font face="Arial" size="2">Given your stated table structure**, a query like the following should
give you the records you want.</font></span></div>
<div dir="ltr" align="left"><span><font face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span><font face="Arial" size="2">SELECT t2.*, t1.geometry_column --substitute your newly created
geometry column for "geometry_column"</font></span></div>
<div dir="ltr" align="left"><span><font face="Arial" size="2">FROM table2 t2 LEFT JOIN table1 t1 ON(t2.stationid =
t1.stationid);</font></span></div>
<div dir="ltr" align="left"><span><font face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span><font face="Arial" size="2">The "LEFT JOIN" specifies that you want all rows from table2 and just the
matching rows from table1.</font></span></div>
<div dir="ltr" align="left"><span><font face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span><font face="Arial" size="2">**It's difficult to predict success of any proposed solution without more
info on the structure and content of your two tables. </font></span></div>
<div dir="ltr" align="left"><span><font face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span><font face="Arial" size="2">Have you considered merging the data into one table, allowing null values
for the table2 attributes? You would then be able to select specific records
without performing a join. If you've already created your table1 geometry
column, you can quickly generate a "master" table or view (don't know if QGIS
will recognize a view--might have to be a table) by:</font></span></div>
<div dir="ltr" align="left"><span><font face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span><font face="Arial" size="2">
<div dir="ltr" align="left"><span><font face="Arial" size="2">CREATE TABLE stations AS --or CREATE VIEW stations
AS</font></span></div>
<div dir="ltr" align="left"><span><font face="Arial" size="2">SELECT t2.*, t1.*</font></span></div>
<div dir="ltr" align="left"><span></span><span><font face="Arial" size="2">FROM table2 t2 JOIN table1 t1
ON(t2.stationid = t1.stationid);</font></span></div>
<div dir="ltr" align="left"><span></span> </div>
<div dir="ltr" align="left"><span>Hope that
helps.</span></div>
<div dir="ltr" align="left"><span><font color="#0000ff"></font></span> </div>
<div dir="ltr" align="left"><span>Cheers,</span></div>
<div dir="ltr" align="left"><span>Rob</span></div></font></span></div></div><br>
<div dir="ltr" align="left" lang="en-us">
<hr>
<font face="Tahoma" size="2"><b>From:</b>
<a href="mailto:postgis-users-bounces@postgis.refractions.net" target="_blank">postgis-users-bounces@postgis.refractions.net</a>
[mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net" target="_blank">postgis-users-bounces@postgis.refractions.net</a>] <b>On Behalf Of </b>John
Callahan<br><b>Sent:</b> Wednesday, January 19, 2011 9:30 AM<br><b>To:</b>
PostGIS Users Discussion<br><b>Subject:</b> Re: [postgis-users] migrating tables
to postgis<br></font><br></div><div><div></div><div>
<div></div>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="padding-left: 1ex; margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204);">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="padding-left: 1ex; margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204);">
<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>
</div></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>