1
|
-- -------------------------------------------------------------------------------
|
2
|
-- Quantitative validation queries against table Trait in BIEN3 postgres db
|
3
|
--
|
4
|
-- DB: vegbien
|
5
|
-- Host: vegbiendev.nceas.ucsb.edu
|
6
|
-- -------------------------------------------------------------------------------
|
7
|
|
8
|
SET search_path TO public_validations, public;
|
9
|
|
10
|
-- ------------------
|
11
|
-- 1. Count records
|
12
|
-- ------------------
|
13
|
CREATE OR REPLACE VIEW _traits_01_count_records AS
|
14
|
SELECT COUNT(*) AS "totalRecords"
|
15
|
FROM trait
|
16
|
JOIN taxonoccurrence USING (taxonoccurrence_id)
|
17
|
WHERE taxonoccurrence.source_id = source_by_shortname(current_schema)
|
18
|
;
|
19
|
|
20
|
-- ------------------
|
21
|
-- 2. Count trait names
|
22
|
-- ------------------
|
23
|
CREATE OR REPLACE VIEW _traits_02_count_trait_names AS
|
24
|
SELECT COUNT(DISTINCT name) AS traits
|
25
|
FROM trait
|
26
|
JOIN taxonoccurrence USING (taxonoccurrence_id)
|
27
|
WHERE taxonoccurrence.source_id = source_by_shortname(current_schema);
|
28
|
|
29
|
-- ------------------
|
30
|
-- 3. List trait names
|
31
|
-- ------------------
|
32
|
CREATE OR REPLACE VIEW _traits_03_list_trait_names AS
|
33
|
SELECT DISTINCT name AS trait
|
34
|
FROM trait
|
35
|
JOIN taxonoccurrence USING (taxonoccurrence_id)
|
36
|
WHERE taxonoccurrence.source_id = source_by_shortname(current_schema)
|
37
|
ORDER BY name
|
38
|
;
|
39
|
|
40
|
-- ------------------
|
41
|
-- 4. Count records per trait
|
42
|
-- ------------------
|
43
|
CREATE OR REPLACE VIEW _traits_04_count_records_per_trait AS
|
44
|
SELECT name AS trait, COUNT(*) AS measurements
|
45
|
FROM trait
|
46
|
JOIN taxonoccurrence USING (taxonoccurrence_id)
|
47
|
WHERE taxonoccurrence.source_id = source_by_shortname(current_schema)
|
48
|
GROUP BY name
|
49
|
ORDER BY name
|
50
|
;
|
51
|
|
52
|
-- ------------------
|
53
|
-- 5. Count taxa
|
54
|
--
|
55
|
-- Note: No morphospecies in trait table, therefore count
|
56
|
-- taxon + authority only
|
57
|
-- ------------------
|
58
|
CREATE OR REPLACE VIEW _traits_05_count_taxa AS
|
59
|
SELECT COUNT(DISTINCT (taxonname, author)) AS taxa
|
60
|
FROM taxonverbatim
|
61
|
WHERE source_id = source_by_shortname(current_schema)
|
62
|
;
|
63
|
|
64
|
-- ------------------
|
65
|
-- 6. List taxa
|
66
|
--
|
67
|
-- Note 1: No morphospecies in trait table, use taxon + authority
|
68
|
-- Note 2: Note formation of taxonCorrected: includes family ONLY if
|
69
|
-- taxon is not determined at least to genus
|
70
|
-- ------------------
|
71
|
CREATE OR REPLACE VIEW _traits_06_list_taxa AS
|
72
|
SELECT DISTINCT concat_ws(' ', taxonname, author) AS taxonwithauthor
|
73
|
FROM taxonverbatim
|
74
|
WHERE source_id = source_by_shortname(current_schema)
|
75
|
ORDER BY concat_ws(' ', taxonname, author)
|
76
|
;
|
77
|
|
78
|
-- ------------------
|
79
|
-- 7. Trait, value and units for first 5000 records
|
80
|
-- ------------------
|
81
|
CREATE OR REPLACE VIEW _traits_07_trait_value_and_units_for_first_5000_records AS
|
82
|
SELECT name AS trait, value, units
|
83
|
FROM trait
|
84
|
JOIN taxonoccurrence USING (taxonoccurrence_id)
|
85
|
WHERE taxonoccurrence.source_id = source_by_shortname(current_schema)
|
86
|
ORDER BY name, value, units
|
87
|
LIMIT 5000
|
88
|
;
|
89
|
|
90
|
-- ------------------
|
91
|
-- 8. Taxon, trait and value for first 5000 records
|
92
|
-- ------------------
|
93
|
CREATE OR REPLACE VIEW _traits_08_taxon_trait_and_value_for_first_5000_records AS
|
94
|
SELECT concat_ws(' ', taxonname, author) AS taxonwithauthor, name AS trait, value
|
95
|
FROM taxonverbatim
|
96
|
JOIN taxondetermination ON taxondetermination.taxonverbatim_id = taxonverbatim.taxonverbatim_id AND taxondetermination.iscurrent
|
97
|
JOIN trait USING (taxonoccurrence_id)
|
98
|
WHERE taxonverbatim.source_id = source_by_shortname(current_schema)
|
99
|
ORDER BY taxonname
|
100
|
LIMIT 5000
|
101
|
;
|