<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML xmlns="http://www.w3.org/TR/REC-html40" xmlns:o =
"urn:schemas-microsoft-com:office:office" xmlns:w =
"urn:schemas-microsoft-com:office:word"><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6001.18148" name=GENERATOR>
<STYLE>@font-face {
font-family: Trebuchet MS;
}
@page Section1 {size: 8.5in 11.0in; margin: 1.0in 1.0in 1.0in 1.0in; }
P.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
LI.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
DIV.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
H2 {
FONT-SIZE: 11pt; MARGIN: 6pt 0in 0pt; COLOR: navy; FONT-FAMILY: "Trebuchet MS"
}
A:link {
COLOR: blue; TEXT-DECORATION: underline
}
SPAN.MsoHyperlink {
COLOR: blue; TEXT-DECORATION: underline
}
A:visited {
COLOR: purple; TEXT-DECORATION: underline
}
SPAN.MsoHyperlinkFollowed {
COLOR: purple; TEXT-DECORATION: underline
}
SPAN.EmailStyle17 {
FONT-WEIGHT: normal; COLOR: windowtext; FONT-STYLE: normal; FONT-FAMILY: "Trebuchet MS"; TEXT-DECORATION: none; mso-style-type: personal-compose
}
DIV.Section1 {
page: Section1
}
</STYLE>
</HEAD>
<BODY lang=EN-US vLink=purple link=blue>
<DIV dir=ltr align=left><SPAN class=585555102-18102008><FONT face=Arial
color=#0000ff size=2>Todd,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=585555102-18102008><FONT face=Arial
color=#0000ff size=2>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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=585555102-18102008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=585555102-18102008><FONT face=Arial
color=#0000ff size=2>There are a couple of ways of doing
this</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=585555102-18102008><FONT face=Arial
color=#0000ff size=2>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</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=585555102-18102008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=585555102-18102008><FONT face=Arial
color=#0000ff size=2>INSERT INTO sometable (.....)</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=585555102-18102008><FONT face=Arial
color=#0000ff size=2>SELECT ...</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=585555102-18102008><FONT face=Arial
color=#0000ff size=2>FROM scratch.imported_table</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=585555102-18102008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=585555102-18102008><FONT face=Arial
color=#0000ff size=2>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</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=585555102-18102008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=585555102-18102008><FONT face=Arial
color=#0000ff size=2>OR </FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=585555102-18102008><FONT face=Arial
color=#0000ff size=2>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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=585555102-18102008><FONT face=Arial
color=#0000ff size=2>(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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=585555102-18102008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=585555102-18102008><FONT face=Arial
color=#0000ff size=2>Hope that helps,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=585555102-18102008><FONT face=Arial
color=#0000ff size=2>Regina</FONT></SPAN></DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of </B>Todd
Fagin<BR><B>Sent:</B> Friday, October 17, 2008 5:39 PM<BR><B>To:</B>
postgis-users@postgis.refractions.net<BR><B>Subject:</B> [postgis-users] Loading
multiple shapefiles with different schemasinto the same PostGIS
layer<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV class=Section1>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Greetings
PostGIS-ers,<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">I have another newbie question for
you.<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">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?
<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">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.<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Here is what I have
done:<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">1. Convert each shapefile to SQL
using shp2pgsql; this is especially important since it creates the the_geom
field<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">2. Run the SQL script for the
shapefile that has the schema I will use<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">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).<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">4. Run the edited SQL scripts for
each of the remaining shapefiles.<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Thanks,<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Todd</SPAN></FONT><FONT
face="Trebuchet MS" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Trebuchet MS'"><o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Trebuchet MS" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Trebuchet MS'"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Trebuchet MS" color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Trebuchet MS'">Todd
Fagin</SPAN></FONT><FONT color=navy><SPAN
style="COLOR: navy"><o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Times New Roman" color=navy size=3><SPAN
style="FONT-SIZE: 12pt; COLOR: navy"> <o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Trebuchet MS" color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Trebuchet MS'">Coordinate
Solutions, Inc.</SPAN></FONT><FONT color=navy><SPAN
style="COLOR: navy"><o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Trebuchet MS" color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Trebuchet MS'">2804 NW 18th
St.</SPAN></FONT><FONT color=navy><SPAN
style="COLOR: navy"><o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Trebuchet MS" color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Trebuchet MS'">Oklahoma City,
OK 73107</SPAN></FONT><FONT color=navy><SPAN
style="COLOR: navy"><o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Trebuchet MS" color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Trebuchet MS'">405.740.4324
(voice)</SPAN></FONT><FONT color=navy><SPAN
style="COLOR: navy"><o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Trebuchet MS" color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Trebuchet MS'">904.471.5548
(fax)</SPAN></FONT><FONT color=navy><SPAN
style="COLOR: navy"><o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Trebuchet MS" color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Trebuchet MS'">www.coordinatesolutions.com<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Trebuchet MS" color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Trebuchet MS'"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"><o:p> </o:p></SPAN></FONT></P></DIV></BODY></HTML>