Revision 5638
Added by Aaron Marcuse-Kubitza about 12 years ago
vegbien.my.sql | ||
---|---|---|
229 | 229 |
rank text, |
230 | 230 |
verbatimrank text, |
231 | 231 |
identifyingtaxonomicname text, |
232 |
taxonomicname text,
|
|
232 |
binomial text,
|
|
233 | 233 |
author text, |
234 | 234 |
taxonomicnamewithauthor text, |
235 | 235 |
morphospecies text, |
... | ... | |
326 | 326 |
|
327 | 327 |
|
328 | 328 |
-- |
329 |
-- Name: COLUMN taxonlabel.taxonomicname; Type: COMMENT; Schema: public; Owner: -
|
|
329 |
-- Name: COLUMN taxonlabel.binomial; Type: COMMENT; Schema: public; Owner: -
|
|
330 | 330 |
-- |
331 | 331 |
|
332 | 332 |
|
... | ... | |
1086 | 1086 |
-- |
1087 | 1087 |
|
1088 | 1088 |
CREATE VIEW analytical_db_view AS |
1089 |
SELECT datasource.organizationname AS `dataSourceName`, accepted_taxonlabel.family, accepted_taxonlabel.genus, accepted_taxonlabel.species, COALESCE(accepted_taxonlabel.taxonomicnamewithauthor, accepted_taxonlabel.taxonomicname) AS taxon, accepted_taxonlabel.author AS `taxonAuthor`, accepted_taxonlabel.taxonepithet 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`, _fraction_to_percent(aggregateoccurrence.cover_fraction) AS `pctCover` FROM ((((((((((((((location JOIN party datasource ON (((datasource.party_id = location.creator_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 taxonlabel datasource_taxonlabel USING (taxonlabel_id)) JOIN taxonlabel accepted_taxonlabel ON ((accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.canon_label_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN specimenreplicate USING (plantobservation_id));
|
|
1089 |
SELECT datasource.organizationname AS `dataSourceName`, accepted_taxonlabel.family, accepted_taxonlabel.genus, accepted_taxonlabel.species, COALESCE(accepted_taxonlabel.taxonomicnamewithauthor, accepted_taxonlabel.binomial) AS taxon, accepted_taxonlabel.author AS `taxonAuthor`, accepted_taxonlabel.taxonepithet 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`, _fraction_to_percent(aggregateoccurrence.cover_fraction) AS `pctCover` FROM ((((((((((((((location JOIN party datasource ON (((datasource.party_id = location.creator_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 taxonlabel datasource_taxonlabel USING (taxonlabel_id)) JOIN taxonlabel accepted_taxonlabel ON ((accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.canon_label_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN specimenreplicate USING (plantobservation_id));
|
|
1090 | 1090 |
|
1091 | 1091 |
|
1092 | 1092 |
-- |
Also available in: Unified diff
schemas/vegbien.sql: taxonlabel: Renamed taxonomicname to binomial because it excludes the author