Revision 6261
Added by Aaron Marcuse-Kubitza over 11 years ago
vegbien.sql | ||
---|---|---|
1322 | 1322 |
county text, |
1323 | 1323 |
"decimalLatitude" double precision, |
1324 | 1324 |
"decimalLongitude" double precision, |
1325 |
"coordinateUncertaintyInMeters" double precision, |
|
1325 | 1326 |
"geosourceValid" boolean, |
1326 | 1327 |
"isNewWorld" boolean, |
1327 | 1328 |
"distanceToCountry_km" double precision, |
... | ... | |
1361 | 1362 |
county text, |
1362 | 1363 |
"decimalLatitude" double precision, |
1363 | 1364 |
"decimalLongitude" double precision, |
1365 |
"coordinateUncertaintyInMeters" double precision, |
|
1364 | 1366 |
"geosourceValid" boolean, |
1365 | 1367 |
"isNewWorld" boolean, |
1366 | 1368 |
"distanceToCountry_km" double precision, |
... | ... | |
1388 | 1390 |
tag text, |
1389 | 1391 |
"organismX_m" double precision, |
1390 | 1392 |
"organismY_m" double precision, |
1391 |
"recordedBy" text,
|
|
1393 |
"recordedBy" integer,
|
|
1392 | 1394 |
"recordNumber" text |
1393 | 1395 |
); |
1394 | 1396 |
|
... | ... | |
1398 | 1400 |
-- |
1399 | 1401 |
|
1400 | 1402 |
CREATE VIEW analytical_aggregate_view AS |
1401 |
SELECT analytical_stem."institutionCode", analytical_stem.country, analytical_stem."stateProvince", analytical_stem.county, analytical_stem."decimalLatitude", analytical_stem."decimalLongitude", analytical_stem."geosourceValid", analytical_stem."isNewWorld", analytical_stem."distanceToCountry_km", analytical_stem."distanceToStateProvince_km", analytical_stem."plotName", analytical_stem."elevationInMeters", analytical_stem."plotArea_ha", analytical_stem."samplingProtocol", analytical_stem."dateCollected", analytical_stem."higherPlantGroup", analytical_stem.family, analytical_stem.genus, analytical_stem."speciesBinomial", analytical_stem."scientificName", analytical_stem."scientificNameAuthorship", analytical_stem."scientificNameWithMorphospecies", analytical_stem.threatened, analytical_stem."growthForm", analytical_stem.cultivated, analytical_stem."cultivatedBasis", sum(analytical_stem."coverPercent") AS "coverPercent", count(analytical_stem."diameterBreastHeight_cm") AS "individualCount", count((analytical_stem."diameterBreastHeight_cm" >= _cm_to_m((1)::double precision))) AS "individualCount_1cm_or_more", count((analytical_stem."diameterBreastHeight_cm" >= _cm_to_m((2.5)::double precision))) AS "individualCount_2_5cm_or_more", count((analytical_stem."diameterBreastHeight_cm" >= _cm_to_m((10)::double precision))) AS "individualCount_10cm_or_more" FROM analytical_stem GROUP BY analytical_stem."institutionCode", analytical_stem.country, analytical_stem."stateProvince", analytical_stem.county, analytical_stem."decimalLatitude", analytical_stem."decimalLongitude", analytical_stem."geosourceValid", analytical_stem."isNewWorld", analytical_stem."distanceToCountry_km", analytical_stem."distanceToStateProvince_km", analytical_stem."plotName", analytical_stem."elevationInMeters", analytical_stem."plotArea_ha", analytical_stem."samplingProtocol", analytical_stem."dateCollected", analytical_stem."higherPlantGroup", analytical_stem.family, analytical_stem.genus, analytical_stem."speciesBinomial", analytical_stem."scientificName", analytical_stem."scientificNameAuthorship", analytical_stem."scientificNameWithMorphospecies", analytical_stem.threatened, analytical_stem."growthForm", analytical_stem.cultivated, analytical_stem."cultivatedBasis";
|
|
1403 |
SELECT analytical_stem."institutionCode", analytical_stem.country, analytical_stem."stateProvince", analytical_stem.county, analytical_stem."decimalLatitude", analytical_stem."decimalLongitude", analytical_stem."coordinateUncertaintyInMeters", analytical_stem."geosourceValid", analytical_stem."isNewWorld", analytical_stem."distanceToCountry_km", analytical_stem."distanceToStateProvince_km", analytical_stem."plotName", analytical_stem."elevationInMeters", analytical_stem."plotArea_ha", analytical_stem."samplingProtocol", analytical_stem."dateCollected", analytical_stem."higherPlantGroup", analytical_stem.family, analytical_stem.genus, analytical_stem."speciesBinomial", analytical_stem."scientificName", analytical_stem."scientificNameAuthorship", analytical_stem."scientificNameWithMorphospecies", analytical_stem.threatened, analytical_stem."growthForm", analytical_stem.cultivated, analytical_stem."cultivatedBasis", sum(analytical_stem."coverPercent") AS "coverPercent", count(analytical_stem."diameterBreastHeight_cm") AS "individualCount", count((analytical_stem."diameterBreastHeight_cm" >= _cm_to_m((1)::double precision))) AS "individualCount_1cm_or_more", count((analytical_stem."diameterBreastHeight_cm" >= _cm_to_m((2.5)::double precision))) AS "individualCount_2_5cm_or_more", count((analytical_stem."diameterBreastHeight_cm" >= _cm_to_m((10)::double precision))) AS "individualCount_10cm_or_more" FROM analytical_stem GROUP BY analytical_stem."institutionCode", analytical_stem.country, analytical_stem."stateProvince", analytical_stem.county, analytical_stem."decimalLatitude", analytical_stem."decimalLongitude", analytical_stem."coordinateUncertaintyInMeters", analytical_stem."geosourceValid", analytical_stem."isNewWorld", analytical_stem."distanceToCountry_km", analytical_stem."distanceToStateProvince_km", analytical_stem."plotName", analytical_stem."elevationInMeters", analytical_stem."plotArea_ha", analytical_stem."samplingProtocol", analytical_stem."dateCollected", analytical_stem."higherPlantGroup", analytical_stem.family, analytical_stem.genus, analytical_stem."speciesBinomial", analytical_stem."scientificName", analytical_stem."scientificNameAuthorship", analytical_stem."scientificNameWithMorphospecies", analytical_stem.threatened, analytical_stem."growthForm", analytical_stem.cultivated, analytical_stem."cultivatedBasis";
|
|
1402 | 1404 |
|
1403 | 1405 |
|
1404 | 1406 |
-- |
... | ... | |
2120 | 2122 |
-- |
2121 | 2123 |
|
2122 | 2124 |
CREATE VIEW analytical_stem_view AS |
2123 |
SELECT source.shortname AS "institutionCode", canon_place.country, canon_place.stateprovince AS "stateProvince", datasource_place.county, coordinates.latitude_deg AS "decimalLatitude", coordinates.longitude_deg AS "decimalLongitude", canon_place.geovalid AS "geosourceValid", "newWorldCountries"."isNewWorld", _m_to_km(canon_place.distance_to_country_m) AS "distanceToCountry_km", _m_to_km(canon_place.distance_to_state_m) AS "distanceToStateProvince_km", location.sourceaccessioncode AS "plotName", location.elevation_m AS "elevationInMeters", _m2_to_ha(location.area_m2) AS "plotArea_ha", method.name AS "samplingProtocol", aggregateoccurrence.collectiondate AS "dateCollected", family_higher_plant_group.higher_plant_group AS "higherPlantGroup", accepted_taxonverbatim.family, accepted_taxonverbatim.genus, ((accepted_taxonverbatim.genus || ' '::text) || accepted_taxonverbatim.specific_epithet) AS "speciesBinomial", COALESCE(accepted_taxonverbatim.taxonomicname, accepted_taxonverbatim.taxonname) AS "scientificName", accepted_taxonverbatim.author AS "scientificNameAuthorship", NULLIF(array_to_string(ARRAY[accepted_taxonlabel.taxonomicname, parsed_taxonverbatim.morphospecies], ' '::text), ''::text) AS "scientificNameWithMorphospecies", (threatened_taxonlabel.taxonlabel_id IS NOT NULL) AS threatened, NULLIF(array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], ' '::text), ''::text) AS "identifiedBy", taxonoccurrence.growthform AS "growthForm", COALESCE(taxonoccurrence.iscultivated, (geoscrub_cultivated."isCultivated")::boolean) AS cultivated, CASE WHEN (taxonoccurrence.iscultivated IS NOT NULL) THEN taxonoccurrence.cultivatedbasis ELSE (geoscrub_cultivated."isCultivatedReason" || ''::text) END AS "cultivatedBasis", _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.collector_id AS "recordedBy", plantobservation.collectionnumber AS "recordNumber" FROM ((((((((((((((((((((((((source JOIN location USING (source_id)) LEFT JOIN locationplace USING (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 geoscrub.geoscrub_cultivated ON (((geoscrub_cultivated."latitudeDecimalVerbatim" = coordinates.latitude_deg) AND (geoscrub_cultivated."longitudeDecimalVerbatim" = coordinates.longitude_deg)))) LEFT JOIN "newWorld".iso_code_gadm ON ((iso_code_gadm."GADM country" = canon_place.country))) LEFT JOIN "newWorld"."newWorldCountries" ON ((("newWorldCountries"."isoCode")::text = iso_code_gadm."2-digit iso code"))) 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 datasource_taxonverbatim USING (taxonverbatim_id)) JOIN taxonlabel datasource_taxonlabel USING (taxonlabel_id)) LEFT JOIN taxonlabel parsed_taxonlabel ON ((parsed_taxonlabel.taxonlabel_id = datasource_taxonlabel.matched_label_id))) LEFT JOIN taxonverbatim parsed_taxonverbatim ON ((parsed_taxonverbatim.taxonlabel_id = parsed_taxonlabel.taxonlabel_id))) LEFT JOIN taxonlabel accepted_taxonlabel ON ((accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.canon_label_id))) LEFT JOIN taxonverbatim accepted_taxonverbatim ON ((accepted_taxonverbatim.taxonlabel_id = accepted_taxonlabel.taxonlabel_id))) LEFT JOIN threatened_taxonlabel ON ((threatened_taxonlabel.taxonlabel_id = accepted_taxonlabel.taxonlabel_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN family_higher_plant_group ON ((family_higher_plant_group.family = accepted_taxonverbatim.family))) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN stemobservation USING (plantobservation_id)) WHERE (NOT taxondetermination.isoriginal); |
|
2125 |
SELECT source.shortname AS "institutionCode", canon_place.country, canon_place.stateprovince AS "stateProvince", datasource_place.county, coordinates.latitude_deg AS "decimalLatitude", coordinates.longitude_deg AS "decimalLongitude", coordinates.coordsaccuracy_m AS "coordinateUncertaintyInMeters", canon_place.geovalid AS "geosourceValid", "newWorldCountries"."isNewWorld", _m_to_km(canon_place.distance_to_country_m) AS "distanceToCountry_km", _m_to_km(canon_place.distance_to_state_m) AS "distanceToStateProvince_km", location.sourceaccessioncode AS "plotName", location.elevation_m AS "elevationInMeters", _m2_to_ha(location.area_m2) AS "plotArea_ha", method.name AS "samplingProtocol", aggregateoccurrence.collectiondate AS "dateCollected", family_higher_plant_group.higher_plant_group AS "higherPlantGroup", accepted_taxonverbatim.family, accepted_taxonverbatim.genus, ((accepted_taxonverbatim.genus || ' '::text) || accepted_taxonverbatim.specific_epithet) AS "speciesBinomial", COALESCE(accepted_taxonverbatim.taxonomicname, accepted_taxonverbatim.taxonname) AS "scientificName", accepted_taxonverbatim.author AS "scientificNameAuthorship", NULLIF(array_to_string(ARRAY[accepted_taxonlabel.taxonomicname, parsed_taxonverbatim.morphospecies], ' '::text), ''::text) AS "scientificNameWithMorphospecies", (threatened_taxonlabel.taxonlabel_id IS NOT NULL) AS threatened, NULLIF(array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], ' '::text), ''::text) AS "identifiedBy", taxonoccurrence.growthform AS "growthForm", COALESCE(taxonoccurrence.iscultivated, (geoscrub_cultivated."isCultivated")::boolean) AS cultivated, CASE WHEN (taxonoccurrence.iscultivated IS NOT NULL) THEN taxonoccurrence.cultivatedbasis ELSE (geoscrub_cultivated."isCultivatedReason" || ''::text) END AS "cultivatedBasis", _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.collector_id AS "recordedBy", plantobservation.collectionnumber AS "recordNumber" FROM ((((((((((((((((((((((((source JOIN location USING (source_id)) LEFT JOIN locationplace USING (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 geoscrub.geoscrub_cultivated ON (((geoscrub_cultivated."latitudeDecimalVerbatim" = coordinates.latitude_deg) AND (geoscrub_cultivated."longitudeDecimalVerbatim" = coordinates.longitude_deg)))) LEFT JOIN "newWorld".iso_code_gadm ON ((iso_code_gadm."GADM country" = canon_place.country))) LEFT JOIN "newWorld"."newWorldCountries" ON ((("newWorldCountries"."isoCode")::text = iso_code_gadm."2-digit iso code"))) 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 datasource_taxonverbatim USING (taxonverbatim_id)) JOIN taxonlabel datasource_taxonlabel USING (taxonlabel_id)) LEFT JOIN taxonlabel parsed_taxonlabel ON ((parsed_taxonlabel.taxonlabel_id = datasource_taxonlabel.matched_label_id))) LEFT JOIN taxonverbatim parsed_taxonverbatim ON ((parsed_taxonverbatim.taxonlabel_id = parsed_taxonlabel.taxonlabel_id))) LEFT JOIN taxonlabel accepted_taxonlabel ON ((accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.canon_label_id))) LEFT JOIN taxonverbatim accepted_taxonverbatim ON ((accepted_taxonverbatim.taxonlabel_id = accepted_taxonlabel.taxonlabel_id))) LEFT JOIN threatened_taxonlabel ON ((threatened_taxonlabel.taxonlabel_id = accepted_taxonlabel.taxonlabel_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN family_higher_plant_group ON ((family_higher_plant_group.family = accepted_taxonverbatim.family))) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN stemobservation USING (plantobservation_id)) WHERE (NOT taxondetermination.isoriginal);
|
|
2124 | 2126 |
|
2125 | 2127 |
|
2126 | 2128 |
-- |
Also available in: Unified diff
schemas/vegbien.sql: analytical_*: Added coordinateUncertaintyInMeters