Project

General

Profile

« Previous | Next » 

Revision 8173

Added by Aaron Marcuse-Kubitza almost 12 years ago

inputs/FIA/*/map.csv: Ensured that joined columns are globally unique, so they don't map to an ambiguous VegCore term in the future

View differences:

inputs/FIA/REF_PLANT_DICTIONARY/map.csv
1 1 
FIA,VegCore,Filter,Comments
2 2 
row_num,*REF_PLANT_DICTIONARY.row_num,,
3 3 
CN,*REF_PLANT_DICTIONARY.CN,,
4 
SYMBOL_TYPE,SYMBOL_TYPE,,
5 
SYMBOL,SYMBOL,,
4 
SYMBOL_TYPE,PLANT_SYMBOL_TYPE,,
5 
SYMBOL,PLANT_SYMBOL,,
6 6 
SCIENTIFIC_NAME,taxonName,,
7 7 
NEW_SYMBOL,*REF_PLANT_DICTIONARY.NEW_SYMBOL,,
8 8 
NEW_SCIENTIFIC_NAME,*REF_PLANT_DICTIONARY.NEW_SCIENTIFIC_NAME,,
inputs/FIA/REF_PLANT_DICTIONARY/import
7 7 
psql <<'EOF'
8 8 
-- Remove 56 duplicate symbols of type 'Old' (these are ambiguous)
9 9 
DELETE FROM "REF_PLANT_DICTIONARY"
10 
WHERE "SYMBOL" IN (
11 
    SELECT "SYMBOL"
10 
WHERE "PLANT_SYMBOL" IN (
11 
    SELECT "PLANT_SYMBOL"
12 12 
    FROM "REF_PLANT_DICTIONARY"
13 
    WHERE "SYMBOL_TYPE" = 'Old'
14 
    GROUP BY "SYMBOL"
13 
    WHERE "PLANT_SYMBOL_TYPE" = 'Old'
14 
    GROUP BY "PLANT_SYMBOL"
15 15 
    HAVING count(*) > 1
16 16 
);
17 17 

  		




  18
  
  
    
SELECT functions.create_if_not_exists($$ALTER TABLE "REF_PLANT_DICTIONARY" ADD CONSTRAINT "REF_PLANT_DICTIONARY.unique" UNIQUE ("SYMBOL_TYPE", "SYMBOL")$$);


  		




  
  18
  
    
SELECT functions.create_if_not_exists($$ALTER TABLE "REF_PLANT_DICTIONARY" ADD CONSTRAINT "REF_PLANT_DICTIONARY.unique" UNIQUE ("PLANT_SYMBOL_TYPE", "PLANT_SYMBOL")$$);


  		












  

    
      inputs/FIA/REF_PLANT_DICTIONARY/new_terms.csv
    
  





  1
  1
  
    
row_num,*REF_PLANT_DICTIONARY.row_num,,


  		




  2
  2
  
    
CN,*REF_PLANT_DICTIONARY.CN,,


  		




  
  3
  
    
SYMBOL_TYPE,PLANT_SYMBOL_TYPE,,


  		




  
  4
  
    
SYMBOL,PLANT_SYMBOL,,


  		




  3
  5
  
    
NEW_SYMBOL,*REF_PLANT_DICTIONARY.NEW_SYMBOL,,


  		




  4
  6
  
    
NEW_SCIENTIFIC_NAME,*REF_PLANT_DICTIONARY.NEW_SCIENTIFIC_NAME,,


  		




  5
  7
  
    
CATEGORY,*REF_PLANT_DICTIONARY.CATEGORY,,


  		












  

    
      inputs/FIA/REF_PLANT_DICTIONARY/unmapped_terms.csv
    
  





  1
  1
  
    
*REF_PLANT_DICTIONARY.row_num


  		




  2
  2
  
    
*REF_PLANT_DICTIONARY.CN


  		




  3
  
  
    
SYMBOL_TYPE


  		




  4
  
  
    
SYMBOL


  		




  
  3
  
    
PLANT_SYMBOL_TYPE


  		




  
  4
  
    
PLANT_SYMBOL


  		




  5
  5
  
    
*REF_PLANT_DICTIONARY.NEW_SYMBOL


  		




  6
  6
  
    
*REF_PLANT_DICTIONARY.NEW_SCIENTIFIC_NAME


  		




  7
  7
  
    
COMMON_NAME


  		












  

    
      inputs/FIA/REF_SPECIES/map.csv
    
  





  6
  6
  
    
SPECIES,*REF_SPECIES.SPECIES,,


  		




  7
  7
  
    
VARIETY,*REF_SPECIES.VARIETY,,


  		




  8
  8
  
    
SUBSPECIES,*REF_SPECIES.SUBSPECIES,,


  		




  9
  
  
    
SPECIES_SYMBOL,SYMBOL,,


  		




  
  9
  
    
SPECIES_SYMBOL,PLANT_SYMBOL,,


  		




  10
  10
  
    
E_SPGRPCD,*REF_SPECIES.E_SPGRPCD,,


  		




  11
  11
  
    
W_SPGRPCD,*REF_SPECIES.W_SPGRPCD,,


  		




  12
  12
  
    
C_SPGRPCD,*REF_SPECIES.C_SPGRPCD,,


  		












  

    
      inputs/FIA/REF_SPECIES/import
    
  





  9
  9
  
    
  ('SPCD', 'integer')


  		




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


  		




  11
  11
  
    

  		




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


  		




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


  		




  13
  13
  
    

  		




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


  		












  

    
      inputs/FIA/REF_SPECIES/new_terms.csv
    
  





  1
  1
  
    
row_num,*REF_SPECIES.row_num,,


  		




  2
  2
  
    
COMMON_NAME,*REF_SPECIES.COMMON_NAME,,


  		




  3
  
  
    
SPECIES_SYMBOL,SYMBOL,,


  		




  
  3
  
    
SPECIES_SYMBOL,PLANT_SYMBOL,,


  		




  4
  4
  
    
E_SPGRPCD,*REF_SPECIES.E_SPGRPCD,,


  		




  5
  5
  
    
W_SPGRPCD,*REF_SPECIES.W_SPGRPCD,,


  		




  6
  6
  
    
C_SPGRPCD,*REF_SPECIES.C_SPGRPCD,,


  		












  

    
      inputs/FIA/REF_SPECIES/unmapped_terms.csv
    
  





  5
  5
  
    
*REF_SPECIES.SPECIES


  		




  6
  6
  
    
*REF_SPECIES.VARIETY


  		




  7
  7
  
    
*REF_SPECIES.SUBSPECIES


  		




  8
  
  
    
SYMBOL


  		




  
  8
  
    
PLANT_SYMBOL


  		




  9
  9
  
    
*REF_SPECIES.E_SPGRPCD


  		




  10
  10
  
    
*REF_SPECIES.W_SPGRPCD


  		




  11
  11
  
    
*REF_SPECIES.C_SPGRPCD


  		












Also available in:  Unified diff