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

--  Quantitative validation queries against NYBG source db

--

-- Queries staging table vegbien."NY"."Ecatalog_all" @vegbiendev.nceas.ucsb.edu

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

SET search_path TO "NY";

CREATE VIEW _specimens_01_count_of_total_records_specimens_in_source_db AS

 SELECT count(*) AS "totalSpecimenRecords"

   FROM "Ecatalog_all";

COMMENT ON VIEW _specimens_01_count_of_total_records_specimens_in_source_db IS '

Check: should return 1 row

';

CREATE VIEW _specimens_02_count_of_unique_verbatim_families AS

 SELECT count(DISTINCT "Ecatalog_all".family) AS families

   FROM "Ecatalog_all"

  WHERE ("Ecatalog_all".family IS NOT NULL);

COMMENT ON VIEW _specimens_02_count_of_unique_verbatim_families IS '

Check: should return 1 row

';

CREATE VIEW _specimens_03_list_of_verbatim_families AS

 SELECT DISTINCT concat_ws(' '::text, "Ecatalog_all".genus, "Ecatalog_all"."specificEpithet") AS "specificEpithet"

   FROM "Ecatalog_all"

  WHERE (("Ecatalog_all".genus IS NOT NULL) AND ("Ecatalog_all"."specificEpithet" IS NOT NULL));

COMMENT ON VIEW _specimens_03_list_of_verbatim_families IS '

Check: should return same number of rows

';

CREATE VIEW _specimens_04_count_of_unique_verbatim_species_without_author AS

 SELECT count(DISTINCT concat_ws(' '::text, "Ecatalog_all".genus, "Ecatalog_all"."specificEpithet")) AS subspecies

   FROM "Ecatalog_all"

  WHERE ((("Ecatalog_all".genus IS NOT NULL) AND ("Ecatalog_all"."specificEpithet" IS NOT NULL)) AND ("Ecatalog_all".subspecies IS NOT NULL));

COMMENT ON VIEW _specimens_04_count_of_unique_verbatim_species_without_author IS '

Check: should return 1 row

';

CREATE VIEW _specimens_05_list_of_verbatim_species_excluding_author AS

 SELECT DISTINCT concat_ws(' '::text, "Ecatalog_all".genus, "Ecatalog_all"."specificEpithet") AS subspecies

   FROM "Ecatalog_all"

  WHERE ((("Ecatalog_all".genus IS NOT NULL) AND ("Ecatalog_all"."specificEpithet" IS NOT NULL)) AND ("Ecatalog_all".subspecies IS NOT NULL));

COMMENT ON VIEW _specimens_05_list_of_verbatim_species_excluding_author IS '

Check: should return 3335 rows

';

CREATE VIEW _specimens_06_count_of_unique_verb_subsp_taxa_without_author AS

 SELECT count(DISTINCT concat_ws(' '::text, "Ecatalog_all".genus, "Ecatalog_all"."specificEpithet", "Ecatalog_all".subspecies)) AS "specificEpithet"

   FROM "Ecatalog_all"

  WHERE (("Ecatalog_all".genus IS NOT NULL) AND ("Ecatalog_all"."specificEpithet" IS NOT NULL));

COMMENT ON VIEW _specimens_06_count_of_unique_verb_subsp_taxa_without_author IS '

Check: should return 1 row

';

CREATE VIEW _specimens_07_list_of_verbatim_subspecific_taxa_without_author AS

 SELECT DISTINCT concat_ws(' '::text, "Ecatalog_all".genus, "Ecatalog_all"."specificEpithet") AS "specificEpithet"

   FROM "Ecatalog_all"

  WHERE (("Ecatalog_all".genus IS NOT NULL) AND ("Ecatalog_all"."specificEpithet" IS NOT NULL));

COMMENT ON VIEW _specimens_07_list_of_verbatim_subspecific_taxa_without_author IS '

Check: should return 40145 rows

';

CREATE VIEW _specimens_08_count_of_unique_verbatim_author_taxa_with_genus AS

 SELECT count(DISTINCT btrim(concat_ws(' '::text, btrim(concat_ws(' '::text, "Ecatalog_all".genus, ifnull("Ecatalog_all"."specificEpithet", ''::text), ifnull("Ecatalog_all".subspecies, ''::text))), btrim(ifnull("Ecatalog_all"."scientificNameAuthorship", ''::text))))) AS "fullScientificNames"

   FROM "Ecatalog_all"

  WHERE ("Ecatalog_all".genus IS NOT NULL);

COMMENT ON VIEW _specimens_08_count_of_unique_verbatim_author_taxa_with_genus IS '

Check: should return 1 row

';

CREATE VIEW _specimens_09_list_of_unique_verbatim_author_taxa_with_genus AS

 SELECT DISTINCT btrim(concat_ws(' '::text, btrim(concat_ws(' '::text, "Ecatalog_all".genus, ifnull("Ecatalog_all"."specificEpithet", ''::text), ifnull("Ecatalog_all".subspecies, ''::text))), btrim(ifnull("Ecatalog_all"."scientificNameAuthorship", ''::text)))) AS "fullScientificName"

   FROM "Ecatalog_all"

  WHERE ("Ecatalog_all".genus IS NOT NULL);

