1 |
12397
|
aaronmk
|
-- -----------------------------------------------------------------------------
|
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 |
12401
|
aaronmk
|
FROM projects p JOIN "plotMetadata" pm
|
34 |
12397
|
aaronmk
|
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 |
12401
|
aaronmk
|
FROM projects p JOIN "plotMetadata" pm
|
43 |
12397
|
aaronmk
|
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 |
12401
|
aaronmk
|
FROM projects p JOIN "plotMetadata" pm
|
53 |
12397
|
aaronmk
|
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 |
12403
|
aaronmk
|
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 |
12397
|
aaronmk
|
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 |
12398
|
aaronmk
|
FROM projects p
|
75 |
12401
|
aaronmk
|
JOIN "plotMetadata" pm ON p.project_id=pm.project_id
|
76 |
|
|
JOIN "plotObservations" po ON pm."PlotID"=po."PlotID"
|
77 |
12397
|
aaronmk
|
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 |
12398
|
aaronmk
|
FROM projects p
|
88 |
12401
|
aaronmk
|
JOIN "plotMetadata" pm ON p.project_id=pm.project_id
|
89 |
|
|
JOIN "plotObservations" po ON pm."PlotID"=po."PlotID"
|
90 |
12397
|
aaronmk
|
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 |
12398
|
aaronmk
|
FROM projects p
|
101 |
12401
|
aaronmk
|
JOIN "plotMetadata" pm ON p.project_id=pm.project_id
|
102 |
|
|
JOIN "plotObservations" po ON pm."PlotID"=po."PlotID"
|
103 |
12397
|
aaronmk
|
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 |
12401
|
aaronmk
|
-- Note 2: Do not count records in plotObservations table, as this will give
|
115 |
12397
|
aaronmk
|
-- 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 |
12398
|
aaronmk
|
FROM projects p
|
122 |
12401
|
aaronmk
|
JOIN "plotMetadata" pm ON p.project_id=pm.project_id
|
123 |
|
|
JOIN "plotObservations" po ON pm."PlotID"=po."PlotID"
|
124 |
12397
|
aaronmk
|
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 |
12403
|
aaronmk
|
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 |
12397
|
aaronmk
|
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 |
12398
|
aaronmk
|
FROM projects p
|
160 |
12401
|
aaronmk
|
JOIN "plotMetadata" pm ON p.project_id=pm.project_id
|
161 |
|
|
JOIN "plotObservations" po ON pm."PlotID"=po."PlotID"
|
162 |
12397
|
aaronmk
|
) 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 |
12404
|
aaronmk
|
CREATE OR REPLACE VIEW _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p AS
|
174 |
12397
|
aaronmk
|
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 |
12398
|
aaronmk
|
FROM projects p
|
180 |
12401
|
aaronmk
|
JOIN "plotMetadata" pm ON p.project_id=pm.project_id
|
181 |
|
|
JOIN "plotObservations" po ON pm."PlotID"=po."PlotID"
|
182 |
12397
|
aaronmk
|
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 |
12404
|
aaronmk
|
CREATE OR REPLACE VIEW _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr AS
|
192 |
12397
|
aaronmk
|
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 |
12398
|
aaronmk
|
FROM projects p
|
199 |
12401
|
aaronmk
|
JOIN "plotMetadata" pm ON p.project_id=pm.project_id
|
200 |
|
|
JOIN "plotObservations" po ON pm."PlotID"=po."PlotID"
|
201 |
12397
|
aaronmk
|
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 |
12404
|
aaronmk
|
CREATE OR REPLACE VIEW _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro AS
|
219 |
12397
|
aaronmk
|
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 |
12398
|
aaronmk
|
FROM projects p
|
226 |
12401
|
aaronmk
|
JOIN "plotMetadata" pm ON p.project_id=pm.project_id
|
227 |
|
|
JOIN "plotObservations" po ON pm."PlotID"=po."PlotID"
|
228 |
12397
|
aaronmk
|
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 |
12404
|
aaronmk
|
CREATE OR REPLACE VIEW _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj AS
|
246 |
12397
|
aaronmk
|
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 |
12398
|
aaronmk
|
FROM projects p
|
253 |
12401
|
aaronmk
|
JOIN "plotMetadata" pm ON p.project_id=pm.project_id
|
254 |
|
|
JOIN "plotObservations" po ON pm."PlotID"=po."PlotID"
|
255 |
12397
|
aaronmk
|
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 |
12398
|
aaronmk
|
FROM projects p
|
265 |
12401
|
aaronmk
|
JOIN "plotMetadata" pm ON p.project_id=pm.project_id
|
266 |
|
|
JOIN "plotObservations" po ON pm."PlotID"=po."PlotID"
|
267 |
12397
|
aaronmk
|
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 |
12398
|
aaronmk
|
FROM projects p
|
279 |
12401
|
aaronmk
|
JOIN "plotMetadata" pm ON p.project_id=pm.project_id
|
280 |
|
|
JOIN "plotObservations" po ON pm."PlotID"=po."PlotID"
|
281 |
12397
|
aaronmk
|
AND pm."SiteCode" IS NOT NULL
|
282 |
|
|
ORDER BY project_name, pm."SiteCode", subplot
|
283 |
|
|
;
|