| 2577 |
|
SELECT source.shortname AS datasource, sourcelist.name AS "institutionCode", specimenreplicate.collectioncode_dwc AS "collectionCode", specimenreplicate.catalognumber_dwc AS "catalogNumber", specimenreplicate.sourceaccessioncode AS "occurrenceID", canon_place.country, canon_place.stateprovince AS "stateProvince", datasource_place.county, location.locationnarrative AS locality, CASE WHEN (coordinates.latitude_deg IS NOT NULL) THEN coordinates.latitude_deg ELSE county_centroids.latitude END AS "decimalLatitude", CASE WHEN (coordinates.latitude_deg IS NOT NULL) THEN coordinates.longitude_deg ELSE county_centroids.longitude END AS "decimalLongitude", CASE WHEN (coordinates.latitude_deg IS NOT NULL) THEN coordinates.coordsaccuracy_m ELSE _km_to_m(county_centroids.error_km) END AS "coordinateUncertaintyInMeters", CASE WHEN (coordinates.latitude_deg IS NOT NULL) THEN 'source data'::coordinatesource WHEN (county_centroids.row_num IS NOT NULL) THEN 'georeferencing'::coordinatesource ELSE NULL::coordinatesource END AS "coordinateSource_bien", CASE WHEN (coordinates.latitude_deg IS NOT NULL) THEN NULL::text WHEN (county_centroids.row_num IS NOT NULL) THEN 'county centroid'::text ELSE NULL::text END AS "georeferenceProtocol_bien", (canon_place.geovalid)::integer AS geovalid_bien, ("newWorldCountries"."isNewWorld")::integer AS "isNewWorld_bien", COALESCE(project.sourceaccessioncode, project.projectname) AS "projectID", COALESCE(location.sourceaccessioncode, concat_delim('; '::text, VARIADIC ARRAY[COALESCE(parent_location.sourceaccessioncode, parent_location.authorlocationcode), location.authorlocationcode])) AS "locationID", COALESCE(parent_location.authorlocationcode, location.authorlocationcode) AS "locationName", CASE WHEN (parent_location.location_id IS NOT NULL) THEN location.authorlocationcode ELSE NULL::text END AS subplot, plantobservation.authorplantcode AS "individualCode", COALESCE(location.elevation_m, parent_location.elevation_m) AS "elevationInMeters", _m2_to_ha(COALESCE(location.area_m2, parent_location.area_m2)) AS "plotArea_ha", method.name AS "samplingProtocol", collector.fullname AS "recordedBy", plantobservation.authorplantcode AS "recordNumber", COALESCE(locationevent.obsstartdate, parent_event.obsstartdate, aggregateoccurrence.collectiondate) AS "dateCollected", datasource_taxonverbatim.family AS family_verbatim, COALESCE(datasource_taxonverbatim.taxonname, datasource_taxonverbatim.taxonomicname, datasource_taxonlabel.taxonomicname) AS "taxonName_verbatim", datasource_taxonverbatim.author AS "scientificNameAuthorship_verbatim", identifiedby.fullname AS "identifiedBy", datasource_taxondetermination.determinationdate AS "dateIdentified", datasource_taxondetermination.notes AS "identificationRemarks", matched_taxonverbatim.family AS family_matched, matched_taxonverbatim.taxonname AS "taxonName_matched", matched_taxonverbatim.author AS "scientificNameAuthorship_matched", family_higher_plant_group.higher_plant_group AS "higherPlantGroup_bien", canon_taxonverbatim.family, canon_taxonverbatim.genus, COALESCE(concat_delim(' '::text, VARIADIC ARRAY[COALESCE(canon_taxonverbatim.genus, canon_taxonverbatim.family), canon_taxonverbatim.specific_epithet, canon_taxonverbatim.morphospecies]), canon_taxonverbatim.taxonname, canon_taxonverbatim.taxonomicname) AS "speciesBinomialWithMorphospecies", canon_taxonverbatim.taxonname AS "taxonName", canon_taxonverbatim.author AS "scientificNameAuthorship", taxonoccurrence.growthform AS "growthForm", plantobservation.reproductivecondition AS "reproductiveCondition", ((threatened_taxonlabel.taxonlabel_id IS NOT NULL))::integer AS threatened_bien, (((cultivated_family_locations.country IS NOT NULL) OR _or(taxonoccurrence.iscultivated, location.iscultivated)))::integer AS cultivated_bien, CASE WHEN (taxonoccurrence.iscultivated IS NOT NULL) THEN taxonoccurrence.cultivatedbasis WHEN (location.iscultivated IS NOT NULL) THEN NULL::text ELSE NULL::text END AS "cultivatedBasis_bien", aggregateoccurrence.notes AS "occurrenceRemarks", _fraction_to_percent(aggregateoccurrence.cover_fraction) AS "coverPercent", _m_to_cm(stemobservation.diameterbreastheight_m) AS "diameterBreastHeight_cm", stemobservation.height_m, stemobservation.tag, stemobservation.xposition_m AS "organismX_m", stemobservation.yposition_m AS "organismY_m", taxonoccurrence.sourceaccessioncode AS "taxonOccurrenceID", taxonoccurrence.authortaxoncode AS "authorTaxonCode", plantobservation.sourceaccessioncode AS "individualObservationID", stemobservation.authorstemcode AS "authorStemCode" FROM ((((((((((((((((((((((((((((((((source JOIN location USING (source_id)) LEFT JOIN locationevent USING (location_id)) LEFT JOIN location parent_location ON ((parent_location.location_id = location.parent_id))) LEFT JOIN locationplace ON ((locationplace.location_id = COALESCE(parent_location.location_id, location.location_id)))) LEFT JOIN place datasource_place USING (place_id)) LEFT JOIN place canon_place ON ((canon_place.place_id = datasource_place.canon_place_id))) LEFT JOIN coordinates ON ((coordinates.coordinates_id = canon_place.coordinates_id))) LEFT JOIN "newWorld".iso_code_gadm ON ((iso_code_gadm."GADM country" = canon_place.country))) LEFT JOIN "newWorld"."newWorldCountries" ON (("newWorldCountries"."isoCode" = iso_code_gadm."2-digit iso code"))) LEFT JOIN geoscrub.county_centroids ON ((((canon_place.country = 'United States'::text) AND (county_centroids.state = canon_place.stateprovince)) AND (county_centroids.county = canon_place.county)))) LEFT JOIN taxonoccurrence USING (locationevent_id)) LEFT JOIN locationevent parent_event ON ((parent_event.locationevent_id = locationevent.parent_id))) LEFT JOIN project ON ((project.project_id = COALESCE(locationevent.project_id, parent_event.project_id)))) LEFT JOIN method ON ((method.method_id = COALESCE(locationevent.method_id, parent_event.method_id)))) LEFT JOIN party collector ON ((collector.party_id = taxonoccurrence.collector_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN stemobservation USING (plantobservation_id)) LEFT JOIN specimenreplicate USING (plantobservation_id)) LEFT JOIN sourcelist ON ((sourcelist.sourcelist_id = specimenreplicate.institution_id))) LEFT JOIN taxondetermination datasource_taxondetermination ON (((datasource_taxondetermination.taxonoccurrence_id = taxonoccurrence.taxonoccurrence_id) AND datasource_taxondetermination.is_datasource_current))) LEFT JOIN party identifiedby ON ((identifiedby.party_id = datasource_taxondetermination.party_id))) LEFT JOIN taxonverbatim datasource_taxonverbatim USING (taxonverbatim_id)) LEFT JOIN taxonlabel datasource_taxonlabel USING (taxonlabel_id)) LEFT JOIN taxondetermination matched_taxondetermination ON (((matched_taxondetermination.taxonoccurrence_id = taxonoccurrence.taxonoccurrence_id) AND (matched_taxondetermination.determinationtype = 'matched'::text)))) LEFT JOIN taxonverbatim matched_taxonverbatim ON ((matched_taxonverbatim.taxonverbatim_id = matched_taxondetermination.taxonverbatim_id))) LEFT JOIN taxondetermination canon_taxondetermination ON (((canon_taxondetermination.taxonoccurrence_id = taxonoccurrence.taxonoccurrence_id) AND canon_taxondetermination.iscurrent))) LEFT JOIN taxonverbatim canon_taxonverbatim ON ((canon_taxonverbatim.taxonverbatim_id = canon_taxondetermination.taxonverbatim_id))) LEFT JOIN taxonlabel canon_taxonlabel ON ((canon_taxonlabel.taxonlabel_id = canon_taxonverbatim.taxonlabel_id))) LEFT JOIN family_higher_plant_group ON ((family_higher_plant_group.family = canon_taxonverbatim.family))) LEFT JOIN cultivated_family_locations ON (((cultivated_family_locations.family = canon_taxonverbatim.family) AND (cultivated_family_locations.country = canon_place.country)))) LEFT JOIN threatened_taxonlabel ON ((threatened_taxonlabel.taxonlabel_id = canon_taxonlabel.taxonlabel_id)));
|
|
2579 |
SELECT source.shortname AS datasource, sourcelist.name AS "institutionCode", specimenreplicate.collectioncode_dwc AS "collectionCode", specimenreplicate.catalognumber_dwc AS "catalogNumber", specimenreplicate.sourceaccessioncode AS "occurrenceID", canon_place.country, canon_place.stateprovince AS "stateProvince", datasource_place.county, location.locationnarrative AS locality, CASE WHEN (coordinates.latitude_deg IS NOT NULL) THEN coordinates.latitude_deg ELSE county_centroids.latitude END AS "decimalLatitude", CASE WHEN (coordinates.latitude_deg IS NOT NULL) THEN coordinates.longitude_deg ELSE county_centroids.longitude END AS "decimalLongitude", CASE WHEN (coordinates.latitude_deg IS NOT NULL) THEN coordinates.coordsaccuracy_m ELSE _km_to_m(county_centroids.error_km) END AS "coordinateUncertaintyInMeters", CASE WHEN (coordinates.latitude_deg IS NOT NULL) THEN 'source data'::coordinatesource WHEN (county_centroids.row_num IS NOT NULL) THEN 'georeferencing'::coordinatesource ELSE NULL::coordinatesource END AS "coordinateSource_bien", CASE WHEN (coordinates.latitude_deg IS NOT NULL) THEN NULL::text WHEN (county_centroids.row_num IS NOT NULL) THEN 'county centroid'::text ELSE NULL::text END AS "georeferenceProtocol_bien", (canon_place.geovalid)::integer AS geovalid_bien, ("newWorldCountries"."isNewWorld")::integer AS "isNewWorld_bien", COALESCE(project.sourceaccessioncode, project.projectname) AS "projectID", COALESCE(location.sourceaccessioncode, concat_delim('; '::text, VARIADIC ARRAY[COALESCE(parent_location.sourceaccessioncode, parent_location.authorlocationcode), location.authorlocationcode])) AS "locationID", COALESCE(parent_location.authorlocationcode, location.authorlocationcode) AS "locationName", CASE WHEN (parent_location.location_id IS NOT NULL) THEN location.authorlocationcode ELSE NULL::text END AS subplot, plantobservation.authorplantcode AS "individualCode", COALESCE(location.elevation_m, parent_location.elevation_m) AS "elevationInMeters", _m2_to_ha(COALESCE(location.area_m2, parent_location.area_m2)) AS "plotArea_ha", method.name AS "samplingProtocol", COALESCE(locationevent.temperature_c, parent_event.temperature_c) AS "temperature_C", COALESCE(locationevent.precipitation_m, parent_event.precipitation_m) AS precipitation_m, collector.fullname AS "recordedBy", plantobservation.authorplantcode AS "recordNumber", COALESCE(locationevent.obsstartdate, parent_event.obsstartdate, aggregateoccurrence.collectiondate) AS "dateCollected", datasource_taxonverbatim.family AS family_verbatim, COALESCE(datasource_taxonverbatim.taxonname, datasource_taxonverbatim.taxonomicname, datasource_taxonlabel.taxonomicname) AS "taxonName_verbatim", datasource_taxonverbatim.author AS "scientificNameAuthorship_verbatim", identifiedby.fullname AS "identifiedBy", datasource_taxondetermination.determinationdate AS "dateIdentified", datasource_taxondetermination.notes AS "identificationRemarks", matched_taxonverbatim.family AS family_matched, matched_taxonverbatim.taxonname AS "taxonName_matched", matched_taxonverbatim.author AS "scientificNameAuthorship_matched", family_higher_plant_group.higher_plant_group AS "higherPlantGroup_bien", canon_taxonverbatim.family, canon_taxonverbatim.genus, COALESCE(concat_delim(' '::text, VARIADIC ARRAY[COALESCE(canon_taxonverbatim.genus, canon_taxonverbatim.family), canon_taxonverbatim.specific_epithet, canon_taxonverbatim.morphospecies]), canon_taxonverbatim.taxonname, canon_taxonverbatim.taxonomicname) AS "speciesBinomialWithMorphospecies", canon_taxonverbatim.taxonname AS "taxonName", canon_taxonverbatim.author AS "scientificNameAuthorship", taxonoccurrence.growthform AS "growthForm", plantobservation.reproductivecondition AS "reproductiveCondition", ((threatened_taxonlabel.taxonlabel_id IS NOT NULL))::integer AS threatened_bien, (((cultivated_family_locations.country IS NOT NULL) OR _or(taxonoccurrence.iscultivated, location.iscultivated)))::integer AS cultivated_bien, CASE WHEN (taxonoccurrence.iscultivated IS NOT NULL) THEN taxonoccurrence.cultivatedbasis WHEN (location.iscultivated IS NOT NULL) THEN NULL::text ELSE NULL::text END AS "cultivatedBasis_bien", aggregateoccurrence.notes AS "occurrenceRemarks", _fraction_to_percent(aggregateoccurrence.cover_fraction) AS "coverPercent", _m_to_cm(stemobservation.diameterbreastheight_m) AS "diameterBreastHeight_cm", stemobservation.height_m, stemobservation.tag, stemobservation.xposition_m AS "organismX_m", stemobservation.yposition_m AS "organismY_m", taxonoccurrence.sourceaccessioncode AS "taxonOccurrenceID", taxonoccurrence.authortaxoncode AS "authorTaxonCode", plantobservation.sourceaccessioncode AS "individualObservationID", stemobservation.authorstemcode AS "authorStemCode" FROM ((((((((((((((((((((((((((((((((source JOIN location USING (source_id)) LEFT JOIN locationevent USING (location_id)) LEFT JOIN location parent_location ON ((parent_location.location_id = location.parent_id))) LEFT JOIN locationplace ON ((locationplace.location_id = COALESCE(parent_location.location_id, location.location_id)))) LEFT JOIN place datasource_place USING (place_id)) LEFT JOIN place canon_place ON ((canon_place.place_id = datasource_place.canon_place_id))) LEFT JOIN coordinates ON ((coordinates.coordinates_id = canon_place.coordinates_id))) LEFT JOIN "newWorld".iso_code_gadm ON ((iso_code_gadm."GADM country" = canon_place.country))) LEFT JOIN "newWorld"."newWorldCountries" ON (("newWorldCountries"."isoCode" = iso_code_gadm."2-digit iso code"))) LEFT JOIN geoscrub.county_centroids ON ((((canon_place.country = 'United States'::text) AND (county_centroids.state = canon_place.stateprovince)) AND (county_centroids.county = canon_place.county)))) LEFT JOIN taxonoccurrence USING (locationevent_id)) LEFT JOIN locationevent parent_event ON ((parent_event.locationevent_id = locationevent.parent_id))) LEFT JOIN project ON ((project.project_id = COALESCE(locationevent.project_id, parent_event.project_id)))) LEFT JOIN method ON ((method.method_id = COALESCE(locationevent.method_id, parent_event.method_id)))) LEFT JOIN party collector ON ((collector.party_id = taxonoccurrence.collector_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN stemobservation USING (plantobservation_id)) LEFT JOIN specimenreplicate USING (plantobservation_id)) LEFT JOIN sourcelist ON ((sourcelist.sourcelist_id = specimenreplicate.institution_id))) LEFT JOIN taxondetermination datasource_taxondetermination ON (((datasource_taxondetermination.taxonoccurrence_id = taxonoccurrence.taxonoccurrence_id) AND datasource_taxondetermination.is_datasource_current))) LEFT JOIN party identifiedby ON ((identifiedby.party_id = datasource_taxondetermination.party_id))) LEFT JOIN taxonverbatim datasource_taxonverbatim USING (taxonverbatim_id)) LEFT JOIN taxonlabel datasource_taxonlabel USING (taxonlabel_id)) LEFT JOIN taxondetermination matched_taxondetermination ON (((matched_taxondetermination.taxonoccurrence_id = taxonoccurrence.taxonoccurrence_id) AND (matched_taxondetermination.determinationtype = 'matched'::text)))) LEFT JOIN taxonverbatim matched_taxonverbatim ON ((matched_taxonverbatim.taxonverbatim_id = matched_taxondetermination.taxonverbatim_id))) LEFT JOIN taxondetermination canon_taxondetermination ON (((canon_taxondetermination.taxonoccurrence_id = taxonoccurrence.taxonoccurrence_id) AND canon_taxondetermination.iscurrent))) LEFT JOIN taxonverbatim canon_taxonverbatim ON ((canon_taxonverbatim.taxonverbatim_id = canon_taxondetermination.taxonverbatim_id))) LEFT JOIN taxonlabel canon_taxonlabel ON ((canon_taxonlabel.taxonlabel_id = canon_taxonverbatim.taxonlabel_id))) LEFT JOIN family_higher_plant_group ON ((family_higher_plant_group.family = canon_taxonverbatim.family))) LEFT JOIN cultivated_family_locations ON (((cultivated_family_locations.family = canon_taxonverbatim.family) AND (cultivated_family_locations.country = canon_place.country)))) LEFT JOIN threatened_taxonlabel ON ((threatened_taxonlabel.taxonlabel_id = canon_taxonlabel.taxonlabel_id)));
|
schemas/vegbien.sql: analytical_stem_view: Added temperature_C, precipitation_m for possible use in John Wiens' plant climatic niche evolution project ("how fast is the rate of climatic niche evolution among species compared to projected changes in climate over the next 100 years? How do these rates compare for temperature and precipitation variables?")