-- -----------------------------------------------------------------------------

-- Quantitative validation queries against the original SALVIAS database

-- -------------------------------------------------------------------------

-- ------------------

-- Set variables specific to this source

-- Currently configured for SALVIAS

-- ------------------

SET search_path TO "SALVIAS";

-- ------------------

-- 1. Count of projects

-- ------------------

CREATE OR REPLACE VIEW _plots_01_count_of_projects AS

SELECT COUNT(*) AS projects

FROM projects

;

-- ------------------

-- 2. List of project names

-- ------------------

CREATE OR REPLACE VIEW _plots_02_list_of_project_names AS

SELECT project_name AS projectname

FROM projects

;

-- ------------------

-- 3. Count of all plots in this source

-- ------------------

CREATE OR REPLACE VIEW _plots_03_count_of_all_plots_in_this_source AS

SELECT COUNT(DISTINCT "SiteCode") AS plots

FROM projects p JOIN "plotMetadata" pm

ON p.project_id=pm.project_id

;

-- ------------------

-- 4. Count of plots in each project in this source

-- ------------------

CREATE OR REPLACE 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

;

-- ------------------

-- 5.List of plot codes by project

-- ------------------

CREATE OR REPLACE 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

;

-- ------------------

-- 6. List of plots with stem measurements

-- ------------------

CREATE OR REPLACE 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, "SiteCode"

ORDER BY p.project_name, "SiteCode"

;

-- ------------------

-- 7.List of plots with counts of individuals per species

-- ------------------

CREATE OR REPLACE 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, "SiteCode"

ORDER BY p.project_name, "SiteCode"

;

-- ------------------

-- 8.List of plots which use percent cover

-- ------------------

CREATE OR REPLACE 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 cover_percent IS NOT NULL

GROUP BY p.project_name, "SiteCode"

ORDER BY p.project_name, "SiteCode"

;

-- ------------------

-- 9.List of plots which use line-intercept

-- ------------------

CREATE OR REPLACE 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 intercept_cm IS NOT NULL

GROUP BY p.project_name, "SiteCode"

ORDER BY p.project_name, "SiteCode"

;

-- ------------------

-- 10. Count of individuals per plot in each project

--

-- 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

-- ------------------

CREATE OR REPLACE VIEW _plots_10_count_of_individuals_per_plot_in_each_project AS

SELECT p.project_name, "SiteCode" AS plotcode,

