CREATE VIEW _specimens_01_count_of_total_records_specimens_in_source_db AS

SELECT count(*) AS "totalSpecimenRecords"

FROM taxonoccurrence

;

CREATE VIEW _specimens_02_count_of_unique_verbatim_families AS

SELECT count(DISTINCT family) AS families

FROM taxonverbatim

AND family IS NOT NULL

;

CREATE VIEW _specimens_03_list_of_verbatim_families AS

SELECT DISTINCT family

FROM taxonverbatim

AND family IS NOT NULL

;

CREATE VIEW _specimens_04_count_of_species_binomials AS

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

FROM taxonverbatim

AND genus IS NOT NULL AND specific_epithet IS NOT NULL;

;

CREATE VIEW _specimens_05_list_of_species_binomials AS

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

FROM taxonverbatim

AND genus IS NOT NULL AND specific_epithet IS NOT NULL;

;

CREATE VIEW _specimens_06_count_of_unique_verb_subsp_taxa_with_author AS

SELECT count(DISTINCT taxonlabel.taxonomicname) AS taxon_names_with_author

FROM taxonlabel

AND taxonlabel.taxonomicname IS NOT NULL

;

CREATE VIEW _specimens_07_list_of_verbatim_subspecific_taxa_with_author AS

SELECT DISTINCT taxonlabel.taxonomicname AS taxon_name_with_author

FROM taxonlabel

AND taxonlabel.taxonomicname IS NOT NULL

;

SELECT count(DISTINCT taxonlabel.taxonomicname) AS taxon_names_with_author

FROM taxonverbatim

JOIN taxonlabel USING (taxonlabel_id)

AND genus IS NOT NULL

;

SELECT DISTINCT taxonlabel.taxonomicname AS taxon_name_with_author

FROM taxonverbatim

JOIN taxonlabel USING (taxonlabel_id)

AND genus IS NOT NULL

;

SELECT specimenreplicate.duplicate_institutions_sourcelist_id AS specimen_duplicate_institutions,

count(*) AS records

FROM specimenreplicate

GROUP BY specimen_duplicate_institutions

;

CREATE VIEW _specimens_11_list_of_three_standard_political_divisions AS

SELECT DISTINCT country, stateprovince, county

FROM place

;

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

JOIN aggregateoccurrence USING (taxonoccurrence_id)

JOIN plantobservation USING (aggregateoccurrence_id)

JOIN specimenreplicate USING (plantobservation_id)

GROUP BY "collectorName", "collectionNumber", "dateCollected"

ORDER BY "collectorName", "collectionNumber", "dateCollected"

;

SELECT

   (SELECT count(*) AS count

	FROM coordinates

	AND (verbatimlatitude IS NOT NULL OR latitude_deg IS NOT NULL))

	AS "allLats"

,  (SELECT count(*) AS count

	FROM coordinates

	AND (latitude_deg IS NOT NULL))

	AS "decimalLats"

,  (SELECT count(*) AS count

	FROM coordinates

	AND (verbatimlongitude IS NOT NULL OR longitude_deg IS NOT NULL))

	AS "allLongs"

,  (SELECT count(*) AS count

	FROM coordinates

	AND (longitude_deg IS NOT NULL))

	AS "decimalLongs"

;

SELECT

  (SELECT count(*) AS count

	FROM coordinates

	AND (((coordinates.latitude_deg)::double precision > (90)::double precision) OR ((coordinates.latitude_deg)::double precision < ((-90))::double precision))) AS "badLats"

, (SELECT count(*) AS count

	FROM coordinates

	AND (((coordinates.longitude_deg)::double precision > (180)::double precision) OR ((coordinates.longitude_deg)::double precision < ((-180))::double precision))) AS "badLongs"

;

CREATE VIEW _specimens_15_list_distinct_locality_descriptions AS

SELECT DISTINCT locationnarrative AS "localityDescription"

FROM location

AND locationnarrative IS NOT NULL

;

CREATE VIEW _specimens_16_list_distinct_specimen_descriptions AS

SELECT DISTINCT notes AS "specimenDescription"

FROM aggregateoccurrence

AND notes IS NOT NULL

;