/* 2012-03-09
SQL script to create SSURGO tables in postgres database. Specific tables are
mapunit, component, and coecoclass. Create in 'ssurgo' schema.

Constraints and indices are indicated and will be added after bulk import. All
data will be imported into staging tables first and then inserted into working
tables.*/

create table ssurgo.mapunit (
musym character varying(6),
muname character varying(175),
mukind character varying(254),
mustatus character varying(254),
muacres integer,
mapunitlfw_l integer,
mapunitlfw_r integer,
mapunitlfw_h integer,
mapunitpfa_l double precision,
mapunitpfa_r double precision,
mapunitpfa_h double precision,
farmlndcl character varying(254),
muhelcl character varying(254),
muwathelcl character varying(254),
muwndhelcl character varying(254),
interpfocus character varying(30),
invesintens character varying(254),
iacornsr integer,
nhiforsoigrp character varying(254),
nhspiagr double precision,
vtsepticsyscl character varying(254),
mucertstat character varying(254),
lkey character varying(30),
mukey character varying(30) not null); --primary key, btree index

create table ssurgo.component (
comppct_l integer,
comppct_r integer,
comppct_h integer,
compname character varying(60),
compkind character varying(254),
majcompflag character(3),
otherph character varying(40),
localphase character varying(40),
slope_l double precision,
slope_r double precision,
slope_h double precision,
slopelenusle_l integer,
slopelenusle_r integer,
slopelenusle_h integer,
runoff character varying(254),
tfact integer,
wei character varying(254),
weg character varying(254),
erocl character varying(254),
earthcovkind1 character varying(254),
earthcovkind2 character varying(254),
hydricon character varying(254),
hydricrating character varying(254),
drainagecl character varying(254),
elev_l double precision,
elev_r double precision,
elev_h double precision,
aspectccwise integer,
aspectrep integer,
aspectcwise integer,
geomdesc text,
albedodry_l double precision,
albedodry_r double precision,
albedodry_h double precision,
airtempa_l double precision,
airtempa_r double precision,
airtempa_h double precision,
map_l integer,
map_r integer,
map_h integer,
reannualprecip_l integer,
reannualprecip_r integer,
reannualprecip_h integer,
ffd_l integer,
ffd_r integer,
ffd_h integer,
nirrcapcl character varying(254),
nirrcapscl character varying(254),
nirrcapunit integer,
irrcapcl character varying(254),
irrcapscl character varying(254),
irrcapunit integer,
cropprodindex integer,
constreeshrubgrp character varying(254),
wndbrksuitgrp character varying(254),
rsprod_l integer,
rsprod_r integer,
rsprod_h integer,
foragesuitgrpid character varying(11),
wlgrain character varying(254),
wlgrass character varying(254),
wlherbaceous character varying(254),
wlshrub character varying(254),
wlconiferous character varying(254),
wlhardwood character varying(254),
wlwetplant character varying(254),
wlshallowwat character varying(254),
wlrangeland character varying(254),
wlopenland character varying(254),
wlwoodland character varying(254),
wlwetland character varying(254),
soilslippot character varying(254),
frostact character varying(254),
initsub_l integer,
initsub_r integer,
initsub_h integer,
totalsub_l integer,
totalsub_r integer,
totalsub_h integer,
hydgrp character varying(254),
corcon character varying(254),
corsteel character varying(254),
taxclname character varying(120),
taxorder character varying(254),
taxsuborder character varying(254),
taxgrtgroup character varying(254),
taxsubgrp character varying(254),
taxpartsize character varying(254),
taxpartsizemod character varying(254),
taxceactcl character varying(254),
taxreaction character varying(254),
taxtempcl character varying(254),
taxmoistscl character varying(254),
taxtempregime character varying(254),
soiltaxedition character varying(254),
castorieindex integer,
flecolcomnum character varying(5),
flhe character(3),
flphe character(3),
flsoilleachpot character varying(254),
flsoirunoffpot character varying(254),
fltemik2use character(3),
fltriumph2use character(3),
indraingrp character(3),
innitrateleachi integer,
misoimgmtgrp character varying(254),
vasoimgtgrp character varying(254),
mukey character varying(30), --foreign key; references mapunit
cokey character varying(30) not null); --primary key, btree index

create table ssurgo.coecoclass (
ecoclasstypename character varying(255),
ecoclassref text,
ecoclassid character varying(30),
ecoclassname character varying(255),
cokey character varying(30), --btree index, foreign key; references compoenent
coecoclasskey character varying(30));

/* ssurgo.mupolygon will hold SSURGO shapefiles. Shapefiles will be imported
using shp2pgsql; this, however, creates indices and constraints before (or
after?) the shapefile is imported. Since 1000 or more shapefile will be
imported, indices and constraints will be added after bulk import*/

create table ssurgo.mupolygon (
gid integer, --large seq, primary key constraint, btree index
areasymbol character varying(20),
spatialver numeric(10,0),
musym character varying(6),
mukey character varying(30),
the_geom geometry);