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