-- Name: _traits_01_count_records; Type: VIEW; Schema: bien2_traits; Owner: -

--

 SELECT count(*) AS "totalRecords"

   FROM "TraitObservation"

  WHERE (((("TraitObservation"."taxonName" IS NOT NULL) AND ("TraitObservation"."taxonName" <> ''::text)) AND ("TraitObservation"."measurementName" IS NOT NULL)) AND ("TraitObservation"."measurementName" <> ''::text));

-- Name: _traits_02_count_trait_names; Type: VIEW; Schema: bien2_traits; Owner: -

--

 SELECT count(DISTINCT "TraitObservation"."measurementName") AS traits

   FROM "TraitObservation"

  WHERE (((("TraitObservation"."taxonName" IS NOT NULL) AND ("TraitObservation"."taxonName" <> ''::text)) AND ("TraitObservation"."measurementName" IS NOT NULL)) AND ("TraitObservation"."measurementName" <> ''::text));

-- Name: _traits_03_list_trait_names; Type: VIEW; Schema: bien2_traits; Owner: -

--

 SELECT DISTINCT "TraitObservation"."measurementName" AS trait

   FROM "TraitObservation"

  WHERE (((("TraitObservation"."taxonName" IS NOT NULL) AND ("TraitObservation"."taxonName" <> ''::text)) AND ("TraitObservation"."measurementName" IS NOT NULL)) AND ("TraitObservation"."measurementName" <> ''::text))

-- Name: _traits_04_count_records_per_trait; Type: VIEW; Schema: bien2_traits; Owner: -

--

 SELECT "TraitObservation"."measurementName" AS trait, 

    count(*) AS measurements

   FROM "TraitObservation"

-- Name: _traits_05_count_taxa; Type: VIEW; Schema: bien2_traits; Owner: -

--

 SELECT count(DISTINCT btrim(concat_ws(' '::text, ifnull("TraitObservation"."taxonName", ''::text), ifnull("TraitObservation"."scientificNameAuthorship", ''::text)))) AS taxa

   FROM "TraitObservation"

  WHERE (((("TraitObservation"."taxonName" IS NOT NULL) AND ("TraitObservation"."taxonName" <> ''::text)) AND ("TraitObservation"."measurementName" IS NOT NULL)) AND ("TraitObservation"."measurementName" <> ''::text));

-- Name: _traits_06_list_distinct_taxa; Type: VIEW; Schema: bien2_traits; Owner: -

--

 SELECT DISTINCT btrim(concat_ws(' '::text, ifnull("TraitObservation"."taxonName", ''::text), ifnull("TraitObservation"."scientificNameAuthorship", ''::text))) AS taxonwithauthor

   FROM "TraitObservation"

  WHERE (((("TraitObservation"."taxonName" IS NOT NULL) AND ("TraitObservation"."taxonName" <> ''::text)) AND ("TraitObservation"."measurementName" IS NOT NULL)) AND ("TraitObservation"."measurementName" <> ''::text))

-- Name: _traits_07_trait_value_and_units; Type: VIEW; Schema: bien2_traits; Owner: -

--

 SELECT "TraitObservation"."measurementName" AS trait, 

    "TraitObservation"."measurementValue" AS value, 

    "TraitObservation"."measurementUnit" AS units

-- Name: _traits_08_taxonname_trait_and_value; Type: VIEW; Schema: bien2_traits; Owner: -

--

 SELECT btrim(concat_ws(' '::text, ifnull("TraitObservation"."taxonName", ''::text), ifnull("TraitObservation"."scientificNameAuthorship", ''::text))) AS taxonwithauthor, 

    "TraitObservation"."measurementName" AS trait, 

    "TraitObservation"."measurementValue" AS value

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

--

 SELECT count(*) AS "totalSpecimenRecords"

   FROM "Ecatalog_all";

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

--

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

   FROM "Ecatalog_all"

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

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

--

 SELECT DISTINCT "Ecatalog_all".family

   FROM "Ecatalog_all"

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

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

--

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

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

--

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

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

--

 SELECT count(DISTINCT "Ecatalog_all"."scientificName") AS taxon_names_with_author

   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: _specimens_07_list_of_verbatim_subspecific_taxa_with_author; Type: VIEW; Schema: NY; Owner: -

--

 SELECT DISTINCT "Ecatalog_all"."scientificName" AS taxon_name_with_author

   FROM "Ecatalog_all"

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

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

--

 SELECT count(DISTINCT "Ecatalog_all"."scientificName") AS taxon_names_with_author

   FROM "Ecatalog_all"

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

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

--

 SELECT DISTINCT "Ecatalog_all"."scientificName" AS taxon_name_with_author

   FROM "Ecatalog_all"

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

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

--

 SELECT "Ecatalog_all".specimen_duplicate_institutions, 

    count(*) AS records

   FROM "Ecatalog_all"

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

--

 SELECT DISTINCT "Ecatalog_all".country, 

    "Ecatalog_all"."stateProvince", 

    "Ecatalog_all".county

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

--

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

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

--

 SELECT ( SELECT count(DISTINCT ROW("Ecatalog_all"."decimalLatitude", "Ecatalog_all"."decimalLongitude")) AS count

           FROM "Ecatalog_all"

          WHERE (("Ecatalog_all"."decimalLatitude" IS NOT NULL) OR ("Ecatalog_all"."decimalLongitude" IS NOT NULL))) AS "allLatLongs", 

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

--

 SELECT DISTINCT "Ecatalog_all"."decimalLatitude" AS "verbatimLatitude", 

    "Ecatalog_all"."decimalLongitude" AS "verbatimLongitude"

   FROM "Ecatalog_all"

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

--

 SELECT DISTINCT ("Ecatalog_all"."decimalLatitude")::double precision AS "decimalLatitude", 

    ("Ecatalog_all"."decimalLongitude")::double precision AS "decimalLongitude"

   FROM "Ecatalog_all"

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

--

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

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

--

 SELECT DISTINCT "Ecatalog_all".locality AS "localityDescription"

   FROM "Ecatalog_all"

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

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

--

 SELECT DISTINCT "Ecatalog_all"."specimenDescription"

   FROM "Ecatalog_all"

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