Project

General

Profile

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
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")::text) 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")::text AS plot_code
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")::text AS plot_code
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")::text
77
  ORDER BY p.project_name, (pm."SiteCode")::text;
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")::text AS plot_code
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")::text
91
  ORDER BY p.project_name, (pm."SiteCode")::text;
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")::text AS plot_code
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")::text
105
  ORDER BY p.project_name, (pm."SiteCode")::text;
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")::text AS plot_code
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")::text
119
  ORDER BY p.project_name, (pm."SiteCode")::text;
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")::text AS plot_code, 
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")::text
134
  ORDER BY p.project_name, (pm."SiteCode")::text;
135

    
136

    
137
--
138
-- Name: VIEW _plots_10_count_of_individuals_per_plot_in_each_project; Type: COMMENT; Schema: SALVIAS; Owner: -
139
--
140

    
141
COMMENT ON VIEW _plots_10_count_of_individuals_per_plot_in_each_project IS '
142
Method: Sum on NoInd (number of individuals) column.
143
Note 1: `individuals` should be NULL for plots which use percent cover or line-intercept methods.
144
Note 2: Do not count records in plotObservations table, as this will give incorrect total for plots such as Gentry plot, which count individuals per species per subplot
145
';
146

    
147

    
148
--
149
-- Name: _plots_10a_aggregate_observation_individual_counts; Type: VIEW; Schema: SALVIAS; Owner: -
150
--
151

    
152
CREATE VIEW _plots_10a_aggregate_observation_individual_counts AS
153
 SELECT p.project_name, 
154
    (pm."SiteCode")::text AS plot_code, 
155
    (po."PlotObsID")::text AS individual_id, 
156
    po."NoInd" AS individuals
157
   FROM ((projects p
158
   JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
159
   JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID")))
160
  ORDER BY p.project_name, (pm."SiteCode")::text, (po."PlotObsID")::text;
161

    
162

    
163
--
164
-- Name: VIEW _plots_10a_aggregate_observation_individual_counts; Type: COMMENT; Schema: SALVIAS; Owner: -
165
--
166

    
167
COMMENT ON VIEW _plots_10a_aggregate_observation_individual_counts IS '
168
Note 1: `individuals` should be NULL for plots which use percent cover or line-intercept methods.
169
';
170

    
171

    
172
--
173
-- Name: _plots_11_count_of_stems_per_plot_in_each_project; Type: VIEW; Schema: SALVIAS; Owner: -
174
--
175

    
176
CREATE VIEW _plots_11_count_of_stems_per_plot_in_each_project AS
177
 SELECT p.project_name, 
178
    (pm."SiteCode")::text AS plot_code, 
179
    count(DISTINCT s.stem_id) AS stems
180
   FROM (((projects p
181
   JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
182
   JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID")))
183
   JOIN stems s ON ((po."PlotObsID" = s.plotobs_id)))
184
  GROUP BY p.project_name, (pm."SiteCode")::text
185
  ORDER BY p.project_name, (pm."SiteCode")::text;
186

    
187

    
188
--
189
-- Name: VIEW _plots_11_count_of_stems_per_plot_in_each_project; Type: COMMENT; Schema: SALVIAS; Owner: -
190
--
191

    
192
COMMENT ON VIEW _plots_11_count_of_stems_per_plot_in_each_project IS '
193
Method: count records in stems table
194
';
195

    
196

    
197
--
198
-- Name: _plots_12_count_of_verbatim_taxa_per_plot_in_each_project; Type: VIEW; Schema: SALVIAS; Owner: -
199
--
200

    
201
CREATE VIEW _plots_12_count_of_verbatim_taxa_per_plot_in_each_project AS
202
 SELECT a.project_name, 
203
    a.plot_code, 
204
    count(a.taxon) AS taxa
205
   FROM ( SELECT DISTINCT p.project_name, 
206
            (pm."SiteCode")::text AS plot_code, 
207
            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
208
           FROM ((projects p
209
      JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
210
   JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID")))) a
211
  GROUP BY a.project_name, a.plot_code
212
  ORDER BY a.project_name, a.plot_code;
213

    
214

    
215
--
216
-- Name: VIEW _plots_12_count_of_verbatim_taxa_per_plot_in_each_project; Type: COMMENT; Schema: SALVIAS; Owner: -
217
--
218

    
219
COMMENT ON VIEW _plots_12_count_of_verbatim_taxa_per_plot_in_each_project IS '
220
Note: Assumes identical concatenation of taxonomic fields to form verbatim taxon name in vegBIEN
221
';
222

    
223

    
224
--
225
-- Name: _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p; Type: VIEW; Schema: SALVIAS; Owner: -
226
--
227

    
228
CREATE VIEW _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p AS
229
 SELECT DISTINCT p.project_name, 
230
    (pm."SiteCode")::text AS plot_code, 
231
    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
232
   FROM ((projects p
233
   JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
234
   JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID")))
235
  ORDER BY p.project_name, (pm."SiteCode")::text, 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))))));
236

    
237

    
238
--
239
-- Name: VIEW _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p; Type: COMMENT; Schema: SALVIAS; Owner: -
240
--
241

    
242
COMMENT ON VIEW _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p IS '
243
Note: Assumes identical concatenation of taxonomic fields to form verbatim taxon name in vegBIEN.
244
';
245

    
246

    
247
--
248
-- Name: _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr; Type: VIEW; Schema: SALVIAS; Owner: -
249
--
250

    
251
CREATE VIEW _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr AS
252
 SELECT DISTINCT p.project_name, 
253
    (pm."SiteCode")::text AS plot_code, 
254
    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, 
255
    sum(po."NoInd") AS individuals
256
   FROM ((projects p
257
   JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
258
   JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID")))
259
  GROUP BY p.project_name, (pm."SiteCode")::text, 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))))))
