[postgis-users] migrating tables to postgis

Sun Jan 23 15:16:36 PST 2011

Good point.  Thanks for the tip.

- John

On Sun, Jan 23, 2011 at 5:50 PM, Etienne Bellemare <etiennebr at gmail.com>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 <john.callahan at udel.edu>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 <mark.wimer at gmail.com> 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 <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
>>>>
>>>>
>>>
>>> _______________________________________________
>>> 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/20110123/17cc3781/attachment.html>