Revision 6427
Added by Aaron Marcuse-Kubitza over 11 years ago
| vegbien.sql | ||
|---|---|---|
| 1383 | 1383 |
"scientificNameAuthorship" text, |
| 1384 | 1384 |
"scientificNameWithMorphospecies" text NOT NULL, |
| 1385 | 1385 |
threatened boolean, |
| 1386 |
"identifiedBy" text, |
|
| 1386 | 1387 |
"growthForm" growthform, |
| 1387 | 1388 |
cultivated boolean, |
| 1388 | 1389 |
"cultivatedBasis" text, |
| ... | ... | |
| 1443 | 1444 |
-- |
| 1444 | 1445 |
|
| 1445 | 1446 |
CREATE VIEW analytical_aggregate_view AS |
| 1446 |
SELECT analytical_stem."institutionCode", analytical_stem.country, analytical_stem."stateProvince", analytical_stem.county, analytical_stem."decimalLatitude", analytical_stem."decimalLongitude", analytical_stem."coordinateUncertaintyInMeters", analytical_stem.geovalid, 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.geovalid, 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";
|
|
| 1447 |
SELECT analytical_stem."institutionCode", analytical_stem.country, analytical_stem."stateProvince", analytical_stem.county, analytical_stem."decimalLatitude", analytical_stem."decimalLongitude", analytical_stem."coordinateUncertaintyInMeters", analytical_stem.geovalid, 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."identifiedBy", 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.geovalid, 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."identifiedBy", analytical_stem."growthForm", analytical_stem.cultivated, analytical_stem."cultivatedBasis";
|
|
| 1447 | 1448 |
|
| 1448 | 1449 |
|
| 1449 | 1450 |
-- |
| ... | ... | |
| 7406 | 7407 |
|
| 7407 | 7408 |
|
| 7408 | 7409 |
-- |
| 7409 |
-- Name: analytical_aggregate; Type: ACL; Schema: public; Owner: - |
|
| 7410 |
-- |
|
| 7411 |
|
|
| 7412 |
REVOKE ALL ON TABLE analytical_aggregate FROM PUBLIC; |
|
| 7413 |
REVOKE ALL ON TABLE analytical_aggregate FROM bien; |
|
| 7414 |
GRANT ALL ON TABLE analytical_aggregate TO bien; |
|
| 7415 |
GRANT SELECT ON TABLE analytical_aggregate TO bien_read; |
|
| 7416 |
|
|
| 7417 |
|
|
| 7418 |
-- |
|
| 7419 | 7410 |
-- Name: analytical_stem; Type: ACL; Schema: public; Owner: - |
| 7420 | 7411 |
-- |
| 7421 | 7412 |
|
Also available in: Unified diff
schemas/vegbien.sql: analytical_aggregate: Added identifiedBy, which is no longer a scoping field (which would prevent scientificNameWithMorphospecies from being unique) now that there is only one taxondetermination for each taxonoccurrence