Project

General

Profile

« Previous | Next » 

Revision 11723

Added by Aaron Marcuse-Kubitza about 11 years ago

inputs/CVS/plantConcept_/: translated multi-column filters to postprocessing derived columns, using the steps at http://wiki.vegpath.org/Adding_new-style_import_to_a_datasource#1-Translate-filters-to-postprocessing-derived-columns

View differences:

inputs/CVS/plantConcept_/postprocess.sql
1 1 
SELECT util.search_path_append('util');
2 2 

  		




  
  3
  
    
SELECT util.rename_cols(:table_str, $$


  		




  
  4
  
    
genus => *genus,


  		




  
  5
  
    
$$::hstore);


  		




  
  6
  
    

  		




  3
  7
  
    
SELECT create_if_not_exists($$ALTER TABLE $$||:table_str||$$ ADD PRIMARY KEY ("PLANTCONCEPT_ID")$$); -- runtime: ~0 @starscream


  		




  
  8
  
    

  		




  
  9
  
    

  		




  
  10
  
    
SELECT mk_derived_col((:table_str, 'division'),


  		




  
  11
  
    
$$_alt(denorm_division, "Division")$$)


  		




  
  12
  
    
; -- runtime: 0.2 s ("212.478 ms") @starscream


  		




  
  13
  
    

  		




  
  14
  
    
SELECT mk_derived_col((:table_str, 'genus'),


  		




  
  15
  
    
$$_alt(denorm_genus, "*genus")$$)


  		




  
  16
  
    
; -- runtime: 0.1 s ("127.546 ms") @starscream


  		












  

    
      inputs/CVS/plantConcept_/map.csv
    
  





  12
  12
  
    
USDAPlantsCode,*USDAPlantsCode,,


  		




  13
  13
  
    
VascularPlant,*VascularPlant,,


  		




  14
  14
  
    
denorm_kingdom,kingdom,,


  		




  15
  
  
    
denorm_division,*division,/_alt/1,"must be _alt-ed with Division because this is missing in 2 rows that have Division, and different from Division in 1 row"


  		




  
  15
  
    
denorm_division,division_main,/_alt/1,"must be _alt-ed with Division because this is missing in 2 rows that have Division, and different from Division in 1 row"


  		




  16
  16
  
    
denorm_category,*category,,


  		




  17
  17
  
    
denorm_family,family,,


  		




  18
  
  
    
denorm_genus,genus,/_alt/1,must be _alt-ed with genus because this is missing in 1 row that has genus


  		




  
  18
  
    
denorm_genus,genus_main,/_alt/1,must be _alt-ed with genus because this is missing in 1 row that has genus


  		




  19
  19
  
    
denorm_species,specificEpithet,,"unlike VegBank, this is in fact the epithet, not the full name"


  		




  20
  20
  
    
denorm_triAbbr,infraspecificRank,,


  		




  21
  21
  
    
denorm_triName,infraspecificEpithet,,


  		




  ......




  35
  35
  
    
usda_growthForm,*usda_growthForm,,


  		




  36
  36
  
    
Kingdom,DUPLICATE#of:denorm_kingdom#Kingdom,,


  		




  37
  37
  
    
Subkingdom,*Subkingdom,,


  		




  38
  
  
    
Division,*division,/_alt/2,


  		




  
  38
  
    
Division,division_normalized,/_alt/2,


  		




  39
  39
  
    
Class,class,,


  		




  40
  40
  
    
Subclass,*Subclass,,


  		




  41
  41
  
    
Order,order,,


  		




  ......




  43
  43
  
    
family,DUPLICATE#of:denorm_family#family,,


  		




  44
  44
  
    
tribe,*tribe,,


  		




  45
  45
  
    
composite genus,*composite genus,,


  		




  46
  
  
    
genus,genus,/_alt/2,"unlike VegBank, does *not* include genus author"


  		




  
  46
  
    
genus,genus_normalized,/_alt/2,"unlike VegBank, does *not* include genus author"


  		




  47
  47
  
    
subgenus,subgenus,,


  		




  48
  48
  
    
section,*section,,


  		




  49
  49
  
    
composite species,*composite species,,


  		




  ......




  51
  51
  
    
