-- Quantitative validation queries on the BIEN database

--

-- Applies to any datasource with specimens

--

-- DB: vegbien

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

\set datasource '''NY'''

SET search_path TO public_validations, public;

SET enable_seqscan = off;

SET join_collapse_limit = 1; -- turn it off

-- _specimens_01_count_of_total_records_specimens_in_source_db

 SELECT count(*) AS "totalSpecimenRecords"

   FROM taxonoccurrence

-- _specimens_02_count_of_unique_verbatim_families

 SELECT count(DISTINCT taxonverbatim.family) AS families

   FROM taxonverbatim

-- _specimens_03_list_of_verbatim_families

 SELECT DISTINCT taxonverbatim.family

   FROM taxonverbatim

-- _specimens_04_count_of_species_binomials

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

   FROM taxonverbatim

-- _specimens_05_list_of_species_binomials

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

   FROM taxonverbatim

-- _specimens_06_count_of_unique_verb_subsp_taxa_with_author

 SELECT count(DISTINCT taxonlabel.taxonomicname) AS taxon_names_with_author

   FROM (taxonverbatim

-- _specimens_07_list_of_verbatim_subspecific_taxa_with_author

 SELECT DISTINCT taxonlabel.taxonomicname AS taxon_name_with_author

   FROM (taxonverbatim

-- _specimens_08_count_of_unique_verbatim_author_taxa_with_genus

 SELECT count(DISTINCT taxonlabel.taxonomicname) AS taxon_names_with_author

   FROM (taxonverbatim

-- _specimens_09_list_of_unique_verbatim_author_taxa_with_genus

 SELECT DISTINCT taxonlabel.taxonomicname AS taxon_name_with_author

   FROM (taxonverbatim

-- _specimens_10_count_number_of_records_by_institution

 SELECT ( SELECT sourcelist.name

           FROM sourcelist

          WHERE (sourcelist.sourcelist_id = specimenreplicate.duplicate_institutions_sourcelist_id)) AS specimen_duplicate_institutions,

    count(*) AS records

   FROM specimenreplicate

  GROUP BY specimenreplicate.duplicate_institutions_sourcelist_id;

-- _specimens_11_list_of_three_standard_political_divisions

 SELECT DISTINCT place.country,

    place.stateprovince AS "stateProvince",

    place.county

   FROM place

/*

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

*/

-- _specimens_12_distinct_collector_name_collect_num_date_w_count

 SELECT ( SELECT party.fullname

           FROM party

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

    (aggregateoccurrence.collectiondate)::text AS "dateCollected",

    count(*) AS "specimenRecords"

   FROM (((taxonoccurrence

  GROUP BY ( SELECT party.fullname

   FROM party

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

  ORDER BY ( SELECT party.fullname

   FROM party

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

-- _specimens_13_count_of_all_verbatim_and_decimal_lat_long

 SELECT ( SELECT count(DISTINCT ROW(coordinates.latitude_deg, coordinates.longitude_deg, coordinates.verbatimlatitude, coordinates.verbatimlongitude, coordinates.coordsaccuracy_m)) AS count

           FROM coordinates

          WHERE ((coordinates.source_id = ( SELECT source_by_shortname((:datasource)::text) AS source_by_shortname)) AND (((coordinates.verbatimlatitude IS NOT NULL) OR (coordinates.latitude_deg IS NOT NULL)) OR ((coordinates.verbatimlongitude IS NOT NULL) OR (coordinates.longitude_deg IS NOT NULL))))) AS "allLatLongs",

           FROM coordinates

          WHERE (((coordinates.source_id = ( SELECT source_by_shortname((:datasource)::text) AS source_by_shortname)) AND (coordinates.latitude_deg IS NOT NULL)) AND (coordinates.longitude_deg IS NOT NULL))) AS "decimalLatLongs";

-- _specimens_13a_list_of_all_verbatim_lat_long

-- _specimens_14_count_of_all_invalid_verbatim_lat_long

 SELECT ( SELECT count(*) AS count

           FROM coordinates

    ( SELECT count(*) AS count

           FROM coordinates

-- _specimens_15_list_distinct_locality_descriptions

 SELECT DISTINCT location.locationnarrative AS "localityDescription"

   FROM location

-- _specimens_16_list_distinct_specimen_descriptions

 SELECT DISTINCT aggregateoccurrence.notes AS "specimenDescription"

   FROM aggregateoccurrence