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

-- Quantitative validation queries on the BIEN database

--

-- Applies to any datasource with specimens

--

-- DB: vegbien

-- Host: vegbiendev.nceas.ucsb.edu

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

\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

  WHERE (taxonoccurrence.source_id = ( SELECT source_by_shortname((:datasource)::text) AS source_by_shortname));

-- _specimens_02_count_of_unique_verbatim_families

 SELECT count(DISTINCT taxonverbatim.family) AS families

   FROM taxonverbatim

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

-- _specimens_03_list_of_verbatim_families

 SELECT DISTINCT taxonverbatim.family

   FROM taxonverbatim

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

-- _specimens_04_count_of_species_binomials

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

   FROM taxonverbatim

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

-- _specimens_05_list_of_species_binomials

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

   FROM taxonverbatim

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

-- _specimens_06_count_of_unique_verb_subsp_taxa_with_author

 SELECT count(DISTINCT taxonlabel.taxonomicname) AS taxon_names_with_author

   FROM (taxonverbatim

   JOIN taxonlabel USING (taxonlabel_id))

  WHERE ((taxonverbatim.source_id = ( SELECT source_by_shortname((:datasource)::text) AS source_by_shortname)) AND (taxonverbatim.subspecies IS NOT NULL));

-- _specimens_07_list_of_verbatim_subspecific_taxa_with_author

 SELECT DISTINCT taxonlabel.taxonomicname AS taxon_name_with_author

   FROM (taxonverbatim

   JOIN taxonlabel USING (taxonlabel_id))

  WHERE ((taxonverbatim.source_id = ( SELECT source_by_shortname((:datasource)::text) AS source_by_shortname)) AND (taxonverbatim.subspecies IS NOT NULL));

-- _specimens_08_count_of_unique_verbatim_author_taxa_with_genus

 SELECT count(DISTINCT taxonlabel.taxonomicname) AS taxon_names_with_author

   FROM (taxonverbatim

   JOIN taxonlabel USING (taxonlabel_id))

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

-- _specimens_09_list_of_unique_verbatim_author_taxa_with_genus

 SELECT DISTINCT taxonlabel.taxonomicname AS taxon_name_with_author

   FROM (taxonverbatim

   JOIN taxonlabel USING (taxonlabel_id))

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

-- _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

  WHERE (specimenreplicate.source_id = ( SELECT source_by_shortname((:datasource)::text) AS source_by_shortname))

  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

  WHERE (place.source_id = ( SELECT source_by_shortname((:datasource)::text) AS source_by_shortname));

/*

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

    specimenreplicate.collectionnumber AS "collectionNumber", 

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

    count(*) AS "specimenRecords"

   FROM (((taxonoccurrence

   JOIN aggregateoccurrence USING (taxonoccurrence_id))

   JOIN plantobservation USING (aggregateoccurrence_id))

   JOIN specimenreplicate USING (plantobservation_id))

  WHERE (taxonoccurrence.source_id = ( SELECT source_by_shortname((:datasource)::text) AS source_by_shortname))

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

    ( 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.latitude_deg IS NOT NULL)) AND (coordinates.longitude_deg IS NOT NULL))) AS "decimalLatLongs";

-- _specimens_13a_list_of_all_verbatim_lat_long

 SELECT DISTINCT COALESCE(coordinates.verbatimlatitude, (coordinates.latitude_deg)::text) AS "verbatimLatitude", 

    COALESCE(coordinates.verbatimlatitude, (coordinates.longitude_deg)::text) AS "verbatimLongitude"

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

-- _specimens_13b_list_of_all_decimal_lat_long

 SELECT DISTINCT coordinates.latitude_deg AS "decimalLatitude", 

    coordinates.longitude_deg AS "decimalLongitude"

   FROM coordinates

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

-- _specimens_14_count_of_all_invalid_verbatim_lat_long

 SELECT ( SELECT count(*) AS count

           FROM coordinates

          WHERE ((coordinates.source_id = ( SELECT source_by_shortname((:datasource)::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 coordinates

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

-- _specimens_15_list_distinct_locality_descriptions

 SELECT DISTINCT location.locationnarrative AS "localityDescription"

   FROM location

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

-- _specimens_16_list_distinct_specimen_descriptions

 SELECT DISTINCT aggregateoccurrence.notes AS "specimenDescription"

   FROM aggregateoccurrence

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