subspecies,EQUIV#to:species#subspecies,,full taxonomic name at that rank


  		




  52
  52
  
    
composite variety,EQUIV#to:species#composite variety,,full taxonomic name at that rank


  		




  53
  53
  
    
variety,EQUIV#to:species#variety,,full taxonomic name at that rank


  		




  
  54
  
    
division,division,,


  		




  
  55
  
    
genus,genus,,


  		












  

    
      inputs/CVS/plantConcept_/VegBIEN.csv
    
  





  3
  3
  
    
plantname,"/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/authortaxoncode/_if[@name=""if in plot""]/then/_first/3/_first/3",


  		




  4
  4
  
    
taxonLevel,"/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/taxondetermination[!isoriginal=true]/*_id/taxonverbatim/*_id/taxonlabel/parent_id/taxonlabel/_if[@name=""if has explicit parent""]/else/rank",


  		




  5
  5
  
    
denorm_species,"/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/taxondetermination[!isoriginal=true]/*_id/taxonverbatim/*_id/taxonlabel/parent_id/taxonlabel/parent_id/_forEach:[in:[cultivar,forma,variety,subspecies,],do=""taxonlabel[rank=_val]/parent_id""]/taxonlabel[rank=species]/taxonepithet","unlike VegBank, this is in fact the epithet, not the full name"


  		




  6
  
  
    
denorm_genus,"/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/taxondetermination[!isoriginal=true]/*_id/taxonverbatim/*_id/taxonlabel/parent_id/taxonlabel/parent_id/_forEach:[in:[cultivar,forma,variety,subspecies,species,],do=""taxonlabel[rank=_val]/parent_id""]/taxonlabel[rank=genus]/taxonepithet/_alt/1",must be _alt-ed with genus because this is missing in 1 row that has genus


  		




  7
  
  
    
genus,"/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/taxondetermination[!isoriginal=true]/*_id/taxonverbatim/*_id/taxonlabel/parent_id/taxonlabel/parent_id/_forEach:[in:[cultivar,forma,variety,subspecies,species,],do=""taxonlabel[rank=_val]/parent_id""]/taxonlabel[rank=genus]/taxonepithet/_alt/2","unlike VegBank, does *not* include genus author"


  		




  
  6
  
    
genus,"/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/taxondetermination[!isoriginal=true]/*_id/taxonverbatim/*_id/taxonlabel/parent_id/taxonlabel/parent_id/_forEach:[in:[cultivar,forma,variety,subspecies,species,],do=""taxonlabel[rank=_val]/parent_id""]/taxonlabel[rank=genus]/taxonepithet",


  		




  8
  7
  
    
denorm_family,"/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/taxondetermination[!isoriginal=true]/*_id/taxonverbatim/*_id/taxonlabel/parent_id/taxonlabel/parent_id/_forEach:[in:[cultivar,forma,variety,subspecies,species,genus,],do=""taxonlabel[rank=_val]/parent_id""]/taxonlabel[rank=family]/taxonepithet",


  		




  9
  8
  
    
Order,"/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/taxondetermination[!isoriginal=true]/*_id/taxonverbatim/*_id/taxonlabel/parent_id/taxonlabel/parent_id/_forEach:[in:[cultivar,forma,variety,subspecies,species,genus,family,],do=""taxonlabel[rank=_val]/parent_id""]/taxonlabel[rank=order]/taxonepithet",


  		




  10
  9
  
    
Class,"/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/taxondetermination[!isoriginal=true]/*_id/taxonverbatim/*_id/taxonlabel/parent_id/taxonlabel/parent_id/_forEach:[in:[cultivar,forma,variety,subspecies,species,genus,family,order,],do=""taxonlabel[rank=_val]/parent_id""]/taxonlabel[rank=class]/taxonepithet",


  		




  ......




  15
  14
  
    
