Revision 12526
Added by Aaron Marcuse-Kubitza almost 11 years ago
| trunk/inputs/SALVIAS/validations.sql | ||
|---|---|---|
| 125 | 125 |
|
| 126 | 126 |
CREATE VIEW _plots_10_count_of_individuals_per_plot_in_each_project AS |
| 127 | 127 |
SELECT p.project_name, |
| 128 |
(pm."SiteCode")::text AS plotcode, |
|
| 128 |
(pm."SiteCode")::text AS plot_code,
|
|
| 129 | 129 |
sum(po."NoInd") AS individuals |
| 130 | 130 |
FROM ((projects p |
| 131 | 131 |
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id))) |
| ... | ... | |
| 151 | 151 |
|
| 152 | 152 |
CREATE VIEW _plots_11_count_of_stems_per_plot_in_each_project AS |
| 153 | 153 |
SELECT p.project_name, |
| 154 |
(pm."SiteCode")::text AS plotcode, |
|
| 154 |
(pm."SiteCode")::text AS plot_code,
|
|
| 155 | 155 |
count(DISTINCT s.stem_id) AS stems |
| 156 | 156 |
FROM (((projects p |
| 157 | 157 |
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id))) |
| ... | ... | |
| 176 | 176 |
|
| 177 | 177 |
CREATE VIEW _plots_12_count_of_verbatim_taxa_per_plot_in_each_project AS |
| 178 | 178 |
SELECT a.project_name, |
| 179 |
a.plotcode, |
|
| 179 |
a.plot_code,
|
|
| 180 | 180 |
count(a.taxon) AS taxa |
| 181 | 181 |
FROM ( SELECT DISTINCT p.project_name, |
| 182 |
(pm."SiteCode")::text AS plotcode, |
|
| 182 |
(pm."SiteCode")::text AS plot_code,
|
|
| 183 | 183 |
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
|
| 184 | 184 |
FROM ((projects p |
| 185 | 185 |
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id))) |
| 186 | 186 |
JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID")))) a |
| 187 |
GROUP BY a.project_name, a.plotcode |
|
| 188 |
ORDER BY a.project_name, a.plotcode; |
|
| 187 |
GROUP BY a.project_name, a.plot_code
|
|
| 188 |
ORDER BY a.project_name, a.plot_code;
|
|
| 189 | 189 |
|
| 190 | 190 |
|
| 191 | 191 |
-- |
| ... | ... | |
| 203 | 203 |
|
| 204 | 204 |
CREATE VIEW _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p AS |
| 205 | 205 |
SELECT DISTINCT p.project_name, |
| 206 |
(pm."SiteCode")::text AS plotcode, |
|
| 206 |
(pm."SiteCode")::text AS plot_code,
|
|
| 207 | 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 | 208 |
FROM ((projects p |
| 209 | 209 |
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id))) |
| ... | ... | |
| 226 | 226 |
|
| 227 | 227 |
CREATE VIEW _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr AS |
| 228 | 228 |
SELECT DISTINCT p.project_name, |
| 229 |
(pm."SiteCode")::text AS plotcode, |
|
| 229 |
(pm."SiteCode")::text AS plot_code,
|
|
| 230 | 230 |
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,
|
| 231 | 231 |
sum(po."NoInd") AS individuals |
| 232 | 232 |
FROM ((projects p |
| ... | ... | |
| 251 | 251 |
|
| 252 | 252 |
CREATE VIEW _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro AS |
| 253 | 253 |
SELECT DISTINCT p.project_name, |
| 254 |
(pm."SiteCode")::text AS plotcode, |
|
| 254 |
(pm."SiteCode")::text AS plot_code,
|
|
| 255 | 255 |
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,
|
| 256 | 256 |
avg(po.cover_percent) AS mean_cover |
| 257 | 257 |
FROM ((projects p |
| ... | ... | |
| 278 | 278 |
|
| 279 | 279 |
CREATE VIEW _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj AS |
| 280 | 280 |
SELECT DISTINCT p.project_name, |
| 281 |
(pm."SiteCode")::text AS plotcode, |
|
| 281 |
(pm."SiteCode")::text AS plot_code,
|
|
| 282 | 282 |
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,
|
| 283 | 283 |
po.intercept_cm |
| 284 | 284 |
FROM ((projects p |
| ... | ... | |
| 303 | 303 |
|
| 304 | 304 |
CREATE VIEW _plots_17_count_of_subplots_per_plot_for_each_project AS |
| 305 | 305 |
SELECT p.project_name, |
| 306 |
(pm."SiteCode")::text AS plotcode, |
|
| 306 |
(pm."SiteCode")::text AS plot_code,
|
|
| 307 | 307 |
count(DISTINCT po."Line") AS subplots |
| 308 | 308 |
FROM ((projects p |
| 309 | 309 |
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id))) |
| ... | ... | |
| 318 | 318 |
|
| 319 | 319 |
CREATE VIEW _plots_18_list_of_subplots_codes_for_each_plot_for_each_project AS |
| 320 | 320 |
SELECT DISTINCT p.project_name, |
| 321 |
(pm."SiteCode")::text AS plotcode, |
|
| 321 |
(pm."SiteCode")::text AS plot_code,
|
|
| 322 | 322 |
po."Line" AS subplot |
| 323 | 323 |
FROM ((projects p |
| 324 | 324 |
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id))) |
Also available in: Unified diff
inputs/SALVIAS/validations.sql: use plot_code instead of plotcode for easier readability