Revision 4944
Added by Aaron Marcuse-Kubitza about 12 years ago
| schemas/vegbien.sql | ||
|---|---|---|
| 1118 | 1118 |
canon_taxonpath_id integer, |
| 1119 | 1119 |
taxon_id integer, |
| 1120 | 1120 |
scientificname text, |
| 1121 |
scientificnameauthor text,
|
|
| 1121 |
author text, |
|
| 1122 | 1122 |
scientificnamewithauthor text, |
| 1123 | 1123 |
domain text, |
| 1124 | 1124 |
kingdom text, |
| ... | ... | |
| 1146 | 1146 |
|
| 1147 | 1147 |
|
| 1148 | 1148 |
-- |
| 1149 |
-- Name: COLUMN taxonpath.author; Type: COMMENT; Schema: public; Owner: - |
|
| 1150 |
-- |
|
| 1151 |
|
|
| 1152 |
COMMENT ON COLUMN taxonpath.author IS 'The author of the scientific name'; |
|
| 1153 |
|
|
| 1154 |
|
|
| 1155 |
-- |
|
| 1149 | 1156 |
-- Name: COLUMN taxonpath.scientificnamewithauthor; Type: COMMENT; Schema: public; Owner: - |
| 1150 | 1157 |
-- |
| 1151 | 1158 |
|
| ... | ... | |
| 1171 | 1178 |
-- |
| 1172 | 1179 |
|
| 1173 | 1180 |
CREATE VIEW analytical_db_view AS |
| 1174 |
SELECT datasource.organizationname AS "dataSourceName", taxonpath.family, taxonpath.genus, taxonpath.species, COALESCE(taxonpath.scientificnamewithauthor, taxonpath.scientificname) AS taxon, taxonpath.scientificnameauthor AS "taxonAuthor", taxonpath.variety AS "taxonMorphospecies", placepath.country, placepath.stateprovince AS "stateProvince", placepath.county AS "countyParish", taxonoccurrence.verbatimcollectorname AS collector, plantobservation.collectionnumber AS "collectionNumber", array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], ' '::text) AS "identifiedBy", aggregateoccurrence.collectiondate AS "observationDate", location.authorlocationcode AS "plotCode", functions._m2_to_ha(location.area_m2) AS "plotAreaHa", method.name AS "plotMethod", locationcoords.latitude_deg AS latitude, locationcoords.longitude_deg AS longitude, location.elevation_m, taxonoccurrence.iscultivated AS "isCultivated", taxonoccurrence.cultivatedbasis AS "isCultivatedReason", functions._fraction_to_percent(aggregateoccurrence.cover_fraction) AS "pctCover" FROM (((((((((((((location JOIN party datasource ON (((datasource.party_id = location.datasource_id) AND (datasource.organizationname IS NOT NULL)))) LEFT JOIN locationcoords USING (location_id)) LEFT JOIN locationplace USING (location_id)) LEFT JOIN placepath USING (placepath_id)) JOIN locationevent USING (location_id)) LEFT JOIN method USING (method_id)) JOIN taxonoccurrence USING (locationevent_id)) JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) JOIN taxonpath USING (taxonpath_id)) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN specimenreplicate USING (plantobservation_id));
|
|
| 1181 |
SELECT datasource.organizationname AS "dataSourceName", taxonpath.family, taxonpath.genus, taxonpath.species, COALESCE(taxonpath.scientificnamewithauthor, taxonpath.scientificname) AS taxon, taxonpath.author AS "taxonAuthor", taxonpath.variety AS "taxonMorphospecies", placepath.country, placepath.stateprovince AS "stateProvince", placepath.county AS "countyParish", taxonoccurrence.verbatimcollectorname AS collector, plantobservation.collectionnumber AS "collectionNumber", array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], ' '::text) AS "identifiedBy", aggregateoccurrence.collectiondate AS "observationDate", location.authorlocationcode AS "plotCode", functions._m2_to_ha(location.area_m2) AS "plotAreaHa", method.name AS "plotMethod", locationcoords.latitude_deg AS latitude, locationcoords.longitude_deg AS longitude, location.elevation_m, taxonoccurrence.iscultivated AS "isCultivated", taxonoccurrence.cultivatedbasis AS "isCultivatedReason", functions._fraction_to_percent(aggregateoccurrence.cover_fraction) AS "pctCover" FROM (((((((((((((location JOIN party datasource ON (((datasource.party_id = location.datasource_id) AND (datasource.organizationname IS NOT NULL)))) LEFT JOIN locationcoords USING (location_id)) LEFT JOIN locationplace USING (location_id)) LEFT JOIN placepath USING (placepath_id)) JOIN locationevent USING (location_id)) LEFT JOIN method USING (method_id)) JOIN taxonoccurrence USING (locationevent_id)) JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) JOIN taxonpath USING (taxonpath_id)) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN specimenreplicate USING (plantobservation_id)); |
|
| 1175 | 1182 |
|
| 1176 | 1183 |
|
| 1177 | 1184 |
-- |
| ... | ... | |
| 4679 | 4686 |
-- Name: taxonpath_unique_within_datasource_by_name; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4680 | 4687 |
-- |
| 4681 | 4688 |
|
| 4682 |
CREATE UNIQUE INDEX taxonpath_unique_within_datasource_by_name ON taxonpath USING btree (datasource_id, (COALESCE(taxon_id, 2147483647)), (COALESCE(scientificname, '\\N'::text)), (COALESCE(scientificnameauthor, '\\N'::text)), (COALESCE(scientificnamewithauthor, '\\N'::text)), (COALESCE(domain, '\\N'::text)), (COALESCE(kingdom, '\\N'::text)), (COALESCE(phylum, '\\N'::text)), (COALESCE(class, '\\N'::text)), (COALESCE("order", '\\N'::text)), (COALESCE(family, '\\N'::text)), (COALESCE(genus, '\\N'::text)), (COALESCE(species, '\\N'::text)), (COALESCE(subspecies, '\\N'::text)), (COALESCE(variety, '\\N'::text)), (COALESCE(forma, '\\N'::text)), (COALESCE(cultivar, '\\N'::text)), (COALESCE(otherranks, ARRAY[]::rankedtaxonname[])), (COALESCE(scope_id, 2147483647))) WHERE (plantcode IS NULL);
|
|
| 4689 |
CREATE UNIQUE INDEX taxonpath_unique_within_datasource_by_name ON taxonpath USING btree (datasource_id, (COALESCE(taxon_id, 2147483647)), (COALESCE(scientificname, '\\N'::text)), (COALESCE(author, '\\N'::text)), (COALESCE(scientificnamewithauthor, '\\N'::text)), (COALESCE(domain, '\\N'::text)), (COALESCE(kingdom, '\\N'::text)), (COALESCE(phylum, '\\N'::text)), (COALESCE(class, '\\N'::text)), (COALESCE("order", '\\N'::text)), (COALESCE(family, '\\N'::text)), (COALESCE(genus, '\\N'::text)), (COALESCE(species, '\\N'::text)), (COALESCE(subspecies, '\\N'::text)), (COALESCE(variety, '\\N'::text)), (COALESCE(forma, '\\N'::text)), (COALESCE(cultivar, '\\N'::text)), (COALESCE(otherranks, ARRAY[]::rankedtaxonname[])), (COALESCE(scope_id, 2147483647))) WHERE (plantcode IS NULL);
|
|
| 4683 | 4690 |
|
| 4684 | 4691 |
|
| 4685 | 4692 |
-- |
| mappings/for_review/VegCore-VegBIEN.csv | ||
|---|---|---|
| 126 | 126 |
identifiedBy,//taxonoccurrence/taxondetermination[]/*_id/party/givenname, |
| 127 | 127 |
identifiedBy,//taxonoccurrence/taxondetermination[]/*_id/party/middlename, |
| 128 | 128 |
identifiedBy,//taxonoccurrence/taxondetermination[]/*_id/party/surname, |
| 129 |
scientificNameAuthorship,//taxonoccurrence/taxondetermination[]/*_id/taxonpath/author, |
|
| 129 | 130 |
class,//taxonoccurrence/taxondetermination[]/*_id/taxonpath/class, |
| 130 | 131 |
cultivar,//taxonoccurrence/taxondetermination[]/*_id/taxonpath/cultivar, |
| 131 | 132 |
family,//taxonoccurrence/taxondetermination[]/*_id/taxonpath/family, |
| ... | ... | |
| 135 | 136 |
order,//taxonoccurrence/taxondetermination[]/*_id/taxonpath/order, |
| 136 | 137 |
phylum,//taxonoccurrence/taxondetermination[]/*_id/taxonpath/phylum, |
| 137 | 138 |
scientificName,//taxonoccurrence/taxondetermination[]/*_id/taxonpath/scientificname, |
| 138 |
scientificNameAuthorship,//taxonoccurrence/taxondetermination[]/*_id/taxonpath/scientificnameauthor, |
|
| 139 | 139 |
scientificNameWithAuthorship,//taxonoccurrence/taxondetermination[]/*_id/taxonpath/scientificnamewithauthor, |
| 140 | 140 |
specificEpithet,//taxonoccurrence/taxondetermination[]/*_id/taxonpath/species, |
| 141 | 141 |
subspecies,//taxonoccurrence/taxondetermination[]/*_id/taxonpath/subspecies, |
| ... | ... | |
| 148 | 148 |
yearIdentified,//taxonoccurrence/taxondetermination[]/determinationdate, |
| 149 | 149 |
taxonRemarks,//taxonoccurrence/taxondetermination[]/notes, |
| 150 | 150 |
identificationQualifier,//taxonoccurrence/taxondetermination[]/taxonfit, |
| 151 |
verbatimScientificNameAuthorship,//taxonoccurrence/taxondetermination[isoriginal=true]/*_id/taxonpath/author, |
|
| 151 | 152 |
verbatimClass,//taxonoccurrence/taxondetermination[isoriginal=true]/*_id/taxonpath/class, |
| 152 | 153 |
verbatimFamily,//taxonoccurrence/taxondetermination[isoriginal=true]/*_id/taxonpath/family, |
| 153 | 154 |
verbatimGenus,//taxonoccurrence/taxondetermination[isoriginal=true]/*_id/taxonpath/genus, |
| ... | ... | |
| 155 | 156 |
verbatimOrder,//taxonoccurrence/taxondetermination[isoriginal=true]/*_id/taxonpath/order, |
| 156 | 157 |
verbatimPhylum,//taxonoccurrence/taxondetermination[isoriginal=true]/*_id/taxonpath/phylum, |
| 157 | 158 |
verbatimScientificName,//taxonoccurrence/taxondetermination[isoriginal=true]/*_id/taxonpath/scientificname, |
| 158 |
verbatimScientificNameAuthorship,//taxonoccurrence/taxondetermination[isoriginal=true]/*_id/taxonpath/scientificnameauthor, |
|
| 159 | 159 |
verbatimSpecificEpithet,//taxonoccurrence/taxondetermination[isoriginal=true]/*_id/taxonpath/species, |
| 160 | 160 |
verbatimInfraspecificEpithet,//taxonoccurrence/taxondetermination[isoriginal=true]/*_id/taxonpath/subspecies, |
| 161 | 161 |
verbatimIdentificationQualifier,//taxonoccurrence/taxondetermination[isoriginal=true]/taxonfit, |
| inputs/XAL/Specimen/VegBIEN.csv | ||
|---|---|---|
| 22 | 22 |
darwin:IdentifiedBy,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/party/givenname/_namePart/first, |
| 23 | 23 |
darwin:IdentifiedBy,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/party/middlename/_namePart/middle, |
| 24 | 24 |
darwin:IdentifiedBy,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/party/surname/_namePart/last, |
| 25 |
darwin:ScientificNameAuthor,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/author, |
|
| 25 | 26 |
darwin:Class,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/class, |
| 26 | 27 |
darwin:Family,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/family, |
| 27 | 28 |
darwin:Genus,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/genus, |
| ... | ... | |
| 29 | 30 |
darwin:Order,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/order, |
| 30 | 31 |
darwin:Phylum,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/phylum, |
| 31 | 32 |
darwin:ScientificName,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/scientificname, |
| 32 |
darwin:ScientificNameAuthor,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/scientificnameauthor, |
|
| 33 | 33 |
darwin:Species,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/species, |
| 34 | 34 |
darwin:Subspecies,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/subspecies/_alt/1, |
| 35 | 35 |
darwin:DayIdentified,"/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/determinationdate/_alt/2/_date/day/_nullIf:[null=0,type=float]/value", |
| inputs/NY/Specimen/VegBIEN.csv | ||
|---|---|---|
| 27 | 27 |
IdentifiedBy,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/party/givenname/_namePart/first, |
| 28 | 28 |
IdentifiedBy,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/party/middlename/_namePart/middle, |
| 29 | 29 |
IdentifiedBy,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/party/surname/_namePart/last, |
| 30 |
ScientificNameAuthor,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/author, |
|
| 30 | 31 |
Class,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/class, |
| 31 | 32 |
Family,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/family, |
| 32 | 33 |
Genus,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/genus, |
| ... | ... | |
| 34 | 35 |
Order,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/order, |
| 35 | 36 |
Phylum,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/phylum, |
| 36 | 37 |
ScientificName,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/scientificname, |
| 37 |
ScientificNameAuthor,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/scientificnameauthor, |
|
| 38 | 38 |
Species,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/species, |
| 39 | 39 |
Subspecies,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/subspecies/_alt/1, |
| 40 | 40 |
DayIdentified,"/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/determinationdate/_alt/2/_date/day/_nullIf:[null=0,type=float]/value", |
| inputs/NY/Specimen/test.xml.ref | ||
|---|---|---|
| 116 | 116 |
</party_id> |
| 117 | 117 |
<taxonpath_id> |
| 118 | 118 |
<taxonpath> |
| 119 |
<author>$ScientificNameAuthor</author> |
|
| 119 | 120 |
<class>$Class</class> |
| 120 | 121 |
<family>$Family</family> |
| 121 | 122 |
<genus>$Genus</genus> |
| ... | ... | |
| 123 | 124 |
<order>$Order</order> |
| 124 | 125 |
<phylum>$Phylum</phylum> |
| 125 | 126 |
<scientificname>$ScientificName</scientificname> |
| 126 |
<scientificnameauthor>$ScientificNameAuthor</scientificnameauthor> |
|
| 127 | 127 |
<species>$Species</species> |
| 128 | 128 |
<subspecies>$Subspecies</subspecies> |
| 129 | 129 |
</taxonpath> |
| inputs/Madidi/Organism/VegBIEN.csv | ||
|---|---|---|
| 31 | 31 |
Determined by,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/party/givenname/_namePart/first, |
| 32 | 32 |
Determined by,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/party/middlename/_namePart/middle, |
| 33 | 33 |
Determined by,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/party/surname/_namePart/last, |
| 34 |
Autor (revised),/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/author, |
|
| 34 | 35 |
Family (revised),/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/family, |
| 35 | 36 |
Genera,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/genus, |
| 36 | 37 |
Species and morphotypes,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/scientificname,"Contains less than ""Specie+autor""" |
| 37 |
Autor (revised),/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/scientificnameauthor, |
|
| 38 | 38 |
Specie+autor,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/scientificnamewithauthor, |
| 39 | 39 |
Specific epithet,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/species, |
| 40 | 40 |
Qualifier,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/taxonfit, |
| inputs/Madidi/Organism/test.xml.ref | ||
|---|---|---|
| 70 | 70 |
</party_id> |
| 71 | 71 |
<taxonpath_id> |
| 72 | 72 |
<taxonpath> |
| 73 |
<author>$Autor (revised)</author> |
|
| 73 | 74 |
<family>$Family (revised)</family> |
| 74 | 75 |
<genus>$Genera</genus> |
| 75 | 76 |
<scientificname>$Species and morphotypes</scientificname> |
| 76 |
<scientificnameauthor>$Autor (revised)</scientificnameauthor> |
|
| 77 | 77 |
<scientificnamewithauthor>$Specie+autor</scientificnamewithauthor> |
| 78 | 78 |
<species>$Specific epithet</species> |
| 79 | 79 |
</taxonpath> |
| inputs/UNCC/Specimen/VegBIEN.csv | ||
|---|---|---|
| 18 | 18 |
SciName,/location/locationevent/taxonoccurrence/authortaxoncode/_first/2/_first/3, |
| 19 | 19 |
cultivated,/location/locationevent/taxonoccurrence/iscultivated/_alt/1,Ignore other values |
| 20 | 20 |
accession,/location/locationevent/taxonoccurrence/sourceaccessioncode/_first/3, |
| 21 |
authors,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/author, |
|
| 21 | 22 |
family,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/family, |
| 22 | 23 |
genus,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/genus, |
| 23 | 24 |
SciName,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/scientificname, |
| 24 |
authors,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/scientificnameauthor, |
|
| 25 | 25 |
species,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/species, |
| 26 | 26 |
usdaRank,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/subspecies/_alt/2/_name/first/_alt/1, |
| 27 | 27 |
infrarank,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/subspecies/_alt/2/_name/first/_alt/2, |
| inputs/UNCC/Specimen/test.xml.ref | ||
|---|---|---|
| 78 | 78 |
<taxondetermination> |
| 79 | 79 |
<taxonpath_id> |
| 80 | 80 |
<taxonpath> |
| 81 |
<author>$authors</author> |
|
| 81 | 82 |
<family>$family</family> |
| 82 | 83 |
<genus>$genus</genus> |
| 83 | 84 |
<scientificname>$SciName</scientificname> |
| 84 |
<scientificnameauthor>$authors</scientificnameauthor> |
|
| 85 | 85 |
<species>$species</species> |
| 86 | 86 |
<subspecies> |
| 87 | 87 |
<_name> |
| inputs/ACAD/Specimen/VegBIEN.csv | ||
|---|---|---|
| 21 | 21 |
identifiedBy,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/party/givenname/_namePart/first, |
| 22 | 22 |
identifiedBy,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/party/middlename/_namePart/middle, |
| 23 | 23 |
identifiedBy,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/party/surname/_namePart/last, |
| 24 |
scientificNameAuthorship,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/author, |
|
| 24 | 25 |
class,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/class, |
| 25 | 26 |
family,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/family, |
| 26 | 27 |
genus,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/genus, |
| ... | ... | |
| 28 | 29 |
order,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/order, |
| 29 | 30 |
phylum,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/phylum, |
| 30 | 31 |
scientificName,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/scientificname, |
| 31 |
scientificNameAuthorship,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/scientificnameauthor, |
|
| 32 | 32 |
specificEpithet,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/species, |
| 33 | 33 |
taxonRank,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/subspecies/_alt/2/_name/first, |
| 34 | 34 |
infraspecificEpithet,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/subspecies/_alt/2/_name/last, |
| inputs/ACAD/Specimen/test.xml.ref | ||
|---|---|---|
| 76 | 76 |
</party_id> |
| 77 | 77 |
<taxonpath_id> |
| 78 | 78 |
<taxonpath> |
| 79 |
<author>$scientificNameAuthorship</author> |
|
| 79 | 80 |
<class>$class</class> |
| 80 | 81 |
<family>$family</family> |
| 81 | 82 |
<genus>$genus</genus> |
| ... | ... | |
| 83 | 84 |
<order>$order</order> |
| 84 | 85 |
<phylum>$phylum</phylum> |
| 85 | 86 |
<scientificname>$scientificName</scientificname> |
| 86 |
<scientificnameauthor>$scientificNameAuthorship</scientificnameauthor> |
|
| 87 | 87 |
<species>$specificEpithet</species> |
| 88 | 88 |
<subspecies> |
| 89 | 89 |
<_name> |
| inputs/SALVIAS-CSV/Organism/VegBIEN.csv | ||
|---|---|---|
| 62 | 62 |
tag1,"/location/locationevent/taxonoccurrence/authortaxoncode/_first/1/_first/2/_if[@name=""if is organism""]/then/_alt/2/_alt/2","The second tag supercedes the first. The stem tag supercedes the tree tag. Brad: Another type of code, typically a number, used by the original data provider to indicate an individual tree. These are numbers on physical tags attached to the tree. Tag2 Is the same thing, only used if the first tag was lost. Obviously not a good system as it's possible a tree tag could be lost and changed more than once." |
| 63 | 63 |
habit,"/location/locationevent/taxonoccurrence/growthform/_map:[./{T,Arbol,palm}=tree,./{H,""Hemiepífito""}=hemiepiphyte,./{L,l,""L?"",Liana}=liana,E=epiphyte,S=shrub,Hb=herb,""vine-herbaceous""=vine,graminoid=grass,rosette=forb,*=*]/value","Brad: This is growth form (tree, shrub, herb, etc.). It is an observation of a trait.; According to <http://salvias.net/Documents/salvias_data_dictionary.html#Plot+data.habit>, <https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/SALVIAS#Habit>"
|
| 64 | 64 |
OBSERVATION_ID,/location/locationevent/taxonoccurrence/sourceaccessioncode/_first/2,"Brad: Neither is correct; this is just an internal ID for table plotObservations. However, it has the important property of uniquely identifying an ""observation"", which is an individual tree, in the case of an individual observation, or a records of a species with an associated count of individuals or measurement of percent cover, in the case of aggregate observations. Not sure where to store this. Main point is that it is not part of the original data, but an auto_increment added later." |
| 65 |
specific_authority,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/author,Brad: This is the author of the scientificName. |
|
| 65 | 66 |
family,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/family, |
| 66 | 67 |
genus,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/genus, |
| 67 |
specific_authority,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/scientificnameauthor,Brad: This is the author of the scientificName. |
|
| 68 | 68 |
specific_epithet,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/species, |
| 69 | 69 |
infra_rank_1,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/subspecies/_alt/2/_name/first, |
| 70 | 70 |
infra_ep_1,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/subspecies/_alt/2/_name/last, |
| inputs/SALVIAS-CSV/Organism/test.xml.ref | ||
|---|---|---|
| 147 | 147 |
<taxondetermination> |
| 148 | 148 |
<taxonpath_id> |
| 149 | 149 |
<taxonpath> |
| 150 |
<author>$specific_authority</author> |
|
| 150 | 151 |
<family>$family</family> |
| 151 | 152 |
<genus>$genus</genus> |
| 152 |
<scientificnameauthor>$specific_authority</scientificnameauthor> |
|
| 153 | 153 |
<species>$specific_epithet</species> |
| 154 | 154 |
<subspecies> |
| 155 | 155 |
<_name> |
| inputs/CTFS/StemObservation/VegBIEN.csv | ||
|---|---|---|
| 20 | 20 |
StemTag,"/location/locationevent/taxonoccurrence/authortaxoncode/_first/1/_first/2/_if[@name=""if is organism""]/then", |
| 21 | 21 |
Tag,"/location/locationevent/taxonoccurrence/authortaxoncode/_first/1/_first/2/_if[@name=""if is organism""]/then", |
| 22 | 22 |
MeasureID,/location/locationevent/taxonoccurrence/sourceaccessioncode/_first/2, |
| 23 |
SpeciesAuthority,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/author, |
|
| 23 | 24 |
Family,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/family, |
| 24 | 25 |
Genus,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/genus, |
| 25 |
SpeciesAuthority,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/scientificnameauthor, |
|
| 26 | 26 |
SpeciesName,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/species, |
| 27 | 27 |
SubSpeciesName,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/subspecies/_alt/1, |
| 28 | 28 |
Description,,** No join mapping for Description ** |
| inputs/CTFS/StemObservation/test.xml.ref | ||
|---|---|---|
| 41 | 41 |
<taxondetermination> |
| 42 | 42 |
<taxonpath_id> |
| 43 | 43 |
<taxonpath> |
| 44 |
<author>$SpeciesAuthority</author> |
|
| 44 | 45 |
<family>$Family</family> |
| 45 | 46 |
<genus>$Genus</genus> |
| 46 |
<scientificnameauthor>$SpeciesAuthority</scientificnameauthor> |
|
| 47 | 47 |
<species>$SpeciesName</species> |
| 48 | 48 |
<subspecies>$SubSpeciesName</subspecies> |
| 49 | 49 |
</taxonpath> |
| inputs/SALVIAS/plotObservations/VegBIEN.csv | ||
|---|---|---|
| 55 | 55 |
tag1,"/location/locationevent/taxonoccurrence/authortaxoncode/_first/1/_first/2/_if[@name=""if is organism""]/then/_alt/2","The second tag supercedes the first. Brad: Another type of code, typically a number, used by the original data provider to indicate an individual tree. These are numbers on physical tags attached to the tree. Tag2 Is the same thing, only used if the first tag was lost. Obviously not a good system as it's possible a tree tag could be lost and changed more than once." |
| 56 | 56 |
Habit,"/location/locationevent/taxonoccurrence/growthform/_map:[./{T,Arbol,palm}=tree,./{H,""Hemiepífito""}=hemiepiphyte,./{L,l,""L?"",Liana}=liana,E=epiphyte,S=shrub,Hb=herb,""vine-herbaceous""=vine,graminoid=grass,rosette=forb,*=*]/value","Brad: This is growth form (tree, shrub, herb, etc.). It is an observation of a trait.; According to <http://salvias.net/Documents/salvias_data_dictionary.html#Plot+data.habit>, <https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/SALVIAS#Habit>"
|
| 57 | 57 |
PlotObsID,/location/locationevent/taxonoccurrence/sourceaccessioncode/_first/2,"Brad: Neither is correct; this is just an internal ID for table plotObservations. However, it has the important property of uniquely identifying an ""observation"", which is an individual tree, in the case of an individual observation, or a records of a species with an associated count of individuals or measurement of percent cover, in the case of aggregate observations. Not sure where to store this. Main point is that it is not part of the original data, but an auto_increment added later." |
| 58 |
auth,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/author,Brad: This is the author of the scientificName. |
|
| 58 | 59 |
Family,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/family, |
| 59 | 60 |
Genus,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/genus, |
| 60 |
auth,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/scientificnameauthor,Brad: This is the author of the scientificName. |
|
| 61 | 61 |
Species,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/species, |
| 62 | 62 |
infra_rank_1,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/subspecies/_alt/2/_name/first, |
| 63 | 63 |
infra_ep_1,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/subspecies/_alt/2/_name/last, |
| inputs/SALVIAS/plotObservations/test.xml.ref | ||
|---|---|---|
| 118 | 118 |
<taxondetermination> |
| 119 | 119 |
<taxonpath_id> |
| 120 | 120 |
<taxonpath> |
| 121 |
<author>$auth</author> |
|
| 121 | 122 |
<family>$Family</family> |
| 122 | 123 |
<genus>$Genus</genus> |
| 123 |
<scientificnameauthor>$auth</scientificnameauthor> |
|
| 124 | 124 |
<species>$Species</species> |
| 125 | 125 |
<subspecies> |
| 126 | 126 |
<_name> |
| inputs/GBIF/Specimen/VegBIEN.csv | ||
|---|---|---|
| 15 | 15 |
IdentifiedBy,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/party/givenname/_namePart/first, |
| 16 | 16 |
IdentifiedBy,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/party/middlename/_namePart/middle, |
| 17 | 17 |
IdentifiedBy,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/party/surname/_namePart/last, |
| 18 |
AuthorOfScientificName,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/author, |
|
| 18 | 19 |
Family,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/family, |
| 19 | 20 |
Genus,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/genus, |
| 20 | 21 |
ScientificName,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/scientificname, |
| 21 |
AuthorOfScientificName,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/scientificnameauthor, |
|
| 22 | 22 |
SpecificEpithet,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/species, |
| 23 | 23 |
InfraspecificEpithet,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/subspecies/_alt/2/_name/last, |
| 24 | 24 |
GBIFFamilyOriginal,/location/locationevent/taxonoccurrence/taxondetermination[isoriginal=true]/*_id/taxonpath/family, |
| inputs/GBIF/Specimen/test.xml.ref | ||
|---|---|---|
| 56 | 56 |
</party_id> |
| 57 | 57 |
<taxonpath_id> |
| 58 | 58 |
<taxonpath> |
| 59 |
<author>$AuthorOfScientificName</author> |
|
| 59 | 60 |
<family>$Family</family> |
| 60 | 61 |
<genus>$Genus</genus> |
| 61 | 62 |
<scientificname>$ScientificName</scientificname> |
| 62 |
<scientificnameauthor>$AuthorOfScientificName</scientificnameauthor> |
|
| 63 | 63 |
<species>$SpecificEpithet</species> |
| 64 | 64 |
<subspecies>$InfraspecificEpithet</subspecies> |
| 65 | 65 |
</taxonpath> |
| inputs/SpeciesLink/Specimen/VegBIEN.csv | ||
|---|---|---|
| 99 | 99 |
conceptual_darwin_2003_1_0_IdentifiedBy,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/party/surname/_namePart/last, |
| 100 | 100 |
dwc_curatorial_IdentifiedBy,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/party/surname/_namePart/last, |
| 101 | 101 |
dwc_terms_identifiedBy,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/party/surname/_namePart/last, |
| 102 |
dwc_terms_scientificNameAuthorship,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/author/_alt/1, |
|
| 103 |
conceptual_darwin_2003_1_0_ScientificNameAuthor,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/author/_alt/2, |
|
| 104 |
dwc_dwcore_AuthorYearOfScientificName,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/author/_alt/3, |
|
| 102 | 105 |
conceptual_darwin_2003_1_0_Class,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/class, |
| 103 | 106 |
dwc_dwcore_Class,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/class, |
| 104 | 107 |
dwc_terms_class,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/class, |
| ... | ... | |
| 120 | 123 |
conceptual_darwin_2003_1_0_ScientificName,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/scientificname, |
| 121 | 124 |
dwc_dwcore_ScientificName,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/scientificname, |
| 122 | 125 |
dwc_terms_scientificName,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/scientificname, |
| 123 |
dwc_terms_scientificNameAuthorship,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/scientificnameauthor/_alt/1, |
|
| 124 |
conceptual_darwin_2003_1_0_ScientificNameAuthor,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/scientificnameauthor/_alt/2, |
|
| 125 |
dwc_dwcore_AuthorYearOfScientificName,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/scientificnameauthor/_alt/3, |
|
| 126 | 126 |
conceptual_darwin_2003_1_0_Species,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/species, |
| 127 | 127 |
dwc_dwcore_SpecificEpithet,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/species, |
| 128 | 128 |
dwc_terms_specificEpithet,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/species, |
| inputs/SpeciesLink/Specimen/test.xml.ref | ||
|---|---|---|
| 272 | 272 |
</party_id> |
| 273 | 273 |
<taxonpath_id> |
| 274 | 274 |
<taxonpath> |
| 275 |
<author> |
|
| 276 |
<_alt> |
|
| 277 |
<1>$dwc_terms_scientificNameAuthorship</1> |
|
| 278 |
<2>$conceptual_darwin_2003_1_0_ScientificNameAuthor</2> |
|
| 279 |
<3>$dwc_dwcore_AuthorYearOfScientificName</3> |
|
| 280 |
</_alt> |
|
| 281 |
</author> |
|
| 275 | 282 |
<class> |
| 276 | 283 |
<_alt> |
| 277 | 284 |
<0>$conceptual_darwin_2003_1_0_Class</0> |
| ... | ... | |
| 321 | 328 |
<2>$dwc_terms_scientificName</2> |
| 322 | 329 |
</_alt> |
| 323 | 330 |
</scientificname> |
| 324 |
<scientificnameauthor> |
|
| 325 |
<_alt> |
|
| 326 |
<1>$dwc_terms_scientificNameAuthorship</1> |
|
| 327 |
<2>$conceptual_darwin_2003_1_0_ScientificNameAuthor</2> |
|
| 328 |
<3>$dwc_dwcore_AuthorYearOfScientificName</3> |
|
| 329 |
</_alt> |
|
| 330 |
</scientificnameauthor> |
|
| 331 | 331 |
<species> |
| 332 | 332 |
<_alt> |
| 333 | 333 |
<0>$conceptual_darwin_2003_1_0_Species</0> |
| inputs/MO/Specimen/VegBIEN.csv | ||
|---|---|---|
| 21 | 21 |
IdentifiedBy,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/party/givenname/_namePart/first, |
| 22 | 22 |
IdentifiedBy,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/party/middlename/_namePart/middle, |
| 23 | 23 |
IdentifiedBy,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/party/surname/_namePart/last, |
| 24 |
ScientificNameAuthor,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/author, |
|
| 24 | 25 |
family,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/family, |
| 25 | 26 |
genus,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/genus, |
| 26 | 27 |
Kingdom,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/kingdom, |
| 27 | 28 |
ScientificName,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/scientificname, |
| 28 |
ScientificNameAuthor,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/scientificnameauthor, |
|
| 29 | 29 |
species,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/species, |
| 30 | 30 |
Subspecies,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/subspecies/_alt/1, |
| 31 | 31 |
Collector,/location/locationevent/taxonoccurrence/verbatimcollectorname, |
| inputs/MO/Specimen/test.xml.ref | ||
|---|---|---|
| 88 | 88 |
</party_id> |
| 89 | 89 |
<taxonpath_id> |
| 90 | 90 |
<taxonpath> |
| 91 |
<author>$ScientificNameAuthor</author> |
|
| 91 | 92 |
<family>$family</family> |
| 92 | 93 |
<genus>$genus</genus> |
| 93 | 94 |
<kingdom>$Kingdom</kingdom> |
| 94 | 95 |
<scientificname>$ScientificName</scientificname> |
| 95 |
<scientificnameauthor>$ScientificNameAuthor</scientificnameauthor> |
|
| 96 | 96 |
<species>$species</species> |
| 97 | 97 |
<subspecies>$Subspecies</subspecies> |
| 98 | 98 |
</taxonpath> |
| inputs/QMOR/Specimen/VegBIEN.csv | ||
|---|---|---|
| 28 | 28 |
identifiedBy,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/party/givenname/_namePart/first, |
| 29 | 29 |
identifiedBy,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/party/middlename/_namePart/middle, |
| 30 | 30 |
identifiedBy,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/party/surname/_namePart/last, |
| 31 |
scientificNameAuthorship,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/author, |
|
| 31 | 32 |
class,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/class, |
| 32 | 33 |
family,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/family, |
| 33 | 34 |
genus,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/genus, |
| ... | ... | |
| 35 | 36 |
order,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/order, |
| 36 | 37 |
phylum,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/phylum, |
| 37 | 38 |
scientificName,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/scientificname, |
| 38 |
scientificNameAuthorship,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/scientificnameauthor, |
|
| 39 | 39 |
specificEpithet,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/species, |
| 40 | 40 |
taxonRank,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/subspecies/_alt/2/_name/first, |
| 41 | 41 |
infraspecificEpithet,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/subspecies/_alt/2/_name/last, |
| inputs/QMOR/Specimen/test.xml.ref | ||
|---|---|---|
| 100 | 100 |
</party_id> |
| 101 | 101 |
<taxonpath_id> |
| 102 | 102 |
<taxonpath> |
| 103 |
<author>$scientificNameAuthorship</author> |
|
| 103 | 104 |
<class>$class</class> |
| 104 | 105 |
<family>$family</family> |
| 105 | 106 |
<genus>$genus</genus> |
| ... | ... | |
| 107 | 108 |
<order>$order</order> |
| 108 | 109 |
<phylum>$phylum</phylum> |
| 109 | 110 |
<scientificname>$scientificName</scientificname> |
| 110 |
<scientificnameauthor>$scientificNameAuthorship</scientificnameauthor> |
|
| 111 | 111 |
<species>$specificEpithet</species> |
| 112 | 112 |
<subspecies> |
| 113 | 113 |
<_name> |
| schemas/vegbien.my.sql | ||
|---|---|---|
| 834 | 834 |
canon_taxonpath_id int(11), |
| 835 | 835 |
taxon_id int(11), |
| 836 | 836 |
scientificname text, |
| 837 |
scientificnameauthor text,
|
|
| 837 |
author text, |
|
| 838 | 838 |
scientificnamewithauthor text, |
| 839 | 839 |
domain text, |
| 840 | 840 |
kingdom text, |
| ... | ... | |
| 862 | 862 |
|
| 863 | 863 |
|
| 864 | 864 |
-- |
| 865 |
-- Name: COLUMN taxonpath.author; Type: COMMENT; Schema: public; Owner: - |
|
| 866 |
-- |
|
| 867 |
|
|
| 868 |
|
|
| 869 |
|
|
| 870 |
|
|
| 871 |
-- |
|
| 865 | 872 |
-- Name: COLUMN taxonpath.scientificnamewithauthor; Type: COMMENT; Schema: public; Owner: - |
| 866 | 873 |
-- |
| 867 | 874 |
|
| ... | ... | |
| 887 | 894 |
-- |
| 888 | 895 |
|
| 889 | 896 |
CREATE VIEW analytical_db_view AS |
| 890 |
SELECT datasource.organizationname AS `dataSourceName`, taxonpath.family, taxonpath.genus, taxonpath.species, COALESCE(taxonpath.scientificnamewithauthor, taxonpath.scientificname) AS taxon, taxonpath.scientificnameauthor AS `taxonAuthor`, taxonpath.variety AS `taxonMorphospecies`, placepath.country, placepath.stateprovince AS `stateProvince`, placepath.county AS `countyParish`, taxonoccurrence.verbatimcollectorname AS collector, plantobservation.collectionnumber AS `collectionNumber`, array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], CAST(' ' AS text)) AS `identifiedBy`, aggregateoccurrence.collectiondate AS `observationDate`, location.authorlocationcode AS `plotCode`, functions._m2_to_ha(location.area_m2) AS `plotAreaHa`, method.name AS `plotMethod`, locationcoords.latitude_deg AS latitude, locationcoords.longitude_deg AS longitude, location.elevation_m, taxonoccurrence.iscultivated AS `isCultivated`, taxonoccurrence.cultivatedbasis AS `isCultivatedReason`, functions._fraction_to_percent(aggregateoccurrence.cover_fraction) AS `pctCover` FROM (((((((((((((location JOIN party datasource ON (((datasource.party_id = location.datasource_id) AND (datasource.organizationname IS NOT NULL)))) LEFT JOIN locationcoords USING (location_id)) LEFT JOIN locationplace USING (location_id)) LEFT JOIN placepath USING (placepath_id)) JOIN locationevent USING (location_id)) LEFT JOIN method USING (method_id)) JOIN taxonoccurrence USING (locationevent_id)) JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) JOIN taxonpath USING (taxonpath_id)) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN specimenreplicate USING (plantobservation_id));
|
|
| 897 |
SELECT datasource.organizationname AS `dataSourceName`, taxonpath.family, taxonpath.genus, taxonpath.species, COALESCE(taxonpath.scientificnamewithauthor, taxonpath.scientificname) AS taxon, taxonpath.author AS `taxonAuthor`, taxonpath.variety AS `taxonMorphospecies`, placepath.country, placepath.stateprovince AS `stateProvince`, placepath.county AS `countyParish`, taxonoccurrence.verbatimcollectorname AS collector, plantobservation.collectionnumber AS `collectionNumber`, array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], CAST(' ' AS text)) AS `identifiedBy`, aggregateoccurrence.collectiondate AS `observationDate`, location.authorlocationcode AS `plotCode`, functions._m2_to_ha(location.area_m2) AS `plotAreaHa`, method.name AS `plotMethod`, locationcoords.latitude_deg AS latitude, locationcoords.longitude_deg AS longitude, location.elevation_m, taxonoccurrence.iscultivated AS `isCultivated`, taxonoccurrence.cultivatedbasis AS `isCultivatedReason`, functions._fraction_to_percent(aggregateoccurrence.cover_fraction) AS `pctCover` FROM (((((((((((((location JOIN party datasource ON (((datasource.party_id = location.datasource_id) AND (datasource.organizationname IS NOT NULL)))) LEFT JOIN locationcoords USING (location_id)) LEFT JOIN locationplace USING (location_id)) LEFT JOIN placepath USING (placepath_id)) JOIN locationevent USING (location_id)) LEFT JOIN method USING (method_id)) JOIN taxonoccurrence USING (locationevent_id)) JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) JOIN taxonpath USING (taxonpath_id)) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN specimenreplicate USING (plantobservation_id));
|
|
| 891 | 898 |
|
| 892 | 899 |
|
| 893 | 900 |
-- |
| mappings/VegCore-VegBIEN.csv | ||
|---|---|---|
| 126 | 126 |
identifiedBy,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/party/givenname/_namePart/first, |
| 127 | 127 |
identifiedBy,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/party/middlename/_namePart/middle, |
| 128 | 128 |
identifiedBy,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/party/surname/_namePart/last, |
| 129 |
scientificNameAuthorship,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/author, |
|
| 129 | 130 |
class,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/class, |
| 130 | 131 |
cultivar,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/cultivar, |
| 131 | 132 |
family,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/family, |
| ... | ... | |
| 135 | 136 |
order,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/order, |
| 136 | 137 |
phylum,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/phylum, |
| 137 | 138 |
scientificName,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/scientificname, |
| 138 |
scientificNameAuthorship,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/scientificnameauthor, |
|
| 139 | 139 |
scientificNameWithAuthorship,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/scientificnamewithauthor, |
| 140 | 140 |
specificEpithet,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/species, |
| 141 | 141 |
subspecies,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonpath/subspecies/_alt/1, |
| ... | ... | |
| 148 | 148 |
yearIdentified,"/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/determinationdate/_alt/2/_date/year/_nullIf:[null=0,type=float]/value", |
| 149 | 149 |
taxonRemarks,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/notes, |
| 150 | 150 |
identificationQualifier,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/taxonfit, |
| 151 |
verbatimScientificNameAuthorship,/location/locationevent/taxonoccurrence/taxondetermination[isoriginal=true]/*_id/taxonpath/author, |
|
| 151 | 152 |
verbatimClass,/location/locationevent/taxonoccurrence/taxondetermination[isoriginal=true]/*_id/taxonpath/class, |
| 152 | 153 |
verbatimFamily,/location/locationevent/taxonoccurrence/taxondetermination[isoriginal=true]/*_id/taxonpath/family, |
| 153 | 154 |
verbatimGenus,/location/locationevent/taxonoccurrence/taxondetermination[isoriginal=true]/*_id/taxonpath/genus, |
| ... | ... | |
| 155 | 156 |
verbatimOrder,/location/locationevent/taxonoccurrence/taxondetermination[isoriginal=true]/*_id/taxonpath/order, |
| 156 | 157 |
verbatimPhylum,/location/locationevent/taxonoccurrence/taxondetermination[isoriginal=true]/*_id/taxonpath/phylum, |
| 157 | 158 |
verbatimScientificName,/location/locationevent/taxonoccurrence/taxondetermination[isoriginal=true]/*_id/taxonpath/scientificname, |
| 158 |
verbatimScientificNameAuthorship,/location/locationevent/taxonoccurrence/taxondetermination[isoriginal=true]/*_id/taxonpath/scientificnameauthor, |
|
| 159 | 159 |
verbatimSpecificEpithet,/location/locationevent/taxonoccurrence/taxondetermination[isoriginal=true]/*_id/taxonpath/species, |
| 160 | 160 |
verbatimInfraspecificEpithet,/location/locationevent/taxonoccurrence/taxondetermination[isoriginal=true]/*_id/taxonpath/subspecies, |
| 161 | 161 |
verbatimIdentificationQualifier,/location/locationevent/taxonoccurrence/taxondetermination[isoriginal=true]/taxonfit, |
Also available in: Unified diff
schemas/vegbien.sql: taxonpath: Renamed scientificnameauthor to author so the column name doesn't have "scientificname" in it, which made the term look confusingly like scientificname itself. Added descriptive comment that this is the author of the scientific name.