--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

SET search_path = "NY", pg_catalog;

--
-- Name: _specimens_01_count_of_total_records_specimens_in_source_db; Type: VIEW; Schema: NY; Owner: -
--

CREATE VIEW _specimens_01_count_of_total_records_specimens_in_source_db AS
 SELECT count(*) AS "totalSpecimenRecords"
   FROM "Ecatalog_all";


--
-- Name: VIEW _specimens_01_count_of_total_records_specimens_in_source_db; Type: COMMENT; Schema: NY; Owner: -
--

COMMENT ON VIEW _specimens_01_count_of_total_records_specimens_in_source_db IS '
Check: should return 1 row
';


--
-- Name: _specimens_02_count_of_unique_verbatim_families; Type: VIEW; Schema: NY; Owner: -
--

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


--
-- Name: VIEW _specimens_02_count_of_unique_verbatim_families; Type: COMMENT; Schema: NY; Owner: -
--

COMMENT ON VIEW _specimens_02_count_of_unique_verbatim_families IS '
Check: should return 1 row
';


--
-- Name: _specimens_03_list_of_verbatim_families; Type: VIEW; Schema: NY; Owner: -
--

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


--
-- Name: VIEW _specimens_03_list_of_verbatim_families; Type: COMMENT; Schema: NY; Owner: -
--

COMMENT ON VIEW _specimens_03_list_of_verbatim_families IS '
Check: should return same number of rows
';


--
-- Name: _specimens_04_count_of_species_binomials; Type: VIEW; Schema: NY; Owner: -
--

CREATE VIEW _specimens_04_count_of_species_binomials AS
 SELECT count(DISTINCT concat_ws(' '::text, "Ecatalog_all".genus, "Ecatalog_all"."specificEpithet")) AS species_binomials
   FROM "Ecatalog_all"
  WHERE ((("Ecatalog_all".genus IS NOT NULL) AND ("Ecatalog_all"."specificEpithet" IS NOT NULL)) AND ("Ecatalog_all".subspecies IS NOT NULL));


--
-- Name: VIEW _specimens_04_count_of_species_binomials; Type: COMMENT; Schema: NY; Owner: -
--

COMMENT ON VIEW _specimens_04_count_of_species_binomials IS '
Check: should return 1 row
';


--
-- Name: _specimens_05_list_of_species_binomials; Type: VIEW; Schema: NY; Owner: -
--

CREATE VIEW _specimens_05_list_of_species_binomials AS
 SELECT DISTINCT concat_ws(' '::text, "Ecatalog_all".genus, "Ecatalog_all"."specificEpithet") AS species_binomial
   FROM "Ecatalog_all"
  WHERE ((("Ecatalog_all".genus IS NOT NULL) AND ("Ecatalog_all"."specificEpithet" IS NOT NULL)) AND ("Ecatalog_all".subspecies IS NOT NULL));


--
-- Name: VIEW _specimens_05_list_of_species_binomials; Type: COMMENT; Schema: NY; Owner: -
--

COMMENT ON VIEW _specimens_05_list_of_species_binomials IS '
Check: should return 3335 rows
';


--
-- Name: _specimens_06_count_of_unique_verb_subsp_taxa_with_author; Type: VIEW; Schema: NY; Owner: -
--

CREATE VIEW _specimens_06_count_of_unique_verb_subsp_taxa_with_author AS
 SELECT count(DISTINCT concat_ws(' '::text, "Ecatalog_all".genus, "Ecatalog_all"."specificEpithet", "Ecatalog_all".subspecies)) AS taxon_names_with_author
   FROM "Ecatalog_all"
  WHERE (("Ecatalog_all".genus IS NOT NULL) AND ("Ecatalog_all"."specificEpithet" IS NOT NULL));


--
-- Name: VIEW _specimens_06_count_of_unique_verb_subsp_taxa_with_author; Type: COMMENT; Schema: NY; Owner: -
--

COMMENT ON VIEW _specimens_06_count_of_unique_verb_subsp_taxa_with_author IS '
Check: should return 1 row
';


--
-- Name: _specimens_07_list_of_verbatim_subspecific_taxa_with_author; Type: VIEW; Schema: NY; Owner: -
--

CREATE VIEW _specimens_07_list_of_verbatim_subspecific_taxa_with_author AS
 SELECT DISTINCT concat_ws(' '::text, "Ecatalog_all".genus, "Ecatalog_all"."specificEpithet") AS taxon_name_with_author
   FROM "Ecatalog_all"
  WHERE (("Ecatalog_all".genus IS NOT NULL) AND ("Ecatalog_all"."specificEpithet" IS NOT NULL));


