<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.2900.6049" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=114315714-19012011><FONT face=Arial
size=2>Hi John:</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=114315714-19012011><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=114315714-19012011><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 class=114315714-19012011><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=114315714-19012011><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 class=114315714-19012011><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 class=114315714-19012011><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=114315714-19012011><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 class=114315714-19012011><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=114315714-19012011><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 class=114315714-19012011><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=114315714-19012011><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 class=114315714-19012011><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=114315714-19012011><FONT face=Arial size=2>
<DIV dir=ltr align=left><SPAN class=114315714-19012011><FONT face=Arial
size=2>CREATE TABLE stations AS --or CREATE VIEW stations
AS</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=114315714-19012011><FONT face=Arial
size=2>SELECT t2.*, t1.*</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=114315714-19012011></SPAN><SPAN
class=114315714-19012011><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 class=114315714-19012011></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=114315714-19012011>Hope that
helps.</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=114315714-19012011><FONT
color=#0000ff></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=114315714-19012011>Cheers,</SPAN></DIV>
<DIV dir=ltr align=left><SPAN
class=114315714-19012011>Rob</SPAN></FONT></SPAN></DIV></DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <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>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">mark.wimer@gmail.com</A>></SPAN>
wrote:<BR>
<BLOCKQUOTE class=gmail_quote
style="PADDING-LEFT: 1ex; MARGIN: 0pt 0pt 0pt 0.8ex; BORDER-LEFT: rgb(204,204,204) 1px solid">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 class=h5>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: rgb(204,204,204) 1px solid">
<DIV>
<DIV></DIV>
<DIV class=h5>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">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></BODY></HTML>