Revision 5596
Added by Aaron Marcuse-Kubitza over 11 years ago
vegbien.my.sql | ||
---|---|---|
203 | 203 |
matched_concept_id int(11), |
204 | 204 |
matched_concept_fit_fraction double precision, |
205 | 205 |
parent_id int(11), |
206 |
taxonname text,
|
|
206 |
taxonepithet text,
|
|
207 | 207 |
rank text, |
208 | 208 |
verbatimrank text, |
209 | 209 |
identifyingtaxonomicname text, |
... | ... | |
275 | 275 |
|
276 | 276 |
|
277 | 277 |
-- |
278 |
-- Name: COLUMN taxonconcept.taxonname; Type: COMMENT; Schema: public; Owner: -
|
|
278 |
-- Name: COLUMN taxonconcept.taxonepithet; Type: COMMENT; Schema: public; Owner: -
|
|
279 | 279 |
-- |
280 | 280 |
|
281 | 281 |
|
... | ... | |
1062 | 1062 |
-- |
1063 | 1063 |
|
1064 | 1064 |
CREATE VIEW analytical_db_view AS |
1065 |
SELECT datasource.organizationname AS `dataSourceName`, accepted_taxonconcept.family, accepted_taxonconcept.genus, accepted_taxonconcept.species, COALESCE(accepted_taxonconcept.taxonomicnamewithauthor, accepted_taxonconcept.taxonomicname) AS taxon, accepted_taxonconcept.author AS `taxonAuthor`, accepted_taxonconcept.taxonname 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 taxonconcept datasource_taxonconcept USING (taxonconcept_id)) JOIN taxonconcept accepted_taxonconcept ON ((accepted_taxonconcept.taxonconcept_id = datasource_taxonconcept.accepted_concept_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN specimenreplicate USING (plantobservation_id));
|
|
1065 |
SELECT datasource.organizationname AS `dataSourceName`, accepted_taxonconcept.family, accepted_taxonconcept.genus, accepted_taxonconcept.species, COALESCE(accepted_taxonconcept.taxonomicnamewithauthor, accepted_taxonconcept.taxonomicname) AS taxon, accepted_taxonconcept.author AS `taxonAuthor`, accepted_taxonconcept.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 taxonconcept datasource_taxonconcept USING (taxonconcept_id)) JOIN taxonconcept accepted_taxonconcept ON ((accepted_taxonconcept.taxonconcept_id = datasource_taxonconcept.accepted_concept_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN specimenreplicate USING (plantobservation_id));
|
|
1066 | 1066 |
|
1067 | 1067 |
|
1068 | 1068 |
-- |
Also available in: Unified diff
schemas/vegbien.sql: taxonconcept: Renamed taxonname to taxonepithet for clarity and to be consistent with TCS's use of "epithet" to denote what the taxonname was intended to be (http://www.tdwg.org/standards/117/download/#/UserGuidev_1.3.pdf)