-- -------------------------------------------------------------------------

-- Quantitative validation queries on the BIEN database

-- 

-- Applies to any datasource with specimens

-- 

-- DB: vegbien

-- Host: vegbiendev.nceas.ucsb.edu

-- -------------------------------------------------------------------------

-- -------------------------------

-- 1. Count of total records (specimens) in source db

-- Check: full join against equivalent query on BIEN3 db should return 1 row

-- -------------------------------

-- -------------------------------

-- 2. Count of unique (verbatim) non-null families

-- Check: Full inner join to equivalent query on BIEN3 db should return 1 row

-- -------------------------------

-- -------------------------------

-- 3. List of verbatim families

-- Check: Full inner join to equivalent query on BIEN3 db should return same number of rows

-- -------------------------------

-- -------------------------------

-- 4. Count of unique (verbatim) non-null species, without author

-- Check: Full inner join to equivalent query on BIEN3 db should return 1 row

-- -------------------------------

-- -------------------------------

-- 5. List of verbatim species, excluding author

-- Check: Full inner join to equivalent query on BIEN3 db should return 3335 rows

-- -------------------------------

-- -------------------------------

-- 6. Count of unique (verbatim) non-null subspecific taxa, without author

-- Check: Full inner join to equivalent query on BIEN3 db should return 1 row

-- -------------------------------

-- -------------------------------

-- 7. List of verbatim subspecific taxa, without author

-- Check: Full inner join to equivalent query on BIEN3 db should return 40145 rows

-- -------------------------------

-- -------------------------------

-- 8. Count of unique (verbatim) taxa including author, for all taxa identified at least to genus

-- Check: Full inner join to equivalent query on BIEN3 db should return 1 row

-- -------------------------------

-- -------------------------------

-- 9. List of unique (verbatim) taxa including author, for all taxa identified at least to genus

-- Check: Full inner join to equivalent query on BIEN3 db should return 45997 rows

-- -------------------------------

-- -------------------------------

-- 10. Count number of records by institution

-- Check: Full inner join to equivalent query on BIEN3 db should return 6 rows

-- Note: Majority should be from 'NY'; these are standard herbarium acronyms

-- -------------------------------

-- -------------------------------

-- 11. List of three standard political divisions

-- Check: Full inner join to equivalent query on BIEN3 db should return 5232 rows

-- Note: character set issues may cause mis-matches. This query is a good way to reveal

-- character set issues, either in source db or in BIEN

-- -------------------------------

-- -------------------------------

-- 12. Check distinct Collector names + collection numbers + collection dates, 

-- plus total records

-- Check: Full inner join to equivalent query on BIEN3 db should return 309396 rows

-- -------------------------------

-- -------------------------------

-- 13. Count of all  verbatim Latitude and Longitude values, as well as all 

-- latitude and longitude values that are decimals numbers

-- Check: full join to equivalent query against BIEN3 should return 1 row

-- -------------------------------

-- -------------------------------

-- 14. Count of all verbatim Latitude and Longitude values that are not valid values

-- of decimal latitude or decimal longitude

-- Check: full join to equivalent query against BIEN3 should return 1 row

-- -------------------------------

-- -------------------------------

-- 15. List distinct non-null locality descriptions

-- Check: full join to equivalent query against BIEN3 should return 125592 records

-- -------------------------------

-- -------------------------------

-- 16. List distinct non-null specimen descriptions

-- Check: full join to equivalent query against BIEN3 should return 158460 records

-- Note: specimens descriptions in nybg extract is in column PlantFungusDescription

-- -------------------------------