Revision 6039
Added by Aaron Marcuse-Kubitza over 11 years ago
vegbien.sql | ||
---|---|---|
1291 | 1291 |
"plotArea_ha" double precision, |
1292 | 1292 |
"samplingProtocol" text, |
1293 | 1293 |
"dateCollected" date, |
1294 |
"higherPlantGroup" higher_plant_group, |
|
1294 | 1295 |
family text, |
1295 | 1296 |
genus text, |
1296 | 1297 |
"speciesBinomial" text, |
... | ... | |
1328 | 1329 |
"plotArea_ha" double precision, |
1329 | 1330 |
"samplingProtocol" text, |
1330 | 1331 |
"dateCollected" date, |
1332 |
"higherPlantGroup" higher_plant_group, |
|
1331 | 1333 |
family text, |
1332 | 1334 |
genus text, |
1333 | 1335 |
"speciesBinomial" text, |
... | ... | |
1354 | 1356 |
-- |
1355 | 1357 |
|
1356 | 1358 |
CREATE VIEW analytical_aggregate_view AS |
1357 |
SELECT analytical_stem."institutionCode", analytical_stem.country, analytical_stem."stateProvince", analytical_stem.county, analytical_stem."decimalLatitude", analytical_stem."decimalLongitude", analytical_stem."georeferenceValid", 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.family, analytical_stem.genus, analytical_stem."speciesBinomial", analytical_stem."scientificName", analytical_stem."scientificNameAuthorship", analytical_stem."scientificNameWithMorphospecies", 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."georeferenceValid", 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.family, analytical_stem.genus, analytical_stem."speciesBinomial", analytical_stem."scientificName", analytical_stem."scientificNameAuthorship", analytical_stem."scientificNameWithMorphospecies", analytical_stem."growthForm", analytical_stem.cultivated, analytical_stem."cultivatedBasis";
|
|
1359 |
SELECT analytical_stem."institutionCode", analytical_stem.country, analytical_stem."stateProvince", analytical_stem.county, analytical_stem."decimalLatitude", analytical_stem."decimalLongitude", analytical_stem."georeferenceValid", 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."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."georeferenceValid", 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."growthForm", analytical_stem.cultivated, analytical_stem."cultivatedBasis";
|
|
1358 | 1360 |
|
1359 | 1361 |
|
1360 | 1362 |
-- |
... | ... | |
1389 | 1391 |
|
1390 | 1392 |
|
1391 | 1393 |
-- |
1394 |
-- Name: family_higher_plant_group; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
1395 |
-- |
|
1396 |
|
|
1397 |
CREATE TABLE family_higher_plant_group ( |
|
1398 |
family text NOT NULL, |
|
1399 |
higher_plant_group higher_plant_group |
|
1400 |
); |
|
1401 |
|
|
1402 |
|
|
1403 |
-- |
|
1392 | 1404 |
-- Name: location; Type: TABLE; Schema: public; Owner: -; Tablespace: |
1393 | 1405 |
-- |
1394 | 1406 |
|
... | ... | |
2091 | 2103 |
-- |
2092 | 2104 |
|
2093 | 2105 |
CREATE VIEW analytical_stem_view AS |
2094 |
SELECT reference.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.georeference_valid AS "georeferenceValid", "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", 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", NULLIF(array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], ' '::text), ''::text) AS "identifiedBy", taxonoccurrence.growthform AS "growthForm", taxonoccurrence.iscultivated AS cultivated, taxonoccurrence.cultivatedbasis 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.verbatimcollectorname AS "recordedBy", plantobservation.collectionnumber AS "recordNumber" FROM (((((((((((((((((((((reference JOIN location USING (reference_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 "newWorld"."newWorldCountries" ON ((("newWorldCountries"."countryNameStd")::text = canon_place.country))) 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)) 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))) JOIN taxonlabel accepted_taxonlabel ON ((accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.canon_label_id))) JOIN taxonverbatim accepted_taxonverbatim ON ((accepted_taxonverbatim.taxonlabel_id = accepted_taxonlabel.taxonlabel_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)) WHERE (NOT taxondetermination.isoriginal);
|
|
2106 |
SELECT reference.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.georeference_valid AS "georeferenceValid", "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", NULLIF(array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], ' '::text), ''::text) AS "identifiedBy", taxonoccurrence.growthform AS "growthForm", taxonoccurrence.iscultivated AS cultivated, taxonoccurrence.cultivatedbasis 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.verbatimcollectorname AS "recordedBy", plantobservation.collectionnumber AS "recordNumber" FROM ((((((((((((((((((((((reference JOIN location USING (reference_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 "newWorld"."newWorldCountries" ON ((("newWorldCountries"."countryNameStd")::text = canon_place.country))) 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)) 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))) JOIN taxonlabel accepted_taxonlabel ON ((accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.canon_label_id))) JOIN taxonverbatim accepted_taxonverbatim ON ((accepted_taxonverbatim.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)) LEFT JOIN specimenreplicate USING (plantobservation_id)) WHERE (NOT taxondetermination.isoriginal);
|
|
2095 | 2107 |
|
2096 | 2108 |
|
2097 | 2109 |
-- |
... | ... | |
2662 | 2674 |
|
2663 | 2675 |
|
2664 | 2676 |
-- |
2665 |
-- Name: family_higher_plant_group; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
2666 |
-- |
|
2667 |
|
|
2668 |
CREATE TABLE family_higher_plant_group ( |
|
2669 |
family text NOT NULL, |
|
2670 |
higher_plant_group higher_plant_group |
|
2671 |
); |
|
2672 |
|
|
2673 |
|
|
2674 |
-- |
|
2675 | 2677 |
-- Name: geoscrub_input; Type: TABLE; Schema: public; Owner: -; Tablespace: |
2676 | 2678 |
-- |
2677 | 2679 |
|
Also available in: Unified diff
schemas/vegbien.sql: analytical_*: Added higherPlantGroup