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
