Revision 5682
Added by Aaron Marcuse-Kubitza about 12 years ago
schemas/vegbien.my.sql | ||
---|---|---|
1110 | 1110 |
-- |
1111 | 1111 |
|
1112 | 1112 |
CREATE VIEW analytical_db_view AS |
1113 |
SELECT datasource.organizationname AS `dataSourceName`, taxonverbatim.family, taxonverbatim.genus, taxonverbatim.species, COALESCE(taxonverbatim.taxonomicname, taxonverbatim.binomial) AS taxon, taxonverbatim.author AS `taxonAuthor`, taxonverbatim.morphospecies 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 taxonverbatim ON ((taxonverbatim.taxonlabel_id = taxondetermination.taxonverbatim_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));
|
|
1113 |
SELECT datasource.organizationname AS `dataSourceName`, taxonverbatim.family, taxonverbatim.genus, taxonverbatim.species, COALESCE(taxonverbatim.taxonomicname, taxonverbatim.binomial) AS taxon, taxonverbatim.author AS `taxonAuthor`, taxonverbatim.morphospecies 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 taxonverbatim USING (taxonverbatim_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));
|
|
1114 | 1114 |
|
1115 | 1115 |
|
1116 | 1116 |
-- |
schemas/vegbien.sql | ||
---|---|---|
1575 | 1575 |
-- |
1576 | 1576 |
|
1577 | 1577 |
CREATE VIEW analytical_db_view AS |
1578 |
SELECT datasource.organizationname AS "dataSourceName", taxonverbatim.family, taxonverbatim.genus, taxonverbatim.species, COALESCE(taxonverbatim.taxonomicname, taxonverbatim.binomial) AS taxon, taxonverbatim.author AS "taxonAuthor", taxonverbatim.morphospecies 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", _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 taxonverbatim ON ((taxonverbatim.taxonlabel_id = taxondetermination.taxonverbatim_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));
|
|
1578 |
SELECT datasource.organizationname AS "dataSourceName", taxonverbatim.family, taxonverbatim.genus, taxonverbatim.species, COALESCE(taxonverbatim.taxonomicname, taxonverbatim.binomial) AS taxon, taxonverbatim.author AS "taxonAuthor", taxonverbatim.morphospecies 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", _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 taxonverbatim USING (taxonverbatim_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));
|
|
1579 | 1579 |
|
1580 | 1580 |
|
1581 | 1581 |
-- |
Also available in: Unified diff
schemas/vegbien.sql: analytical_db_view: Fixed bug where needed to join to taxonverbatim on taxonverbatim_id (the pkey) instead of taxonlabel_id, which used to be the pkey but is now an fkey