|
1
|
--
|
|
2
|
-- PostgreSQL database dump
|
|
3
|
--
|
|
4
|
|
|
5
|
SET statement_timeout = 0;
|
|
6
|
SET lock_timeout = 0;
|
|
7
|
SET client_encoding = 'UTF8';
|
|
8
|
SET standard_conforming_strings = on;
|
|
9
|
SET check_function_bodies = false;
|
|
10
|
SET client_min_messages = warning;
|
|
11
|
|
|
12
|
SET search_path = "SALVIAS", pg_catalog;
|
|
13
|
|
|
14
|
--
|
|
15
|
-- Name: _plots_01_count_of_projects; Type: VIEW; Schema: SALVIAS; Owner: -
|
|
16
|
--
|
|
17
|
|
|
18
|
CREATE VIEW _plots_01_count_of_projects AS
|
|
19
|
SELECT count(*) AS projects
|
|
20
|
FROM projects;
|
|
21
|
|
|
22
|
|
|
23
|
--
|
|
24
|
-- Name: _plots_02_list_of_project_names; Type: VIEW; Schema: SALVIAS; Owner: -
|
|
25
|
--
|
|
26
|
|
|
27
|
CREATE VIEW _plots_02_list_of_project_names AS
|
|
28
|
SELECT projects.project_name AS projectname
|
|
29
|
FROM projects;
|
|
30
|
|
|
31
|
|
|
32
|
--
|
|
33
|
-- Name: _plots_03_count_of_all_plots_in_this_source; Type: VIEW; Schema: SALVIAS; Owner: -
|
|
34
|
--
|
|
35
|
|
|
36
|
CREATE VIEW _plots_03_count_of_all_plots_in_this_source AS
|
|
37
|
SELECT count(DISTINCT pm."SiteCode") AS plots
|
|
38
|
FROM (projects p
|
|
39
|
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)));
|
|
40
|
|
|
41
|
|
|
42
|
--
|
|
43
|
-- Name: _plots_04_count_of_plots_in_each_project_in_this_source; Type: VIEW; Schema: SALVIAS; Owner: -
|
|
44
|
--
|
|
45
|
|
|
46
|
CREATE VIEW _plots_04_count_of_plots_in_each_project_in_this_source AS
|
|
47
|
SELECT p.project_name,
|
|
48
|
count(*) AS plots
|
|
49
|
FROM (projects p
|
|
50
|
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
|
|
51
|
GROUP BY p.project_name;
|
|
52
|
|
|
53
|
|
|
54
|
--
|
|
55
|
-- Name: _plots_05_list_of_plot_codes_by_project; Type: VIEW; Schema: SALVIAS; Owner: -
|
|
56
|
--
|
|
57
|
|
|
58
|
CREATE VIEW _plots_05_list_of_plot_codes_by_project AS
|
|
59
|
SELECT p.project_name,
|
|
60
|
pm."SiteCode"
|
|
61
|
FROM (projects p
|
|
62
|
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)));
|
|
63
|
|
|
64
|
|
|
65
|
--
|
|
66
|
-- Name: _plots_06_list_of_plots_with_stem_measurements; Type: VIEW; Schema: SALVIAS; Owner: -
|
|
67
|
--
|
|
68
|
|
|
69
|
CREATE VIEW _plots_06_list_of_plots_with_stem_measurements AS
|
|
70
|
SELECT DISTINCT p.project_name,
|
|
71
|
pm."SiteCode"
|
|
72
|
FROM (((projects p
|
|
73
|
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
|
|
74
|
JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID")))
|
|
75
|
JOIN stems s ON ((po."PlotObsID" = s.plotobs_id)))
|
|
76
|
GROUP BY p.project_name, pm."SiteCode"
|
|
77
|
ORDER BY p.project_name, pm."SiteCode";
|
|
78
|
|
|
79
|
|
|
80
|
--
|
|
81
|
-- Name: _plots_07_list_of_plots_with_counts_of_individuals_per_species; Type: VIEW; Schema: SALVIAS; Owner: -
|
|
82
|
--
|
|
83
|
|
|
84
|
CREATE VIEW _plots_07_list_of_plots_with_counts_of_individuals_per_species AS
|
|
85
|
SELECT DISTINCT p.project_name,
|
|
86
|
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") AND (po."NoInd" > 1))))
|
|
90
|
GROUP BY p.project_name, pm."SiteCode"
|
|
91
|
ORDER BY p.project_name, pm."SiteCode";
|
|
92
|
|
|
93
|
|
|
94
|
--
|
|
95
|
-- Name: _plots_08_list_of_plots_which_use_percent_cover; Type: VIEW; Schema: SALVIAS; Owner: -
|
|
96
|
--
|
|
97
|
|
|
98
|
CREATE VIEW _plots_08_list_of_plots_which_use_percent_cover AS
|
|
99
|
SELECT DISTINCT p.project_name,
|
|
100
|
pm."SiteCode"
|
|
101
|
FROM ((projects p
|
|
102
|
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
|
|
103
|
JOIN "plotObservations" po ON (((pm."PlotID" = po."PlotID") AND (po.cover_percent IS NOT NULL))))
|
|
104
|
GROUP BY p.project_name, pm."SiteCode"
|
|
105
|
ORDER BY p.project_name, pm."SiteCode";
|
|
106
|
|
|
107
|
|
|
108
|
--
|
|
109
|
-- Name: _plots_09_list_of_plots_which_use_line_intercept; Type: VIEW; Schema: SALVIAS; Owner: -
|
|
110
|
--
|
|
111
|
|
|
112
|
CREATE VIEW _plots_09_list_of_plots_which_use_line_intercept AS
|
|
113
|
SELECT DISTINCT p.project_name,
|
|
114
|
pm."SiteCode"
|
|
115
|
FROM ((projects p
|
|
116
|
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
|
|
117
|
JOIN "plotObservations" po ON (((pm."PlotID" = po."PlotID") AND (po.intercept_cm IS NOT NULL))))
|
|
118
|
GROUP BY p.project_name, pm."SiteCode"
|
|
119
|
ORDER BY p.project_name, pm."SiteCode";
|
|
120
|
|
|
121
|
|
|
122
|
--
|
|
123
|
-- Name: _plots_10_count_of_individuals_per_plot_in_each_project; Type: VIEW; Schema: SALVIAS; Owner: -
|
|
124
|
--
|
|
125
|
|
|
126
|
CREATE VIEW _plots_10_count_of_individuals_per_plot_in_each_project AS
|
|
127
|
SELECT p.project_name,
|
|
128
|
pm."SiteCode" AS plotcode,
|
|
129
|
sum(po."NoInd") AS individuals
|
|
130
|
FROM ((projects p
|
|
131
|
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
|
|
132
|
JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID")))
|
|
133
|
GROUP BY p.project_name, pm."SiteCode"
|
|
134
|
ORDER BY p.project_name, pm."SiteCode";
|
|
135
|
|
|
136
|
|
|
137
|
--
|
|
138
|
-- Name: _plots_11_count_of_stems_per_plot_in_each_project; Type: VIEW; Schema: SALVIAS; Owner: -
|
|
139
|
--
|
|
140
|
|
|
141
|
CREATE VIEW _plots_11_count_of_stems_per_plot_in_each_project AS
|
|
142
|
SELECT p.project_name,
|
|
143
|
pm."SiteCode" AS plotcode,
|
|
144
|
count(DISTINCT s.stem_id) AS stems
|
|
145
|
FROM (((projects p
|
|
146
|
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
|
|
147
|
JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID")))
|
|
148
|
JOIN stems s ON ((po."PlotObsID" = s.plotobs_id)))
|
|
149
|
GROUP BY p.project_name, pm."SiteCode"
|
|
150
|
ORDER BY p.project_name, pm."SiteCode";
|
|
151
|
|
|
152
|
|
|
153
|
--
|
|
154
|
-- Name: _plots_12_count_of_verbatim_taxa_per_plot_in_each_project; Type: VIEW; Schema: SALVIAS; Owner: -
|
|
155
|
--
|
|
156
|
|
|
157
|
CREATE VIEW _plots_12_count_of_verbatim_taxa_per_plot_in_each_project AS
|
|
158
|
SELECT a.project_name,
|
|
159
|
a.plotcode,
|
|
160
|
count(a.taxon) AS taxa
|
|
161
|
FROM ( SELECT DISTINCT p.project_name,
|
|
162
|
pm."SiteCode" AS plotcode,
|
|
163
|
btrim(concat_ws(' '::text, ifnull(po."Family", ''::text), ifnull(po."Genus", ''::text), ifnull(po."Species", ''::text), if((po.infra_ep_1 IS NULL), ifnull(po.auth, ''::text), btrim(concat_ws(' '::text, ifnull(po.infra_rank_1, ''::text), ifnull(po.infra_ep_1, ''::text), ifnull(po.infra_auth_1, ''::text)))))) AS taxon
|
|
164
|
FROM ((projects p
|
|
165
|
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
|
|
166
|
JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID")))) a
|
|
167
|
GROUP BY a.project_name, a.plotcode
|
|
168
|
ORDER BY a.project_name, a.plotcode;
|
|
169
|
|
|
170
|
|
|
171
|
--
|
|
172
|
-- Name: _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p; Type: VIEW; Schema: SALVIAS; Owner: -
|
|
173
|
--
|
|
174
|
|
|
175
|
CREATE VIEW _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p AS
|
|
176
|
SELECT DISTINCT p.project_name,
|
|
177
|
pm."SiteCode" AS plotcode,
|
|
178
|
btrim(concat_ws(' '::text, ifnull(po."Family", ''::text), ifnull(po."Genus", ''::text), ifnull(po."Species", ''::text), if((po.infra_ep_1 IS NULL), ifnull(po.auth, ''::text), btrim(concat_ws(' '::text, ifnull(po.infra_rank_1, ''::text), ifnull(po.infra_ep_1, ''::text), ifnull(po.infra_auth_1, ''::text)))))) 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, pm."SiteCode", btrim(concat_ws(' '::text, ifnull(po."Family", ''::text), ifnull(po."Genus", ''::text), ifnull(po."Species", ''::text), if((po.infra_ep_1 IS NULL), ifnull(po.auth, ''::text), btrim(concat_ws(' '::text, ifnull(po.infra_rank_1, ''::text), ifnull(po.infra_ep_1, ''::text), ifnull(po.infra_auth_1, ''::text))))));
|
|
183
|
|
|
184
|
|
|
185
|
--
|
|
186
|
-- Name: _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr; Type: VIEW; Schema: SALVIAS; Owner: -
|
|
187
|
--
|
|
188
|
|
|
189
|
CREATE VIEW _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr AS
|
|
190
|
SELECT DISTINCT p.project_name,
|
|
191
|
pm."SiteCode" AS plotcode,
|
|
192
|
btrim(concat_ws(' '::text, ifnull(po."Family", ''::text), ifnull(po."Genus", ''::text), ifnull(po."Species", ''::text), if((po.infra_ep_1 IS NULL), ifnull(po.auth, ''::text), btrim(concat_ws(' '::text, ifnull(po.infra_rank_1, ''::text), ifnull(po.infra_ep_1, ''::text), ifnull(po.infra_auth_1, ''::text)))))) AS taxon,
|
|
193
|
sum(po."NoInd") AS individuals
|
|
194
|
FROM ((projects p
|
|
195
|
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
|
|
196
|
JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID")))
|
|
197
|
GROUP BY p.project_name, pm."SiteCode", btrim(concat_ws(' '::text, ifnull(po."Family", ''::text), ifnull(po."Genus", ''::text), ifnull(po."Species", ''::text), if((po.infra_ep_1 IS NULL), ifnull(po.auth, ''::text), btrim(concat_ws(' '::text, ifnull(po.infra_rank_1, ''::text), ifnull(po.infra_ep_1, ''::text), ifnull(po.infra_auth_1, ''::text))))))
|
|
198
|
ORDER BY p.project_name, pm."SiteCode", btrim(concat_ws(' '::text, ifnull(po."Family", ''::text), ifnull(po."Genus", ''::text), ifnull(po."Species", ''::text), if((po.infra_ep_1 IS NULL), ifnull(po.auth, ''::text), btrim(concat_ws(' '::text, ifnull(po.infra_rank_1, ''::text), ifnull(po.infra_ep_1, ''::text), ifnull(po.infra_auth_1, ''::text))))));
|
|
199
|
|
|
200
|
|
|
201
|
--
|
|
202
|
-- Name: _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro; Type: VIEW; Schema: SALVIAS; Owner: -
|
|
203
|
--
|
|
204
|
|
|
205
|
CREATE VIEW _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro AS
|
|
206
|
SELECT DISTINCT p.project_name,
|
|
207
|
pm."SiteCode" AS plotcode,
|
|
208
|
btrim(concat_ws(' '::text, ifnull(po."Family", ''::text), ifnull(po."Genus", ''::text), ifnull(po."Species", ''::text), if((po.infra_ep_1 IS NULL), ifnull(po.auth, ''::text), btrim(concat_ws(' '::text, ifnull(po.infra_rank_1, ''::text), ifnull(po.infra_ep_1, ''::text), ifnull(po.infra_auth_1, ''::text)))))) AS taxon,
|
|
209
|
avg(po.cover_percent) AS mean_cover
|
|
210
|
FROM ((projects p
|
|
211
|
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
|
|
212
|
JOIN "plotObservations" po ON (((pm."PlotID" = po."PlotID") AND (po.cover_percent IS NOT NULL))))
|
|
213
|
GROUP BY p.project_name, pm."SiteCode", btrim(concat_ws(' '::text, ifnull(po."Family", ''::text), ifnull(po."Genus", ''::text), ifnull(po."Species", ''::text), if((po.infra_ep_1 IS NULL), ifnull(po.auth, ''::text), btrim(concat_ws(' '::text, ifnull(po.infra_rank_1, ''::text), ifnull(po.infra_ep_1, ''::text), ifnull(po.infra_auth_1, ''::text))))))
|
|
214
|
ORDER BY p.project_name, pm."SiteCode", btrim(concat_ws(' '::text, ifnull(po."Family", ''::text), ifnull(po."Genus", ''::text), ifnull(po."Species", ''::text), if((po.infra_ep_1 IS NULL), ifnull(po.auth, ''::text), btrim(concat_ws(' '::text, ifnull(po.infra_rank_1, ''::text), ifnull(po.infra_ep_1, ''::text), ifnull(po.infra_auth_1, ''::text))))));
|
|
215
|
|
|
216
|
|
|
217
|
--
|
|
218
|
-- Name: _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj; Type: VIEW; Schema: SALVIAS; Owner: -
|
|
219
|
--
|
|
220
|
|
|
221
|
CREATE VIEW _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj AS
|
|
222
|
SELECT DISTINCT p.project_name,
|
|
223
|
pm."SiteCode" AS plotcode,
|
|
224
|
btrim(concat_ws(' '::text, ifnull(po."Family", ''::text), ifnull(po."Genus", ''::text), ifnull(po."Species", ''::text), if((po.infra_ep_1 IS NULL), ifnull(po.auth, ''::text), btrim(concat_ws(' '::text, ifnull(po.infra_rank_1, ''::text), ifnull(po.infra_ep_1, ''::text), ifnull(po.infra_auth_1, ''::text)))))) AS taxon,
|
|
225
|
po.intercept_cm
|
|
226
|
FROM ((projects p
|
|
227
|
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
|
|
228
|
JOIN "plotObservations" po ON (((pm."PlotID" = po."PlotID") AND (po.intercept_cm IS NOT NULL))))
|
|
229
|
ORDER BY p.project_name, pm."SiteCode", btrim(concat_ws(' '::text, ifnull(po."Family", ''::text), ifnull(po."Genus", ''::text), ifnull(po."Species", ''::text), if((po.infra_ep_1 IS NULL), ifnull(po.auth, ''::text), btrim(concat_ws(' '::text, ifnull(po.infra_rank_1, ''::text), ifnull(po.infra_ep_1, ''::text), ifnull(po.infra_auth_1, ''::text))))));
|
|
230
|
|
|
231
|
|
|
232
|
--
|
|
233
|
-- Name: _plots_17_count_of_subplots_per_plot_for_each_project; Type: VIEW; Schema: SALVIAS; Owner: -
|
|
234
|
--
|
|
235
|
|
|
236
|
CREATE VIEW _plots_17_count_of_subplots_per_plot_for_each_project AS
|
|
237
|
SELECT p.project_name,
|
|
238
|
pm."SiteCode" AS plotcode,
|
|
239
|
count(DISTINCT po."Line") AS subplots
|
|
240
|
FROM ((projects p
|
|
241
|
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
|
|
242
|
JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID")))
|
|
243
|
GROUP BY p.project_name, pm."SiteCode"
|
|
244
|
ORDER BY p.project_name, pm."SiteCode";
|
|
245
|
|
|
246
|
|
|
247
|
--
|
|
248
|
-- Name: _plots_18_list_of_subplots_codes_for_each_plot_for_each_project; Type: VIEW; Schema: SALVIAS; Owner: -
|
|
249
|
--
|
|
250
|
|
|
251
|
CREATE VIEW _plots_18_list_of_subplots_codes_for_each_plot_for_each_project AS
|
|
252
|
SELECT DISTINCT p.project_name,
|
|
253
|
pm."SiteCode" AS plotcode,
|
|
254
|
po."Line" AS subplot
|
|
255
|
FROM ((projects p
|
|
256
|
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
|
|
257
|
JOIN "plotObservations" po ON (((pm."PlotID" = po."PlotID") AND (pm."SiteCode" IS NOT NULL))))
|
|
258
|
ORDER BY p.project_name, pm."SiteCode", po."Line";
|
|
259
|
|
|
260
|
|
|
261
|
--
|
|
262
|
-- PostgreSQL database dump complete
|
|
263
|
--
|
|
264
|
|
