Project

General

Profile

« Previous | Next » 

Revision 11520

Added by Aaron Marcuse-Kubitza over 11 years ago

inputs/VegBank/taxonobservation_/create.sql: join taxonobservation to taxoninterpretation (as in CVS) instead of vice versa, since taxonobservation is the primary, operative table. having VegBank and CVS do things the same way helps ensure that fixes in one can transfer easily to the other.

View differences:

inputs/VegBank/taxonobservation_/create.sql
1 
/* there is only 1 taxonobservation without a taxoninterpretation [1], so we can
2 
just include one row for each taxoninterpretation. this allows us to use the
3 
taxoninterpretation_id as the row_num.
4 

  		




  5
  
  
    
[1]


  		




  6
  1
  
    
SELECT *


  		




  7
  2
  
    
FROM taxonobservation


  		




  8
  3
  
    
LEFT JOIN taxoninterpretation USING (taxonobservation_id)


  		




  9
  
  
    
WHERE taxoninterpretation_id IS NULL


  		




  10
  
  
    
*/


  		




  11
  
  
    
SELECT *


  		




  12
  
  
    
FROM taxoninterpretation


  		




  13
  
  
    
LEFT JOIN taxonobservation    USING (taxonobservation_id)


  		




  14
  4
  
    
LEFT JOIN plantconcept_       USING (plantconcept_id)


  		




  15
  5
  
    
LEFT JOIN plantname           USING (plantname_id)


  		




  16
  
  
    
; -- runtime: 20 s ("Time: 20279.441 ms") @starscream


  		




  
  6
  
    
; -- runtime: 20 s ("18564.276") @starscream


  		












  

    
      inputs/VegBank/taxonobservation_/header.csv
    
  





  1
  
  
    
plantname_id,plantconcept_id,taxonobservation_id,taxoninterpretation_id,stemlocation_id,taxoninterpretation_plantname_id,taxoninterpretation_party_id,role_id,interpretationtype,reference_id,originalinterpretation,currentinterpretation,taxonfit,taxonconfidence,collector_id,collectionnumber,museum_id,museumaccessionnumber,grouptype,notes,notespublic,notesmgt,revisions,interpretationdate,collectiondate,emb_taxoninterpretation,taxoninterpretation_accessioncode,observation_id,authorplantname,taxonobservation_reference_id,taxoninferencearea,emb_taxonobservation,int_origplantconcept_id,int_origplantscifull,int_origplantscinamenoauth,int_origplantcommon,int_origplantcode,int_currplantconcept_id,int_currplantscifull,int_currplantscinamenoauth,int_currplantcommon,int_currplantcode,taxonobservation_accessioncode,plantconcept_reference_id,plantconcept_plantname,plantcode,plantdescription,d_obscount,d_currentaccepted,plantconcept_accessioncode,Kingdom,Subkingdom,Superdivision,Division,Subdivision,Class,Subclass,Order,Family,Genus,Species,Subspecies,Variety,Forma,plantname,plantname_reference_id,dateentered


  		




  
  1
  
    
plantname_id,plantconcept_id,taxonobservation_id,observation_id,authorplantname,taxonobservation_reference_id,taxoninferencearea,emb_taxonobservation,int_origplantconcept_id,int_origplantscifull,int_origplantscinamenoauth,int_origplantcommon,int_origplantcode,int_currplantconcept_id,int_currplantscifull,int_currplantscinamenoauth,int_currplantcommon,int_currplantcode,taxonobservation_accessioncode,taxoninterpretation_id,stemlocation_id,taxoninterpretation_plantname_id,taxoninterpretation_party_id,role_id,interpretationtype,reference_id,originalinterpretation,currentinterpretation,taxonfit,taxonconfidence,collector_id,collectionnumber,museum_id,museumaccessionnumber,grouptype,notes,notespublic,notesmgt,revisions,interpretationdate,collectiondate,emb_taxoninterpretation,taxoninterpretation_accessioncode,*plantconcept_--plantconcept_reference_id,scientificName__for_nearest_taxon_concept,*plantconcept_--plantcode,*plantconcept_--plantdescription,*plantconcept_--d_obscount,*plantconcept_--d_currentaccepted,*plantconcept_--plantconcept_accessioncode,kingdom,*Subkingdom,*Superdivision,*Division,*Subdivision,class,*Subclass,order,family,genus,speciesBinomial,EQUIV#to:Species#Subspecies,EQUIV#to:Species#Variety,EQUIV#to:Species#Forma,plantname,plantname_reference_id,dateentered


  		












  

    
      inputs/VegBank/taxonobservation_/postprocess.sql
    
  





  1
  1
  
    
