--
-- 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 AS projectname
   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") 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"
   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"
   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"
  ORDER BY p.project_name, pm."SiteCode";


--
-- 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"
   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"
  ORDER BY p.project_name, pm."SiteCode";


--
-- 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"
   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"
  ORDER BY p.project_name, pm."SiteCode";


--
-- 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"
   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"
  ORDER BY p.project_name, pm."SiteCode";


--
-- 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" AS plotcode, 
    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"
  ORDER BY p.project_name, pm."SiteCode";


--
-- 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" AS plotcode, 
    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"
  ORDER BY p.project_name, pm."SiteCode";


--
-- 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.plotcode, 
    count(a.taxon) AS taxa
   FROM ( SELECT DISTINCT p.project_name, 
            pm."SiteCode" AS plotcode, 
            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.plotcode
  ORDER BY a.project_name, a.plotcode;


--
-- 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" AS plotcode, 
    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", 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" AS plotcode, 
    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", 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", 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" AS plotcode, 
    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", 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", 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" AS plotcode, 
    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", 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" AS plotcode, 
    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"
  ORDER BY p.project_name, pm."SiteCode";


--
-- 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" AS plotcode, 
    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" IS NOT NULL))))
  ORDER BY p.project_name, pm."SiteCode", 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
--