--
-- Name: VIEW _specimens_07_list_of_verbatim_subspecific_taxa_with_author; Type: COMMENT; Schema: NY; Owner: -
--

COMMENT ON VIEW _specimens_07_list_of_verbatim_subspecific_taxa_with_author IS '
Check: should return 40145 rows
';


--
-- Name: _specimens_08_count_of_unique_verbatim_author_taxa_with_genus; Type: VIEW; Schema: NY; Owner: -
--

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 taxon_names_with_author
   FROM "Ecatalog_all"
  WHERE ("Ecatalog_all".genus IS NOT NULL);


--
-- Name: VIEW _specimens_08_count_of_unique_verbatim_author_taxa_with_genus; Type: COMMENT; Schema: NY; Owner: -
--

COMMENT ON VIEW _specimens_08_count_of_unique_verbatim_author_taxa_with_genus IS '
Check: should return 1 row
';


--
-- Name: _specimens_09_list_of_unique_verbatim_author_taxa_with_genus; Type: VIEW; Schema: NY; Owner: -
--

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 taxon_name_with_author
   FROM "Ecatalog_all"
  WHERE ("Ecatalog_all".genus IS NOT NULL);


--
-- Name: VIEW _specimens_09_list_of_unique_verbatim_author_taxa_with_genus; Type: COMMENT; Schema: NY; Owner: -
--

COMMENT ON VIEW _specimens_09_list_of_unique_verbatim_author_taxa_with_genus IS '
Check: should return 45997 rows
';


--
-- Name: _specimens_10_count_number_of_records_by_institution; Type: VIEW; Schema: NY; Owner: -
--

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;


--
-- Name: VIEW _specimens_10_count_number_of_records_by_institution; Type: COMMENT; Schema: NY; Owner: -
--

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


--
-- Name: _specimens_11_list_of_three_standard_political_divisions; Type: VIEW; Schema: NY; Owner: -
--

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


--
-- Name: VIEW _specimens_11_list_of_three_standard_political_divisions; Type: COMMENT; Schema: NY; Owner: -
--

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


--
-- Name: _specimens_12_distinct_collector_name_collect_num_date_w_count; Type: VIEW; Schema: NY; Owner: -
--

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");


--
-- Name: VIEW _specimens_12_distinct_collector_name_collect_num_date_w_count; Type: COMMENT; Schema: NY; Owner: -
--

COMMENT ON VIEW _specimens_12_distinct_collector_name_collect_num_date_w_count IS '
Check: should return 309396 rows
';


--
-- Name: _specimens_13_count_of_all_verbatim_and_decimal_lat_long; Type: VIEW; Schema: NY; Owner: -
--

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


--
-- Name: VIEW _specimens_13_count_of_all_verbatim_and_decimal_lat_long; Type: COMMENT; Schema: NY; Owner: -
--

COMMENT ON VIEW _specimens_13_count_of_all_verbatim_and_decimal_lat_long IS '
Check: should return 1 row
';


--
-- Name: _specimens_14_count_of_all_invalid_verbatim_lat_long; Type: VIEW; Schema: NY; Owner: -
--

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


--
-- Name: VIEW _specimens_14_count_of_all_invalid_verbatim_lat_long; Type: COMMENT; Schema: NY; Owner: -
--

COMMENT ON VIEW _specimens_14_count_of_all_invalid_verbatim_lat_long IS '
Check: should return 1 row
';


--
-- Name: _specimens_15_list_distinct_locality_descriptions; Type: VIEW; Schema: NY; Owner: -
--

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


--
-- Name: VIEW _specimens_15_list_distinct_locality_descriptions; Type: COMMENT; Schema: NY; Owner: -
--

COMMENT ON VIEW _specimens_15_list_distinct_locality_descriptions IS '
Check: should return 125592 records
';


--
-- Name: _specimens_16_list_distinct_specimen_descriptions; Type: VIEW; Schema: NY; Owner: -
--

CREATE VIEW _specimens_16_list_distinct_specimen_descriptions AS
 SELECT "Ecatalog_all"."specimenDescription"
   FROM "Ecatalog_all"
  WHERE ("Ecatalog_all"."specimenDescription" IS NOT NULL);


--
-- Name: VIEW _specimens_16_list_distinct_specimen_descriptions; Type: COMMENT; Schema: NY; Owner: -
--

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


--
-- PostgreSQL database dump complete
--