Project

General

Profile

« Previous | Next » 

Revision 6427

Added by Aaron Marcuse-Kubitza almost 12 years ago

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

View differences:

schemas/vegbien.my.sql
698 698 
    `scientificNameAuthorship` text,
699 699 
    `scientificNameWithMorphospecies` text NOT NULL,
700 700 
    threatened int(1),
701 
    `identifiedBy` text,
701 702 
    `growthForm` text,
702 703 
    cultivated int(1),
703 704 
    `cultivatedBasis` text,
......
6345 6346 

  		




  6346
  6347
  
    

  		




  6347
  6348
  
    
--


  		




  6348
  
  
    
-- Name: analytical_aggregate; Type: ACL; Schema: public; Owner: -


  		




  6349
  
  
    
--


  		




  6350
  
  
    

  		




  6351
  
  
    

  		




  6352
  
  
    

  		




  6353
  
  
    

  		




  6354
  
  
    

  		




  6355
  
  
    

  		




  6356
  
  
    

  		




  6357
  
  
    
--


  		




  6358
  6349
  
    
-- Name: analytical_stem; Type: ACL; Schema: public; Owner: -


  		




  6359
  6350
  
    
--


  		




  6360
  6351
  
    

  		












  

    
      schemas/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