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

-- Quantitative validation queries against the original SALVIAS database

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

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

-- Set variables specific to this source

-- Currently configured for SALVIAS

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

USE salvias_plots;

-- SALVIAS projects in BIEN

-- Must filter out all others as not all SALVIAS plots exported to BIEN

SET @p = "'Gentry Transect Dataset','Boyle Transects','Enquist Lab Transect Dataset','ACA Amazon Forest Inventories','Bonifacino Forest Transects','Cam Webb Borneo Plots','DeWalt Bolivia forest plots','La Selva Secondary Forest Plots','Noel Kempff Forest Plots','Noel Kempff Savanna Plots','OTS Transects','Pilon Lajas Treeplots Bolivia','RAINFOR - 0.1 ha Madre de Dios, Peru','RAINFOR - 1 ha Peru'";

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

-- 1. Count of projects

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

SET @sql= CONCAT('

SELECT COUNT(*) AS projects

FROM projects 

WHERE project_name IN (',@p,')

LIMIT 10

');

PREPARE stmt FROM @sql;

EXECUTE stmt;

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

-- 2. List of project names

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

SET @sql= CONCAT('

SELECT project_name AS projectname 

FROM projects 

WHERE project_name IN (',@p,')

LIMIT 10

');

PREPARE stmt FROM @sql;

EXECUTE stmt;

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

-- 3. Count of all plots in this source

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

SET @sql= CONCAT('

SELECT COUNT(DISTINCT SiteCode) AS plots

FROM projects p JOIN PlotMetadata pm

ON p.project_id=pm.project_id

WHERE p.project_name IN (',@p,')

LIMIT 10

');

PREPARE stmt FROM @sql;

EXECUTE stmt;

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

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

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

SET @sql= CONCAT('

SELECT p.project_name, COUNT(*) AS plots

FROM projects p JOIN PlotMetadata pm

ON p.project_id=pm.project_id

WHERE p.project_name IN (',@p,')

GROUP BY p.project_name

LIMIT 10

');

PREPARE stmt FROM @sql;

EXECUTE stmt;

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

-- 5.List of plot codes by project

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

SET @sql= CONCAT('

SELECT p.project_name, pm.SiteCode

FROM projects p JOIN PlotMetadata pm

ON p.project_id=pm.project_id

WHERE p.project_name IN (',@p,')

LIMIT 10

');

PREPARE stmt FROM @sql;

EXECUTE stmt;

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

-- 6. List of plots with stem measurements

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

SET @sql= CONCAT('

SELECT DISTINCT p.project_name, pm.SiteCode

FROM projects p JOIN PlotMetadata pm 

JOIN PlotObservations po JOIN stems s

ON p.project_id=pm.project_id AND pm.PlotID=po.PlotID

AND po.PlotObsID=s.plotobs_id

WHERE p.project_name IN (',@p,')

GROUP BY p.project_name, SiteCode

ORDER BY p.project_name, SiteCode

LIMIT 10

');

PREPARE stmt FROM @sql;

EXECUTE stmt;

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

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

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

SET @sql= CONCAT('

SELECT DISTINCT p.project_name, pm.SiteCode

FROM projects p JOIN PlotMetadata pm JOIN PlotObservations po

ON p.project_id=pm.project_id AND pm.PlotID=po.PlotID

WHERE p.project_name IN (',@p,')

AND po.NoInd>1

GROUP BY p.project_name, SiteCode

ORDER BY p.project_name, SiteCode

LIMIT 10

');

PREPARE stmt FROM @sql;

EXECUTE stmt;

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

-- 8.List of plots which use percent cover

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

SET @sql= CONCAT('

SELECT DISTINCT p.project_name, pm.SiteCode

FROM projects p JOIN PlotMetadata pm JOIN PlotObservations po

ON p.project_id=pm.project_id AND pm.PlotID=po.PlotID

WHERE p.project_name IN (',@p,')

AND cover_percent IS NOT NULL

GROUP BY p.project_name, SiteCode

ORDER BY p.project_name, SiteCode

LIMIT 10

');

PREPARE stmt FROM @sql;

EXECUTE stmt;

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

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

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

SET @sql= CONCAT('

SELECT DISTINCT p.project_name, pm.SiteCode

FROM projects p JOIN PlotMetadata pm JOIN PlotObservations po

ON p.project_id=pm.project_id AND pm.PlotID=po.PlotID

WHERE p.project_name IN (',@p,')

AND intercept_cm IS NOT NULL

GROUP BY p.project_name, SiteCode

ORDER BY p.project_name, SiteCode

LIMIT 10

');

PREPARE stmt FROM @sql;

EXECUTE stmt;

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

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

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

SET @sql= CONCAT('

SELECT p.project_name, SiteCode AS plotcode, 

SUM(NoInd) AS individuals

FROM projects p JOIN PlotMetadata pm JOIN PlotObservations po

ON p.project_id=pm.project_id AND pm.PlotID=po.PlotID

WHERE p.project_name IN (',@p,')

GROUP BY p.project_name, SiteCode

ORDER BY p.project_name, SiteCode

LIMIT 10

');

PREPARE stmt FROM @sql;

EXECUTE stmt;

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

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

-- 

-- Method: count records in stems table

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

SET @sql= CONCAT('

SELECT p.project_name, SiteCode AS plotcode, 

COUNT(DISTINCT stem_id) AS stems

FROM projects p JOIN PlotMetadata pm 

JOIN PlotObservations po JOIN stems s

ON p.project_id=pm.project_id AND pm.PlotID=po.PlotID

AND po.PlotObsID=s.plotobs_id

WHERE p.project_name IN (',@p,')

GROUP BY p.project_name, SiteCode

ORDER BY p.project_name, SiteCode

LIMIT 10

');

PREPARE stmt FROM @sql;

EXECUTE stmt;

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

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

-- 

-- Note: Assumes identical concatenation of taxonomic fields

--       to form verbatim taxon name in vegBIEN

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

SET @sql= CONCAT("

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 JOIN PlotObservations po

ON p.project_id=pm.project_id AND pm.PlotID=po.PlotID

WHERE p.project_name IN (",@p,")

) AS a

GROUP BY project_name, plotcode 

ORDER BY project_name, plotcode

LIMIT 10

");

PREPARE stmt FROM @sql;

EXECUTE stmt;

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

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

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

SET @sql= CONCAT("

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 JOIN PlotObservations po

ON p.project_id=pm.project_id AND pm.PlotID=po.PlotID

WHERE p.project_name IN (",@p,")

ORDER BY p.project_name, SiteCode, taxon 

LIMIT 10

");

PREPARE stmt FROM @sql;

EXECUTE stmt;

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

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

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

SET @sql= CONCAT("

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 JOIN PlotObservations po

ON p.project_id=pm.project_id AND pm.PlotID=po.PlotID

WHERE p.project_name IN (",@p,")

GROUP BY p.project_name, SiteCode, taxon

ORDER BY p.project_name, SiteCode, taxon 

LIMIT 10

");

PREPARE stmt FROM @sql;

EXECUTE stmt;

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

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

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

SET @sql= CONCAT("

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 JOIN PlotObservations po

ON p.project_id=pm.project_id AND pm.PlotID=po.PlotID

WHERE p.project_name IN (",@p,")

AND cover_percent IS NOT NULL

GROUP BY p.project_name, SiteCode, taxon

ORDER BY p.project_name, SiteCode, taxon 

LIMIT 10

");

PREPARE stmt FROM @sql;

EXECUTE stmt;

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

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

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

SET @sql= CONCAT("

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 JOIN PlotObservations po

ON p.project_id=pm.project_id AND pm.PlotID=po.PlotID

WHERE p.project_name IN (",@p,") 

AND intercept_cm IS NOT NULL 

ORDER BY p.project_name, SiteCode, taxon 

LIMIT 10

");

PREPARE stmt FROM @sql;

EXECUTE stmt;

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

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

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

SET @sql= CONCAT("

SELECT project_name, pm.SiteCode AS plotcode, COUNT(DISTINCT Line) as subplots

FROM projects p JOIN PlotMetadata pm JOIN PlotObservations po

ON p.project_id=pm.project_id AND pm.PlotID=po.PlotID

WHERE p.project_name IN (",@p,")

GROUP BY project_name, pm.SiteCode

ORDER BY project_name, pm.SiteCode 

LIMIT 10

");

PREPARE stmt FROM @sql;

EXECUTE stmt;

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

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

-- 

-- Note: includes plots with and without subplots

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

SET @sql= CONCAT("

SELECT DISTINCT project_name, pm.SiteCode AS plotcode, Line as subplot

FROM projects p JOIN PlotMetadata pm JOIN PlotObservations po

ON p.project_id=pm.project_id AND pm.PlotID=po.PlotID

WHERE p.project_name IN (",@p,")

AND pm.SiteCode IS NOT NULL

ORDER BY project_name, pm.SiteCode, subplot

LIMIT 10

");

PREPARE stmt FROM @sql;

EXECUTE stmt;