Good point. Thanks for the tip.<br><br>- John<br><br><br><br><div class="gmail_quote">On Sun, Jan 23, 2011 at 5:50 PM, 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;">Little detail, it's not really mandatory to include "Table1"."DGSID" as dgsid2 as you do the join on it. It should be the same then DGSID.<br>
<font color="#888888"><br>Etienne</font><div><div></div><div class="h5"><br><br><div class="gmail_quote">On Fri, Jan 21, 2011 at 1:38 PM, 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;">Thank you for all your help. In the end, it turns out to be relatively simple (as long as you know who to ask...) For anyone interested, here are the steps I performed to display these tables together in QGIS.<br>
<br><br>
1. Add a geometry column to the table that contains the coordinates, which is my station inventory table, Table1.<br><br>SELECT AddGeometryColumn('Table1','the_geom', 26918,'POINT', 2);<br>UPDATE Table1 SET the_geom = ST_SetSRID(ST_MakePoint(EASTING,NORTHING), 26918);<br>
<br><br>2. Add a primary key field (of integer type) to my project data table (Table2) that will be used as the primary key in QGIS.<br><br>ALTER TABLE "Table2" ADD COLUMN id SERIAL PRIMARY KEY;<br><br><br>3. Create a view containing all records in my project data table and the matching geometry (and a few other fields I need) in the station inventory table. "DGSID" is the common field between them, which I had to give an alias to since it had the same name in both tables.<br>
<br>CREATE VIEW "siteview" AS <br>SELECT <br> "Table2".*, <br> "Table1"."DGSID" as dgsid2, <br> "Table1"."EASTING", <br> "Table1"."NORTHING",<br>
"Table1"."the_geom"<br>FROM <br> public."Table2", <br> public."Table1"<br>WHERE <br> "Table1"."DGSID" = "Table2"."DGSID"<br>ORDER BY<br>
"Table2"."DGSID" ASC;<br><br><br>4. Manually add a record for the view in the geometry_columns table.<br><br>INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")<br>
VALUES ('', 'public', 'siteview', 'the_geom', 2, 26918, 'POINT');<br><br><br>That's it. It's working beautifully. Thanks again.<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 12: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;">
Views are fine in Qgis for me, but in my experience you'll need to manually add a record to the GeometryColumns table, and include a suitable unique ID field into the view or Qgis will complain. <br><br>I think views are what you want based on your description. I think the table structure qualifier was because you said "etc" without giving all fields from each table, and a one-to-many relationship (or other) wasn't all that clear in your first message. My guess. But yeah, it's easier if you give the relationship between tables and a few rows of sample data (in, out) can't hurt in communicating.<br>
<br>Finally, Etienne's suggestion was better - my impression (from the PostGIS in Action book which I recommend by the way!) gives the example of ST_point for your purpose and I've had an easier time using it with column names than ST_GeomFromText.<br>
<br>Mark<div><div></div><div><br><br><div class="gmail_quote">On Wed, Jan 19, 2011 at 11:18 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 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.<div><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><div><div></div><div><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>
</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>
</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>
<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>