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

--

COMMENT ON VIEW _specimens_01_count_of_total_records_specimens_in_source_db 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

';

--

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

--

CREATE VIEW _specimens_02_count_of_unique_verbatim_families AS

 SELECT count(DISTINCT taxonverbatim.family) AS families

   FROM public.taxonverbatim

  WHERE ((taxonverbatim.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname)) AND (taxonverbatim.family IS NOT NULL));

--

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

--

CREATE VIEW _specimens_03_list_of_verbatim_families AS

 SELECT DISTINCT taxonverbatim.family

   FROM public.taxonverbatim

  WHERE ((taxonverbatim.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname)) AND (taxonverbatim.family IS NOT NULL));

--

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

--

CREATE VIEW _specimens_04_count_of_species_binomials AS

 SELECT count(DISTINCT concat_ws(' '::text, taxonverbatim.genus, taxonverbatim.specific_epithet)) AS species_binomials

   FROM public.taxonverbatim

  WHERE (((taxonverbatim.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname)) AND (taxonverbatim.genus IS NOT NULL)) AND (taxonverbatim.specific_epithet IS NOT NULL));

--

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

--

CREATE VIEW _specimens_05_list_of_species_binomials AS

 SELECT DISTINCT concat_ws(' '::text, taxonverbatim.genus, taxonverbatim.specific_epithet) AS species_binomial

   FROM public.taxonverbatim

  WHERE (((taxonverbatim.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname)) AND (taxonverbatim.genus IS NOT NULL)) AND (taxonverbatim.specific_epithet IS NOT NULL));

--

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

--

CREATE VIEW _specimens_06_count_of_unique_verb_subsp_taxa_with_author AS

 SELECT count(DISTINCT taxonlabel.taxonomicname) AS taxon_names_with_author

   FROM public.taxonlabel

  WHERE ((taxonlabel.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname)) AND (taxonlabel.taxonomicname IS NOT NULL));

--

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

--

CREATE VIEW _specimens_07_list_of_verbatim_subspecific_taxa_with_author AS

 SELECT DISTINCT taxonlabel.taxonomicname AS taxon_name_with_author

   FROM public.taxonlabel

  WHERE ((taxonlabel.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname)) AND (taxonlabel.taxonomicname IS NOT NULL));

--

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

--

CREATE VIEW _specimens_08_count_of_unique_verbatim_author_taxa_with_genus AS

 SELECT count(DISTINCT taxonlabel.taxonomicname) AS taxon_names_with_author

   FROM (public.taxonverbatim

   JOIN public.taxonlabel USING (taxonlabel_id))

  WHERE ((taxonverbatim.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname)) AND (taxonverbatim.genus IS NOT NULL));

--

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

--

CREATE VIEW _specimens_09_list_of_unique_verbatim_author_taxa_with_genus AS

 SELECT DISTINCT taxonlabel.taxonomicname AS taxon_name_with_author

   FROM (public.taxonverbatim

   JOIN public.taxonlabel USING (taxonlabel_id))

  WHERE ((taxonverbatim.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname)) AND (taxonverbatim.genus IS NOT NULL));

--

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

--

CREATE VIEW _specimens_10_count_number_of_records_by_institution AS

 SELECT specimenreplicate.duplicate_institutions_sourcelist_id AS specimen_duplicate_institutions, 

    count(*) AS records

   FROM public.specimenreplicate

  WHERE (specimenreplicate.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname))

  GROUP BY specimenreplicate.duplicate_institutions_sourcelist_id;

--

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

--

CREATE VIEW _specimens_11_list_of_three_standard_political_divisions AS

 SELECT DISTINCT place.country, 

    place.stateprovince, 

    place.county

   FROM public.place

  WHERE (place.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname));

--

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

--

CREATE VIEW _specimens_12_distinct_collector_name_collect_num_date_w_count AS

 SELECT ( SELECT party.fullname

           FROM public.party

          WHERE (party.party_id = taxonoccurrence.collector_id)) AS "collectorName", 

    specimenreplicate.collectionnumber AS "collectionNumber", 

    aggregateoccurrence.collectiondate AS "dateCollected", 

    count(*) AS "specimenRecords"

   FROM (((public.taxonoccurrence

   JOIN public.aggregateoccurrence USING (taxonoccurrence_id))

   JOIN public.plantobservation USING (aggregateoccurrence_id))

   JOIN public.specimenreplicate USING (plantobservation_id))

  WHERE (taxonoccurrence.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname))

  GROUP BY ( SELECT party.fullname

   FROM public.party

  WHERE (party.party_id = taxonoccurrence.collector_id)), specimenreplicate.collectionnumber, aggregateoccurrence.collectiondate

  ORDER BY ( SELECT party.fullname

   FROM public.party

  WHERE (party.party_id = taxonoccurrence.collector_id)), specimenreplicate.collectionnumber, aggregateoccurrence.collectiondate;

--

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

--

CREATE VIEW _specimens_13_count_of_all_verbatim_and_decimal_lat_long AS

 SELECT ( SELECT count(*) AS count

           FROM public.coordinates

          WHERE ((coordinates.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname)) AND ((coordinates.verbatimlatitude IS NOT NULL) OR (coordinates.latitude_deg IS NOT NULL)))) AS "allLats", 

    ( SELECT count(*) AS count

           FROM public.coordinates

          WHERE ((coordinates.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname)) AND (coordinates.latitude_deg IS NOT NULL))) AS "decimalLats", 

    ( SELECT count(*) AS count

           FROM public.coordinates

          WHERE ((coordinates.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname)) AND ((coordinates.verbatimlongitude IS NOT NULL) OR (coordinates.longitude_deg IS NOT NULL)))) AS "allLongs", 

    ( SELECT count(*) AS count

           FROM public.coordinates

          WHERE ((coordinates.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname)) AND (coordinates.longitude_deg IS NOT NULL))) AS "decimalLongs";

--

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

--

CREATE VIEW _specimens_14_count_of_all_invalid_verbatim_lat_long AS

 SELECT ( SELECT count(*) AS count

           FROM public.coordinates

          WHERE ((coordinates.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname)) AND ((coordinates.latitude_deg > (90)::double precision) OR (coordinates.latitude_deg < ((-90))::double precision)))) AS "badLats", 

    ( SELECT count(*) AS count

           FROM public.coordinates

          WHERE ((coordinates.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname)) AND ((coordinates.longitude_deg > (180)::double precision) OR (coordinates.longitude_deg < ((-180))::double precision)))) AS "badLongs";

--

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

--

CREATE VIEW _specimens_15_list_distinct_locality_descriptions AS

 SELECT DISTINCT location.locationnarrative AS "localityDescription"

   FROM public.location

  WHERE ((location.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname)) AND (location.locationnarrative IS NOT NULL));

--

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

--

CREATE VIEW _specimens_16_list_distinct_specimen_descriptions AS

 SELECT DISTINCT aggregateoccurrence.notes AS "specimenDescription"

   FROM public.aggregateoccurrence

  WHERE ((aggregateoccurrence.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname)) AND (aggregateoccurrence.notes IS NOT NULL));

--