260
  ORDER BY p.project_name, (pm."SiteCode")::text, 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))))));
261

    
262

    
263
--
264
-- Name: VIEW _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr; Type: COMMENT; Schema: SALVIAS; Owner: -
265
--
266

    
267
COMMENT ON VIEW _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr IS '
268
Note: Assumes identical concatenation of taxonomic fields to form verbatim taxon name in vegBIEN
269
';
270

    
271

    
272
--
273
-- Name: _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro; Type: VIEW; Schema: SALVIAS; Owner: -
274
--
275

    
276
CREATE VIEW _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro AS
277
 SELECT DISTINCT p.project_name, 
278
    (pm."SiteCode")::text AS plot_code, 
279
    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, 
280
    sum(po.cover_percent) AS totalpercentcover
281
   FROM ((projects p
282
   JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
283
   JOIN "plotObservations" po ON (((pm."PlotID" = po."PlotID") AND (po.cover_percent IS NOT NULL))))
284
  GROUP BY p.project_name, (pm."SiteCode")::text, 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))))))
285
  ORDER BY p.project_name, (pm."SiteCode")::text, 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))))));
286

    
287

    
288
--
289
-- Name: VIEW _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro; Type: COMMENT; Schema: SALVIAS; Owner: -
290
--
291

    
292
COMMENT ON VIEW _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro IS '
293
Note 1: Assumes identical concatenation of taxonomic fields to form verbatim taxon name in vegbien
294
Note 2: Grouping mean cover for entire plot allows inclusion of plots with subplots (separate cover measure for each species in each subplot),  plots with strata (separate cover measures for each species in each stratum), and plots without subplots or strata (one cover measure per species per plot) in same query.
295
Note 3: currently, there are no SALVIAS percent cover plots in BIEN
296
';
297

    
298

    
299
--
300
-- Name: _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj; Type: VIEW; Schema: SALVIAS; Owner: -
301
--
302

    
303
CREATE VIEW _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj AS
304
 SELECT DISTINCT p.project_name, 
305
    (pm."SiteCode")::text AS plot_code, 
306
    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, 
307
    po.intercept_cm
308
   FROM ((projects p
309
   JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
310
   JOIN "plotObservations" po ON (((pm."PlotID" = po."PlotID") AND (po.intercept_cm IS NOT NULL))))
311
  ORDER BY p.project_name, (pm."SiteCode")::text, 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))))));
312

    
313

    
314
--
315
-- Name: VIEW _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj; Type: COMMENT; Schema: SALVIAS; Owner: -
316
--
317

    
318
COMMENT ON VIEW _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj IS '
319
Note 1: Assumes identical concatenation of taxonomic fields to form verbatim taxon name in vegbien
320
Note 2: Grouping mean cover for entire plot combines plots with subplots (separate cover measure for each species in each subplot) with plots with strata (separate cover measures for each species in each stratum) with plots without subplots or strata (one cover measure per species per plot.
321
';
322

    
323

    
324
--
325
-- Name: _plots_17_count_of_subplots_per_plot_for_each_project; Type: VIEW; Schema: SALVIAS; Owner: -
326
--
327

    
328
CREATE VIEW _plots_17_count_of_subplots_per_plot_for_each_project AS
329
 SELECT p.project_name, 
330
    (pm."SiteCode")::text AS plot_code, 
331
    count(DISTINCT po."Line") AS subplots
332
   FROM ((projects p
333
   JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
334
   JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID")))
335
  GROUP BY p.project_name, (pm."SiteCode")::text
336
  ORDER BY p.project_name, (pm."SiteCode")::text;
337

    
338

    
339
--
340
-- Name: _plots_18_list_of_subplots_codes_for_each_plot_for_each_project; Type: VIEW; Schema: SALVIAS; Owner: -
341
--
342

    
343
CREATE VIEW _plots_18_list_of_subplots_codes_for_each_plot_for_each_project AS
344
 SELECT DISTINCT p.project_name, 
345
    (pm."SiteCode")::text AS plot_code, 
346
    po."Line" AS subplot
347
   FROM ((projects p
348
   JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
349
   JOIN "plotObservations" po ON (((pm."PlotID" = po."PlotID") AND ((pm."SiteCode")::text IS NOT NULL))))
350
  ORDER BY p.project_name, (pm."SiteCode")::text, po."Line";
351

    
352

    
353
--
354
-- Name: VIEW _plots_18_list_of_subplots_codes_for_each_plot_for_each_project; Type: COMMENT; Schema: SALVIAS; Owner: -
355
--
356

    
357
COMMENT ON VIEW _plots_18_list_of_subplots_codes_for_each_plot_for_each_project IS '
358
Note: includes plots with and without subplots
359
';
360

    
361

    
362
--
363
-- PostgreSQL database dump complete
364
--
365

    
(13-13/14)