SELECT util.search_path_append('util');


  		




  2
  2
  
    

  		




  3
  3
  
    
SELECT create_if_not_exists($$CREATE INDEX $$||:table_str||%'__parent'||$$ ON $$||:table_str||$$ ("eventID")$$); -- runtime: 2 s ("2416.676 ms") @starscream


  		




  4
  
  
    

  		




  5
  
  
    
SELECT create_if_not_exists($$ALTER TABLE $$||:table_str||$$ ADD PRIMARY KEY ("identificationID")$$); -- runtime: 1 s ("Time: 1342.115 ms") @starscream


  		












  

    
      inputs/VegBank/taxonobservation_/map.csv
    
  





  2
  2
  
    
plantname_id,*taxonobservation_--plantname_id,,


  		




  3
  3
  
    
plantconcept_id,*taxonobservation_--plantconcept_id,,


  		




  4
  4
  
    
taxonobservation_id,taxonOccurrenceID__overall_plot,,


  		




  
  5
  
    
observation_id,eventID,,


  		




  
  6
  
    
authorplantname,OMIT#authorplantname,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead). = originalScientificName


  		




  
  7
  
    
taxonobservation_reference_id,*taxonobservation_--taxonobservation_reference_id,,


  		




  
  8
  
    
taxoninferencearea,*taxonobservation_--taxoninferencearea,,


  		




  
  9
  
    
emb_taxonobservation,*taxonobservation_--emb_taxonobservation,,


  		




  
  10
  
    
int_origplantconcept_id,OMIT#int_origplantconcept_id,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead).


  		




  
  11
  
    
int_origplantscifull,OMIT#int_origplantscifull,,"omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead). = EQUIV#to:authorplantname#int_origplantscifull, originalScientificName_scrubbed"


  		




  
  12
  
    
int_origplantscinamenoauth,OMIT#int_origplantscinamenoauth,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead). = originalTaxonName


  		




  
  13
  
    
int_origplantcommon,OMIT#int_origplantcommon,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead). = originalVernacularName


  		




  
  14
  
    
int_origplantcode,OMIT#int_origplantcode,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead).


  		




  
  15
  
    
int_currplantconcept_id,OMIT#int_currplantconcept_id,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead).


  		




  
  16
  
    
int_currplantscifull,OMIT#int_currplantscifull,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead). = scientificName_verbatim


  		




  
  17
  
    
int_currplantscinamenoauth,OMIT#int_currplantscinamenoauth,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead). = taxonName


  		




  
  18
  
    
int_currplantcommon,OMIT#int_currplantcommon,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead). = vernacularName


  		




  
  19
  
    
int_currplantcode,OMIT#int_currplantcode,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead).


  		




  
  20
  
    
taxonobservation_accessioncode,*taxonobservation_--taxonobservation_accessioncode,,


  		




  5
  21
  
    
taxoninterpretation_id,identificationID,,


  		




  6
  22
  
    
stemlocation_id,*taxonobservation_--stemlocation_id,,


  		




  7
  23
  
    
taxoninterpretation_plantname_id,*taxonobservation_--taxoninterpretation_plantname_id,,


  		




  ......




  26
  42
  
    
collectiondate,dateCollected,,


  		




  27
  43
  
    
emb_taxoninterpretation,*taxonobservation_--emb_taxoninterpretation,,


  		




  28
  44
  
    
taxoninterpretation_accessioncode,*taxonobservation_--taxoninterpretation_accessioncode,,


  		




  29
  
  
    
observation_id,eventID,,


  		




  30
  
  
    
authorplantname,OMIT#authorplantname,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead). = originalScientificName


  		




  31
  
  
    
taxonobservation_reference_id,*taxonobservation_--taxonobservation_reference_id,,


  		




  32
  
  
    
taxoninferencearea,*taxonobservation_--taxoninferencearea,,


  		




  33
  
  
    
emb_taxonobservation,*taxonobservation_--emb_taxonobservation,,


  		




  34
  
  
    