SUM("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, "SiteCode"

ORDER BY p.project_name, "SiteCode"

;

-- ------------------

-- 11. Count of stems per plot in each project

--

-- Method: count records in stems table

-- ------------------

CREATE OR REPLACE VIEW _plots_11_count_of_stems_per_plot_in_each_project AS

SELECT p.project_name, "SiteCode" AS plotcode,

COUNT(DISTINCT 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, "SiteCode"

ORDER BY p.project_name, "SiteCode"

;

-- ------------------

-- 12. Count of verbatim taxa per plot in each project

--

-- Note: Assumes identical concatenation of taxonomic fields

--       to form verbatim taxon name in vegBIEN

-- ------------------

CREATE OR REPLACE VIEW _plots_12_count_of_verbatim_taxa_per_plot_in_each_project AS

SELECT project_name, plotcode, COUNT(taxon) AS taxa

FROM

(

SELECT DISTINCT p.project_name, "SiteCode" AS plotcode,

TRIM(CONCAT_WS(' ',IFNULL("Family",''),IFNULL("Genus",''),IFNULL("Species",''),

IF(infra_ep_1 IS NULL,IFNULL(auth,''),

TRIM(CONCAT_WS(' ',IFNULL(infra_rank_1,''),IFNULL(infra_ep_1,''),IFNULL(infra_auth_1,'')))

))) AS taxon

FROM projects p

JOIN "plotMetadata" pm ON p.project_id=pm.project_id

JOIN "plotObservations" po ON pm."PlotID"=po."PlotID"

) AS a

GROUP BY project_name, plotcode

ORDER BY project_name, plotcode

;

-- ------------------

-- 13. List of distinct verbatim taxa in each plot in each project

--

-- Note: Assumes identical concatenation of taxonomic fields

--       to form verbatim taxon name in vegBIEN.

-- ------------------

CREATE OR REPLACE VIEW _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p AS

SELECT DISTINCT p.project_name, "SiteCode" AS plotcode,

TRIM(CONCAT_WS(' ',IFNULL("Family",''),IFNULL("Genus",''),IFNULL("Species",''),

IF(infra_ep_1 IS NULL,IFNULL(auth,''),

TRIM(CONCAT_WS(' ',IFNULL(infra_rank_1,''),IFNULL(infra_ep_1,''),IFNULL(infra_auth_1,'')))

))) 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, "SiteCode", taxon

;

-- ------------------

-- 14. Count of individuals per (verbatim) taxon per plot in each project

--

-- Note: Assumes identical concatenation of taxonomic fields

--       to form verbatim taxon name in vegBIEN

-- ------------------

CREATE OR REPLACE VIEW _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr AS

SELECT DISTINCT p.project_name, "SiteCode" AS plotcode,

TRIM(CONCAT_WS(' ',IFNULL("Family",''),IFNULL("Genus",''),IFNULL("Species",''),

IF(infra_ep_1 IS NULL,IFNULL(auth,''),

TRIM(CONCAT_WS(' ',IFNULL(infra_rank_1,''),IFNULL(infra_ep_1,''),IFNULL(infra_auth_1,'')))

))) AS taxon,

SUM("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, "SiteCode", taxon

ORDER BY p.project_name, "SiteCode", taxon

;

-- ------------------

-- 15. Percent cover of each (verbatim) taxon in each plot in each project

--      where percent cover is recorded

--

-- 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

-- ------------------

CREATE OR REPLACE VIEW _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro AS

SELECT DISTINCT p.project_name, "SiteCode" AS plotcode,

TRIM(CONCAT_WS(' ',IFNULL("Family",''),IFNULL("Genus",''),IFNULL("Species",''),

IF(infra_ep_1 IS NULL,IFNULL(auth,''),

TRIM(CONCAT_WS(' ',IFNULL(infra_rank_1,''),IFNULL(infra_ep_1,''),IFNULL(infra_auth_1,'')))

))) AS taxon,

AVG(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 cover_percent IS NOT NULL

GROUP BY p.project_name, "SiteCode", taxon

ORDER BY p.project_name, "SiteCode", taxon

;

-- ------------------

-- 16. Intercept values for each (verbatim) taxon in each plot in each project

--      where line-intercept values are recorded

--

-- 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.

-- ------------------

CREATE OR REPLACE VIEW _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj AS

SELECT DISTINCT p.project_name, "SiteCode" AS plotcode,

TRIM(CONCAT_WS(' ',IFNULL("Family",''),IFNULL("Genus",''),IFNULL("Species",''),

IF(infra_ep_1 IS NULL,IFNULL(auth,''),

TRIM(CONCAT_WS(' ',IFNULL(infra_rank_1,''),IFNULL(infra_ep_1,''),IFNULL(infra_auth_1,'')))

))) AS taxon,

intercept_cm

FROM projects p

JOIN "plotMetadata" pm ON p.project_id=pm.project_id

JOIN "plotObservations" po ON pm."PlotID"=po."PlotID"

AND intercept_cm IS NOT NULL

ORDER BY p.project_name, "SiteCode", taxon

;

-- ------------------

-- 17. Count of subplots per plot, for each project

-- ------------------

CREATE OR REPLACE VIEW _plots_17_count_of_subplots_per_plot_for_each_project AS

SELECT project_name, pm."SiteCode" AS plotcode, COUNT(DISTINCT "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 project_name, pm."SiteCode"

ORDER BY project_name, pm."SiteCode"

;

-- ------------------

-- 18. List of subplots codes for each plot for each project

--

-- Note: includes plots with and without subplots

-- ------------------

CREATE OR REPLACE VIEW _plots_18_list_of_subplots_codes_for_each_plot_for_each_project AS

SELECT DISTINCT project_name, pm."SiteCode" AS plotcode, "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 project_name, pm."SiteCode", subplot

;