[postgis-users] migrating tables to postgis

Fri Jan 21 10:38:37 PST 2011

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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110121/3050fef6/attachment.html>