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

-- Quantitative validation queries against table Trait in BIEN3 postgres db

-- 

-- DB: vegbien

-- Host: vegbiendev.nceas.ucsb.edu

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

SET search_path TO public_validations, public;

SET enable_seqscan = off;

SET join_collapse_limit = 1; -- turn it off

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

-- 1. Count records

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

CREATE VIEW _traits_01_count_records AS

SELECT COUNT(*) AS "totalRecords"

FROM trait

JOIN taxonoccurrence USING (taxonoccurrence_id)

WHERE taxonoccurrence.source_id = (SELECT source_by_shortname(current_schema))

;

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

-- 2. Count trait names

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

CREATE VIEW _traits_02_count_trait_names AS

SELECT COUNT(DISTINCT name) AS traits

FROM trait

JOIN taxonoccurrence USING (taxonoccurrence_id)

WHERE taxonoccurrence.source_id = (SELECT source_by_shortname(current_schema));

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

-- 3. List trait names

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

CREATE VIEW _traits_03_list_trait_names AS

SELECT DISTINCT name AS trait

FROM trait

JOIN taxonoccurrence USING (taxonoccurrence_id)

WHERE taxonoccurrence.source_id = (SELECT source_by_shortname(current_schema))

ORDER BY name

;

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

-- 4. Count records per trait

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

CREATE VIEW _traits_04_count_records_per_trait AS

SELECT name AS trait, COUNT(*) AS measurements

FROM trait

JOIN taxonoccurrence USING (taxonoccurrence_id)

WHERE taxonoccurrence.source_id = (SELECT source_by_shortname(current_schema))

GROUP BY name

ORDER BY name

;

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

-- 5. Count taxa

-- 

-- Note: No morphospecies in trait table, therefore count

-- 		taxon + authority only

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

CREATE VIEW _traits_05_count_taxa AS

SELECT COUNT(DISTINCT (taxonname, author)) AS taxa

FROM taxonverbatim

WHERE source_id = (SELECT source_by_shortname(current_schema))

;

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

-- 6. List taxa

-- 

-- Note 1: No morphospecies in trait table, use taxon + authority 

-- Note 2: Note formation of taxonCorrected: includes family ONLY if

-- taxon is not determined at least to genus

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

CREATE VIEW _traits_06_list_taxa AS

SELECT DISTINCT concat_ws(' ', taxonname, author) AS taxonwithauthor

FROM taxonverbatim

WHERE source_id = (SELECT source_by_shortname(current_schema))

ORDER BY concat_ws(' ', taxonname, author)

;

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

-- 7. Trait, value and units for first 5000 records 

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

CREATE VIEW _traits_07_trait_value_and_units_for_first_5000_records AS

SELECT name AS trait, value, units

FROM trait

JOIN taxonoccurrence USING (taxonoccurrence_id)

WHERE taxonoccurrence.source_id = (SELECT source_by_shortname(current_schema))

ORDER BY name, value, units

LIMIT 5000

;

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

-- 8. Taxon, trait and value for first 5000 records 

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

CREATE VIEW _traits_08_taxon_trait_and_value_for_first_5000_records AS

SELECT concat_ws(' ', taxonname, author) AS taxonwithauthor, name AS trait, value

FROM taxonverbatim

JOIN taxondetermination ON taxondetermination.taxonverbatim_id = taxonverbatim.taxonverbatim_id AND taxondetermination.iscurrent

JOIN trait USING (taxonoccurrence_id)

WHERE taxonverbatim.source_id = (SELECT source_by_shortname(current_schema))

ORDER BY taxonname

LIMIT 5000

;