1
|
-- -----------------------------------------------------------------------------
|
2
|
-- Quantitative validation queries against the original SALVIAS database
|
3
|
-- -------------------------------------------------------------------------
|
4
|
|
5
|
-- ------------------
|
6
|
-- Set variables specific to this source
|
7
|
-- Currently configured for SALVIAS
|
8
|
-- ------------------
|
9
|
|
10
|
SET search_path TO "SALVIAS";
|
11
|
|
12
|
-- ------------------
|
13
|
-- 1. Count of projects
|
14
|
-- ------------------
|
15
|
CREATE OR REPLACE VIEW _plots_01_count_of_projects AS
|
16
|
SELECT COUNT(*) AS projects
|
17
|
FROM projects
|
18
|
;
|
19
|
|
20
|
-- ------------------
|
21
|
-- 2. List of project names
|
22
|
-- ------------------
|
23
|
CREATE OR REPLACE VIEW _plots_02_list_of_project_names AS
|
24
|
SELECT project_name AS projectname
|
25
|
FROM projects
|
26
|
;
|
27
|
|
28
|
-- ------------------
|
29
|
-- 3. Count of all plots in this source
|
30
|
-- ------------------
|
31
|
CREATE OR REPLACE VIEW _plots_03_count_of_all_plots_in_this_source AS
|
32
|
SELECT COUNT(DISTINCT "SiteCode") AS plots
|
33
|
FROM projects p JOIN "plotMetadata" pm
|
34
|
ON p.project_id=pm.project_id
|
35
|
;
|
36
|
|
37
|
-- ------------------
|
38
|
-- 4. Count of plots in each project in this source
|
39
|
-- ------------------
|
40
|
CREATE OR REPLACE VIEW _plots_04_count_of_plots_in_each_project_in_this_source AS
|
41
|
SELECT p.project_name, COUNT(*) AS plots
|
42
|
FROM projects p JOIN "plotMetadata" pm
|
43
|
ON p.project_id=pm.project_id
|
44
|
GROUP BY p.project_name
|
45
|
;
|
46
|
|
47
|
-- ------------------
|
48
|
-- 5.List of plot codes by project
|
49
|
-- ------------------
|
50
|
CREATE OR REPLACE VIEW _plots_05_list_of_plot_codes_by_project AS
|
51
|
SELECT p.project_name, pm."SiteCode"
|
52
|
FROM projects p JOIN "plotMetadata" pm
|
53
|
ON p.project_id=pm.project_id
|
54
|
;
|
55
|
|
56
|
-- ------------------
|
57
|
-- 6. List of plots with stem measurements
|
58
|
-- ------------------
|
59
|
CREATE OR REPLACE VIEW _plots_06_list_of_plots_with_stem_measurements AS
|
60
|
SELECT DISTINCT p.project_name, pm."SiteCode"
|
61
|
FROM projects p
|
62
|
JOIN "plotMetadata" pm ON p.project_id=pm.project_id
|
63
|
JOIN "plotObservations" po ON pm."PlotID"=po."PlotID"
|
64
|
JOIN stems s ON po."PlotObsID"=s.plotobs_id
|
65
|
GROUP BY p.project_name, "SiteCode"
|
66
|
ORDER BY p.project_name, "SiteCode"
|
67
|
;
|
68
|
|
69
|
-- ------------------
|
70
|
-- 7.List of plots with counts of individuals per species
|
71
|
-- ------------------
|
72
|
CREATE OR REPLACE VIEW _plots_07_list_of_plots_with_counts_of_individuals_per_species AS
|
73
|
SELECT DISTINCT p.project_name, pm."SiteCode"
|
74
|
FROM projects p
|
75
|
JOIN "plotMetadata" pm ON p.project_id=pm.project_id
|
76
|
JOIN "plotObservations" po ON pm."PlotID"=po."PlotID"
|
77
|
AND po."NoInd">1
|
78
|
GROUP BY p.project_name, "SiteCode"
|
79
|
ORDER BY p.project_name, "SiteCode"
|
80
|
;
|
81
|
|
82
|
-- ------------------
|
83
|
-- 8.List of plots which use percent cover
|
84
|
-- ------------------
|
85
|
CREATE OR REPLACE VIEW _plots_08_list_of_plots_which_use_percent_cover AS
|
86
|
SELECT DISTINCT p.project_name, pm."SiteCode"
|
87
|
FROM projects p
|
88
|
JOIN "plotMetadata" pm ON p.project_id=pm.project_id
|
89
|
JOIN "plotObservations" po ON pm."PlotID"=po."PlotID"
|
90
|
AND cover_percent IS NOT NULL
|
91
|
GROUP BY p.project_name, "SiteCode"
|
92
|
ORDER BY p.project_name, "SiteCode"
|
93
|
;
|
94
|
|
95
|
-- ------------------
|
96
|
-- 9.List of plots which use line-intercept
|
97
|
-- ------------------
|
98
|
CREATE OR REPLACE VIEW _plots_09_list_of_plots_which_use_line_intercept AS
|
99
|
SELECT DISTINCT p.project_name, pm."SiteCode"
|
100
|
FROM projects p
|
101
|
JOIN "plotMetadata" pm ON p.project_id=pm.project_id
|
102
|
JOIN "plotObservations" po ON pm."PlotID"=po."PlotID"
|
103
|
AND intercept_cm IS NOT NULL
|
104
|
GROUP BY p.project_name, "SiteCode"
|
105
|
ORDER BY p.project_name, "SiteCode"
|
106
|
;
|
107
|
|
108
|
-- ------------------
|
109
|
-- 10. Count of individuals per plot in each project
|
110
|
--
|
111
|
-- Method: Sum on NoInd (number of individuals) column.
|
112
|
-- Note 1: `individuals` should be NULL for plots which use percent cover
|
113
|
-- or line-intercept methods.
|
114
|
-- Note 2: Do not count records in plotObservations table, as this will give
|
115
|
-- incorrect total for plots such as Gentry plot, which count individuals
|
116
|
-- per species per subplot
|
117
|
-- ------------------
|
118
|
CREATE OR REPLACE VIEW _plots_10_count_of_individuals_per_plot_in_each_project AS
|
119
|
SELECT p.project_name, "SiteCode" AS plotcode,
|
120
|
SUM("NoInd") AS individuals
|
121
|
FROM projects p
|
122
|
JOIN "plotMetadata" pm ON p.project_id=pm.project_id
|
123
|
JOIN "plotObservations" po ON pm."PlotID"=po."PlotID"
|
124
|
GROUP BY p.project_name, "SiteCode"
|
125
|
ORDER BY p.project_name, "SiteCode"
|
126
|
;
|
127
|
|
128
|
-- ------------------
|
129
|
-- 11. Count of stems per plot in each project
|
130
|
--
|
131
|
-- Method: count records in stems table
|
132
|
-- ------------------
|
133
|
CREATE OR REPLACE VIEW _plots_11_count_of_stems_per_plot_in_each_project AS
|
134
|
SELECT p.project_name, "SiteCode" AS plotcode,
|
135
|
COUNT(DISTINCT stem_id) AS stems
|
136
|
FROM projects p
|
137
|
JOIN "plotMetadata" pm ON p.project_id=pm.project_id
|
138
|
JOIN "plotObservations" po ON pm."PlotID"=po."PlotID"
|
139
|
JOIN stems s ON po."PlotObsID"=s.plotobs_id
|
140
|
GROUP BY p.project_name, "SiteCode"
|
141
|
ORDER BY p.project_name, "SiteCode"
|
142
|
;
|
143
|
|
144
|
-- ------------------
|
145
|
-- 12. Count of verbatim taxa per plot in each project
|
146
|
--
|
147
|
-- Note: Assumes identical concatenation of taxonomic fields
|
148
|
-- to form verbatim taxon name in vegBIEN
|
149
|
-- ------------------
|
150
|
CREATE OR REPLACE VIEW _plots_12_count_of_verbatim_taxa_per_plot_in_each_project AS
|
151
|
SELECT project_name, plotcode, COUNT(taxon) AS taxa
|
152
|
FROM
|
153
|
(
|
154
|
SELECT DISTINCT p.project_name, "SiteCode" AS plotcode,
|
155
|
TRIM(CONCAT_WS(' ',IFNULL("Family",''),IFNULL("Genus",''),IFNULL("Species",''),
|
156
|
IF(infra_ep_1 IS NULL,IFNULL(auth,''),
|
157
|
TRIM(CONCAT_WS(' ',IFNULL(infra_rank_1,''),IFNULL(infra_ep_1,''),IFNULL(infra_auth_1,'')))
|
158
|
))) AS taxon
|
159
|
FROM projects p
|
160
|
JOIN "plotMetadata" pm ON p.project_id=pm.project_id
|
161
|
JOIN "plotObservations" po ON pm."PlotID"=po."PlotID"
|
162
|
) AS a
|
163
|
GROUP BY project_name, plotcode
|
164
|
ORDER BY project_name, plotcode
|
165
|
;
|
166
|
|
167
|
-- ------------------
|
168
|
-- 13. List of distinct verbatim taxa in each plot in each project
|
169
|
--
|
170
|
-- Note: Assumes identical concatenation of taxonomic fields
|
171
|
-- to form verbatim taxon name in vegBIEN.
|
172
|
-- ------------------
|
173
|
CREATE OR REPLACE VIEW _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_project AS
|
174
|
SELECT DISTINCT p.project_name, "SiteCode" AS plotcode,
|
175
|
TRIM(CONCAT_WS(' ',IFNULL("Family",''),IFNULL("Genus",''),IFNULL("Species",''),
|
176
|
IF(infra_ep_1 IS NULL,IFNULL(auth,''),
|
177
|
TRIM(CONCAT_WS(' ',IFNULL(infra_rank_1,''),IFNULL(infra_ep_1,''),IFNULL(infra_auth_1,'')))
|
178
|
))) AS taxon
|
179
|
FROM projects p
|
180
|
JOIN "plotMetadata" pm ON p.project_id=pm.project_id
|
181
|
JOIN "plotObservations" po ON pm."PlotID"=po."PlotID"
|
182
|
ORDER BY p.project_name, "SiteCode", taxon
|
183
|
;
|
184
|
|
185
|
-- ------------------
|
186
|
-- 14. Count of individuals per (verbatim) taxon per plot in each project
|
187
|
--
|
188
|
-- Note: Assumes identical concatenation of taxonomic fields
|
189
|
-- to form verbatim taxon name in vegBIEN
|
190
|
-- ------------------
|
191
|
CREATE OR REPLACE VIEW _plots_14_count_of_individuals_per_verbatim_taxon_per_plot_in_each_project AS
|
192
|
SELECT DISTINCT p.project_name, "SiteCode" AS plotcode,
|
193
|
TRIM(CONCAT_WS(' ',IFNULL("Family",''),IFNULL("Genus",''),IFNULL("Species",''),
|
194
|
IF(infra_ep_1 IS NULL,IFNULL(auth,''),
|
195
|
TRIM(CONCAT_WS(' ',IFNULL(infra_rank_1,''),IFNULL(infra_ep_1,''),IFNULL(infra_auth_1,'')))
|
196
|
))) AS taxon,
|
197
|
SUM("NoInd") AS individuals
|
198
|
FROM projects p
|
199
|
JOIN "plotMetadata" pm ON p.project_id=pm.project_id
|
200
|
JOIN "plotObservations" po ON pm."PlotID"=po."PlotID"
|
201
|
GROUP BY p.project_name, "SiteCode", taxon
|
202
|
ORDER BY p.project_name, "SiteCode", taxon
|
203
|
;
|
204
|
|
205
|
-- ------------------
|
206
|
-- 15. Percent cover of each (verbatim) taxon in each plot in each project
|
207
|
-- where percent cover is recorded
|
208
|
--
|
209
|
-- Note 1: Assumes identical concatenation of taxonomic fields
|
210
|
-- to form verbatim taxon name in vegbien
|
211
|
-- Note 2: Grouping mean cover for entire plot allows inclusion of plots with
|
212
|
-- subplots (separate cover measure for each species in each subplot),
|
213
|
-- plots with strata (separate cover measures for each species in each
|
214
|
-- stratum), and plots without subplots or strata (one cover measure
|
215
|
-- per species per plot) in same query.
|
216
|
-- Note 3: currently, there are no SALVIAS percent cover plots in BIEN
|
217
|
-- ------------------
|
218
|
CREATE OR REPLACE VIEW _plots_15_percent_cover_of_each_verbatim_taxon_in_each_plot_in_each_project AS
|
219
|
SELECT DISTINCT p.project_name, "SiteCode" AS plotcode,
|
220
|
TRIM(CONCAT_WS(' ',IFNULL("Family",''),IFNULL("Genus",''),IFNULL("Species",''),
|
221
|
IF(infra_ep_1 IS NULL,IFNULL(auth,''),
|
222
|
TRIM(CONCAT_WS(' ',IFNULL(infra_rank_1,''),IFNULL(infra_ep_1,''),IFNULL(infra_auth_1,'')))
|
223
|
))) AS taxon,
|
224
|
AVG(cover_percent) AS mean_cover
|
225
|
FROM projects p
|
226
|
JOIN "plotMetadata" pm ON p.project_id=pm.project_id
|
227
|
JOIN "plotObservations" po ON pm."PlotID"=po."PlotID"
|
228
|
AND cover_percent IS NOT NULL
|
229
|
GROUP BY p.project_name, "SiteCode", taxon
|
230
|
ORDER BY p.project_name, "SiteCode", taxon
|
231
|
;
|
232
|
|
233
|
-- ------------------
|
234
|
-- 16. Intercept values for each (verbatim) taxon in each plot in each project
|
235
|
-- where line-intercept values are recorded
|
236
|
--
|
237
|
-- Note 1: Assumes identical concatenation of taxonomic fields
|
238
|
-- to form verbatim taxon name in vegbien
|
239
|
-- Note 2: Grouping mean cover for entire plot combines plots with
|
240
|
-- subplots (separate cover measure for each species in each subplot) with
|
241
|
-- plots with strata (separate cover measures for each species in each
|
242
|
-- stratum) with plots without subplots or strata (one cover measure
|
243
|
-- per species per plot.
|
244
|
-- ------------------
|
245
|
CREATE OR REPLACE VIEW _plots_16_intercept_values_for_each_verbatim_taxon_in_each_plot_in_each_project AS
|
246
|
SELECT DISTINCT p.project_name, "SiteCode" AS plotcode,
|
247
|
TRIM(CONCAT_WS(' ',IFNULL("Family",''),IFNULL("Genus",''),IFNULL("Species",''),
|
248
|
IF(infra_ep_1 IS NULL,IFNULL(auth,''),
|
249
|
TRIM(CONCAT_WS(' ',IFNULL(infra_rank_1,''),IFNULL(infra_ep_1,''),IFNULL(infra_auth_1,'')))
|
250
|
))) AS taxon,
|
251
|
intercept_cm
|
252
|
FROM projects p
|
253
|
JOIN "plotMetadata" pm ON p.project_id=pm.project_id
|
254
|
JOIN "plotObservations" po ON pm."PlotID"=po."PlotID"
|
255
|
AND intercept_cm IS NOT NULL
|
256
|
ORDER BY p.project_name, "SiteCode", taxon
|
257
|
;
|
258
|
|
259
|
-- ------------------
|
260
|
-- 17. Count of subplots per plot, for each project
|
261
|
-- ------------------
|
262
|
CREATE OR REPLACE VIEW _plots_17_count_of_subplots_per_plot_for_each_project AS
|
263
|
SELECT project_name, pm."SiteCode" AS plotcode, COUNT(DISTINCT "Line") as subplots
|
264
|
FROM projects p
|
265
|
JOIN "plotMetadata" pm ON p.project_id=pm.project_id
|
266
|
JOIN "plotObservations" po ON pm."PlotID"=po."PlotID"
|
267
|
GROUP BY project_name, pm."SiteCode"
|
268
|
ORDER BY project_name, pm."SiteCode"
|
269
|
;
|
270
|
|
271
|
-- ------------------
|
272
|
-- 18. List of subplots codes for each plot for each project
|
273
|
--
|
274
|
-- Note: includes plots with and without subplots
|
275
|
-- ------------------
|
276
|
CREATE OR REPLACE VIEW _plots_18_list_of_subplots_codes_for_each_plot_for_each_project AS
|
277
|
SELECT DISTINCT project_name, pm."SiteCode" AS plotcode, "Line" as subplot
|
278
|
FROM projects p
|
279
|
JOIN "plotMetadata" pm ON p.project_id=pm.project_id
|
280
|
JOIN "plotObservations" po ON pm."PlotID"=po."PlotID"
|
281
|
AND pm."SiteCode" IS NOT NULL
|
282
|
ORDER BY project_name, pm."SiteCode", subplot
|
283
|
;
|