-- -- PostgreSQL database dump -- SET statement_timeout = 0; SET lock_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = on; SET check_function_bodies = false; SET client_min_messages = warning; SET search_path = "SALVIAS", pg_catalog; -- -- Name: _plots_01_count_of_projects; Type: VIEW; Schema: SALVIAS; Owner: - -- CREATE VIEW _plots_01_count_of_projects AS SELECT count(*) AS projects FROM projects; -- -- Name: _plots_02_list_of_project_names; Type: VIEW; Schema: SALVIAS; Owner: - -- CREATE VIEW _plots_02_list_of_project_names AS SELECT projects.project_name FROM projects; -- -- Name: _plots_03_count_of_all_plots_in_this_source; Type: VIEW; Schema: SALVIAS; Owner: - -- CREATE VIEW _plots_03_count_of_all_plots_in_this_source AS SELECT count(DISTINCT (pm."SiteCode")::text) AS plots FROM (projects p JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id))); -- -- Name: _plots_04_count_of_plots_in_each_project_in_this_source; Type: VIEW; Schema: SALVIAS; Owner: - -- CREATE VIEW _plots_04_count_of_plots_in_each_project_in_this_source AS SELECT p.project_name, count(*) AS plots FROM (projects p JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id))) GROUP BY p.project_name; -- -- Name: _plots_05_list_of_plot_codes_by_project; Type: VIEW; Schema: SALVIAS; Owner: - -- CREATE VIEW _plots_05_list_of_plot_codes_by_project AS SELECT p.project_name, (pm."SiteCode")::text AS "SiteCode" FROM (projects p JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id))); -- -- Name: _plots_06_list_of_plots_with_stem_measurements; Type: VIEW; Schema: SALVIAS; Owner: - -- CREATE VIEW _plots_06_list_of_plots_with_stem_measurements AS SELECT DISTINCT p.project_name, (pm."SiteCode")::text AS "SiteCode" FROM (((projects p JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id))) JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID"))) JOIN stems s ON ((po."PlotObsID" = s.plotobs_id))) GROUP BY p.project_name, (pm."SiteCode")::text ORDER BY p.project_name, (pm."SiteCode")::text; -- -- Name: _plots_07_list_of_plots_with_counts_of_individuals_per_species; Type: VIEW; Schema: SALVIAS; Owner: - -- CREATE VIEW _plots_07_list_of_plots_with_counts_of_individuals_per_species AS SELECT DISTINCT p.project_name, (pm."SiteCode")::text AS "SiteCode" FROM ((projects p JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id))) JOIN "plotObservations" po ON (((pm."PlotID" = po."PlotID") AND (po."NoInd" > 1)))) GROUP BY p.project_name, (pm."SiteCode")::text ORDER BY p.project_name, (pm."SiteCode")::text; -- -- Name: _plots_08_list_of_plots_which_use_percent_cover; Type: VIEW; Schema: SALVIAS; Owner: - -- CREATE VIEW _plots_08_list_of_plots_which_use_percent_cover AS SELECT DISTINCT p.project_name, (pm."SiteCode")::text AS "SiteCode" FROM ((projects p JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id))) JOIN "plotObservations" po ON (((pm."PlotID" = po."PlotID") AND (po.cover_percent IS NOT NULL)))) GROUP BY p.project_name, (pm."SiteCode")::text ORDER BY p.project_name, (pm."SiteCode")::text; -- -- Name: _plots_09_list_of_plots_which_use_line_intercept; Type: VIEW; Schema: SALVIAS; Owner: - -- CREATE VIEW _plots_09_list_of_plots_which_use_line_intercept AS SELECT DISTINCT p.project_name, (pm."SiteCode")::text AS "SiteCode" FROM ((projects p JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id))) JOIN "plotObservations" po ON (((pm."PlotID" = po."PlotID") AND (po.intercept_cm IS NOT NULL)))) GROUP BY p.project_name, (pm."SiteCode")::text ORDER BY p.project_name, (pm."SiteCode")::text; -- -- Name: _plots_10_count_of_individuals_per_plot_in_each_project; Type: VIEW; Schema: SALVIAS; Owner: - -- CREATE VIEW _plots_10_count_of_individuals_per_plot_in_each_project AS SELECT p.project_name, (pm."SiteCode")::text AS plot_code, sum(po."NoInd") AS individuals FROM ((projects p JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id))) JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID"))) GROUP BY p.project_name, (pm."SiteCode")::text ORDER BY p.project_name, (pm."SiteCode")::text; -- -- Name: VIEW _plots_10_count_of_individuals_per_plot_in_each_project; Type: COMMENT; Schema: SALVIAS; Owner: - -- COMMENT ON VIEW _plots_10_count_of_individuals_per_plot_in_each_project IS ' Method: Sum on NoInd (number of individuals) column. Note 1: `individuals` should be NULL for plots which use percent cover or line-intercept methods. 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 '; -- -- Name: _plots_11_count_of_stems_per_plot_in_each_project; Type: VIEW; Schema: SALVIAS; Owner: - -- CREATE VIEW _plots_11_count_of_stems_per_plot_in_each_project AS SELECT p.project_name, (pm."SiteCode")::text AS plot_code, count(DISTINCT s.stem_id) AS stems FROM (((projects p JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id))) JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID"))) JOIN stems s ON ((po."PlotObsID" = s.plotobs_id))) GROUP BY p.project_name, (pm."SiteCode")::text ORDER BY p.project_name, (pm."SiteCode")::text; -- -- Name: VIEW _plots_11_count_of_stems_per_plot_in_each_project; Type: COMMENT; Schema: SALVIAS; Owner: - -- COMMENT ON VIEW _plots_11_count_of_stems_per_plot_in_each_project IS ' Method: count records in stems table '; -- -- Name: _plots_12_count_of_verbatim_taxa_per_plot_in_each_project; Type: VIEW; Schema: SALVIAS; Owner: - -- CREATE VIEW _plots_12_count_of_verbatim_taxa_per_plot_in_each_project AS SELECT a.project_name, a.plot_code, count(a.taxon) AS taxa FROM ( SELECT DISTINCT p.project_name, (pm."SiteCode")::text AS plot_code, 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 FROM ((projects p JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id))) JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID")))) a GROUP BY a.project_name, a.plot_code ORDER BY a.project_name, a.plot_code; -- -- Name: VIEW _plots_12_count_of_verbatim_taxa_per_plot_in_each_project; Type: COMMENT; Schema: SALVIAS; Owner: - -- COMMENT ON VIEW _plots_12_count_of_verbatim_taxa_per_plot_in_each_project IS ' Note: Assumes identical concatenation of taxonomic fields to form verbatim taxon name in vegBIEN '; -- -- Name: _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p; Type: VIEW; Schema: SALVIAS; Owner: - -- CREATE VIEW _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p AS SELECT DISTINCT p.project_name, (pm."SiteCode")::text AS plot_code, 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 FROM ((projects p JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id))) JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID"))) 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)))))); -- -- Name: VIEW _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p; Type: COMMENT; Schema: SALVIAS; Owner: - -- COMMENT ON VIEW _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p IS ' Note: Assumes identical concatenation of taxonomic fields to form verbatim taxon name in vegBIEN. '; -- -- Name: _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr; Type: VIEW; Schema: SALVIAS; Owner: - -- CREATE VIEW _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr AS SELECT DISTINCT p.project_name, (pm."SiteCode")::text AS plot_code, 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, sum(po."NoInd") AS individuals FROM ((projects p JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id))) JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID"))) 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)))))) 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)))))); -- -- Name: VIEW _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr; Type: COMMENT; Schema: SALVIAS; Owner: - -- COMMENT ON VIEW _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr IS ' Note: Assumes identical concatenation of taxonomic fields to form verbatim taxon name in vegBIEN '; -- -- Name: _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro; Type: VIEW; Schema: SALVIAS; Owner: - -- CREATE VIEW _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro AS SELECT DISTINCT p.project_name, (pm."SiteCode")::text AS plot_code, 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, avg(po.cover_percent) AS mean_cover FROM ((projects p JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id))) JOIN "plotObservations" po ON (((pm."PlotID" = po."PlotID") AND (po.cover_percent IS NOT NULL)))) 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)))))) 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)))))); -- -- Name: VIEW _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro; Type: COMMENT; Schema: SALVIAS; Owner: - -- COMMENT ON VIEW _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro IS ' Note 1: Assumes identical concatenation of taxonomic fields to form verbatim taxon name in vegbien 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. Note 3: currently, there are no SALVIAS percent cover plots in BIEN '; -- -- Name: _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj; Type: VIEW; Schema: SALVIAS; Owner: - -- CREATE VIEW _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj AS SELECT DISTINCT p.project_name, (pm."SiteCode")::text AS plot_code, 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, po.intercept_cm FROM ((projects p JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id))) JOIN "plotObservations" po ON (((pm."PlotID" = po."PlotID") AND (po.intercept_cm IS NOT NULL)))) 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)))))); -- -- Name: VIEW _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj; Type: COMMENT; Schema: SALVIAS; Owner: - -- COMMENT ON VIEW _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj IS ' Note 1: Assumes identical concatenation of taxonomic fields to form verbatim taxon name in vegbien 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. '; -- -- Name: _plots_17_count_of_subplots_per_plot_for_each_project; Type: VIEW; Schema: SALVIAS; Owner: - -- CREATE VIEW _plots_17_count_of_subplots_per_plot_for_each_project AS SELECT p.project_name, (pm."SiteCode")::text AS plot_code, count(DISTINCT po."Line") AS subplots FROM ((projects p JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id))) JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID"))) GROUP BY p.project_name, (pm."SiteCode")::text ORDER BY p.project_name, (pm."SiteCode")::text; -- -- Name: _plots_18_list_of_subplots_codes_for_each_plot_for_each_project; Type: VIEW; Schema: SALVIAS; Owner: - -- CREATE VIEW _plots_18_list_of_subplots_codes_for_each_plot_for_each_project AS SELECT DISTINCT p.project_name, (pm."SiteCode")::text AS plot_code, po."Line" AS subplot FROM ((projects p JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id))) JOIN "plotObservations" po ON (((pm."PlotID" = po."PlotID") AND ((pm."SiteCode")::text IS NOT NULL)))) ORDER BY p.project_name, (pm."SiteCode")::text, po."Line"; -- -- Name: VIEW _plots_18_list_of_subplots_codes_for_each_plot_for_each_project; Type: COMMENT; Schema: SALVIAS; Owner: - -- COMMENT ON VIEW _plots_18_list_of_subplots_codes_for_each_plot_for_each_project IS ' Note: includes plots with and without subplots '; -- -- PostgreSQL database dump complete --