Project

General

Profile

« Previous | Next » 

Revision 10688

Added by Aaron Marcuse-Kubitza over 11 years ago

inputs/VegBank/taxonobservation_/create.sql: also join to plantname, since plantconcept.plantname may not always be populated when plantname.plantname is

View differences:

inputs/VegBank/taxonobservation_/create.sql
1 1 
SELECT *
2 2 
FROM taxonobservation
3 3 
LEFT JOIN taxoninterpretation USING (taxonobservation_id)
4 
LEFT JOIN plantconcept_ USING (plantconcept_id)
5 
; -- runtime: 15 s ("Time: 13141.443 ms") @starscream
4 
LEFT JOIN plantconcept_       USING (plantconcept_id)
5 
LEFT JOIN plantname           USING (plantname_id)
6 
; -- runtime: 15 s ("Time: 14639.599 ms") @starscream
inputs/VegBank/taxonobservation_/header.csv
1 
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,plantname_id,plantconcept_reference_id,plantname,plantcode,plantdescription,d_obscount,d_currentaccepted,plantconcept_accessioncode,Kingdom,Subkingdom,Superdivision,Division,Subdivision,Class,Subclass,Order,Family,Genus,Species,Subspecies,Variety,Forma
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
inputs/VegBank/taxonobservation_/map.csv
43 43 
taxoninterpretation_accessioncode,*taxoninterpretation_accessioncode,,
44 44 
plantname_id,*plantname_id,,
45 45 
plantconcept_reference_id,*plantconcept_reference_id,,
46 
plantname,scientificName,/_alt/2,"when int_currplantscifull also provided, this often leaves out lower ranks. however, int_currplantscifull is not always provided when this is."
46 
plantname,scientificName,/_alt/2/_alt/1,"when int_currplantscifull also provided, this often leaves out lower ranks. however, int_currplantscifull is not always provided when this is."
47 47 
plantcode,*plantcode,,
48 48 
plantdescription,*plantdescription,,
49 49 
d_obscount,*d_obscount,,
......
63 63 
Subspecies,scientificName,/_alt/3/_alt/3,*full* taxonomic name at that rank
64 64 
Variety,scientificName,/_alt/3/_alt/2,*full* taxonomic name at that rank
65 65 
Forma,scientificName,/_alt/3/_alt/1,*full* taxonomic name at that rank
66 
plantname,scientificName,/_alt/2/_alt/2,
67 
plantname_reference_id,*plantname_reference_id,,
68 
dateentered,*dateentered,,
66 69 
:plot,observationGranularity,,
inputs/VegBank/taxonobservation_/VegBIEN.csv
22 22 
collectionnumber,"/location/locationevent/taxonoccurrence/authortaxoncode/_if[@name=""if in plot""]/then/_first/2/_first/3",
23 23 
int_origplantscinamenoauth,"/location/locationevent/taxonoccurrence/authortaxoncode/_if[@name=""if in plot""]/then/_first/3/_first/1",
24 24 
int_currplantscifull,"/location/locationevent/taxonoccurrence/authortaxoncode/_if[@name=""if in plot""]/then/_first/3/_first/2/_alt/1",
25 
plantname,"/location/locationevent/taxonoccurrence/authortaxoncode/_if[@name=""if in plot""]/then/_first/3/_first/2/_alt/2","when int_currplantscifull also provided, this often leaves out lower ranks. however, int_currplantscifull is not always provided when this is."
25 
plantname,"/location/locationevent/taxonoccurrence/authortaxoncode/_if[@name=""if in plot""]/then/_first/3/_first/2/_alt/2/_alt/1","when int_currplantscifull also provided, this often leaves out lower ranks. however, int_currplantscifull is not always provided when this is."
26 
plantname,"/location/locationevent/taxonoccurrence/authortaxoncode/_if[@name=""if in plot""]/then/_first/3/_first/2/_alt/2/_alt/2",
26 27 
Forma,"/location/locationevent/taxonoccurrence/authortaxoncode/_if[@name=""if in plot""]/then/_first/3/_first/2/_alt/3/_alt/1",*full* taxonomic name at that rank
27 28 
Variety,"/location/locationevent/taxonoccurrence/authortaxoncode/_if[@name=""if in plot""]/then/_first/3/_first/2/_alt/3/_alt/2",*full* taxonomic name at that rank
28 29 
Subspecies,"/location/locationevent/taxonoccurrence/authortaxoncode/_if[@name=""if in plot""]/then/_first/3/_first/2/_alt/3/_alt/3",*full* taxonomic name at that rank
......
38 39 
Kingdom,"/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonverbatim/*_id/taxonlabel/parent_id/taxonlabel/parent_id/_forEach:[in:[cultivar,forma,variety,subspecies,species,genus,family,order,class,phylum,],do=""taxonlabel[rank=_val]/parent_id""]/taxonlabel[rank=kingdom]/taxonepithet",
39 40 
Family,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonverbatim/*_id/taxonlabel/taxonomicname/_merge_prefix/prefix/_taxon_family_require_std/family,
40 41 
int_currplantscifull,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonverbatim/*_id/taxonlabel/taxonomicname/_merge_prefix/value/_alt/1/_alt/1,
41 
plantname,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonverbatim/*_id/taxonlabel/taxonomicname/_merge_prefix/value/_alt/1/_alt/2,"when int_currplantscifull also provided, this often leaves out lower ranks. however, int_currplantscifull is not always provided when this is."
42 
plantname,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonverbatim/*_id/taxonlabel/taxonomicname/_merge_prefix/value/_alt/1/_alt/2/_alt/1,"when int_currplantscifull also provided, this often leaves out lower ranks. however, int_currplantscifull is not always provided when this is."
43 
plantname,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonverbatim/*_id/taxonlabel/taxonomicname/_merge_prefix/value/_alt/1/_alt/2/_alt/2,
42 44 
Forma,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonverbatim/*_id/taxonlabel/taxonomicname/_merge_prefix/value/_alt/1/_alt/3/_alt/1,*full* taxonomic name at that rank
43 45 
Variety,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonverbatim/*_id/taxonlabel/taxonomicname/_merge_prefix/value/_alt/1/_alt/3/_alt/2,*full* taxonomic name at that rank
44 46 
Subspecies,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonverbatim/*_id/taxonlabel/taxonomicname/_merge_prefix/value/_alt/1/_alt/3/_alt/3,*full* taxonomic name at that rank
......
49 51 
Genus,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonverbatim/genus,includes genus author
50 52 
int_currplantscinamenoauth,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonverbatim/taxonname,
51 53 
int_currplantscifull,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonverbatim/taxonomicname/_alt/1,
52 
plantname,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonverbatim/taxonomicname/_alt/2,"when int_currplantscifull also provided, this often leaves out lower ranks. however, int_currplantscifull is not always provided when this is."
54 
plantname,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonverbatim/taxonomicname/_alt/2/_alt/1,"when int_currplantscifull also provided, this often leaves out lower ranks. however, int_currplantscifull is not always provided when this is."
55 
plantname,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonverbatim/taxonomicname/_alt/2/_alt/2,
53 56 
Forma,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonverbatim/taxonomicname/_alt/3/_alt/1,*full* taxonomic name at that rank
54 57 
Variety,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonverbatim/taxonomicname/_alt/3/_alt/2,*full* taxonomic name at that rank
55 58 
Subspecies,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonverbatim/taxonomicname/_alt/3/_alt/3,*full* taxonomic name at that rank
......
71 74 
currentinterpretation,,** No join mapping for *currentinterpretation **
72 75 
d_currentaccepted,,** No join mapping for *d_currentaccepted **
73 76 
d_obscount,,** No join mapping for *d_obscount **
77 
dateentered,,** No join mapping for *dateentered **
74 78 
emb_taxoninterpretation,,** No join mapping for *emb_taxoninterpretation **
75 79 
emb_taxonobservation,,** No join mapping for *emb_taxonobservation **
76 80 
grouptype,,** No join mapping for *grouptype **
......
91 95 
plantconcept_reference_id,,** No join mapping for *plantconcept_reference_id **
92 96 
plantdescription,,** No join mapping for *plantdescription **
93 97 
plantname_id,,** No join mapping for *plantname_id **
98 
plantname_reference_id,,** No join mapping for *plantname_reference_id **
94 99 
reference_id,,** No join mapping for *reference_id **
95 100 
revisions,,** No join mapping for *revisions **
96 101 
role_id,,** No join mapping for *role_id **
inputs/VegBank/taxonobservation_/test.xml.ref
100 100 
                                                                    <1>
101 101 
                                                                        <_alt>
102 102 
                                                                            <1>$int_currplantscifull</1>
103 
                                                                            <2>$plantname</2>
103 
                                                                            <2>
104 
                                                                                <_alt>
105 
                                                                                    <1>$plantname</1>
106 
                                                                                    <2>$plantname</2>
107 
                                                                                </_alt>
108 
                                                                            </2>
104 109 
                                                                            <3>
105 110 
                                                                                <_alt>
106 111 
                                                                                    <1>$Forma</1>
......
129 134 
                                            <taxonomicname>
130 135 
                                                <_alt>
131 136 
                                                    <1>$int_currplantscifull</1>
132 
                                                    <2>$plantname</2>
137 
                                                    <2>
138 
                                                        <_alt>
139 
                                                            <1>$plantname</1>
140 
                                                            <2>$plantname</2>
141 
                                                        </_alt>
142 
                                                    </2>
133 143 
                                                    <3>
134 144 
                                                        <_alt>
135 145 
                                                            <1>$Forma</1>
inputs/VegBank/taxonobservation_/new_terms.csv
7 7 
int_currplantcommon,vernacularName,,
8 8 
interpretationdate,dateIdentified,,
9 9 
collectiondate,dateCollected,,
10 
plantname,scientificName,/_alt/2,"when int_currplantscifull also provided, this often leaves out lower ranks. however, int_currplantscifull is not always provided when this is."
10 
plantname,scientificName,/_alt/2/_alt/1,"when int_currplantscifull also provided, this often leaves out lower ranks. however, int_currplantscifull is not always provided when this is."
11 
plantname,scientificName,/_alt/2/_alt/2,
inputs/VegBank/taxonobservation_/unmapped_terms.csv
37 37 
*Division
38 38 
*Subdivision
39 39 
*Subclass
40 
*plantname_reference_id
41 
*dateentered

Also available in: Unified diff