denorm_family,/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/taxondetermination[!isoriginal=true]/*_id/taxonverbatim/*_id/taxonlabel/taxonomicname/_merge_prefix/prefix/_taxon_family_require_std/family,


  		




  16
  15
  
    
plantNameWithAuthority,/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/taxondetermination[!isoriginal=true]/*_id/taxonverbatim/*_id/taxonlabel/taxonomicname/_merge_prefix/value/_alt/1,


  		




  17
  16
  
    
plantname,/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/taxondetermination[!isoriginal=true]/*_id/taxonverbatim/*_id/taxonlabel/taxonomicname/_merge_prefix/value/_alt/2/_join_words/1/_alt/1,


  		




  18
  
  
    
denorm_genus,/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/taxondetermination[!isoriginal=true]/*_id/taxonverbatim/*_id/taxonlabel/taxonomicname/_merge_prefix/value/_alt/2/_join_words/1/_alt/2/_join_words/2/_filter_genus/value/_alt/1,must be _alt-ed with genus because this is missing in 1 row that has genus


  		




  19
  
  
    
genus,/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/taxondetermination[!isoriginal=true]/*_id/taxonverbatim/*_id/taxonlabel/taxonomicname/_merge_prefix/value/_alt/2/_join_words/1/_alt/2/_join_words/2/_filter_genus/value/_alt/2,"unlike VegBank, does *not* include genus author"


  		




  
  17
  
    
genus,/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/taxondetermination[!isoriginal=true]/*_id/taxonverbatim/*_id/taxonlabel/taxonomicname/_merge_prefix/value/_alt/2/_join_words/1/_alt/2/_join_words/2/_filter_genus/value,


  		




  20
  18
  
    
denorm_species,/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/taxondetermination[!isoriginal=true]/*_id/taxonverbatim/*_id/taxonlabel/taxonomicname/_merge_prefix/value/_alt/2/_join_words/1/_alt/2/_join_words/3/_join_words/1,"unlike VegBank, this is in fact the epithet, not the full name"


  		




  21
  19
  
    
denorm_triName,"/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/taxondetermination[!isoriginal=true]/*_id/taxonverbatim/*_id/taxonlabel/taxonomicname/_merge_prefix/value/_alt/2/_join_words/1/_alt/2/_join_words/5/_join_words/1/_if[@name=""if has infraspecificEpithet""]/cond",


  		




  22
  20
  
    
taxonLevel,"/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/taxondetermination[!isoriginal=true]/*_id/taxonverbatim/*_id/taxonlabel/taxonomicname/_merge_prefix/value/_alt/2/_join_words/1/_alt/2/_join_words/5/_join_words/1/_if[@name=""if has infraspecificEpithet""]/then",


  		




  23
  21
  
    
denorm_triName,/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/taxondetermination[!isoriginal=true]/*_id/taxonverbatim/*_id/taxonlabel/taxonomicname/_merge_prefix/value/_alt/2/_join_words/1/_alt/2/_join_words/5/_join_words/2,


  		




  24
  22
  
    
denorm_family,/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/taxondetermination[!isoriginal=true]/*_id/taxonverbatim/family,


  		




  25
  
  
    
denorm_genus,/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/taxondetermination[!isoriginal=true]/*_id/taxonverbatim/genus/_alt/1,must be _alt-ed with genus because this is missing in 1 row that has genus


  		




  26
  
  
    
genus,/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/taxondetermination[!isoriginal=true]/*_id/taxonverbatim/genus/_alt/2,"unlike VegBank, does *not* include genus author"


  		




  
  23
  
    
genus,/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/taxondetermination[!isoriginal=true]/*_id/taxonverbatim/genus,


  		




  27
  24
  
    
denorm_species,/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/taxondetermination[!isoriginal=true]/*_id/taxonverbatim/specific_epithet,"unlike VegBank, this is in fact the epithet, not the full name"


  		




  28
  25
  
    
plantname,/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/taxondetermination[!isoriginal=true]/*_id/taxonverbatim/taxonname,


  		




  29
  26
  
    
plantNameWithAuthority,/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/taxondetermination[!isoriginal=true]/*_id/taxonverbatim/taxonomicname,


  		




  30
  27
  
    
taxonLevel,/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/taxondetermination[!isoriginal=true]/*_id/taxonverbatim/verbatimrank,


  		




  31
  
  
    
Division,,** No join mapping for *division/_alt/2 ** 


  		




  
  28
  
    
Division,,** No join mapping for division_normalized/_alt/2 ** 


  		




  32
  29
  
    
Kingdom,,** No non-empty join mapping for DUPLICATE#of:denorm_kingdom#Kingdom ** 


  		




  33
  30
  
    
PLANTCONCEPT_ID,,** No join mapping for *PLANTCONCEPT_ID ** 


  		




  34
  31
  
    
PLANTNAME_ID,,** No join mapping for *PLANTNAME_ID ** 


  		




  ......




  42
  39
  
    
composite species,,** No join mapping for *composite species ** 


  		




  43
  40
  
    
composite variety,,** No non-empty join mapping for EQUIV#to:species#composite variety ** full taxonomic name at that rank


  		




  44
  41
  
    
denorm_category,,** No join mapping for *category ** 


  		




  45
  
  
    
denorm_division,,"** No join mapping for *division/_alt/1 ** must be _alt-ed with Division because this is missing in 2 rows that have Division, and different from Division in 1 row"


  		




  
  42
  
    
denorm_division,,"** No join mapping for division_main/_alt/1 ** must be _alt-ed with Division because this is missing in 2 rows that have Division, and different from Division in 1 row"


  		




  
  43
  
    
denorm_genus,,** No join mapping for genus_main/_alt/1 ** must be _alt-ed with genus because this is missing in 1 row that has genus


  		




  46
  44
  
    
denorm_hybridX,,** No join mapping for *hybridX ** 


  		




  47
  45
  
    
denorm_quadAbbr,,** No join mapping for infraspecificRank2 ** sometimes misparsed as the last + sign in the plantNameWithAuthority


  		




  48
  46
  
    
denorm_quadName,,** No join mapping for infraspecificEpithet2 ** sometimes misparsed as the part after the last + sign in the plantNameWithAuthority


  		




  49
  47
  
    
denorm_triAbbr,,** No join mapping for infraspecificRank ** 


  		




  
  48
  
    
division,,** No join mapping for division ** 


  		




  50
  49
  
    
exotic,,** No join mapping for *exotic ** 


  		




  51
  50
  
    
family,,** No non-empty join mapping for DUPLICATE#of:denorm_family#family ** 


  		




  
  51
  
    
genus,,"** No join mapping for genus_normalized/_alt/2 ** unlike VegBank, does *not* include genus author"


  		




  52
  52
  
    
growthForm_short,,** No join mapping for *growthForm_short ** 


  		




  53
  53
  
    
lowestChildRank,,** No join mapping for *lowestChildRank ** 


  		




  54
  54
  
    
lowestParentConcept_ID,,** No join mapping for *lowestParentConcept_ID ** 


  		












  

    
      inputs/CVS/plantConcept_/test.xml.ref
    
  





  37
  37
  
    
                                                                                                        <parent_id>


  		




  38
  38
  
    
                                                                                                            <taxonlabel>


  		




  39
  39
  
    
                                                                                                                <rank>genus</rank>


  		




  40
  
  
    
                                                                                                                <taxonepithet>


  		




  41
  
  
    
                                                                                                                    <_alt>


  		




  42
  
  
    
                                                                                                                        <1>$denorm_genus</1>


  		




  43
  
  
    
                                                                                                                        <2>$genus</2>


  		




  44
  
  
    
                                                                                                                    </_alt>


  		




  45
  
  
    
                                                                                                                </taxonepithet>


  		




  
  40
  
    
                                                                                                                <taxonepithet>$genus</taxonepithet>


  		




  46
  41
  
    
                                                                                                                <parent_id>


  		




  47
  42
  
    
                                                                                                                    <taxonlabel>


  		




  48
  43
  
    
                                                                                                                        <rank>family</rank>


  		




  ......




  99
  94
  
    
                                                                                <1>$plantname</1>


  		




  100
  95
  
    
                                                                                <2>


  		




  101
  96
  
    
                                                                                    <_join_words>


  		




  102
  
  
    
                                                                                        <2>


  		




  103
  
  
    
                                                                                            <_filter_genus>


  		




  104
  
  
    
                                                                                                <value>


  		




  105
  
  
    
                                                                                                    <_alt>


  		




  106
  
  
    
                                                                                                        <1>$denorm_genus</1>


  		




  107
  
  
    
                                                                                                        <2>$genus</2>


  		




  108
  
  
    
                                                                                                    </_alt>


  		




  109
  
  
    
                                                                                                </value>


  		




  110
  
  
    
                                                                                            </_filter_genus>


  		




  111
  
  
    
                                                                                        </2>


  		




  
  97
  
    
                                                                                        <2><_filter_genus><value>$genus</value></_filter_genus></2>


  		




  112
  98
  
    
                                                                                        <3>$denorm_species</3>


  		




  113
  99
  
    
                                                                                        <5>


  		




  114
  100
  
    
                                                                                            <_join_words>


  		




  ......




  132
  118
  
    
                                                    </taxonlabel>


  		




  133
  119
  
    
                                                </taxonlabel_id>


  		




  134
  120
  
    
                                                <family>$denorm_family</family>


  		




  135
  
  
    
                                                <genus>


  		




  136
  
  
    
                                                    <_alt>


  		




  137
  
  
    
                                                        <1>$denorm_genus</1>


  		




  138
  
  
    
                                                        <2>$genus</2>


  		




  139
  
  
    
                                                    </_alt>


  		




  140
  
  
    
                                                </genus>


  		




  
  121
  
    
                                                <genus>$genus</genus>


  		




  141
  122
  
    
                                                <specific_epithet>$denorm_species</specific_epithet>


  		




  142
  123
  
    
                                                <taxonname>$plantname</taxonname>


  		




  143
  124
  
    
                                                <taxonomicname>$plantNameWithAuthority</taxonomicname>


  		












  

    
      inputs/CVS/plantConcept_/new_terms.csv
    
  





  1
  1
  
    
plantname,taxonName,,


  		




  2
  2
  
    
plantNameWithAuthority,scientificName,,


  		




  3
  3
  
    
denorm_kingdom,kingdom,,


  		




  4
  
  
    
denorm_division,*division,/_alt/1,"must be _alt-ed with Division because this is missing in 2 rows that have Division, and different from Division in 1 row"


  		




  
  4
  
    
denorm_division,division_main,/_alt/1,"must be _alt-ed with Division because this is missing in 2 rows that have Division, and different from Division in 1 row"


  		




  5
  5
  
    
denorm_category,*category,,


  		




  6
  6
  
    
denorm_family,family,,


  		




  7
  
  
    
denorm_genus,genus,/_alt/1,must be _alt-ed with genus because this is missing in 1 row that has genus


  		




  
  7
  
    
denorm_genus,genus_main,/_alt/1,must be _alt-ed with genus because this is missing in 1 row that has genus


  		




  8
  8
  
    
denorm_species,specificEpithet,,"unlike VegBank, this is in fact the epithet, not the full name"


  		




  9
  9
  
    
denorm_triAbbr,infraspecificRank,,


  		




  10
  10
  
    
denorm_triName,infraspecificEpithet,,


  		












  

    
      inputs/CVS/plantConcept_/unmapped_terms.csv
    
  





  8
  8
  
    
*plantConcept_entry_sppID


  		




  9
  9
  
    
*USDAPlantsCode


  		




  10
  10
  
    
*VascularPlant


  		




  11
  
  
    
*division


  		




  
  11
  
    
division_main


  		




  12
  12
  
    
*category


  		




  
  13
  
    
genus_main


  		




  13
  14
  
    
infraspecificRank


  		




  14
  15
  
    
infraspecificRank2


  		




  15
  16
  
    
infraspecificEpithet2


  		




  ......




  24
  25
  
    
*usda_code


  		




  25
  26
  
    
*usda_growthForm


  		




  26
  27
  
    
*Subkingdom


  		




  27
  
  
    
*division


  		




  
  28
  
    
division_normalized


  		




  28
  29
  
    
*Subclass


  		




  29
  30
  
    
*composite family


  		




  30
  31
  
    
*tribe


  		




  31
  32
  
    
*composite genus


  		




  
  33
  
    
genus_normalized


  		




  32
  34
  
    
subgenus


  		




  33
  35
  
    
*section


  		




  34
  36
  
    
*composite species


  		




  35
  37
  
    
speciesBinomial


  		




  
  38
  
    
division


  		












Also available in:  Unified diff