--
-- 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 plot_code
   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 plot_code
   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_06a_list_of_stems; Type: VIEW; Schema: SALVIAS; Owner: -
--

CREATE VIEW _plots_06a_list_of_stems AS
 SELECT p.project_name, 
    (pm."SiteCode")::text AS plot_code, 
    (s.stem_id)::text AS stem_id
   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)))
  ORDER BY p.project_name, (pm."SiteCode")::text, (s.stem_id)::text;


--
-- Name: _plots_07_list_of_plots_which_use_counts_of_indiv_per_species; Type: VIEW; Schema: SALVIAS; Owner: -
--

CREATE VIEW _plots_07_list_of_plots_which_use_counts_of_indiv_per_species AS
 SELECT DISTINCT p.project_name, 
    (pm."SiteCode")::text AS plot_code
   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 plot_code
   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 plot_code
   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_10a_aggregate_observation_individual_counts; Type: VIEW; Schema: SALVIAS; Owner: -
--

CREATE VIEW _plots_10a_aggregate_observation_individual_counts AS
 SELECT p.project_name, 
    (pm."SiteCode")::text AS plot_code, 
    (po."PlotObsID")::text AS individual_id, 
    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")))
  ORDER BY p.project_name, (pm."SiteCode")::text, (po."PlotObsID")::text;


--
-- Name: VIEW _plots_10a_aggregate_observation_individual_counts; Type: COMMENT; Schema: SALVIAS; Owner: -
--

COMMENT ON VIEW _plots_10a_aggregate_observation_individual_counts IS '
Note 1: `individuals` should be NULL for plots which use percent cover or line-intercept methods.
';


--
-- 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, 
    sum((po.cover_percent)::double precision) AS totalpercentcover
   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_code
   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
';


--
-- Name: _plots_19_count_of_censuses_per_plot_in_each_project; Type: VIEW; Schema: SALVIAS; Owner: -
--

CREATE VIEW _plots_19_count_of_censuses_per_plot_in_each_project AS
 SELECT p.project_name, 
    (pm."SiteCode")::text AS plot_code, 
    count(DISTINCT ARRAY[po.census_date]) AS inventories
   FROM ((projects p
   JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
   LEFT JOIN "plotObservations" po ON (((pm."PlotID" = po."PlotID") AND ((pm."SiteCode")::text IS NOT NULL))))
  GROUP BY p.project_name, (pm."SiteCode")::text
  ORDER BY p.project_name, (pm."SiteCode")::text;


--
-- PostgreSQL database dump complete
--