Project

General

Profile

« Previous | Next » 

Revision 10998

Added by Aaron Marcuse-Kubitza over 11 years ago

inputs/VegBank/taxonobservation_/create.sql: join starting with taxoninterpretation so that we can use the taxoninterpretation_id as the row_num (text strings, formed from concatenated #s cannot be used as a row_num). there is only 1 taxonobservation without a taxoninterpretation, so we can just include one row for each taxoninterpretation.

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]


  		




  1
  6
  
    
SELECT *


  		




  2
  7
  
    
FROM taxonobservation


  		




  3
  8
  
    
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)


  		




  4
  14
  
    
LEFT JOIN plantconcept_       USING (plantconcept_id)


  		




  5
  15
  
    
LEFT JOIN plantname           USING (plantname_id)


  		




  6
  
  
    
; -- runtime: 15 s ("Time: 14639.599 ms") @starscream


  		




  
  16
  
    
; -- runtime: 15 s ("Time: 15034.628 ms") @starscream


  		












  

    
      inputs/VegBank/taxonobservation_/header.csv
    
  





  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_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,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


  		












  

    
      inputs/VegBank/taxonobservation_/postprocess.sql
    
  





  5
  5
  
    
$$_alt("scientificName_verbatim", "scientificName__for_nearest_taxon_concept")$$)


  		




  6
  6
  
    
; -- runtime: 15 s ("Time: 12334.893 ms") @starscream


  		




  7
  7
  
    

  		




  8
  
  
    
SELECT mk_derived_col((:table_str, 'identificationID'),


  		




  9
  
  
    
$$_join("taxonOccurrenceID"::text, "identificationID__taxon_determination"::text)$$)


  		




  10
  
  
    
; -- runtime: 20 s ("Time: 21724.277 ms") @starscream


  		




  11
  8
  
    

  		




  12
  
  
    

  		




  13
  
  
    
SELECT create_if_not_exists($$ALTER TABLE $$||:table_str||$$ ADD PRIMARY KEY ("identificationID")$$); -- runtime: 20 s ("Time: 19328.716 ms") @starscream


  		




  
  9
  
    
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,,


  		




  5
  
  
    
observation_id,eventID,,


  		




  6
  
  
    
authorplantname,originalScientificName,,


  		




  7
  
  
    
taxonobservation_reference_id,*taxonobservation_--taxonobservation_reference_id,,


  		




  8
  
  
    
taxoninferencearea,*taxonobservation_--taxoninferencearea,,


  		




  9
  
  
    
emb_taxonobservation,*taxonobservation_--emb_taxonobservation,,


  		




  10
  
  
    
int_origplantconcept_id,*taxonobservation_--int_origplantconcept_id,,


  		




  11
  
  
    
int_origplantscifull,EQUIV#to:authorplantname#int_origplantscifull,, = originalScientificName_scrubbed


  		




  12
  
  
    
int_origplantscinamenoauth,originalTaxonName,,


  		




  13
  
  
    
int_origplantcommon,originalVernacularName,,


  		




  14
  
  
    
int_origplantcode,*taxonobservation_--int_origplantcode,,


  		




  15
  
  
    
int_currplantconcept_id,*taxonobservation_--int_currplantconcept_id,,


  		




  16
  
  
    
int_currplantscifull,scientificName_verbatim,,


  		




  17
  
  
    
int_currplantscinamenoauth,taxonName,,


  		




  18
  
  
    
int_currplantcommon,vernacularName,,


  		




  19
  
  
    
int_currplantcode,*taxonobservation_--int_currplantcode,,


  		




  20
  
  
    
taxonobservation_accessioncode,*taxonobservation_--taxonobservation_accessioncode,,


  		




  21
  
  
    
taxoninterpretation_id,identificationID__taxon_determination,,


  		




  
  5
  
    
taxoninterpretation_id,identificationID,,


  		




  22
  6
  
    
stemlocation_id,*taxonobservation_--stemlocation_id,,


  		




  23
  7
  
    
taxoninterpretation_plantname_id,*taxonobservation_--taxoninterpretation_plantname_id,,


  		




  24
  8
  
    
taxoninterpretation_party_id,*taxonobservation_--taxoninterpretation_party_id,,


  		




  ......




  42
  26
  
    
