Revision 12508
Added by Aaron Marcuse-Kubitza almost 11 years ago
| trunk/inputs/SALVIAS/validations.sql | ||
|---|---|---|
| 34 | 34 |
-- |
| 35 | 35 |
|
| 36 | 36 |
CREATE VIEW _plots_03_count_of_all_plots_in_this_source AS |
| 37 |
SELECT count(DISTINCT pm."SiteCode") AS plots
|
|
| 37 |
SELECT count(DISTINCT (pm."SiteCode")::text) AS plots
|
|
| 38 | 38 |
FROM (projects p |
| 39 | 39 |
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id))); |
| 40 | 40 |
|
| ... | ... | |
| 57 | 57 |
|
| 58 | 58 |
CREATE VIEW _plots_05_list_of_plot_codes_by_project AS |
| 59 | 59 |
SELECT p.project_name, |
| 60 |
pm."SiteCode"
|
|
| 60 |
(pm."SiteCode")::text AS "SiteCode"
|
|
| 61 | 61 |
FROM (projects p |
| 62 | 62 |
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id))); |
| 63 | 63 |
|
| ... | ... | |
| 68 | 68 |
|
| 69 | 69 |
CREATE VIEW _plots_06_list_of_plots_with_stem_measurements AS |
| 70 | 70 |
SELECT DISTINCT p.project_name, |
| 71 |
pm."SiteCode"
|
|
| 71 |
(pm."SiteCode")::text AS "SiteCode"
|
|
| 72 | 72 |
FROM (((projects p |
| 73 | 73 |
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id))) |
| 74 | 74 |
JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID"))) |
| 75 | 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";
|
|
| 76 |
GROUP BY p.project_name, (pm."SiteCode")::text
|
|
| 77 |
ORDER BY p.project_name, (pm."SiteCode")::text;
|
|
| 78 | 78 |
|
| 79 | 79 |
|
| 80 | 80 |
-- |
| ... | ... | |
| 83 | 83 |
|
| 84 | 84 |
CREATE VIEW _plots_07_list_of_plots_with_counts_of_individuals_per_species AS |
| 85 | 85 |
SELECT DISTINCT p.project_name, |
| 86 |
pm."SiteCode"
|
|
| 86 |
(pm."SiteCode")::text AS "SiteCode"
|
|
| 87 | 87 |
FROM ((projects p |
| 88 | 88 |
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id))) |
| 89 | 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";
|
|
| 90 |
GROUP BY p.project_name, (pm."SiteCode")::text
|
|
| 91 |
ORDER BY p.project_name, (pm."SiteCode")::text;
|
|
| 92 | 92 |
|
| 93 | 93 |
|
| 94 | 94 |
-- |
| ... | ... | |
| 97 | 97 |
|
| 98 | 98 |
CREATE VIEW _plots_08_list_of_plots_which_use_percent_cover AS |
| 99 | 99 |
SELECT DISTINCT p.project_name, |
| 100 |
pm."SiteCode"
|
|
| 100 |
(pm."SiteCode")::text AS "SiteCode"
|
|
| 101 | 101 |
FROM ((projects p |
| 102 | 102 |
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id))) |
| 103 | 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";
|
|
| 104 |
GROUP BY p.project_name, (pm."SiteCode")::text
|
|
| 105 |
ORDER BY p.project_name, (pm."SiteCode")::text;
|
|
| 106 | 106 |
|
| 107 | 107 |
|
| 108 | 108 |
-- |
| ... | ... | |
| 111 | 111 |
|
| 112 | 112 |
CREATE VIEW _plots_09_list_of_plots_which_use_line_intercept AS |
| 113 | 113 |
SELECT DISTINCT p.project_name, |
| 114 |
pm."SiteCode"
|
|
| 114 |
(pm."SiteCode")::text AS "SiteCode"
|
|
| 115 | 115 |
FROM ((projects p |
| 116 | 116 |
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id))) |
| 117 | 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";
|
|
| 118 |
GROUP BY p.project_name, (pm."SiteCode")::text
|
|
| 119 |
ORDER BY p.project_name, (pm."SiteCode")::text;
|
|
| 120 | 120 |
|
| 121 | 121 |
|
| 122 | 122 |
-- |
| ... | ... | |
| 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" AS plotcode,
|
|
| 128 |
(pm."SiteCode")::text AS plotcode,
|
|
| 129 | 129 |
sum(po."NoInd") AS individuals |
| 130 | 130 |
FROM ((projects p |
| 131 | 131 |
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id))) |
| 132 | 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";
|
|
| 133 |
GROUP BY p.project_name, (pm."SiteCode")::text
|
|
| 134 |
ORDER BY p.project_name, (pm."SiteCode")::text;
|
|
| 135 | 135 |
|
| 136 | 136 |
|
| 137 | 137 |
-- |
| ... | ... | |
| 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" AS plotcode,
|
|
| 154 |
(pm."SiteCode")::text AS plotcode,
|
|
| 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))) |
| 158 | 158 |
JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID"))) |
| 159 | 159 |
JOIN stems s ON ((po."PlotObsID" = s.plotobs_id))) |
| 160 |
GROUP BY p.project_name, pm."SiteCode"
|
|
| 161 |
ORDER BY p.project_name, pm."SiteCode";
|
|
| 160 |
GROUP BY p.project_name, (pm."SiteCode")::text
|
|
| 161 |
ORDER BY p.project_name, (pm."SiteCode")::text;
|
|
| 162 | 162 |
|
| 163 | 163 |
|
| 164 | 164 |
-- |
| ... | ... | |
| 179 | 179 |
a.plotcode, |
| 180 | 180 |
count(a.taxon) AS taxa |
| 181 | 181 |
FROM ( SELECT DISTINCT p.project_name, |
| 182 |
pm."SiteCode" AS plotcode,
|
|
| 182 |
(pm."SiteCode")::text AS plotcode,
|
|
| 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))) |
| ... | ... | |
| 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" AS plotcode,
|
|
| 206 |
(pm."SiteCode")::text AS plotcode,
|
|
| 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))) |
| 210 | 210 |
JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID"))) |
| 211 |
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))))));
|
|
| 211 |
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))))));
|
|
| 212 | 212 |
|
| 213 | 213 |
|
| 214 | 214 |
-- |
| ... | ... | |
| 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" AS plotcode,
|
|
| 229 |
(pm."SiteCode")::text AS plotcode,
|
|
| 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 |
| 233 | 233 |
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id))) |
| 234 | 234 |
JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID"))) |
| 235 |
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))))))
|
|
| 236 |
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))))));
|
|
| 235 |
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))))))
|
|
| 236 |
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))))));
|
|
| 237 | 237 |
|
| 238 | 238 |
|
| 239 | 239 |
-- |
| ... | ... | |
| 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" AS plotcode,
|
|
| 254 |
(pm."SiteCode")::text AS plotcode,
|
|
| 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 |
| 258 | 258 |
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id))) |
| 259 | 259 |
JOIN "plotObservations" po ON (((pm."PlotID" = po."PlotID") AND (po.cover_percent IS NOT NULL)))) |
| 260 |
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))))))
|
|
| 261 |
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))))));
|
|
| 260 |
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))))))
|
|
| 261 |
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))))));
|
|
| 262 | 262 |
|
| 263 | 263 |
|
| 264 | 264 |
-- |
| ... | ... | |
| 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" AS plotcode,
|
|
| 281 |
(pm."SiteCode")::text AS plotcode,
|
|
| 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 |
| 285 | 285 |
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id))) |
| 286 | 286 |
JOIN "plotObservations" po ON (((pm."PlotID" = po."PlotID") AND (po.intercept_cm IS NOT NULL)))) |
| 287 |
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))))));
|
|
| 287 |
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))))));
|
|
| 288 | 288 |
|
| 289 | 289 |
|
| 290 | 290 |
-- |
| ... | ... | |
| 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" AS plotcode,
|
|
| 306 |
(pm."SiteCode")::text AS plotcode,
|
|
| 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))) |
| 310 | 310 |
JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID"))) |
| 311 |
GROUP BY p.project_name, pm."SiteCode"
|
|
| 312 |
ORDER BY p.project_name, pm."SiteCode";
|
|
| 311 |
GROUP BY p.project_name, (pm."SiteCode")::text
|
|
| 312 |
ORDER BY p.project_name, (pm."SiteCode")::text;
|
|
| 313 | 313 |
|
| 314 | 314 |
|
| 315 | 315 |
-- |
| ... | ... | |
| 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" AS plotcode,
|
|
| 321 |
(pm."SiteCode")::text AS plotcode,
|
|
| 322 | 322 |
po."Line" AS subplot |
| 323 | 323 |
FROM ((projects p |
| 324 | 324 |
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id))) |
| 325 |
JOIN "plotObservations" po ON (((pm."PlotID" = po."PlotID") AND (pm."SiteCode" IS NOT NULL))))
|
|
| 326 |
ORDER BY p.project_name, pm."SiteCode", po."Line";
|
|
| 325 |
JOIN "plotObservations" po ON (((pm."PlotID" = po."PlotID") AND ((pm."SiteCode")::text IS NOT NULL))))
|
|
| 326 |
ORDER BY p.project_name, (pm."SiteCode")::text, po."Line";
|
|
| 327 | 327 |
|
| 328 | 328 |
|
| 329 | 329 |
-- |
Also available in: Unified diff
fix: inputs/SALVIAS/validations.sql: plotMetadata.SiteCode: need to match types with the output query column