int_origplantconcept_id,OMIT#int_origplantconcept_id,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead).


  		




  35
  
  
    
int_origplantscifull,OMIT#int_origplantscifull,,"omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead). = EQUIV#to:authorplantname#int_origplantscifull, originalScientificName_scrubbed"


  		




  36
  
  
    
int_origplantscinamenoauth,OMIT#int_origplantscinamenoauth,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead). = originalTaxonName


  		




  37
  
  
    
int_origplantcommon,OMIT#int_origplantcommon,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead). = originalVernacularName


  		




  38
  
  
    
int_origplantcode,OMIT#int_origplantcode,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead).


  		




  39
  
  
    
int_currplantconcept_id,OMIT#int_currplantconcept_id,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead).


  		




  40
  
  
    
int_currplantscifull,OMIT#int_currplantscifull,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead). = scientificName_verbatim


  		




  41
  
  
    
int_currplantscinamenoauth,OMIT#int_currplantscinamenoauth,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead). = taxonName


  		




  42
  
  
    
int_currplantcommon,OMIT#int_currplantcommon,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead). = vernacularName


  		




  43
  
  
    
int_currplantcode,OMIT#int_currplantcode,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead).


  		




  44
  
  
    
taxonobservation_accessioncode,*taxonobservation_--taxonobservation_accessioncode,,


  		




  45
  45
  
    
*plantconcept_--plantconcept_reference_id,*plantconcept_--plantconcept_reference_id,,


  		




  46
  46
  
    
scientificName__for_nearest_taxon_concept,scientificName,,"when int_currplantscifull also provided, this often leaves out lower ranks. however, int_currplantscifull is not always provided when this is."


  		




  47
  47
  
    
*plantconcept_--plantcode,*plantconcept_--plantcode,,


  		












  

    
      inputs/VegBank/taxonobservation_/test.xml.ref
    
  





  126
  126
  
    
        </path>


  		




  127
  127
  
    
    </_setDefault>


  		




  128
  128
  
    
</VegBIEN>


  		




  129
  
  
    
Inserted 13 new rows into database


  		




  
  129
  
    
Inserted 20 new rows into database


  		












  

    
      inputs/VegBank/taxonobservation_/new_terms.csv
    
  





  1
  1
  
    
plantname_id,*taxonobservation_--plantname_id,,


  		




  2
  2
  
    
plantconcept_id,*taxonobservation_--plantconcept_id,,


  		




  
  3
  
    
observation_id,eventID,,


  		




  
  4
  
    
taxoninferencearea,*taxonobservation_--taxoninferencearea,,


  		




  
  5
  
    
emb_taxonobservation,*taxonobservation_--emb_taxonobservation,,


  		




  
  6
  
    
int_origplantconcept_id,OMIT#int_origplantconcept_id,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead).


  		




  
  7
  
    
int_origplantscifull,OMIT#int_origplantscifull,,"omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead). = EQUIV#to:authorplantname#int_origplantscifull, originalScientificName_scrubbed"


  		




  
  8
  
    
int_origplantscinamenoauth,OMIT#int_origplantscinamenoauth,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead). = originalTaxonName


  		




  
  9
  
    
int_origplantcommon,OMIT#int_origplantcommon,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead). = originalVernacularName


  		




  
  10
  
    
int_origplantcode,OMIT#int_origplantcode,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead).


  		




  
  11
  
    
int_currplantconcept_id,OMIT#int_currplantconcept_id,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead).


  		




  
  12
  
    
int_currplantscifull,OMIT#int_currplantscifull,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead). = scientificName_verbatim


  		




  
  13
  
    
int_currplantscinamenoauth,OMIT#int_currplantscinamenoauth,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead). = taxonName


  		




  
  14
  
    
int_currplantcommon,OMIT#int_currplantcommon,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead). = vernacularName


  		




  
  15
  
    
int_currplantcode,OMIT#int_currplantcode,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead).


  		




  
  16
  
    
taxonobservation_accessioncode,*taxonobservation_--taxonobservation_accessioncode,,


  		




  3
  17
  
    
taxoninterpretation_id,identificationID,,


  		




  4
  18
  
    
stemlocation_id,*taxonobservation_--stemlocation_id,,


  		




  5
  19
  
    