collectiondate,dateCollected,,


  		




  43
  27
  
    
emb_taxoninterpretation,*taxonobservation_--emb_taxoninterpretation,,


  		




  44
  28
  
    
taxoninterpretation_accessioncode,*taxonobservation_--taxoninterpretation_accessioncode,,


  		




  
  29
  
    
observation_id,eventID,,


  		




  
  30
  
    
authorplantname,originalScientificName,,


  		




  
  31
  
    
taxonobservation_reference_id,*taxonobservation_--taxonobservation_reference_id,,


  		




  
  32
  
    
taxoninferencearea,*taxonobservation_--taxoninferencearea,,


  		




  
  33
  
    
emb_taxonobservation,*taxonobservation_--emb_taxonobservation,,


  		




  
  34
  
    
int_origplantconcept_id,*taxonobservation_--int_origplantconcept_id,,


  		




  
  35
  
    
int_origplantscifull,EQUIV#to:authorplantname#int_origplantscifull,, = originalScientificName_scrubbed


  		




  
  36
  
    
int_origplantscinamenoauth,originalTaxonName,,


  		




  
  37
  
    
int_origplantcommon,originalVernacularName,,


  		




  
  38
  
    
int_origplantcode,*taxonobservation_--int_origplantcode,,


  		




  
  39
  
    
int_currplantconcept_id,*taxonobservation_--int_currplantconcept_id,,


  		




  
  40
  
    
int_currplantscifull,scientificName_verbatim,,


  		




  
  41
  
    
int_currplantscinamenoauth,taxonName,,


  		




  
  42
  
    
int_currplantcommon,vernacularName,,


  		




  
  43
  
    
int_currplantcode,*taxonobservation_--int_currplantcode,,


  		




  
  44
  
    
taxonobservation_accessioncode,*taxonobservation_--taxonobservation_accessioncode,,


  		




  45
  45
  
    
plantconcept_reference_id,*taxonobservation_--plantconcept_reference_id,,


  		




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


  		




  47
  47
  
    
plantcode,*taxonobservation_--plantcode,,


  		












  

    
      inputs/VegBank/taxonobservation_/test.xml.ref
    
  





  137
  137
  
    
        </path>


  		




  138
  138
  
    
    </_setDefault>


  		




  139
  139
  
    
</VegBIEN>


  		




  140
  
  
    
Inserted 21 new rows into database


  		




  
  140
  
    
Inserted 17 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,*taxonobservation_--int_origplantconcept_id,,


  		




  7
  
  
    
int_origplantscifull,EQUIV#to:authorplantname#int_origplantscifull,, = originalScientificName_scrubbed


  		




  8
  
  
    
int_origplantscinamenoauth,originalTaxonName,,


  		




  9
  
  
    
int_origplantcommon,originalVernacularName,,


  		




  10
  
  
    
int_origplantcode,*taxonobservation_--int_origplantcode,,


  		




  11
  
  
    
int_currplantconcept_id,*taxonobservation_--int_currplantconcept_id,,


  		




  12
  
  
    
int_currplantscifull,scientificName_verbatim,,


  		




  13
  
  
    
int_currplantscinamenoauth,taxonName,,


  		




  14
  
  
    
int_currplantcommon,vernacularName,,


  		




  15
  
  
    
int_currplantcode,*taxonobservation_--int_currplantcode,,


  		




  16
  
  
    
taxonobservation_accessioncode,*taxonobservation_--taxonobservation_accessioncode,,


  		




  17
  
  
    
taxoninterpretation_id,identificationID__taxon_determination,,


  		




  
  3
  
    
taxoninterpretation_id,identificationID,,


  		




  18
  4
  
    
stemlocation_id,*taxonobservation_--stemlocation_id,,


  		




  19
  5
  
    
taxoninterpretation_plantname_id,*taxonobservation_--taxoninterpretation_plantname_id,,


  		




  20
  6
  
    
taxoninterpretation_party_id,*taxonobservation_--taxoninterpretation_party_id,,


  		




  ......




  31
  17
  
    
collectiondate,dateCollected,,


  		




  32
  18
  
    
emb_taxoninterpretation,*taxonobservation_--emb_taxoninterpretation,,


  		




  33
  19
  
    
taxoninterpretation_accessioncode,*taxonobservation_--taxoninterpretation_accessioncode,,


  		




  
  20
  
    
