[postgis-users] migrating tables to postgis

Wed Jan 19 09:20:09 PST 2011

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 <john.callahan at udel.edu>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, <Robert_Clift at doh.state.fl.us> 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:* postgis-users-bounces at postgis.refractions.net [mailto:
>> postgis-users-bounces at postgis.refractions.net] *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 <mark.wimer at gmail.com> 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 <john.callahan at udel.edu
>>> > 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
>>> postgis-users at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>
>>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110119/2a764b9e/attachment.html>