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

[Chris Hermansen]

Depending on the differences between your shapefile schemas, you may be
able to use the INHERITS facility in PostgreSQL.  An example here might
be you make point observations of different types of phenomena, but at
heart these are all some kind of observation.

If your shapefile schemas are wildly different then that may imply you
are trying to put stuff that is wildly different together and that might
indicate you should re-think your plan a bit.  Extending my example
above, you probably wouldn't want to combine "plans" with
"observations", even if you could figure out how, because later on you
might want to compare planned performance with observed performance and
spatial overlay or similar keys are the way to do that, working from two
different physical themes.

Todd Fagin wrote:
>
> 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
>
>  
>
>  
>
>  
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>   


-- 
Regards,

Chris Hermansen         mailto:chris.hermansen at timberline.ca
tel+1.604.714.2878 · fax+1.604.733.0631 · mob+1.778.232.0644
Timberline Natural Resource Group · http://www.timberline.ca
401 · 958 West 8th Avenue  · Vancouver BC · Canada · V5Z 1E5