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

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

-- 

-- DB: vegbien

-- Host: vegbiendev.nceas.ucsb.edu

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

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

-- 1. Count records

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

SELECT COUNT(*) AS totalRecords

FROM trait

;

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

-- 2. Count trait names

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

SELECT COUNT(DISTINCT name) AS traits

FROM trait

;

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

-- 3. List trait names

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

SELECT DISTINCT name AS trait

FROM trait

ORDER BY trait

;

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

-- 4. Count records per trait

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

SELECT name AS trait, COUNT(*) AS measurements

FROM trait

GROUP BY trait

ORDER BY trait

;

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

-- 5. Count taxa

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

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

FROM taxonverbatim

WHERE source_id = source_by_shortname('bien2_traits')

;

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

-- 6. List distinct verbatim taxa

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

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

FROM taxonverbatim

WHERE source_id = source_by_shortname('bien2_traits')

ORDER BY taxonwithauthor

;

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

-- 7. Trait, value and units

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

SELECT name AS trait, value, units

FROM trait

ORDER BY trait, value, units

;

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

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

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

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 = source_by_shortname('bien2_traits')

ORDER BY taxonwithauthor, trait, value

;