observation_id,eventID,,


  		




  
  21
  
    
taxoninferencearea,*taxonobservation_--taxoninferencearea,,


  		




  
  22
  
    
emb_taxonobservation,*taxonobservation_--emb_taxonobservation,,


  		




  
  23
  
    
int_origplantconcept_id,*taxonobservation_--int_origplantconcept_id,,


  		




  
  24
  
    
int_origplantscifull,EQUIV#to:authorplantname#int_origplantscifull,, = originalScientificName_scrubbed


  		




  
  25
  
    
int_origplantscinamenoauth,originalTaxonName,,


  		




  
  26
  
    
int_origplantcommon,originalVernacularName,,


  		




  
  27
  
    
int_origplantcode,*taxonobservation_--int_origplantcode,,


  		




  
  28
  
    
int_currplantconcept_id,*taxonobservation_--int_currplantconcept_id,,


  		




  
  29
  
    
int_currplantscifull,scientificName_verbatim,,


  		




  
  30
  
    
int_currplantscinamenoauth,taxonName,,


  		




  
  31
  
    
int_currplantcommon,vernacularName,,


  		




  
  32
  
    
int_currplantcode,*taxonobservation_--int_currplantcode,,


  		




  
  33
  
    
taxonobservation_accessioncode,*taxonobservation_--taxonobservation_accessioncode,,


  		




  34
  34
  
    
plantconcept_reference_id,*taxonobservation_--plantconcept_reference_id,,


  		




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


  		




  36
  36
  
    
plantcode,*taxonobservation_--plantcode,,


  		












  

    
      inputs/VegBank/taxonobservation_/unmapped_terms.csv
    
  





  1
  1
  
    
*taxonobservation_--plantname_id


  		




  2
  2
  
    
*taxonobservation_--plantconcept_id


  		




  3
  
  
    
*taxonobservation_--taxonobservation_reference_id


  		




  4
  
  
    
*taxonobservation_--taxoninferencearea


  		




  5
  
  
    
*taxonobservation_--emb_taxonobservation


  		




  6
  
  
    
*taxonobservation_--int_origplantconcept_id


  		




  7
  
  
    
originalVernacularName


  		




  8
  
  
    
*taxonobservation_--int_origplantcode


  		




  9
  
  
    
*taxonobservation_--int_currplantconcept_id


  		




  10
  
  
    
scientificName_verbatim


  		




  11
  
  
    
vernacularName


  		




  12
  
  
    
*taxonobservation_--int_currplantcode


  		




  13
  
  
    
*taxonobservation_--taxonobservation_accessioncode


  		




  14
  
  
    
identificationID__taxon_determination


  		




  
  3
  
    
identificationID


  		




  15
  4
  
    
*taxonobservation_--stemlocation_id


  		




  16
  5
  
    
*taxonobservation_--taxoninterpretation_plantname_id


  		




  17
  6
  
    
*taxonobservation_--taxoninterpretation_party_id


  		




  ......




  27
  16
  
    
*taxonobservation_--revisions


  		




  28
  17
  
    
*taxonobservation_--emb_taxoninterpretation


  		




  29
  18
  
    
*taxonobservation_--taxoninterpretation_accessioncode


  		




  
  19
  
    
*taxonobservation_--taxonobservation_reference_id


  		




  
  20
  
    
*taxonobservation_--taxoninferencearea


  		




  
  21
  
    
*taxonobservation_--emb_taxonobservation


  		




  
  22
  
    
*taxonobservation_--int_origplantconcept_id


  		




  
  23
  
    
originalVernacularName


  		




  
  24
  
    
*taxonobservation_--int_origplantcode


  		




  
  25
  
    
*taxonobservation_--int_currplantconcept_id


  		




  
  26
  
    
scientificName_verbatim


  		




  
  27
  
    
vernacularName


  		




  
  28
  
    
*taxonobservation_--int_currplantcode


  		




  
  29
  
    
*taxonobservation_--taxonobservation_accessioncode


  		




  30
  30
  
    
*taxonobservation_--plantconcept_reference_id


  		




  31
  31
  
    
scientificName__for_nearest_taxon_concept


  		




  32
  32
  
    
*taxonobservation_--plantcode


  		












Also available in:  Unified diff