--
-- 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: _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: _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: _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: _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: _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: _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: _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";


--
-- PostgreSQL database dump complete
--