taxoninterpretation_plantname_id,*taxonobservation_--taxoninterpretation_plantname_id,,


  		




  ......




  15
  29
  
    
collectiondate,dateCollected,,


  		




  16
  30
  
    
emb_taxoninterpretation,*taxonobservation_--emb_taxoninterpretation,,


  		




  17
  31
  
    
taxoninterpretation_accessioncode,*taxonobservation_--taxoninterpretation_accessioncode,,


  		




  18
  
  
    
observation_id,eventID,,


  		




  19
  
  
    
taxoninferencearea,*taxonobservation_--taxoninferencearea,,


  		




  20
  
  
    
emb_taxonobservation,*taxonobservation_--emb_taxonobservation,,


  		




  21
  
  
    
int_origplantconcept_id,OMIT#int_origplantconcept_id,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead).


  		




  22
  
  
    
int_origplantscifull,OMIT#int_origplantscifull,,"omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead). = EQUIV#to:authorplantname#int_origplantscifull, originalScientificName_scrubbed"


  		




  23
  
  
    
int_origplantscinamenoauth,OMIT#int_origplantscinamenoauth,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead). = originalTaxonName


  		




  24
  
  
    
int_origplantcommon,OMIT#int_origplantcommon,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead). = originalVernacularName


  		




  25
  
  
    
int_origplantcode,OMIT#int_origplantcode,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead).


  		




  26
  
  
    
int_currplantconcept_id,OMIT#int_currplantconcept_id,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead).


  		




  27
  
  
    
int_currplantscifull,OMIT#int_currplantscifull,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead). = scientificName_verbatim


  		




  28
  
  
    
int_currplantscinamenoauth,OMIT#int_currplantscinamenoauth,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead). = taxonName


  		




  29
  
  
    
int_currplantcommon,OMIT#int_currplantcommon,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead). = vernacularName


  		




  30
  
  
    
int_currplantcode,OMIT#int_currplantcode,,omitted because not specific to the taxoninterpretation row (this is in a separate taxoninterpretation for the original determination instead).


  		




  31
  
  
    
taxonobservation_accessioncode,*taxonobservation_--taxonobservation_accessioncode,,


  		




  32
  32
  
    
scientificName__for_nearest_taxon_concept,scientificName,,"when int_currplantscifull also provided, this often leaves out lower ranks. however, int_currplantscifull is not always provided when this is."


  		




  33
  33
  
    
plantname,DUPLICATE#of:plantconcept_plantname#plantname,,exact duplicate


  		




  34
  34
  
    
plantname_reference_id,*taxonobservation_--plantname_reference_id,,


  		












  

    
      inputs/VegBank/taxonobservation_/unmapped_terms.csv
    
  





  1
  1
  
    
*taxonobservation_--plantname_id


  		




  2
  2
  
    
*taxonobservation_--plantconcept_id


  		




  3
  3
  
    
taxonOccurrenceID__overall_plot


  		




  
  4
  
    
*taxonobservation_--taxonobservation_reference_id


  		




  
  5
  
    
*taxonobservation_--taxoninferencearea


  		




  
  6
  
    
*taxonobservation_--emb_taxonobservation


  		




  
  7
  
    
*taxonobservation_--taxonobservation_accessioncode


  		




  4
  8
  
    
identificationID


  		




  5
  9
  
    
*taxonobservation_--stemlocation_id


  		




  6
  10
  
    
*taxonobservation_--taxoninterpretation_plantname_id


  		




  ......




  15
  19
  
    
*taxonobservation_--revisions


  		




  16
  20
  
    
*taxonobservation_--emb_taxoninterpretation


  		




  17
  21
  
    
*taxonobservation_--taxoninterpretation_accessioncode


  		




  18
  
  
    
*taxonobservation_--taxonobservation_reference_id


  		




  19
  
  
    
*taxonobservation_--taxoninferencearea


  		




  20
  
  
    
*taxonobservation_--emb_taxonobservation


  		




  21
  
  
    
*taxonobservation_--taxonobservation_accessioncode


  		




  22
  22
  
    
*plantconcept_--plantconcept_reference_id


  		




  23
  23
  
    
*plantconcept_--plantcode


  		




  24
  24
  
    
*plantconcept_--plantdescription


  		












Also available in:  Unified diff