| 1 |
12406
|
aaronmk
|
--
|
| 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 |
13367
|
aaronmk
|
CREATE OR REPLACE VIEW _plots_01_count_of_projects AS
|
| 19 |
12406
|
aaronmk
|
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 |
13367
|
aaronmk
|
CREATE OR REPLACE VIEW _plots_02_list_of_project_names AS
|
| 28 |
12417
|
aaronmk
|
SELECT projects.project_name
|
| 29 |
12406
|
aaronmk
|
FROM projects;
|
| 30 |
|
|
|
| 31 |
|
|
|
| 32 |
|
|
--
|
| 33 |
|
|
-- Name: _plots_03_count_of_all_plots_in_this_source; Type: VIEW; Schema: SALVIAS; Owner: -
|
| 34 |
|
|
--
|
| 35 |
|
|
|
| 36 |
13367
|
aaronmk
|
CREATE OR REPLACE VIEW _plots_03_count_of_all_plots_in_this_source AS
|
| 37 |
12508
|
aaronmk
|
SELECT count(DISTINCT (pm."SiteCode")::text) AS plots
|
| 38 |
12406
|
aaronmk
|
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 |
13367
|
aaronmk
|
CREATE OR REPLACE VIEW _plots_04_count_of_plots_in_each_project_in_this_source AS
|
| 47 |
12406
|
aaronmk
|
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 |
13367
|
aaronmk
|
CREATE OR REPLACE VIEW _plots_05_list_of_plot_codes_by_project AS
|
| 59 |
12406
|
aaronmk
|
SELECT p.project_name,
|
| 60 |
12538
|
aaronmk
|
(pm."SiteCode")::text AS plot_code
|
| 61 |
12406
|
aaronmk
|
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 |
13367
|
aaronmk
|
CREATE OR REPLACE VIEW _plots_06_list_of_plots_with_stem_measurements AS
|
| 70 |
12406
|
aaronmk
|
SELECT DISTINCT p.project_name,
|
| 71 |
12538
|
aaronmk
|
(pm."SiteCode")::text AS plot_code
|
| 72 |
12406
|
aaronmk
|
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 |
12508
|
aaronmk
|
GROUP BY p.project_name, (pm."SiteCode")::text
|
| 77 |
|
|
ORDER BY p.project_name, (pm."SiteCode")::text;
|
| 78 |
12406
|
aaronmk
|
|
| 79 |
|
|
|
| 80 |
|
|
--
|
| 81 |
12635
|
aaronmk
|
-- Name: _plots_06a_list_of_stems; Type: VIEW; Schema: SALVIAS; Owner: -
|
| 82 |
|
|
--
|
| 83 |
|
|
|
| 84 |
13367
|
aaronmk
|
CREATE OR REPLACE VIEW _plots_06a_list_of_stems AS
|
| 85 |
12635
|
aaronmk
|
SELECT p.project_name,
|
| 86 |
|
|
(pm."SiteCode")::text AS plot_code,
|
| 87 |
|
|
(s.stem_id)::text AS stem_id
|
| 88 |
|
|
FROM (((projects p
|
| 89 |
|
|
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
|
| 90 |
|
|
JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID")))
|
| 91 |
|
|
JOIN stems s ON ((po."PlotObsID" = s.plotobs_id)))
|
| 92 |
|
|
ORDER BY p.project_name, (pm."SiteCode")::text, (s.stem_id)::text;
|
| 93 |
|
|
|
| 94 |
|
|
|
| 95 |
|
|
--
|
| 96 |
12638
|
aaronmk
|
-- Name: _plots_07_list_of_plots_which_use_counts_of_indiv_per_species; Type: VIEW; Schema: SALVIAS; Owner: -
|
| 97 |
12406
|
aaronmk
|
--
|
| 98 |
|
|
|
| 99 |
13367
|
aaronmk
|
CREATE OR REPLACE VIEW _plots_07_list_of_plots_which_use_counts_of_indiv_per_species AS
|
| 100 |
12406
|
aaronmk
|
SELECT DISTINCT p.project_name,
|
| 101 |
12538
|
aaronmk
|
(pm."SiteCode")::text AS plot_code
|
| 102 |
12406
|
aaronmk
|
FROM ((projects p
|
| 103 |
|
|
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
|
| 104 |
|
|
JOIN "plotObservations" po ON (((pm."PlotID" = po."PlotID") AND (po."NoInd" > 1))))
|
| 105 |
12508
|
aaronmk
|
GROUP BY p.project_name, (pm."SiteCode")::text
|
| 106 |
|
|
ORDER BY p.project_name, (pm."SiteCode")::text;
|
| 107 |
12406
|
aaronmk
|
|
| 108 |
|
|
|
| 109 |
|
|
--
|
| 110 |
|
|
-- Name: _plots_08_list_of_plots_which_use_percent_cover; Type: VIEW; Schema: SALVIAS; Owner: -
|
| 111 |
|
|
--
|
| 112 |
|
|
|
| 113 |
13367
|
aaronmk
|
CREATE OR REPLACE VIEW _plots_08_list_of_plots_which_use_percent_cover AS
|
| 114 |
12406
|
aaronmk
|
SELECT DISTINCT p.project_name,
|
| 115 |
12538
|
aaronmk
|
(pm."SiteCode")::text AS plot_code
|
| 116 |
12406
|
aaronmk
|
FROM ((projects p
|
| 117 |
|
|
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
|
| 118 |
|
|
JOIN "plotObservations" po ON (((pm."PlotID" = po."PlotID") AND (po.cover_percent IS NOT NULL))))
|
| 119 |
12508
|
aaronmk
|
GROUP BY p.project_name, (pm."SiteCode")::text
|
| 120 |
|
|
ORDER BY p.project_name, (pm."SiteCode")::text;
|
| 121 |
12406
|
aaronmk
|
|
| 122 |
|
|
|
| 123 |
|
|
--
|
| 124 |
|
|
-- Name: _plots_09_list_of_plots_which_use_line_intercept; Type: VIEW; Schema: SALVIAS; Owner: -
|
| 125 |
|
|
--
|
| 126 |
|
|
|
| 127 |
13367
|
aaronmk
|
CREATE OR REPLACE VIEW _plots_09_list_of_plots_which_use_line_intercept AS
|
| 128 |
12406
|
aaronmk
|
SELECT DISTINCT p.project_name,
|
| 129 |
12538
|
aaronmk
|
(pm."SiteCode")::text AS plot_code
|
| 130 |
12406
|
aaronmk
|
FROM ((projects p
|
| 131 |
|
|
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
|
| 132 |
|
|
JOIN "plotObservations" po ON (((pm."PlotID" = po."PlotID") AND (po.intercept_cm IS NOT NULL))))
|
| 133 |
12508
|
aaronmk
|
GROUP BY p.project_name, (pm."SiteCode")::text
|
| 134 |
|
|
ORDER BY p.project_name, (pm."SiteCode")::text;
|
| 135 |
12406
|
aaronmk
|
|
| 136 |
|
|
|
| 137 |
|
|
--
|
| 138 |
|
|
-- Name: _plots_10_count_of_individuals_per_plot_in_each_project; Type: VIEW; Schema: SALVIAS; Owner: -
|
| 139 |
|
|
--
|
| 140 |
|
|
|
| 141 |
13367
|
aaronmk
|
CREATE OR REPLACE VIEW _plots_10_count_of_individuals_per_plot_in_each_project AS
|
| 142 |
12406
|
aaronmk
|
SELECT p.project_name,
|
| 143 |
12526
|
aaronmk
|
(pm."SiteCode")::text AS plot_code,
|
| 144 |
12406
|
aaronmk
|
sum(po."NoInd") AS individuals
|
| 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 |
12508
|
aaronmk
|
GROUP BY p.project_name, (pm."SiteCode")::text
|
| 149 |
|
|
ORDER BY p.project_name, (pm."SiteCode")::text;
|
| 150 |
12406
|
aaronmk
|
|
| 151 |
|
|
|
| 152 |
|
|
--
|
| 153 |
12407
|
aaronmk
|
-- Name: VIEW _plots_10_count_of_individuals_per_plot_in_each_project; Type: COMMENT; Schema: SALVIAS; Owner: -
|
| 154 |
|
|
--
|
| 155 |
|
|
|
| 156 |
|
|
COMMENT ON VIEW _plots_10_count_of_individuals_per_plot_in_each_project IS '
|
| 157 |
|
|
Method: Sum on NoInd (number of individuals) column.
|
| 158 |
|
|
Note 1: `individuals` should be NULL for plots which use percent cover or line-intercept methods.
|
| 159 |
|
|
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
|
| 160 |
|
|
';
|
| 161 |
|
|
|
| 162 |
|
|
|
| 163 |
|
|
--
|
| 164 |
12596
|
aaronmk
|
-- Name: _plots_10a_aggregate_observation_individual_counts; Type: VIEW; Schema: SALVIAS; Owner: -
|
| 165 |
|
|
--
|
| 166 |
|
|
|
| 167 |
13367
|
aaronmk
|
CREATE OR REPLACE VIEW _plots_10a_aggregate_observation_individual_counts AS
|
| 168 |
12596
|
aaronmk
|
SELECT p.project_name,
|
| 169 |
|
|
(pm."SiteCode")::text AS plot_code,
|
| 170 |
12601
|
aaronmk
|
(po."PlotObsID")::text AS individual_id,
|
| 171 |
12596
|
aaronmk
|
po."NoInd" AS individuals
|
| 172 |
|
|
FROM ((projects p
|
| 173 |
|
|
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
|
| 174 |
|
|
JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID")))
|
| 175 |
12601
|
aaronmk
|
ORDER BY p.project_name, (pm."SiteCode")::text, (po."PlotObsID")::text;
|
| 176 |
12596
|
aaronmk
|
|
| 177 |
|
|
|
| 178 |
|
|
--
|
| 179 |
|
|
-- Name: VIEW _plots_10a_aggregate_observation_individual_counts; Type: COMMENT; Schema: SALVIAS; Owner: -
|
| 180 |
|
|
--
|
| 181 |
|
|
|
| 182 |
|
|
COMMENT ON VIEW _plots_10a_aggregate_observation_individual_counts IS '
|
| 183 |
|
|
Note 1: `individuals` should be NULL for plots which use percent cover or line-intercept methods.
|
| 184 |
|
|
';
|
| 185 |
|
|
|
| 186 |
|
|
|
| 187 |
|
|
--
|
| 188 |
12406
|
aaronmk
|
-- Name: _plots_11_count_of_stems_per_plot_in_each_project; Type: VIEW; Schema: SALVIAS; Owner: -
|
| 189 |
|
|
--
|
| 190 |
|
|
|
| 191 |
13367
|
aaronmk
|
CREATE OR REPLACE VIEW _plots_11_count_of_stems_per_plot_in_each_project AS
|
| 192 |
12406
|
aaronmk
|
SELECT p.project_name,
|
| 193 |
12526
|
aaronmk
|
(pm."SiteCode")::text AS plot_code,
|
| 194 |
12406
|
aaronmk
|
count(DISTINCT s.stem_id) AS stems
|
| 195 |
|
|
FROM (((projects p
|
| 196 |
|
|
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
|
| 197 |
|
|
JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID")))
|
| 198 |
|
|
JOIN stems s ON ((po."PlotObsID" = s.plotobs_id)))
|
| 199 |
12508
|
aaronmk
|
GROUP BY p.project_name, (pm."SiteCode")::text
|
| 200 |
|
|
ORDER BY p.project_name, (pm."SiteCode")::text;
|
| 201 |
12406
|
aaronmk
|
|
| 202 |
|
|
|
| 203 |
|
|
--
|
| 204 |
12407
|
aaronmk
|
-- Name: VIEW _plots_11_count_of_stems_per_plot_in_each_project; Type: COMMENT; Schema: SALVIAS; Owner: -
|
| 205 |
|
|
--
|
| 206 |
|
|
|
| 207 |
|
|
COMMENT ON VIEW _plots_11_count_of_stems_per_plot_in_each_project IS '
|
| 208 |
|
|
Method: count records in stems table
|
| 209 |
|
|
';
|
| 210 |
|
|
|
| 211 |
|
|
|
| 212 |
|
|
--
|
| 213 |
12406
|
aaronmk
|
-- Name: _plots_12_count_of_verbatim_taxa_per_plot_in_each_project; Type: VIEW; Schema: SALVIAS; Owner: -
|
| 214 |
|
|
--
|
| 215 |
|
|
|
| 216 |
13367
|
aaronmk
|
CREATE OR REPLACE VIEW _plots_12_count_of_verbatim_taxa_per_plot_in_each_project AS
|
| 217 |
12406
|
aaronmk
|
SELECT a.project_name,
|
| 218 |
12526
|
aaronmk
|
a.plot_code,
|
| 219 |
12406
|
aaronmk
|
count(a.taxon) AS taxa
|
| 220 |
|
|
FROM ( SELECT DISTINCT p.project_name,
|
| 221 |
12526
|
aaronmk
|
(pm."SiteCode")::text AS plot_code,
|
| 222 |
13366
|
aaronmk
|
btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text)))))) AS taxon
|
| 223 |
12406
|
aaronmk
|
FROM ((projects p
|
| 224 |
|
|
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
|
| 225 |
|
|
JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID")))) a
|
| 226 |
12526
|
aaronmk
|
GROUP BY a.project_name, a.plot_code
|
| 227 |
|
|
ORDER BY a.project_name, a.plot_code;
|
| 228 |
12406
|
aaronmk
|
|
| 229 |
|
|
|
| 230 |
|
|
--
|
| 231 |
12407
|
aaronmk
|
-- Name: VIEW _plots_12_count_of_verbatim_taxa_per_plot_in_each_project; Type: COMMENT; Schema: SALVIAS; Owner: -
|
| 232 |
|
|
--
|
| 233 |
|
|
|
| 234 |
|
|
COMMENT ON VIEW _plots_12_count_of_verbatim_taxa_per_plot_in_each_project IS '
|
| 235 |
|
|
Note: Assumes identical concatenation of taxonomic fields to form verbatim taxon name in vegBIEN
|
| 236 |
|
|
';
|
| 237 |
|
|
|
| 238 |
|
|
|
| 239 |
|
|
--
|
| 240 |
12406
|
aaronmk
|
-- Name: _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p; Type: VIEW; Schema: SALVIAS; Owner: -
|
| 241 |
|
|
--
|
| 242 |
|
|
|
| 243 |
13367
|
aaronmk
|
CREATE OR REPLACE VIEW _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p AS
|
| 244 |
12406
|
aaronmk
|
SELECT DISTINCT p.project_name,
|
| 245 |
12526
|
aaronmk
|
(pm."SiteCode")::text AS plot_code,
|
| 246 |
13366
|
aaronmk
|
btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text)))))) AS taxon
|
| 247 |
12406
|
aaronmk
|
FROM ((projects p
|
| 248 |
|
|
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
|
| 249 |
|
|
JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID")))
|
| 250 |
13366
|
aaronmk
|
ORDER BY p.project_name, (pm."SiteCode")::text, btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text))))));
|
| 251 |
12406
|
aaronmk
|
|
| 252 |
|
|
|
| 253 |
|
|
--
|
| 254 |
12407
|
aaronmk
|
-- Name: VIEW _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p; Type: COMMENT; Schema: SALVIAS; Owner: -
|
| 255 |
|
|
--
|
| 256 |
|
|
|
| 257 |
|
|
COMMENT ON VIEW _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p IS '
|
| 258 |
|
|
Note: Assumes identical concatenation of taxonomic fields to form verbatim taxon name in vegBIEN.
|
| 259 |
|
|
';
|
| 260 |
|
|
|
| 261 |
|
|
|
| 262 |
|
|
--
|
| 263 |
12406
|
aaronmk
|
-- Name: _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr; Type: VIEW; Schema: SALVIAS; Owner: -
|
| 264 |
|
|
--
|
| 265 |
|
|
|
| 266 |
13367
|
aaronmk
|
CREATE OR REPLACE VIEW _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr AS
|
| 267 |
12406
|
aaronmk
|
SELECT DISTINCT p.project_name,
|
| 268 |
12526
|
aaronmk
|
(pm."SiteCode")::text AS plot_code,
|
| 269 |
13366
|
aaronmk
|
btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text)))))) AS taxon,
|
| 270 |
12406
|
aaronmk
|
sum(po."NoInd") AS individuals
|
| 271 |
|
|
FROM ((projects p
|
| 272 |
|
|
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
|
| 273 |
|
|
JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID")))
|
| 274 |
13366
|
aaronmk
|
GROUP BY p.project_name, (pm."SiteCode")::text, btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text))))))
|
| 275 |
|
|
ORDER BY p.project_name, (pm."SiteCode")::text, btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text))))));
|
| 276 |
12406
|
aaronmk
|
|
| 277 |
|
|
|
| 278 |
|
|
--
|
| 279 |
12407
|
aaronmk
|
-- Name: VIEW _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr; Type: COMMENT; Schema: SALVIAS; Owner: -
|
| 280 |
|
|
--
|
| 281 |
|
|
|
| 282 |
|
|
COMMENT ON VIEW _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr IS '
|
| 283 |
|
|
Note: Assumes identical concatenation of taxonomic fields to form verbatim taxon name in vegBIEN
|
| 284 |
|
|
';
|
| 285 |
|
|
|
| 286 |
|
|
|
| 287 |
|
|
--
|
| 288 |
12406
|
aaronmk
|
-- Name: _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro; Type: VIEW; Schema: SALVIAS; Owner: -
|
| 289 |
|
|
--
|
| 290 |
|
|
|
| 291 |
13367
|
aaronmk
|
CREATE OR REPLACE VIEW _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro AS
|
| 292 |
12406
|
aaronmk
|
SELECT DISTINCT p.project_name,
|
| 293 |
12526
|
aaronmk
|
(pm."SiteCode")::text AS plot_code,
|
| 294 |
13366
|
aaronmk
|
btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text)))))) AS taxon,
|
| 295 |
12603
|
aaronmk
|
sum((po.cover_percent)::double precision) AS totalpercentcover
|
| 296 |
12406
|
aaronmk
|
FROM ((projects p
|
| 297 |
|
|
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
|
| 298 |
|
|
JOIN "plotObservations" po ON (((pm."PlotID" = po."PlotID") AND (po.cover_percent IS NOT NULL))))
|
| 299 |
13366
|
aaronmk
|
GROUP BY p.project_name, (pm."SiteCode")::text, btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text))))))
|
| 300 |
|
|
ORDER BY p.project_name, (pm."SiteCode")::text, btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text))))));
|
| 301 |
12406
|
aaronmk
|
|
| 302 |
|
|
|
| 303 |
|
|
--
|
| 304 |
12407
|
aaronmk
|
-- Name: VIEW _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro; Type: COMMENT; Schema: SALVIAS; Owner: -
|
| 305 |
|
|
--
|
| 306 |
|
|
|
| 307 |
|
|
COMMENT ON VIEW _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro IS '
|
| 308 |
|
|
Note 1: Assumes identical concatenation of taxonomic fields to form verbatim taxon name in vegbien
|
| 309 |
|
|
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.
|
| 310 |
|
|
Note 3: currently, there are no SALVIAS percent cover plots in BIEN
|
| 311 |
|
|
';
|
| 312 |
|
|
|
| 313 |
|
|
|
| 314 |
|
|
--
|
| 315 |
12406
|
aaronmk
|
-- Name: _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj; Type: VIEW; Schema: SALVIAS; Owner: -
|
| 316 |
|
|
--
|
| 317 |
|
|
|
| 318 |
13367
|
aaronmk
|
CREATE OR REPLACE VIEW _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj AS
|
| 319 |
12406
|
aaronmk
|
SELECT DISTINCT p.project_name,
|
| 320 |
12526
|
aaronmk
|
(pm."SiteCode")::text AS plot_code,
|
| 321 |
13366
|
aaronmk
|
btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text)))))) AS taxon,
|
| 322 |
12406
|
aaronmk
|
po.intercept_cm
|
| 323 |
|
|
FROM ((projects p
|
| 324 |
|
|
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
|
| 325 |
|
|
JOIN "plotObservations" po ON (((pm."PlotID" = po."PlotID") AND (po.intercept_cm IS NOT NULL))))
|
| 326 |
13366
|
aaronmk
|
ORDER BY p.project_name, (pm."SiteCode")::text, btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text))))));
|
| 327 |
12406
|
aaronmk
|
|
| 328 |
|
|
|
| 329 |
|
|
--
|
| 330 |
12407
|
aaronmk
|
-- Name: VIEW _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj; Type: COMMENT; Schema: SALVIAS; Owner: -
|
| 331 |
|
|
--
|
| 332 |
|
|
|
| 333 |
|
|
COMMENT ON VIEW _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj IS '
|
| 334 |
|
|
Note 1: Assumes identical concatenation of taxonomic fields to form verbatim taxon name in vegbien
|
| 335 |
|
|
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.
|
| 336 |
|
|
';
|
| 337 |
|
|
|
| 338 |
|
|
|
| 339 |
|
|
--
|
| 340 |
12406
|
aaronmk
|
-- Name: _plots_17_count_of_subplots_per_plot_for_each_project; Type: VIEW; Schema: SALVIAS; Owner: -
|
| 341 |
|
|
--
|
| 342 |
|
|
|
| 343 |
13367
|
aaronmk
|
CREATE OR REPLACE VIEW _plots_17_count_of_subplots_per_plot_for_each_project AS
|
| 344 |
12406
|
aaronmk
|
SELECT p.project_name,
|
| 345 |
12526
|
aaronmk
|
(pm."SiteCode")::text AS plot_code,
|
| 346 |
12406
|
aaronmk
|
count(DISTINCT po."Line") AS subplots
|
| 347 |
|
|
FROM ((projects p
|
| 348 |
|
|
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
|
| 349 |
|
|
JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID")))
|
| 350 |
12508
|
aaronmk
|
GROUP BY p.project_name, (pm."SiteCode")::text
|
| 351 |
|
|
ORDER BY p.project_name, (pm."SiteCode")::text;
|
| 352 |
12406
|
aaronmk
|
|
| 353 |
|
|
|
| 354 |
|
|
--
|
| 355 |
|
|
-- Name: _plots_18_list_of_subplots_codes_for_each_plot_for_each_project; Type: VIEW; Schema: SALVIAS; Owner: -
|
| 356 |
|
|
--
|
| 357 |
|
|
|
| 358 |
13367
|
aaronmk
|
CREATE OR REPLACE VIEW _plots_18_list_of_subplots_codes_for_each_plot_for_each_project AS
|
| 359 |
12406
|
aaronmk
|
SELECT DISTINCT p.project_name,
|
| 360 |
12526
|
aaronmk
|
(pm."SiteCode")::text AS plot_code,
|
| 361 |
12605
|
aaronmk
|
po."Line" AS subplot_code
|
| 362 |
12406
|
aaronmk
|
FROM ((projects p
|
| 363 |
|
|
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
|
| 364 |
12508
|
aaronmk
|
JOIN "plotObservations" po ON (((pm."PlotID" = po."PlotID") AND ((pm."SiteCode")::text IS NOT NULL))))
|
| 365 |
|
|
ORDER BY p.project_name, (pm."SiteCode")::text, po."Line";
|
| 366 |
12406
|
aaronmk
|
|
| 367 |
|
|
|
| 368 |
|
|
--
|
| 369 |
12407
|
aaronmk
|
-- Name: VIEW _plots_18_list_of_subplots_codes_for_each_plot_for_each_project; Type: COMMENT; Schema: SALVIAS; Owner: -
|
| 370 |
|
|
--
|
| 371 |
|
|
|
| 372 |
|
|
COMMENT ON VIEW _plots_18_list_of_subplots_codes_for_each_plot_for_each_project IS '
|
| 373 |
|
|
Note: includes plots with and without subplots
|
| 374 |
|
|
';
|
| 375 |
|
|
|
| 376 |
|
|
|
| 377 |
|
|
--
|
| 378 |
12679
|
aaronmk
|
-- Name: _plots_19_count_of_censuses_per_plot_in_each_project; Type: VIEW; Schema: SALVIAS; Owner: -
|
| 379 |
|
|
--
|
| 380 |
|
|
|
| 381 |
13367
|
aaronmk
|
CREATE OR REPLACE VIEW _plots_19_count_of_censuses_per_plot_in_each_project AS
|
| 382 |
12679
|
aaronmk
|
SELECT p.project_name,
|
| 383 |
|
|
(pm."SiteCode")::text AS plot_code,
|
| 384 |
|
|
count(DISTINCT ARRAY[po.census_date]) AS inventories
|
| 385 |
|
|
FROM ((projects p
|
| 386 |
|
|
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
|
| 387 |
|
|
LEFT JOIN "plotObservations" po ON (((pm."PlotID" = po."PlotID") AND ((pm."SiteCode")::text IS NOT NULL))))
|
| 388 |
|
|
GROUP BY p.project_name, (pm."SiteCode")::text
|
| 389 |
|
|
ORDER BY p.project_name, (pm."SiteCode")::text;
|
| 390 |
|
|
|
| 391 |
|
|
|
| 392 |
|
|
--
|
| 393 |
12406
|
aaronmk
|
-- PostgreSQL database dump complete
|
| 394 |
|
|
--
|
