Revision 5868
Added by Aaron Marcuse-Kubitza about 12 years ago
| schemas/vegbien.sql | ||
|---|---|---|
| 1104 | 1104 |
-- |
| 1105 | 1105 |
|
| 1106 | 1106 |
CREATE VIEW analytical_aggregate_view AS |
| 1107 |
SELECT analytical_stem."institutionCode", analytical_stem.country, analytical_stem."stateProvince", analytical_stem.county, analytical_stem."decimalLatitude", analytical_stem."decimalLongitude", 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", count(analytical_stem."diameterBreastHeight_cm") AS "individualCount", count(((analytical_stem."diameterBreastHeight_cm" >= _cm_to_m((1)::double precision)) AND (analytical_stem."diameterBreastHeight_cm" < _cm_to_m((2.5)::double precision)))) AS "individualCount_1_to_2_5cm", count(((analytical_stem."diameterBreastHeight_cm" >= _cm_to_m((2.5)::double precision)) AND (analytical_stem."diameterBreastHeight_cm" < _cm_to_m((10)::double precision)))) AS "individualCount_2_5_to_10cm", 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."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"; |
|
| 1107 |
SELECT analytical_stem."institutionCode", analytical_stem.country, analytical_stem."stateProvince", analytical_stem.county, analytical_stem."decimalLatitude", analytical_stem."decimalLongitude", 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", sum(analytical_stem."coverPercent") AS "coverPercent", count(analytical_stem."diameterBreastHeight_cm") AS "individualCount", count(((analytical_stem."diameterBreastHeight_cm" >= _cm_to_m((1)::double precision)) AND (analytical_stem."diameterBreastHeight_cm" < _cm_to_m((2.5)::double precision)))) AS "individualCount_1_to_2_5cm", count(((analytical_stem."diameterBreastHeight_cm" >= _cm_to_m((2.5)::double precision)) AND (analytical_stem."diameterBreastHeight_cm" < _cm_to_m((10)::double precision)))) AS "individualCount_2_5_to_10cm", 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."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";
|
|
| 1108 | 1108 |
|
| 1109 | 1109 |
|
| 1110 | 1110 |
-- |
Also available in: Unified diff
schemas/vegbien.sql: analytical_aggregate_view: Added coverPercent, which is the sum of all coverPercents for that species