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

--  Quantitative validation queries against NYBG source db

-- 

-- Queries table `nybg_raw` in mysql db `bien2_staging` on nimoy.nceas.ucsb.edu

-- Verbatim import of NYSpecimenDataAmericas.csv, a DwC format dump of 

-- specimen records from NYBG specimen database, except for the three

-- columns key, IdentifiedDate and CollectedDate, which where added and 

-- populated by Brad after importing raw data. 

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

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

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

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

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

SELECT COUNT(*) totalSpecimenRecords

FROM nybg_raw;

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

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

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

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

SELECT COUNT(DISTINCT Family) AS families FROM nybg_raw WHERE Family IS NOT NULL;

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

-- 3. List of verbatim families

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

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

SELECT DISTINCT CONCAT_WS(' ', Genus, Species) AS Species

FROM nybg_raw 

WHERE Genus IS NOT NULL AND Species IS NOT NULL;

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

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

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

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

SELECT COUNT(DISTINCT CONCAT_WS(' ', Genus, Species)) AS subspecies FROM nybg_raw 

WHERE Genus IS NOT NULL AND Species IS NOT NULL AND Subspecies IS NOT NULL;

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

-- 5. List of verbatim species, excluding author

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

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

SELECT DISTINCT CONCAT_WS(' ', Genus, Species) AS subspecies FROM nybg_raw 

WHERE Genus IS NOT NULL AND Species IS NOT NULL AND Subspecies IS NOT NULL;

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

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

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

SELECT COUNT(DISTINCT CONCAT_WS(' ', Genus, Species, Subspecies)) AS Species FROM nybg_raw 

WHERE Genus IS NOT NULL AND Species IS NOT NULL;

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

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

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

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

SELECT DISTINCT CONCAT_WS(' ', Genus, Species) AS Species

FROM nybg_raw 

WHERE Genus IS NOT NULL AND Species IS NOT NULL;

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

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

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

SELECT COUNT(DISTINCT TRIM(CONCAT_WS(' ',

TRIM(CONCAT_WS(' ', Genus, IFNULL(Species,''), IFNULL(Subspecies,''))), 

TRIM(IFNULL(ScientificNameAuthor,''))

))) AS fullScientificNames

FROM nybg_raw 

WHERE Genus IS NOT NULL;

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

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

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

SELECT DISTINCT TRIM(CONCAT_WS(' ',

TRIM(CONCAT_WS(' ', Genus, IFNULL(Species,''), IFNULL(Subspecies,''))), 

TRIM(IFNULL(ScientificNameAuthor,''))

)) AS fullScientificName

FROM nybg_raw 

WHERE Genus IS NOT NULL;

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

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

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

SELECT InstitutionCode, COUNT(*) AS records

FROM nybg_raw

GROUP BY InstitutionCode;

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

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

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

SELECT DISTINCT Country, StateProvince, County 

FROM nybg_raw; 

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

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

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

SELECT IFNULL(Collector,'') AS collectorName, IFNULL(FieldNumber,'') AS collectionNumber, 

IFNULL(CollectedDate,'') AS dataCollected, COUNT(*) AS specimenRecords

FROM nybg_raw

GROUP BY Collector, FieldNumber, CollectedDate

ORDER BY Collector, FieldNumber, CollectedDate;

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

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

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

SELECT 

(

SELECT COUNT(*)

FROM nybg_raw

WHERE Latitude IS NOT NULL

) AS allLats,

(

SELECT COUNT(*)

FROM nybg_raw

WHERE Latitude REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$'

) AS decimalLats,

(

SELECT COUNT(*)

FROM nybg_raw

WHERE Longitude IS NOT NULL

) AS allLongs,

(

SELECT COUNT(*)

FROM nybg_raw

WHERE Longitude REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$'

) AS decimalLongs;

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

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

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

SELECT 

(

SELECT COUNT(*)

FROM nybg_raw

WHERE Latitude>90 OR Latitude<-90

) AS badLats,

(

SELECT COUNT(*)

FROM nybg_raw

WHERE Longitude>180 OR Longitude<-180

) AS badLongs;

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

-- 15. List distinct non-null locality descriptions

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

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

SELECT DISTINCT Locality AS localityDescription

FROM nybg_raw

WHERE Locality IS NOT NULL;

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

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

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

SELECT PlantFungusDescription AS specimenDescription

FROM nybg_raw

WHERE PlantFungusDescription IS NOT NULL;

mailto:bboyle@email.arizona.edu?Brad_Boyle.2013-12-12-9:01.Quantative+validations+for+NYBG