[postgis-users] Loading multiple shapefiles with different schemasinto the same PostGIS layer

[Paragon Corporation]

Todd,
Hmm your use of the term schema is a bit confusing since schemas exist in
PostgreSQL and most ANSI compliant relational dbs.  I assume you mean table
structures are different.
 
There are a couple of ways of doing this
1) Load the tables into a scratch schema (in postgresql/ANSI sql lingo ) -
just some dummy schema you create and then do a table to table insert
 
INSERT INTO sometable (.....)
SELECT ...
FROM scratch.imported_table
 
I tend to do this since I usually have a lot of extensive cleaning to do
like validating the geometries, extruding multis into singles and so forth
 
OR 
2) Use OGR2OGR.  OGR2OGR will allow you to write a select statement and as
long as you alias the fields to the ones you want to insert into - that may
work.
(I usually do this when piping data from MySQL, but I think it should work
for shape as well, though you probably won't have a CAST option). OGR2OGR
tends to be impoverished with data types so some care needs to be taken.
 
Hope that helps,
Regina

  _____  

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Todd
Fagin
Sent: Friday, October 17, 2008 5:39 PM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] Loading multiple shapefiles with different
schemasinto the same PostGIS layer



Greetings PostGIS-ers,

 

I have another newbie question for you.

 

I have multiple shapefiles I want to load into PostGIS as a single layer.
However, these shapefiles have different schemas.  I know that shp2pgsql has
the -a option for appending  However, the tables must have the same schema,
right?  

 

I have come up with a convoluted way to do this and it works.  However, I
have feeling that someone much smarter than I has already found a more
elegant solution to this.

 

Here is what I have done:

 

1. Convert each shapefile to SQL using shp2pgsql; this is especially
important since it creates the the_geom field

2. Run the SQL script for the shapefile that has the schema I will use

3. Edit the remaining .sql files generated for each shapefile by shp2pgsql,
removing everything but the INSERT statements and editing the fields to map
to the appropriate fields in the existing table (this is the part that could
be cumbersome, hence looking for a more elegant solution).

4. Run the edited SQL scripts for each of the remaining shapefiles.

 

Thanks,

 

Todd

 

Todd Fagin

 

Coordinate Solutions, Inc.

2804 NW 18th St.

Oklahoma City, OK 73107

405.740.4324 (voice)

904.471.5548 (fax)

www.coordinatesolutions.com

 

 

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20081017/0907e6d3/attachment.html>