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 |
|
|
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 |
|
|
;
|