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

-- Quantitative validation queries on the BIEN database

-- 

-- Applies to any datasource with specimens

-- 

-- DB: vegbien

-- Host: vegbiendev.nceas.ucsb.edu

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

SET search_path TO validation;

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

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

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

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

CREATE OR REPLACE VIEW specimens_1_count_of_total_records_specimens_in_source_db AS

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

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

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

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

CREATE OR REPLACE VIEW specimens_2_count_of_unique_verbatim_non_null_families AS

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

-- 3. List of verbatim families

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

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

CREATE OR REPLACE VIEW specimens_3_list_of_verbatim_families AS

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

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

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

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

CREATE OR REPLACE VIEW specimens_4_count_of_unique_verbatim_non_null_species_without_author AS

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

-- 5. List of verbatim species, excluding author

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

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

CREATE OR REPLACE VIEW specimens_5_list_of_verbatim_species_excluding_author AS

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

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

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

CREATE OR REPLACE VIEW specimens_6_count_of_unique_verbatim_non_null_subspecific_taxa_without_author AS

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

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

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

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

CREATE OR REPLACE VIEW specimens_7_list_of_verbatim_subspecific_taxa_without_author AS

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

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

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

CREATE OR REPLACE VIEW specimens_8_count_of_unique_verbatim_taxa_including_author_for_all_taxa_identified_at_least_to_genus AS

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

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

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

CREATE OR REPLACE VIEW specimens_9_list_of_unique_verbatim_taxa_including_author_for_all_taxa_identified_at_least_to_genus AS

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

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

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

CREATE OR REPLACE VIEW specimens_10_count_number_of_records_by_institution AS

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

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

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

CREATE OR REPLACE VIEW specimens_11_list_of_three_standard_political_divisions AS

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

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

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

CREATE OR REPLACE VIEW specimens_12_check_distinct_collector_names_collection_numbers_collection_dates_ AS

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

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

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

CREATE OR REPLACE VIEW specimens_13_count_of_all_verbatim_latitude_and_longitude_values_as_well_as_all_ AS

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

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

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

CREATE OR REPLACE VIEW specimens_14_count_of_all_verbatim_latitude_and_longitude_values_that_are_not_valid_values AS

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

-- 15. List distinct non-null locality descriptions

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

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

CREATE OR REPLACE VIEW specimens_15_list_distinct_non_null_locality_descriptions AS

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

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

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

CREATE OR REPLACE VIEW specimens_16_list_distinct_non_null_specimen_descriptions AS