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

-- Quantitative validation queries against the trait table in db bien2 on nimoy

--

-- For all queries, omit records where taxon or traitName are null or blank

-- This filtering would have been performed prior to import

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

SET search_path TO "bien2_traits", util;

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

-- 1. Count records

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

CREATE OR REPLACE VIEW _traits_01_count_records AS

SELECT COUNT(*) AS totalrecords

FROM "TraitObservation"

WHERE "taxonName" IS NOT NULL AND "taxonName"<>'' AND "measurementName" IS NOT NULL AND "measurementName"<>''

;

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

-- 2. Count trait names

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

CREATE OR REPLACE VIEW _traits_02_count_trait_names AS

SELECT COUNT(DISTINCT "measurementName") AS traits

FROM "TraitObservation"

WHERE "taxonName" IS NOT NULL AND "taxonName"<>'' AND "measurementName" IS NOT NULL AND "measurementName"<>''

;

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

-- 3. List trait names

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

CREATE OR REPLACE VIEW _traits_03_list_trait_names AS

SELECT DISTINCT "measurementName" AS trait

FROM "TraitObservation"

WHERE "taxonName" IS NOT NULL AND "taxonName"<>'' AND "measurementName" IS NOT NULL AND "measurementName"<>''

ORDER BY trait

;

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

-- 4. Count records per trait

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

CREATE OR REPLACE VIEW _traits_04_count_records_per_trait AS

SELECT "measurementName" AS trait, COUNT(*) AS measurements

FROM "TraitObservation"

WHERE "taxonName" IS NOT NULL AND "taxonName"<>'' AND "measurementName" IS NOT NULL AND "measurementName"<>''

GROUP BY trait

ORDER BY trait

;

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

-- 5. Count taxa

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

CREATE OR REPLACE VIEW _traits_05_count_taxa AS

SELECT COUNT(DISTINCT

TRIM(CONCAT_WS(' ',IFNULL("taxonName",''),IFNULL("scientificNameAuthorship",'')))

) AS taxa

FROM "TraitObservation"

WHERE "taxonName" IS NOT NULL AND "taxonName"<>'' AND "measurementName" IS NOT NULL AND "measurementName"<>''

;

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

-- 6. List distinct taxa

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

CREATE OR REPLACE VIEW _traits_06_list_distinct_taxa AS

SELECT DISTINCT

TRIM(CONCAT_WS(' ',IFNULL("taxonName",''),IFNULL("scientificNameAuthorship",'')))

AS taxonwithauthor

FROM "TraitObservation"

WHERE "taxonName" IS NOT NULL AND "taxonName"<>'' AND "measurementName" IS NOT NULL AND "measurementName"<>''

ORDER BY taxonwithauthor

;

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

-- 7. Trait, value and units

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

CREATE OR REPLACE VIEW _traits_07_trait_value_and_units AS

SELECT "measurementName" AS trait, "measurementValue" AS value, "measurementUnit" AS units

FROM "TraitObservation"

WHERE "taxonName" IS NOT NULL AND "taxonName"<>'' AND "measurementName" IS NOT NULL AND "measurementName"<>''

ORDER BY trait, "value", units

;

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

-- 8. "taxonName", trait and value for first 5000 records

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

CREATE OR REPLACE VIEW _traits_08_taxonname_trait_and_value_for_first_5000_records AS

SELECT

TRIM(CONCAT_WS(' ',IFNULL("taxonName",''),IFNULL("scientificNameAuthorship",'')))

AS taxonwithauthor,

"measurementName" AS trait, "measurementValue" AS "value"

FROM "TraitObservation"

WHERE "taxonName" IS NOT NULL AND "taxonName"<>'' AND "measurementName" IS NOT NULL AND "measurementName"<>''

ORDER BY taxonwithauthor, trait, "value"

;