Revision 8184
Added by Aaron Marcuse-Kubitza almost 12 years ago
| inputs/FIA/PLOT/import | ||
|---|---|---|
| 5 | 5 |
|
| 6 | 6 |
map_table |
| 7 | 7 |
psql <<'EOF' |
| 8 |
SELECT util.set_col_types('"PLOT"', ARRAY[
|
|
| 8 |
SELECT set_col_types('"PLOT"', ARRAY[
|
|
| 9 | 9 |
('STATECD', 'integer')
|
| 10 | 10 |
, ('UNITCD', 'integer')
|
| 11 | 11 |
, ('COUNTYCD', 'integer')
|
| 12 | 12 |
, ('locationName', 'integer')
|
| 13 | 13 |
, ('INVYR', 'integer')
|
| 14 |
]::util.col_cast[]);
|
|
| 14 |
]::col_cast[]); |
|
| 15 | 15 |
|
| 16 |
SELECT util.create_if_not_exists($$ALTER TABLE "PLOT" ADD CONSTRAINT "PLOT.ID" UNIQUE ("locationID")$$);
|
|
| 17 |
SELECT util.create_if_not_exists($$ALTER TABLE "PLOT" ADD CONSTRAINT "PLOT.unique" UNIQUE ("STATECD", "UNITCD", "COUNTYCD", "locationName", "INVYR")$$);
|
|
| 18 |
SELECT util.cluster_once('"PLOT"', '"PLOT.unique"');
|
|
| 16 |
SELECT create_if_not_exists($$ALTER TABLE "PLOT" ADD CONSTRAINT "PLOT.ID" UNIQUE ("locationID")$$);
|
|
| 17 |
SELECT create_if_not_exists($$ALTER TABLE "PLOT" ADD CONSTRAINT "PLOT.unique" UNIQUE ("STATECD", "UNITCD", "COUNTYCD", "locationName", "INVYR")$$);
|
|
| 18 |
SELECT cluster_once('"PLOT"', '"PLOT.unique"');
|
|
| inputs/FIA/COUNTY/import | ||
|---|---|---|
| 5 | 5 |
|
| 6 | 6 |
map_table |
| 7 | 7 |
psql <<'EOF' |
| 8 |
SELECT util.set_col_types('"COUNTY"', ARRAY[
|
|
| 8 |
SELECT set_col_types('"COUNTY"', ARRAY[
|
|
| 9 | 9 |
('STATECD', 'integer')
|
| 10 | 10 |
, ('UNITCD', 'integer')
|
| 11 | 11 |
, ('COUNTYCD', 'integer')
|
| 12 |
]::util.col_cast[]);
|
|
| 12 |
]::col_cast[]); |
|
| 13 | 13 |
|
| 14 |
SELECT util.create_if_not_exists($$ALTER TABLE "COUNTY" ADD CONSTRAINT "COUNTY.ID" UNIQUE ("COUNTY
|
|
| 14 |
SELECT create_if_not_exists($$ALTER TABLE "COUNTY" ADD CONSTRAINT "COUNTY.ID" UNIQUE ("COUNTY
|
|
| 15 | 15 |
CN")$$); |
| 16 |
SELECT util.create_if_not_exists($$ALTER TABLE "COUNTY" ADD CONSTRAINT "COUNTY.unique" UNIQUE ("STATECD", "UNITCD", "COUNTYCD")$$);
|
|
| 16 |
SELECT create_if_not_exists($$ALTER TABLE "COUNTY" ADD CONSTRAINT "COUNTY.unique" UNIQUE ("STATECD", "UNITCD", "COUNTYCD")$$);
|
|
| inputs/FIA/REF_PLANT_DICTIONARY/import | ||
|---|---|---|
| 15 | 15 |
HAVING count(*) > 1 |
| 16 | 16 |
); |
| 17 | 17 |
|
| 18 |
SELECT util.create_if_not_exists($$ALTER TABLE "REF_PLANT_DICTIONARY" ADD CONSTRAINT "REF_PLANT_DICTIONARY.unique" UNIQUE ("PLANT_SYMBOL_TYPE", "PLANT_SYMBOL")$$);
|
|
| 18 |
SELECT create_if_not_exists($$ALTER TABLE "REF_PLANT_DICTIONARY" ADD CONSTRAINT "REF_PLANT_DICTIONARY.unique" UNIQUE ("PLANT_SYMBOL_TYPE", "PLANT_SYMBOL")$$);
|
|
| inputs/FIA/REF_UNIT/import | ||
|---|---|---|
| 5 | 5 |
|
| 6 | 6 |
map_table |
| 7 | 7 |
psql <<'EOF' |
| 8 |
SELECT util.set_col_types('"REF_UNIT"', ARRAY[
|
|
| 8 |
SELECT set_col_types('"REF_UNIT"', ARRAY[
|
|
| 9 | 9 |
('STATECD', 'integer')
|
| 10 | 10 |
, ('UNITCD', 'integer')
|
| 11 |
]::util.col_cast[]);
|
|
| 11 |
]::col_cast[]); |
|
| 12 | 12 |
|
| 13 |
SELECT util.create_if_not_exists($$ALTER TABLE "REF_UNIT" ADD CONSTRAINT "REF_UNIT.unique" UNIQUE ("STATECD", "UNITCD")$$);
|
|
| 13 |
SELECT create_if_not_exists($$ALTER TABLE "REF_UNIT" ADD CONSTRAINT "REF_UNIT.unique" UNIQUE ("STATECD", "UNITCD")$$);
|
|
| inputs/FIA/COND/import | ||
|---|---|---|
| 5 | 5 |
|
| 6 | 6 |
map_table |
| 7 | 7 |
psql <<'EOF' |
| 8 |
SELECT util.set_col_types('"COND"', ARRAY[
|
|
| 8 |
SELECT set_col_types('"COND"', ARRAY[
|
|
| 9 | 9 |
('STATECD', 'integer')
|
| 10 | 10 |
, ('UNITCD', 'integer')
|
| 11 | 11 |
, ('COUNTYCD', 'integer')
|
| 12 | 12 |
, ('locationName', 'integer')
|
| 13 | 13 |
, ('INVYR', 'integer')
|
| 14 | 14 |
, ('authorEventCode', 'integer')
|
| 15 |
]::util.col_cast[]);
|
|
| 15 |
]::col_cast[]); |
|
| 16 | 16 |
|
| 17 |
SELECT util.create_if_not_exists($$
|
|
| 17 |
SELECT create_if_not_exists($$ |
|
| 18 | 18 |
ALTER TABLE "COND" ADD COLUMN "oldGrowth" boolean; |
| 19 | 19 |
ALTER TABLE "COND" ALTER COLUMN "oldGrowth" TYPE boolean USING |
| 20 | 20 |
( |
| ... | ... | |
| 30 | 30 |
; |
| 31 | 31 |
$$); |
| 32 | 32 |
|
| 33 |
SELECT util.create_if_not_exists($$ALTER TABLE "COND" ADD CONSTRAINT "COND.ID" UNIQUE ("COND
|
|
| 33 |
SELECT create_if_not_exists($$ALTER TABLE "COND" ADD CONSTRAINT "COND.ID" UNIQUE ("COND
|
|
| 34 | 34 |
CN")$$); |
| 35 |
SELECT util.create_if_not_exists($$ALTER TABLE "COND" ADD CONSTRAINT "COND.unique" UNIQUE ("STATECD", "UNITCD", "COUNTYCD", "locationName", "INVYR", "authorEventCode")$$);
|
|
| 36 |
SELECT util.cluster_once('"COND"', '"COND.unique"');
|
|
| 35 |
SELECT create_if_not_exists($$ALTER TABLE "COND" ADD CONSTRAINT "COND.unique" UNIQUE ("STATECD", "UNITCD", "COUNTYCD", "locationName", "INVYR", "authorEventCode")$$);
|
|
| 36 |
SELECT cluster_once('"COND"', '"COND.unique"');
|
|
| inputs/FIA/REF_SPECIES/import | ||
|---|---|---|
| 5 | 5 |
|
| 6 | 6 |
map_table |
| 7 | 7 |
psql <<'EOF' |
| 8 |
SELECT util.set_col_types('"REF_SPECIES"', ARRAY[
|
|
| 8 |
SELECT set_col_types('"REF_SPECIES"', ARRAY[
|
|
| 9 | 9 |
('SPCD', 'integer')
|
| 10 |
]::util.col_cast[]);
|
|
| 10 |
]::col_cast[]); |
|
| 11 | 11 |
|
| 12 |
SELECT util.create_if_not_exists($$ALTER TABLE "REF_SPECIES" ADD COLUMN "PLANT_SYMBOL_TYPE" text NOT NULL DEFAULT 'Species'$$);
|
|
| 12 |
SELECT create_if_not_exists($$ALTER TABLE "REF_SPECIES" ADD COLUMN "PLANT_SYMBOL_TYPE" text NOT NULL DEFAULT 'Species'$$); |
|
| 13 | 13 |
|
| 14 |
SELECT util.create_if_not_exists($$ALTER TABLE "REF_SPECIES" ADD CONSTRAINT "REF_SPECIES.unique" UNIQUE ("SPCD")$$);
|
|
| 14 |
SELECT create_if_not_exists($$ALTER TABLE "REF_SPECIES" ADD CONSTRAINT "REF_SPECIES.unique" UNIQUE ("SPCD")$$);
|
|
| inputs/FIA/TREE/import | ||
|---|---|---|
| 5 | 5 |
|
| 6 | 6 |
map_table |
| 7 | 7 |
psql <<'EOF' |
| 8 |
SELECT util.set_col_types('"TREE"', ARRAY[
|
|
| 8 |
SELECT set_col_types('"TREE"', ARRAY[
|
|
| 9 | 9 |
('STATECD', 'integer')
|
| 10 | 10 |
, ('UNITCD', 'integer')
|
| 11 | 11 |
, ('COUNTYCD', 'integer')
|
| ... | ... | |
| 14 | 14 |
, ('subplot', 'integer')
|
| 15 | 15 |
, ('authorEventCode', 'integer')
|
| 16 | 16 |
, ('SPCD', 'integer')
|
| 17 |
]::util.col_cast[]);
|
|
| 17 |
]::col_cast[]); |
|
| 18 | 18 |
|
| 19 | 19 |
-- ("STATECD", "UNITCD", "COUNTYCD", "locationName", "INVYR", "authorEventCode", "subplot", "TREE", "STATUSCD") is not ID
|
| 20 |
SELECT util.create_if_not_exists($$ALTER TABLE "TREE" ADD CONSTRAINT "TREE.ID" UNIQUE ("individualObservationID")$$);
|
|
| 21 |
SELECT util.cluster_once('"TREE"', '"TREE.ID"');
|
|
| 20 |
SELECT create_if_not_exists($$ALTER TABLE "TREE" ADD CONSTRAINT "TREE.ID" UNIQUE ("individualObservationID")$$);
|
|
| 21 |
SELECT cluster_once('"TREE"', '"TREE.ID"');
|
|
| 22 | 22 |
|
| 23 |
SELECT util.create_if_not_exists($$CREATE INDEX "TREE.parent" ON "TREE" ("STATECD", "UNITCD", "COUNTYCD", "locationName", "INVYR", "authorEventCode", "subplot")$$);
|
|
| 23 |
SELECT create_if_not_exists($$CREATE INDEX "TREE.parent" ON "TREE" ("STATECD", "UNITCD", "COUNTYCD", "locationName", "INVYR", "authorEventCode", "subplot")$$);
|
|
| inputs/FIA/REF_HABTYP_DESCRIPTION/import | ||
|---|---|---|
| 5 | 5 |
|
| 6 | 6 |
map_table |
| 7 | 7 |
psql <<'EOF' |
| 8 |
SELECT util.create_if_not_exists($$ALTER TABLE "REF_HABTYP_DESCRIPTION" ADD CONSTRAINT "REF_HABTYP_DESCRIPTION.unique" UNIQUE ("HABTYPCD", "HABTYP_PUB_CD")$$);
|
|
| 8 |
SELECT create_if_not_exists($$ALTER TABLE "REF_HABTYP_DESCRIPTION" ADD CONSTRAINT "REF_HABTYP_DESCRIPTION.unique" UNIQUE ("HABTYPCD", "HABTYP_PUB_CD")$$);
|
|
| inputs/FIA/SUBPLOT/import | ||
|---|---|---|
| 5 | 5 |
|
| 6 | 6 |
map_table |
| 7 | 7 |
psql <<'EOF' |
| 8 |
SELECT util.set_col_types('"SUBPLOT"', ARRAY[
|
|
| 8 |
SELECT set_col_types('"SUBPLOT"', ARRAY[
|
|
| 9 | 9 |
('STATECD', 'integer')
|
| 10 | 10 |
, ('UNITCD', 'integer')
|
| 11 | 11 |
, ('COUNTYCD', 'integer')
|
| 12 | 12 |
, ('locationName', 'integer')
|
| 13 | 13 |
, ('INVYR', 'integer')
|
| 14 | 14 |
, ('subplot', 'integer')
|
| 15 |
]::util.col_cast[]);
|
|
| 15 |
]::col_cast[]); |
|
| 16 | 16 |
|
| 17 |
SELECT util.create_if_not_exists($$ALTER TABLE "SUBPLOT" ADD CONSTRAINT "SUBPLOT.ID" UNIQUE ("subplotID")$$);
|
|
| 18 |
SELECT util.create_if_not_exists($$ALTER TABLE "SUBPLOT" ADD CONSTRAINT "SUBPLOT.unique" UNIQUE ("STATECD", "UNITCD", "COUNTYCD", "locationName", "INVYR", "subplot")$$);
|
|
| 19 |
SELECT util.cluster_once('"SUBPLOT"', '"SUBPLOT.unique"');
|
|
| 17 |
SELECT create_if_not_exists($$ALTER TABLE "SUBPLOT" ADD CONSTRAINT "SUBPLOT.ID" UNIQUE ("subplotID")$$);
|
|
| 18 |
SELECT create_if_not_exists($$ALTER TABLE "SUBPLOT" ADD CONSTRAINT "SUBPLOT.unique" UNIQUE ("STATECD", "UNITCD", "COUNTYCD", "locationName", "INVYR", "subplot")$$);
|
|
| 19 |
SELECT cluster_once('"SUBPLOT"', '"SUBPLOT.unique"');
|
|
| inputs/FIA/REF_RESEARCH_STATION/import | ||
|---|---|---|
| 7 | 7 |
psql <<'EOF' |
| 8 | 8 |
-- contains the research station *and state name* for each state |
| 9 | 9 |
|
| 10 |
SELECT util.set_col_types('"REF_RESEARCH_STATION"', ARRAY[
|
|
| 10 |
SELECT set_col_types('"REF_RESEARCH_STATION"', ARRAY[
|
|
| 11 | 11 |
('STATECD', 'integer')
|
| 12 |
]::util.col_cast[]);
|
|
| 12 |
]::col_cast[]); |
|
| 13 | 13 |
|
| 14 |
SELECT util.create_if_not_exists($$ALTER TABLE "REF_RESEARCH_STATION" ADD CONSTRAINT "REF_RESEARCH_STATION.unique" UNIQUE ("STATECD")$$);
|
|
| 14 |
SELECT create_if_not_exists($$ALTER TABLE "REF_RESEARCH_STATION" ADD CONSTRAINT "REF_RESEARCH_STATION.unique" UNIQUE ("STATECD")$$);
|
|
Also available in: Unified diff
inputs/FIA/*/import: Removed util. before function names because util is in the search_path