COMMENT ON VIEW _specimens_09_list_of_unique_verbatim_author_taxa_with_genus IS '

Check: should return 45997 rows

';

CREATE VIEW _specimens_10_count_number_of_records_by_institution AS

 SELECT "Ecatalog_all".specimen_duplicate_institutions, 

    count(*) AS records

   FROM "Ecatalog_all"

  GROUP BY "Ecatalog_all".specimen_duplicate_institutions;

COMMENT ON VIEW _specimens_10_count_number_of_records_by_institution IS '

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

Check: should return 6 rows

';

CREATE VIEW _specimens_11_list_of_three_standard_political_divisions AS

 SELECT DISTINCT "Ecatalog_all".country, 

    "Ecatalog_all"."stateProvince", 

    "Ecatalog_all".county

   FROM "Ecatalog_all";

COMMENT ON VIEW _specimens_11_list_of_three_standard_political_divisions IS '

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

Check: should return 5232 rows

';

CREATE VIEW _specimens_12_distinct_collector_name_collect_num_date_w_count AS

 SELECT ifnull("Ecatalog_all"."recordedBy", ''::text) AS "collectorName", 

    ifnull("Ecatalog_all"."collectorNumber", ''::text) AS "collectionNumber", 

    concat_ws('-'::text, "Ecatalog_all"."yearCollected", "Ecatalog_all"."monthCollected", "Ecatalog_all"."dayCollected") AS "dateCollected", 

    count(*) AS "specimenRecords"

   FROM "Ecatalog_all"

  GROUP BY "Ecatalog_all"."recordedBy", "Ecatalog_all"."collectorNumber", concat_ws('-'::text, "Ecatalog_all"."yearCollected", "Ecatalog_all"."monthCollected", "Ecatalog_all"."dayCollected")

  ORDER BY "Ecatalog_all"."recordedBy", "Ecatalog_all"."collectorNumber", concat_ws('-'::text, "Ecatalog_all"."yearCollected", "Ecatalog_all"."monthCollected", "Ecatalog_all"."dayCollected");

COMMENT ON VIEW _specimens_12_distinct_collector_name_collect_num_date_w_count IS '

Check: should return 309396 rows

';

CREATE VIEW _specimens_13_count_of_all_verbatim_and_decimal_lat_long AS

 SELECT ( SELECT count(*) AS count

           FROM "Ecatalog_all"

          WHERE ("Ecatalog_all"."decimalLatitude" IS NOT NULL)) AS "allLats", 

    ( SELECT count(*) AS count

           FROM "Ecatalog_all"

          WHERE ("Ecatalog_all"."decimalLatitude" ~ '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$'::text)) AS "decimalLats", 

    ( SELECT count(*) AS count

           FROM "Ecatalog_all"

          WHERE ("Ecatalog_all"."decimalLongitude" IS NOT NULL)) AS "allLongs", 

    ( SELECT count(*) AS count

           FROM "Ecatalog_all"

          WHERE ("Ecatalog_all"."decimalLongitude" ~ '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$'::text)) AS "decimalLongs";

COMMENT ON VIEW _specimens_13_count_of_all_verbatim_and_decimal_lat_long IS '

Check: should return 1 row

';

CREATE VIEW _specimens_14_count_of_all_invalid_verbatim_lat_long AS

 SELECT ( SELECT count(*) AS count

           FROM "Ecatalog_all"

          WHERE ((("Ecatalog_all"."decimalLatitude")::double precision > (90)::double precision) OR (("Ecatalog_all"."decimalLatitude")::double precision < ((-90))::double precision))) AS "badLats", 

    ( SELECT count(*) AS count

           FROM "Ecatalog_all"

          WHERE ((("Ecatalog_all"."decimalLongitude")::double precision > (180)::double precision) OR (("Ecatalog_all"."decimalLongitude")::double precision < ((-180))::double precision))) AS "badLongs";

COMMENT ON VIEW _specimens_14_count_of_all_invalid_verbatim_lat_long IS '

Check: should return 1 row

';

CREATE VIEW _specimens_15_list_distinct_locality_descriptions AS

 SELECT DISTINCT "Ecatalog_all".locality__main AS "localityDescription"

   FROM "Ecatalog_all"

  WHERE ("Ecatalog_all".locality__main IS NOT NULL);

COMMENT ON VIEW _specimens_15_list_distinct_locality_descriptions IS '

Check: should return 125592 records

';

CREATE VIEW _specimens_16_list_distinct_specimen_descriptions AS

 SELECT "Ecatalog_all"."specimenDescription"

   FROM "Ecatalog_all"

  WHERE ("Ecatalog_all"."specimenDescription" IS NOT NULL);

COMMENT ON VIEW _specimens_16_list_distinct_specimen_descriptions IS '

Note: specimens descriptions in nybg extract is in column "specimenDescription"

Check: should return 158460 records

';