Good point. Thanks for the tip. - John
On Sun, Jan 23, 2011 at 5:50 PM, Etienne Bellemare <[email protected]>wrote: > 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. > > Etienne > > > On Fri, Jan 21, 2011 at 1:38 PM, John Callahan <[email protected]>wrote: > >> 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. >> >> >> 1. Add a geometry column to the table that contains the coordinates, which >> is my station inventory table, Table1. >> >> SELECT AddGeometryColumn('Table1','the_geom', 26918,'POINT', 2); >> UPDATE Table1 SET the_geom = ST_SetSRID(ST_MakePoint(EASTING,NORTHING), >> 26918); >> >> >> 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. >> >> ALTER TABLE "Table2" ADD COLUMN id SERIAL PRIMARY KEY; >> >> >> 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. >> >> CREATE VIEW "siteview" AS >> SELECT >> "Table2".*, >> "Table1"."DGSID" as dgsid2, >> "Table1"."EASTING", >> "Table1"."NORTHING", >> "Table1"."the_geom" >> FROM >> public."Table2", >> public."Table1" >> WHERE >> "Table1"."DGSID" = "Table2"."DGSID" >> ORDER BY >> "Table2"."DGSID" ASC; >> >> >> 4. Manually add a record for the view in the geometry_columns table. >> >> INSERT INTO geometry_columns(f_table_catalog, f_table_schema, >> f_table_name, f_geometry_column, coord_dimension, srid, "type") >> VALUES ('', 'public', 'siteview', 'the_geom', 2, 26918, 'POINT'); >> >> >> That's it. It's working beautifully. Thanks again. >> >> - John >> >> ************************************************** >> John Callahan, Research Scientist >> Delaware Geological Survey, University of Delaware >> URL: http://www.dgs.udel.edu >> ************************************************** >> >> >> On Wed, Jan 19, 2011 at 12:20 PM, MarkW <[email protected]> wrote: >> >>> 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. >>> >>> 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. >>> >>> 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. >>> >>> Mark >>> >>> >>> On Wed, Jan 19, 2011 at 11:18 AM, John Callahan >>> <[email protected]>wrote: >>> >>>> Thanks for your response Rob. Looks good. I'll give this a try. >>>> >>>> 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. >>>> >>>> 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. >>>> >>>> >>>> - John >>>> >>>> ************************************************** >>>> John Callahan, Research Scientist >>>> Delaware Geological Survey, University of Delaware >>>> URL: http://www.dgs.udel.edu >>>> ************************************************** >>>> >>>> >>>> On Wed, Jan 19, 2011 at 10:56 AM, <[email protected]> wrote: >>>> >>>>> Hi John: >>>>> >>>>> Given your stated table structure**, a query like the following should >>>>> give you the records you want. >>>>> >>>>> SELECT t2.*, t1.geometry_column --substitute your newly created >>>>> geometry column for "geometry_column" >>>>> FROM table2 t2 LEFT JOIN table1 t1 ON(t2.stationid = t1.stationid); >>>>> >>>>> The "LEFT JOIN" specifies that you want all rows from table2 and just >>>>> the matching rows from table1. >>>>> >>>>> **It's difficult to predict success of any proposed solution without >>>>> more info on the structure and content of your two tables. >>>>> >>>>> 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: >>>>> >>>>> CREATE TABLE stations AS --or CREATE VIEW stations AS >>>>> SELECT t2.*, t1.* >>>>> FROM table2 t2 JOIN table1 t1 ON(t2.stationid = t1.stationid); >>>>> >>>>> Hope that helps. >>>>> >>>>> Cheers, >>>>> Rob >>>>> >>>>> ------------------------------ >>>>> *From:* [email protected] [mailto: >>>>> [email protected]] *On Behalf Of *John >>>>> Callahan >>>>> *Sent:* Wednesday, January 19, 2011 9:30 AM >>>>> *To:* PostGIS Users Discussion >>>>> *Subject:* Re: [postgis-users] migrating tables to postgis >>>>> >>>>> 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. >>>>> >>>>> 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.) >>>>> >>>>> - John >>>>> >>>>> >>>>> >>>>> On Tue, Jan 18, 2011 at 4:20 PM, MarkW <[email protected]> wrote: >>>>> >>>>>> Now that you have data in columns in Postgresql, you can use SQL >>>>>> statements to create your spatial data. Here are the steps: >>>>>> 1) identify your SRID/ spatial reference system >>>>>> 2) create geometries by passing your x and y with a spatial ref to the >>>>>> right function, and >>>>>> 3) add a row to the geometry_columns table so that other applications >>>>>> can more easily see the spatial data. >>>>>> >>>>>> 1) It's much easier if you can match your coordinate system to the >>>>>> right UTM srids in the spatial_ref_sys table; see >>>>>> spatialreference.org for help. >>>>>> 2) See this function: >>>>>> http://www.postgis.org/docs/ST_GeomFromText.html >>>>>> (and to prove it works, the reverse is ST_X or ST_Y) to get X,Y back >>>>>> out ) >>>>>> 3) >>>>>> http://postgis.refractions.net/docs/ch04.html#Manual_Register_Spatial_Column >>>>>> (but also see the help under 'AddGeometryColumn ( >>>>>> http://postgis.refractions.net/docs/AddGeometryColumn.html) >>>>>> >>>>>> Hope this helps. >>>>>> >>>>>> Mark >>>>>> >>>>>> >>>>>> On Tue, Jan 18, 2011 at 3:37 PM, John Callahan < >>>>>> [email protected]> wrote: >>>>>> >>>>>>> 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: >>>>>>> >>>>>>> Table1: StationID, easting, northing, elevation, etc... >>>>>>> Table2: StationID, data values... >>>>>>> >>>>>>> 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? >>>>>>> >>>>>>> I am using Postgres 9.0.2/PostGIS 2.0.0 on Windows, and using Quantum >>>>>>> GIS for viewing. Thanks for any guidance. >>>>>>> >>>>>>> - John >>>>>>> >>>>>>> ************************************************** >>>>>>> John Callahan, Research Scientist >>>>>>> Delaware Geological Survey, University of Delaware >>>>>>> URL: http://www.dgs.udel.edu >>>>>>> ************************************************** >>>>>>> >>>>>>> >>>>>> _______________________________________________ >>>>>> postgis-users mailing list >>>>>> [email protected] >>>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users >>>>>> >>>>>> >>>>> >>>>> _______________________________________________ >>>>> postgis-users mailing list >>>>> [email protected] >>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users >>>>> >>>>> >>>> >>>> _______________________________________________ >>>> postgis-users mailing list >>>> [email protected] >>>> http://postgis.refractions.net/mailman/listinfo/postgis-users >>>> >>>> >>> >>> _______________________________________________ >>> postgis-users mailing list >>> [email protected] >>> http://postgis.refractions.net/mailman/listinfo/postgis-users >>> >>> >> >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> http://postgis.refractions.net/mailman/listinfo/postgis-users >> >> > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > >
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
