Project

General

Profile

« Previous | Next » 

Revision 8170

Added by Aaron Marcuse-Kubitza almost 12 years ago

inputs/FIA/*/import: Updated column names to match map.csv

View differences:

inputs/FIA/PLOT/import
6 6 
map_table
7 7 
psql <<'EOF'
8 8 
SELECT functions.set_col_types('"PLOT"', ARRAY[
9 
  ('.STATECD' , 'integer')
10 
, ('.UNITCD'  , 'integer')
11 
, ('.COUNTYCD', 'integer')
12 
, ('.PLOT'    , 'integer')
13 
, ('.INVYR'   , 'integer')
9 
  ('STATECD' , 'integer')
10 
, ('UNITCD'  , 'integer')
11 
, ('COUNTYCD', 'integer')
12 
, ('PLOT'    , 'integer')
13 
, ('INVYR'   , 'integer')
14 14 
]::functions.col_cast[]);
15 15 

  		




  16
  
  
    
SELECT functions.create_if_not_exists($$ALTER TABLE "PLOT" ADD CONSTRAINT "PLOT.ID"     UNIQUE ("PLOT.CN")                                       $$);


  		




  
  16
  
    
SELECT functions.create_if_not_exists($$ALTER TABLE "PLOT" ADD CONSTRAINT "PLOT.ID"     UNIQUE ("*PLOT.CN")                                      $$);


  		




  17
  17
  
    
SELECT functions.create_if_not_exists($$ALTER TABLE "PLOT" ADD CONSTRAINT "PLOT.unique" UNIQUE ("STATECD", "UNITCD", "COUNTYCD", "PLOT", "INVYR")$$);


  		




  18
  18
  
    
SELECT functions.cluster_once('"PLOT"', '"PLOT.unique"');


  		












  

    
      inputs/FIA/COUNTY/import
    
  





  6
  6
  
    
map_table


  		




  7
  7
  
    
psql <<'EOF'


  		




  8
  8
  
    
SELECT functions.set_col_types('"COUNTY"', ARRAY[


  		




  9
  
  
    
  ('.STATECD' , 'integer')


  		




  10
  
  
    
, ('.UNITCD'  , 'integer')


  		




  11
  
  
    
, ('.COUNTYCD', 'integer')


  		




  
  9
  
    
  ('STATECD' , 'integer')


  		




  
  10
  
    
, ('UNITCD'  , 'integer')


  		




  
  11
  
    
, ('COUNTYCD', 'integer')


  		




  12
  12
  
    
]::functions.col_cast[]);


  		




  13
  13
  
    

  		




  14
  
  
    
SELECT functions.create_if_not_exists($$ALTER TABLE "COUNTY" ADD CONSTRAINT "COUNTY.ID"     UNIQUE ("COUNTY.CN")                    $$);


  		




  
  14
  
    
SELECT functions.create_if_not_exists($$ALTER TABLE "COUNTY" ADD CONSTRAINT "COUNTY.ID"     UNIQUE ("*COUNTY.CN")                   $$);


  		




  15
  15
  
    
SELECT functions.create_if_not_exists($$ALTER TABLE "COUNTY" ADD CONSTRAINT "COUNTY.unique" UNIQUE ("STATECD", "UNITCD", "COUNTYCD")$$);


  		












  

    
      inputs/FIA/REF_UNIT/import
    
  





  6
  6
  
    
map_table


  		




  7
  7
  
    
psql <<'EOF'


  		




  8
  8
  
    
SELECT functions.set_col_types('"REF_UNIT"', ARRAY[


  		




  9
  
  
    
  ('.STATECD' , 'integer')


  		




  10
  
  
    
, ('.UNITCD' , 'integer')


  		




  
  9
  
    
  ('STATECD' , 'integer')


  		




  
  10
  
    
, ('UNITCD' , 'integer')


  		




  11
  11
  
    
]::functions.col_cast[]);


  		




  12
  12
  
    

  		




  13
  13
  
    
SELECT functions.create_if_not_exists($$ALTER TABLE "REF_UNIT" ADD CONSTRAINT "REF_UNIT.unique" UNIQUE ("STATECD", "UNITCD")$$);


  		












  

    
      inputs/FIA/COND/import
    
  





  6
  6
  
    
map_table


  		




  7
  7
  
    
psql <<'EOF'


  		




  8
  8
  
    
SELECT functions.set_col_types('"COND"', ARRAY[


  		




  9
  
  
    
  ('.STATECD' , 'integer')


  		




  10
  
  
    
, ('.UNITCD'  , 'integer')


  		




  11
  
  
    
, ('.COUNTYCD', 'integer')


  		




  12
  
  
    
, ('.PLOT'    , 'integer')


  		




  13
  
  
    
, ('.INVYR'   , 'integer')


  		




  14
  
  
    
, ('.CONDID'  , 'integer')


  		




  
  9
  
    
  ('STATECD' , 'integer')


  		




  
  10
  
    
, ('UNITCD'  , 'integer')


  		




  
  11
  
    
, ('COUNTYCD', 'integer')


  		




  
  12
  
    
, ('PLOT'    , 'integer')


  		




  
  13
  
    
, ('INVYR'   , 'integer')


  		




  
  14
  
    
, ('CONDID'  , 'integer')


  		




  15
  15
  
    
]::functions.col_cast[]);


  		




  16
  16
  
    

  		




  17
  17
  
    
SELECT functions.create_if_not_exists($$


  		




  ......




  30
  30
  
    
;


  		




  31
  31
  
    
$$);


  		




  32
  32
  
    

  		




  33
  
  
    
SELECT functions.create_if_not_exists($$ALTER TABLE "COND" ADD CONSTRAINT "COND.ID"     UNIQUE ("COND.CN")                                                 $$);


  		




  
  33
  
    
SELECT functions.create_if_not_exists($$ALTER TABLE "COND" ADD CONSTRAINT "COND.ID"     UNIQUE ("*COND.CN")                                                $$);


  		




  34
  34
  
    
SELECT functions.create_if_not_exists($$ALTER TABLE "COND" ADD CONSTRAINT "COND.unique" UNIQUE ("STATECD", "UNITCD", "COUNTYCD", "PLOT", "INVYR", "CONDID")$$);


  		




  35
  35
  
    
SELECT functions.cluster_once('"COND"', '"COND.unique"');


  		












  

    
      inputs/FIA/REF_SPECIES/import
    
  





  6
  6
  
    
map_table


  		




  7
  7
  
    
psql <<'EOF'


  		




  8
  8
  
    
SELECT functions.set_col_types('"REF_SPECIES"', ARRAY[


  		




  9
  
  
    
  ('.SPCD', 'integer')


  		




  
  9
  
    
  ('SPCD', 'integer')


  		




  10
  10
  
    
]::functions.col_cast[]);


  		




  11
  11
  
    

  		




  12
  12
  
    
SELECT functions.create_if_not_exists($$ALTER TABLE "REF_SPECIES" ADD COLUMN "SYMBOL_TYPE" text NOT NULL DEFAULT 'Species'$$);


  		












  

    
      inputs/FIA/TREE/import
    
  





  6
  6
  
    
map_table


  		




  7
  7
  
    
psql <<'EOF'


  		




  8
  8
  
    
SELECT functions.set_col_types('"TREE"', ARRAY[


  		




  9
  
  
    
  ('.STATECD' , 'integer')


  		




  10
  
  
    
, ('.UNITCD'  , 'integer')


  		




  11
  
  
    
, ('.COUNTYCD', 'integer')


  		




  12
  
  
    
, ('.PLOT'    , 'integer')


  		




  13
  
  
    
, ('.INVYR'   , 'integer')


  		




  14
  
  
    
, ('.SUBP'    , 'integer')


  		




  15
  
  
    
, ('.CONDID'  , 'integer')


  		




  16
  
  
    
, ('.SPCD'    , 'integer')


  		




  
  9
  
    
  ('STATECD' , 'integer')


  		




  
  10
  
    
, ('UNITCD'  , 'integer')


  		




  
  11
  
    
, ('COUNTYCD', 'integer')


  		




  
  12
  
    
, ('PLOT'    , 'integer')


  		




  
  13
  
    
, ('INVYR'   , 'integer')


  		




  
  14
  
    
, ('SUBP'    , 'integer')


  		




  
  15
  
    
, ('CONDID'  , 'integer')


  		




  
  16
  
    
, ('SPCD'    , 'integer')


  		




  17
  17
  
    
]::functions.col_cast[]);


  		




  18
  18
  
    

  		




  19
  19
  
    
-- ("STATECD", "UNITCD", "COUNTYCD", "PLOT", "INVYR", "CONDID", "SUBP", "TREE", "STATUSCD") is not ID


  		




  20
  
  
    
SELECT functions.create_if_not_exists($$ALTER TABLE "TREE" ADD CONSTRAINT "TREE.ID" UNIQUE ("TREE.CN")$$);


  		




  
  20
  
    
SELECT functions.create_if_not_exists($$ALTER TABLE "TREE" ADD CONSTRAINT "TREE.ID" UNIQUE ("*TREE.CN")$$);


  		




  21
  21
  
    
SELECT functions.cluster_once('"TREE"', '"TREE.ID"');


  		




  22
  22
  
    

  		




  23
  23
  
    
SELECT functions.create_if_not_exists($$CREATE INDEX "TREE.parent" ON "TREE" ("STATECD", "UNITCD", "COUNTYCD", "PLOT", "INVYR", "CONDID", "SUBP")$$);


  		












  

    
      inputs/FIA/SUBPLOT/import
    
  





  6
  6
  
    
map_table


  		




  7
  7
  
    
psql <<'EOF'


  		




  8
  8
  
    
SELECT functions.set_col_types('"SUBPLOT"', ARRAY[


  		




  9
  
  
    
  ('.STATECD' , 'integer')


  		




  10
  
  
    
, ('.UNITCD'  , 'integer')


  		




  11
  
  
    
, ('.COUNTYCD', 'integer')


  		




  12
  
  
    
, ('.PLOT'    , 'integer')


  		




  13
  
  
    
, ('.INVYR'   , 'integer')


  		




  14
  
  
    
, ('.SUBP'    , 'integer')


  		




  
  9
  
    
  ('STATECD' , 'integer')


  		




  
  10
  
    
, ('UNITCD'  , 'integer')


  		




  
  11
  
    
, ('COUNTYCD', 'integer')


  		




  
  12
  
    
, ('PLOT'    , 'integer')


  		




  
  13
  
    
, ('INVYR'   , 'integer')


  		




  
  14
  
    
, ('SUBP'    , 'integer')


  		




  15
  15
  
    
]::functions.col_cast[]);


  		




  16
  16
  
    

  		




  17
  
  
    
SELECT functions.create_if_not_exists($$ALTER TABLE "SUBPLOT" ADD CONSTRAINT "SUBPLOT.ID"     UNIQUE ("SUBPLOT.CN")                                            $$);


  		




  
  17
  
    
SELECT functions.create_if_not_exists($$ALTER TABLE "SUBPLOT" ADD CONSTRAINT "SUBPLOT.ID"     UNIQUE ("*SUBPLOT.CN")                                           $$);


  		




  18
  18
  
    
SELECT functions.create_if_not_exists($$ALTER TABLE "SUBPLOT" ADD CONSTRAINT "SUBPLOT.unique" UNIQUE ("STATECD", "UNITCD", "COUNTYCD", "PLOT", "INVYR", "SUBP")$$);


  		




  19
  19
  
    
SELECT functions.cluster_once('"SUBPLOT"', '"SUBPLOT.unique"');


  		












  

    
      inputs/FIA/REF_RESEARCH_STATION/import
    
  





  8
  8
  
    
-- contains the research station *and state name* for each state


  		




  9
  9
  
    

  		




  10
  10
  
    
SELECT functions.set_col_types('"REF_RESEARCH_STATION"', ARRAY[


  		




  11
  
  
    
  ('.STATECD' , 'integer')


  		




  
  11
  
    
  ('STATECD' , 'integer')


  		




  12
  12
  
    
]::functions.col_cast[]);


  		




  13
  13
  
    

  		




  14
  14
  
    
SELECT functions.create_if_not_exists($$ALTER TABLE "REF_RESEARCH_STATION" ADD CONSTRAINT "REF_RESEARCH_STATION.unique" UNIQUE ("STATECD")$$);


  		












Also available in:  Unified diff