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

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

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

CREATE OR REPLACE VIEW _specimens_01_count_of_total_records_specimens_in_source_db AS

SELECT COUNT(*) "totalSpecimenRecords"

FROM "Ecatalog_all";

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

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

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

CREATE OR REPLACE VIEW _specimens_02_count_of_unique_verbatim_families AS

SELECT COUNT(DISTINCT family) AS families FROM "Ecatalog_all" WHERE family IS NOT NULL;

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

-- 3. List of verbatim families

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

CREATE OR REPLACE VIEW _specimens_03_list_of_verbatim_families AS

SELECT DISTINCT CONCAT_WS(' ', genus, "specificEpithet") AS "specificEpithet"

FROM "Ecatalog_all"

WHERE genus IS NOT NULL AND "specificEpithet" IS NOT NULL;

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

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

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

CREATE OR REPLACE VIEW _specimens_04_count_of_unique_verbatim_species_without_author AS

SELECT COUNT(DISTINCT CONCAT_WS(' ', genus, "specificEpithet")) AS subspecies FROM "Ecatalog_all"

WHERE genus IS NOT NULL AND "specificEpithet" IS NOT NULL AND subspecies IS NOT NULL;

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

-- 5. List of verbatim species, excluding author

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

CREATE OR REPLACE VIEW _specimens_05_list_of_verbatim_species_excluding_author AS

SELECT DISTINCT CONCAT_WS(' ', genus, "specificEpithet") AS subspecies FROM "Ecatalog_all"

WHERE genus IS NOT NULL AND "specificEpithet" IS NOT NULL AND subspecies IS NOT NULL;

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

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

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

CREATE OR REPLACE VIEW _specimens_06_count_of_unique_verbatim_subsp_taxa_without_author AS

SELECT COUNT(DISTINCT CONCAT_WS(' ', genus, "specificEpithet", subspecies)) AS "specificEpithet" FROM "Ecatalog_all"

WHERE genus IS NOT NULL AND "specificEpithet" IS NOT NULL;

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

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

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

CREATE OR REPLACE VIEW _specimens_07_list_of_verbatim_subspecific_taxa_without_author AS

SELECT DISTINCT CONCAT_WS(' ', genus, "specificEpithet") AS "specificEpithet"

FROM "Ecatalog_all"

WHERE genus IS NOT NULL AND "specificEpithet" IS NOT NULL;

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

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

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

CREATE OR REPLACE VIEW _specimens_08_count_of_unique_verbatim_author_taxa_with_genus AS

SELECT COUNT(DISTINCT TRIM(CONCAT_WS(' ',

))) AS "fullScientificNames"

FROM "Ecatalog_all"

WHERE genus IS NOT NULL;

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

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

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

CREATE OR REPLACE VIEW _specimens_09_list_of_unique_verbatim_author_taxa_with_genus AS

SELECT DISTINCT TRIM(CONCAT_WS(' ',

)) AS "fullScientificName"

FROM "Ecatalog_all"

WHERE genus IS NOT NULL;

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

-- 10. Count number of records by institution

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

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

CREATE OR REPLACE VIEW _specimens_10_count_number_of_records_by_institution AS

SELECT specimen_duplicate_institutions, COUNT(*) AS records

FROM "Ecatalog_all"

GROUP BY specimen_duplicate_institutions;

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

-- 11. List of three standard political divisions

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

SELECT DISTINCT country, "stateProvince", county

FROM "Ecatalog_all";

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

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

-- plus total records

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

CREATE OR REPLACE VIEW _specimens_12_distinct_collector_name_collect_num_date_w_count AS

SELECT IFNULL(recordedBy,'') AS "collectorName", IFNULL("collectorNumber",'') AS "collectionNumber",

FROM "Ecatalog_all"

GROUP BY recordedBy, "collectorNumber", "CollectedDate"

ORDER BY recordedBy, "collectorNumber", "CollectedDate";

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

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

-- latitude and longitude values that are decimals numbers

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

CREATE OR REPLACE VIEW _specimens_13_count_of_all_verbatim_and_decimal_lat_long AS

SELECT

FROM "Ecatalog_all"

WHERE "decimalLongitude" 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

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

CREATE OR REPLACE VIEW _specimens_14_count_of_all_invalid_verbatim_lat_long AS

SELECT

FROM "Ecatalog_all"

WHERE "decimalLongitude">180 OR "decimalLongitude"<-180

) AS "badLongs";

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

-- 15. List distinct non-null locality descriptions

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

CREATE OR REPLACE VIEW _specimens_15_list_distinct_locality_descriptions AS

SELECT DISTINCT locality__main AS "localityDescription"

FROM "Ecatalog_all"

WHERE locality__main IS NOT NULL;

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

-- 16. List distinct non-null specimen descriptions

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

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

CREATE OR REPLACE VIEW _specimens_16_list_distinct_specimen_descriptions AS

SELECT "specimenDescription" AS "specimenDescription"

FROM "Ecatalog_all"

WHERE "specimenDescription" IS NOT NULL;