[postgis-users] migrating tables to postgis

[Robert_Clift at doh.state.fl.us]

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_Co
lumn
	(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
	
	


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110119/84bc44a5/attachment.html>