Project

General

Profile

« Previous | Next » 

Revision 1298

Added by Aaron Marcuse-Kubitza over 12 years ago

specimens verification: Added # counties test

View differences:

inputs/NYBG/verify/specimens.ref.sql
19 19 
SELECT '# catalog numbers' AS ___;
20 20 
SELECT count(DISTINCT CatalogNumber) AS count FROM nybg_raw;
21 21 

  		




  
  22
  
    
SELECT '# counties' AS ___;


  		




  
  23
  
    
SELECT count(DISTINCT Country, StateProvince, County) AS count FROM nybg_raw;


  		




  
  24
  
    

  		




  22
  25
  
    
/*


  		




  23
  
  
    
SELECT DISTINCT Country, StateProvince, County 


  		




  24
  
  
    
FROM nybg_raw


  		




  25
  
  
    
LIMIT 25;


  		




  26
  
  
    

  		




  27
  26
  
    
SELECT COUNT(*) as totalRecords, COUNT(DISTINCT CatalogNumber) as uniqueCatalogNumbers


  		




  28
  27
  
    
FROM nybg_raw;


  		




  29
  28
  
    

  		












  

    
      inputs/NYBG/verify/specimens.ref
    
  





  26
  26
  
    
# catalog numbers


  		




  27
  27
  
    
count


  		




  28
  28
  
    
327637


  		




  
  29
  
    
___


  		




  
  30
  
    
# counties


  		




  
  31
  
    
count


  		




  
  32
  
    
4655


  		












  

    
      mappings/verify.specimens.sql
    
  





  83
  83
  
    
WHERE datasource_id =


  		




  84
  84
  
    
    (SELECT party_id FROM party WHERE organizationname = :datasource)


  		




  85
  85
  
    
;


  		




  
  86
  
    

  		




  
  87
  
    
SELECT '# counties' AS ___;


  		




  
  88
  
    
SELECT count(DISTINCT


  		




  
  89
  
    
    COALESCE(country_namedplace.placename, '')


  		




  
  90
  
    
    ||','|| COALESCE(state_namedplace.placename, '')


  		




  
  91
  
    
    ||','|| COALESCE(county_namedplace.placename, '')


  		




  
  92
  
    
    ) AS count


  		




  
  93
  
    
FROM specimenreplicate


  		




  
  94
  
    
JOIN taxonoccurrence USING (taxonoccurrence_id)


  		




  
  95
  
    
JOIN locationevent USING (locationevent_id)


  		




  
  96
  
    
JOIN locationdetermination AS country_locationdet


  		




  
  97
  
    
    ON country_locationdet.location_id = locationevent.location_id


  		




  
  98
  
    
JOIN namedplace AS country_namedplace


  		




  
  99
  
    
    ON country_namedplace.namedplace_id = country_locationdet.namedplace_id


  		




  
  100
  
    
    AND country_namedplace.placesystem = 'area|country|territory'


  		




  
  101
  
    
JOIN locationdetermination AS state_locationdet


  		




  
  102
  
    
    ON state_locationdet.location_id = locationevent.location_id


  		




  
  103
  
    
JOIN namedplace AS state_namedplace


  		




  
  104
  
    
    ON state_namedplace.namedplace_id = state_locationdet.namedplace_id


  		




  
  105
  
    
    AND state_namedplace.placesystem = 'region|state|province'


  		




  
  106
  
    
JOIN locationdetermination AS county_locationdet


  		




  
  107
  
    
    ON county_locationdet.location_id = locationevent.location_id


  		




  
  108
  
    
JOIN namedplace AS county_namedplace


  		




  
  109
  
    
    ON county_namedplace.namedplace_id = county_locationdet.namedplace_id


  		




  
  110
  
    
    AND county_namedplace.placesystem = 'county'


  		




  
  111
  
    
WHERE specimenreplicate.datasource_id =


  		




  
  112
  
    
    (SELECT party_id FROM party WHERE organizationname = :datasource)


  		




  
  113
  
    
;


  		












Also available in:  Unified diff