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

-- Quantitative validation queries on the BIEN database

-- 

-- Applies to SALVIAS, or any datasource with all of the following attributes:

-- - Plots

-- - Comprised of multiple datasets organized as "projects"

-- - Maximum 2-level nesting (subplots within plots)

-- - One or more plots contain individual observations with stem measurements

-- - One or more plots contain aggregate observations of counts of individuals within species

-- - One or more plots contain aggregate observations of cover by species

-- 

-- Requires list of projects from source db manager, in three groups: (1) individual observation

--   plot, (2) aggregate individual count plots, (3) aggregate percent cover plots

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

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

-- Set variables specific to this source

-- Currently configured for SALVIAS

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

-- datasource

\set ds '\'SALVIAS\'' 

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

-- 1. Count of projects

-- Check: identical count in source db

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

SELECT COUNT(*) AS projects

FROM project p JOIN source s 

ON p.source_id=s.source_id

WHERE s.shortname=:ds; 

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

-- 2. List of project names

-- Check: join to source db returns same number of rows

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

SELECT p.projectname

FROM project p JOIN source s 

ON p.source_id=s.source_id

WHERE s.shortname=:ds; 

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

-- 3. Count of all plots in this source

-- Check: identical count in source db

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

SELECT COUNT(*) AS plots

FROM location l JOIN locationevent le

ON l.location_id=le.location_id

JOIN project p

ON p.project_id=le.project_id

JOIN source s

ON p.source_id=s.source_id

WHERE s.shortname=:ds;

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

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

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

SELECT p.projectname, COUNT(DISTINCT l.location_id) AS plots

FROM location l JOIN locationevent le

ON l.location_id=le.location_id

JOIN project p

ON p.project_id=le.project_id

JOIN source s

ON p.source_id=s.source_id

WHERE s.shortname=:ds

GROUP BY p.projectname;

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

-- 5.List of plot codes by project

-- Check: join to source db by all columns returns same number of rows

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

SELECT p.projectname, l.authorlocationcode AS plotCode

FROM location l JOIN locationevent le

ON l.location_id=le.location_id

JOIN project p

ON p.project_id=le.project_id

JOIN source s

ON p.source_id=s.source_id

WHERE s.shortname=:ds;

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

-- 6. List of plots with stem measurements

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

SELECT authorlocationcode

FROM top_plot

WHERE source_id = source_by_shortname(:ds)

AND EXISTS(

	SELECT NULL

	FROM location

	JOIN locationevent USING (location_id)

	JOIN taxonoccurrence USING (locationevent_id)

	JOIN aggregateoccurrence USING (taxonoccurrence_id)

	JOIN plantobservation USING (aggregateoccurrence_id)

	JOIN stemobservation USING (plantobservation_id)

	WHERE location.top_plot = top_plot.location_id

	LIMIT 1

)

ORDER BY authorlocationcode

;

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

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

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

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

-- 8.List of plots which use percent cover

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

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

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

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

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

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

--

-- Method: count taxonObservations

-- Note 1: `individuals` should be NULL for plots which use percent cover

-- or line-intercept methods.

-- Note 2: Does this method requires that plots have 2-level nesting of

--       subplots within plots?

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

select p.projectname, l.authorlocationcode as plotcode, sum(ao.count) as individuals

from project p join source s 

on p.source_id=s.source_id

join locationevent le on p.project_id=le.project_id

join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id

join location l on le.location_id=l.location_id

join taxonoccurrence o on sub_locationevent.locationevent_id=o.locationevent_id

join aggregateoccurrence ao on o.taxonoccurrence_id=ao.taxonoccurrence_id

where s.shortname=:ds

group by p.projectname, l.authorlocationcode

order by p.projectname, l.authorlocationcode;

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

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

-- 

-- Method: count records in stemobservation table

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

select p.projectname, l.authorlocationcode as plotcode, count(distinct stemobservation_id) as stems

from project p join source s 

on p.source_id=s.source_id

join locationevent le on p.project_id=le.project_id

join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id

join location l on le.location_id=l.location_id

join taxonoccurrence o on sub_locationevent.locationevent_id=o.locationevent_id

join aggregateoccurrence ao on o.taxonoccurrence_id=ao.taxonoccurrence_id

join plantobservation po on po.aggregateoccurrence_id=ao.aggregateoccurrence_id

join stemobservation so on so.plantobservation_id=po.plantobservation_id

where s.shortname=:ds 

group by p.projectname, l.authorlocationcode

order by p.projectname, l.authorlocationcode;

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

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

-- Check: join to source db by all columns, returns same number of rows

-- Note: Must perform equivalent concatenation of taxonomic field in source db.

-- NOT YET READY, NEED TO INCLUDE SUBSPECIES, IF ANY

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

select p.projectname, l.authorlocationcode as plotcode, 

count(distinct trim('' || coalesce(family::text,'') || ' ' || coalesce(genus::text,'') || ' ' || coalesce(specific_epithet::text,'') || ' ' ||  coalesce(morphospecies::text,'')))

as taxa

from project p join source s 

on p.source_id=s.source_id

join locationevent le on p.project_id=le.project_id

join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id

join location l on le.location_id=l.location_id

join taxonoccurrence o on sub_locationevent.locationevent_id=o.locationevent_id

join taxondetermination td on td.taxonoccurrence_id=o.taxonoccurrence_id

join taxonverbatim tv on td.taxonverbatim_id=tv.taxonverbatim_id

where s.shortname=:ds and td.isoriginal='t'

group by p.projectname, l.authorlocationcode

order by p.projectname, l.authorlocationcode 12;

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

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

-- 

-- Note: Must perform equivalent concatenation of taxonomic field in source db.

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

select distinct p.projectname, l.authorlocationcode as plotcode, 

trim('' || coalesce(family::text,'') || ' ' || coalesce(genus::text,'') || ' ' || coalesce(specific_epithet::text,'') || ' ' ||  coalesce(morphospecies::text,'')) as taxon

from project p join source s 

on p.source_id=s.source_id

join locationevent le on p.project_id=le.project_id

join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id

join location l on le.location_id=l.location_id

join taxonoccurrence o on sub_locationevent.locationevent_id=o.locationevent_id

join taxondetermination td on td.taxonoccurrence_id=o.taxonoccurrence_id

join taxonverbatim tv on td.taxonverbatim_id=tv.taxonverbatim_id

where s.shortname=:ds and td.isoriginal='t'

order by p.projectname, l.authorlocationcode, taxon;

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

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

-- 

-- Note: Must do equivalent concatenation of taxonomic field in source db.

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

select distinct p.projectname, l.authorlocationcode as plotcode, 

trim('' || coalesce(family::text,'') || ' ' || coalesce(genus::text,'') || ' ' || coalesce(specific_epithet::text,'') || ' ' ||  coalesce(morphospecies::text,'')) as taxon,

sum(ao.count) as individuals

from project p join source s 

on p.source_id=s.source_id

join locationevent le on p.project_id=le.project_id

join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id

join location l on le.location_id=l.location_id

join taxonoccurrence o on sub_locationevent.locationevent_id=o.locationevent_id

join taxondetermination td on td.taxonoccurrence_id=o.taxonoccurrence_id

join taxonverbatim tv on td.taxonverbatim_id=tv.taxonverbatim_id

join aggregateoccurrence ao on o.taxonoccurrence_id=ao.taxonoccurrence_id

where s.shortname=:ds and td.isoriginal='t'

group by p.projectname, l.authorlocationcode, taxon

order by p.projectname, l.authorlocationcode, taxon;

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

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

-- Applies to: aggregate-cover plots only

-- Method: sums percent cover in aggregateoccurrence (as recorded in coverindex)

-- Check: join to source db by all columns, returns same number of rows

-- Note: Must do equivalent concatenation of taxonomic field in source db.

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

select distinct p.projectname, l.authorlocationcode as plotcode, 

trim('' || coalesce(family::text,'') || ' ' || coalesce(genus::text,'') || ' ' || coalesce(specific_epithet::text,'') || ' ' ||  coalesce(morphospecies::text,'')) as taxon,

sum(ci.coverpercent) as totalpercentcover

from project p join source s 

on p.source_id=s.source_id

join locationevent le on p.project_id=le.project_id

join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id

join location l on le.location_id=l.location_id

join taxonoccurrence o on sub_locationevent.locationevent_id=o.locationevent_id

join taxondetermination td on td.taxonoccurrence_id=o.taxonoccurrence_id

join taxonverbatim tv on td.taxonverbatim_id=tv.taxonverbatim_id

join aggregateoccurrence ao on o.taxonoccurrence_id=ao.taxonoccurrence_id

join coverindex ci on ao.coverindex_id=ci.coverindex_id

where s.shortname=:ds and td.isoriginal='t'

and ci.coverpercent is not null

group by p.projectname, l.authorlocationcode, taxon

order by p.projectname, l.authorlocationcode, 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 origina db

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

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

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

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

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

select p.projectname, l.authorlocationcode as plotcode, 

count(distinct sub_locationevent.locationevent_id) as subplots

from project p join source s 

on p.source_id=s.source_id

join locationevent le on p.project_id=le.project_id

join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id

join location l on le.location_id=l.location_id

where s.shortname=:ds

group by p.projectname, l.authorlocationcode

order by p.projectname, l.authorlocationcode;

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

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

-- DOESN'T YET WORK PROPERLY

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

select p.projectname, 

l.authorlocationcode as plotcode, 

sub_locationevent.authoreventcode as subplotCode

from project p join source s 

on p.source_id=s.source_id

join locationevent le on p.project_id=le.project_id

join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id

join location l on sub_locationevent.location_id=l.location_id

where s.shortname=:ds

order by p.projectname, l.authorlocationcode;

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

-- Quantitative validation queries on the BIEN database

-- 

-- Applies to SALVIAS, or any datasource with all of the following attributes:

-- - Plots

-- - Comprised of multiple datasets organized as "projects"

-- - Maximum 2-level nesting (subplots within plots)

-- - One or more plots contain individual observations with stem measurements

-- - One or more plots contain aggregate observations of counts of individuals within species

-- - One or more plots contain aggregate observations of cover by species

-- 

-- Requires list of projects from source db manager, in three groups: (1) individual observation

--   plot, (2) aggregate individual count plots, (3) aggregate percent cover plots

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

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

-- Set variables specific to this source

-- Currently configured for SALVIAS

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

-- datasource

\set ds '\'SALVIAS\'' 

-- list of projects with individual observations

\set pio '\'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\',\'OTS Transects\',\'Pilon Lajas Treeplots Bolivia\',\'RAINFOR - 0.1 ha Madre de Dios, Peru\',\'RAINFOR - 1 ha Peru\''

-- list of projects with aggregate individual counts

\set pai '\'Gentry Transect Dataset\''

-- list of projects with aggregate cover counts

\set pac '\'Noel Kempff Savanna Plots\''

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

-- 1. Count of projects

-- Check: identical count in source db

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

SELECT COUNT(*) AS projects

FROM project p JOIN source s 

ON p.source_id=s.source_id

WHERE s.shortname=:ds; 

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

-- 2. List of project names

-- Check: join to source db returns same number of rows

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

SELECT p.projectname

FROM project p JOIN source s 

ON p.source_id=s.source_id

WHERE s.shortname=:ds; 

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

-- 3. Count of all plots in this source

-- Check: identical count in source db

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

SELECT COUNT(*) AS plots

FROM source s JOIN location l

ON s.source_id=l.source_id

WHERE s.shortname=:ds; 

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

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

-- Check: join to source db by all columns returns same number of rows

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

SELECT p.projectname, COUNT(DISTINCT l.location_id) AS plots

FROM location l JOIN locationevent le

ON l.location_id=le.location_id

JOIN project p

ON p.project_id=le.project_id

JOIN source s

ON p.source_id=s.source_id

WHERE s.shortname=:ds

AND p.projectname IN (:pio)

GROUP BY p.projectname;

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

-- 5.List of plot codes by project

-- Check: join to source db by all columns returns same number of rows

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

SELECT p.projectname, l.authorlocationcode AS plotCode

FROM location l JOIN locationevent le

ON l.location_id=le.location_id

JOIN project p

ON p.project_id=le.project_id

JOIN source s

ON p.source_id=s.source_id

WHERE s.shortname=:ds

AND p.projectname IN (:pio);

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

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

-- Applies to: individual observation plots only

-- Method: count taxonObservations

-- Check: join to source db by all columns, returns same number of rows

-- Note: I believe this method requires that plots have 2-level nesting of

--       subplots within plots?

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

select p.projectname, l.authorlocationcode as plotcode, count(*) as individuals

from project p join source s 

on p.source_id=s.source_id

join locationevent le on p.project_id=le.project_id

join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id

join location l on le.location_id=l.location_id

join taxonoccurrence o on sub_locationevent.locationevent_id=o.locationevent_id

where s.shortname=:ds and p.projectname IN (:pio) 

group by p.projectname, l.authorlocationcode

order by p.projectname, l.authorlocationcode;

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

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

-- Applies to: aggregate-individual plots only

-- Method: sum column `count` in table aggregateoccurrence

-- Check: join to source db by all columns, returns same number of rows

-- Note: I believe this method requires that plots have 2-level nesting of

--       subplots within plots?

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

select p.projectname, l.authorlocationcode as plotcode, sum(ao.count) as individuals

from project p join source s 

on p.source_id=s.source_id

join locationevent le on p.project_id=le.project_id

join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id

join location l on le.location_id=l.location_id

join taxonoccurrence o on sub_locationevent.locationevent_id=o.locationevent_id

join aggregateoccurrence ao on o.taxonoccurrence_id=ao.taxonoccurrence_id

where s.shortname=:ds and p.projectname IN (:pai) 

group by p.projectname, l.authorlocationcode

order by p.projectname, l.authorlocationcode;

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

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

-- Applies to: individual observation and aggregate individual count plots with

--   stem counts

-- Method: count records in stemobservation table

-- Check: join to source db by all columns, returns same number of rows

-- Note: I believe this method requires that plots have 2-level nesting of

--       subplots within plots?

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

select p.projectname, l.authorlocationcode as plotcode, count(distinct stemobservation_id) as stems

from project p join source s 

on p.source_id=s.source_id

join locationevent le on p.project_id=le.project_id

join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id

join location l on le.location_id=l.location_id

join taxonoccurrence o on sub_locationevent.locationevent_id=o.locationevent_id

join aggregateoccurrence ao on o.taxonoccurrence_id=ao.taxonoccurrence_id

join plantobservation po on po.aggregateoccurrence_id=ao.aggregateoccurrence_id

join stemobservation so on so.plantobservation_id=po.plantobservation_id

where s.shortname=:ds and (p.projectname IN (:pio)  or p.projectname IN (:pai))

group by p.projectname, l.authorlocationcode

order by p.projectname, l.authorlocationcode;

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

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

-- Check: join to source db by all columns, returns same number of rows

-- Note: Must perform equivalent concatenation of taxonomic field in source db.

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

select p.projectname, l.authorlocationcode as plotcode, 

count(distinct trim('' || coalesce(family::text,'') || ' ' || coalesce(genus::text,'') || ' ' || coalesce(specific_epithet::text,'') || ' ' ||  coalesce(morphospecies::text,'')))

as taxa

from project p join source s 

on p.source_id=s.source_id

join locationevent le on p.project_id=le.project_id

join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id

join location l on le.location_id=l.location_id

join taxonoccurrence o on sub_locationevent.locationevent_id=o.locationevent_id

join taxondetermination td on td.taxonoccurrence_id=o.taxonoccurrence_id

join taxonverbatim tv on td.taxonverbatim_id=tv.taxonverbatim_id

where s.shortname=:ds and td.isoriginal='t'

group by p.projectname, l.authorlocationcode

order by p.projectname, l.authorlocationcode;

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

-- 10. List of verbatim taxa in each plot in each project

-- Check: join to source db by all columns, returns same number of rows.

-- Note: Must perform equivalent concatenation of taxonomic field in source db.

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

select distinct p.projectname, l.authorlocationcode as plotcode, 

trim('' || coalesce(family::text,'') || ' ' || coalesce(genus::text,'') || ' ' || coalesce(specific_epithet::text,'') || ' ' ||  coalesce(morphospecies::text,'')) as taxon

from project p join source s 

on p.source_id=s.source_id

join locationevent le on p.project_id=le.project_id

join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id

join location l on le.location_id=l.location_id

join taxonoccurrence o on sub_locationevent.locationevent_id=o.locationevent_id

join taxondetermination td on td.taxonoccurrence_id=o.taxonoccurrence_id

join taxonverbatim tv on td.taxonverbatim_id=tv.taxonverbatim_id

where s.shortname=:ds and td.isoriginal='t'

order by p.projectname, l.authorlocationcode, taxon;

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

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

-- Applies to: individual observation plots only

-- Method: counts taxonoccurrence records

-- Check: join to source db by all columns, returns same number of rows

-- Note: Must do equivalent concatenation of taxonomic field in source db.

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

select distinct p.projectname, l.authorlocationcode as plotcode, 

trim('' || coalesce(family::text,'') || ' ' || coalesce(genus::text,'') || ' ' || coalesce(specific_epithet::text,'') || ' ' ||  coalesce(morphospecies::text,'')) as taxon,

count(distinct o.taxonoccurrence_id) as individuals

from project p join source s 

on p.source_id=s.source_id

join locationevent le on p.project_id=le.project_id

join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id

join location l on le.location_id=l.location_id

join taxonoccurrence o on sub_locationevent.locationevent_id=o.locationevent_id

join taxondetermination td on td.taxonoccurrence_id=o.taxonoccurrence_id

join taxonverbatim tv on td.taxonverbatim_id=tv.taxonverbatim_id

where s.shortname=:ds and td.isoriginal='t'

and p.projectname IN (:pio)  

group by p.projectname, l.authorlocationcode, taxon

order by p.projectname, l.authorlocationcode, taxon;

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

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

-- Applies to: aggregate-individual count plots only

-- Method: sums column `count` in aggregateoccurrence

-- Check: join to source db by all columns, returns same number of rows

-- Note: Must do equivalent concatenation of taxonomic field in source db.

-- DOESN'T WORK YET

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

select distinct p.projectname, l.authorlocationcode as plotcode, 

trim('' || coalesce(family::text,'') || ' ' || coalesce(genus::text,'') || ' ' || coalesce(specific_epithet::text,'') || ' ' ||  coalesce(morphospecies::text,'')) as taxon,

sum(ao.count) as individuals

from project p join source s 

on p.source_id=s.source_id

join locationevent le on p.project_id=le.project_id

join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id

join location l on le.location_id=l.location_id

join taxonoccurrence o on sub_locationevent.locationevent_id=o.locationevent_id

join taxondetermination td on td.taxonoccurrence_id=o.taxonoccurrence_id

join taxonverbatim tv on td.taxonverbatim_id=tv.taxonverbatim_id

join aggregateoccurrence ao on o.taxonoccurrence_id=ao.taxonoccurrence_id

where s.shortname=:ds and td.isoriginal='t'

and p.projectname IN (:pai)  

group by p.projectname, l.authorlocationcode, taxon

order by p.projectname, l.authorlocationcode, taxon;

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

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

-- Applies to: aggregate-cover plots only

-- Method: sums percent cover in aggregateoccurrence (as recorded in coverindex)

-- Check: join to source db by all columns, returns same number of rows

-- Note: Must do equivalent concatenation of taxonomic field in source db.

-- DOESN'T WORK YET, ALSO MAY NEED LEFT JOINS

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

select distinct p.projectname, l.authorlocationcode as plotcode, 

trim('' || coalesce(family::text,'') || ' ' || coalesce(genus::text,'') || ' ' || coalesce(specific_epithet::text,'') || ' ' ||  coalesce(morphospecies::text,'')) as taxon,

sum(ci.coverpercent) as totalpercentcover

from project p join source s 

on p.source_id=s.source_id

join locationevent le on p.project_id=le.project_id

join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id

join location l on le.location_id=l.location_id

join taxonoccurrence o on sub_locationevent.locationevent_id=o.locationevent_id

join taxondetermination td on td.taxonoccurrence_id=o.taxonoccurrence_id

join taxonverbatim tv on td.taxonverbatim_id=tv.taxonverbatim_id

join aggregateoccurrence ao on o.taxonoccurrence_id=ao.taxonoccurrence_id

join coverindex ci on ao.coverindex_id=ci.coverindex_id

where s.shortname=:ds and td.isoriginal='t'

and p.projectname IN (:pac)  

group by p.projectname, l.authorlocationcode, taxon

order by p.projectname, l.authorlocationcode, taxon;

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

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

-- Check: identical count in source db

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

select p.projectname, l.authorlocationcode as plotcode, 

count(distinct sub_locationevent.locationevent_id) as subplots

from project p join source s 

on p.source_id=s.source_id

join locationevent le on p.project_id=le.project_id

join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id

join location l on le.location_id=l.location_id

where s.shortname=:ds

group by p.projectname, l.authorlocationcode

order by p.projectname, l.authorlocationcode;

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

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

-- Check: join to source db by all columns, returns same number of rows

-- DOESN'T WORK YET, NOT SURE HOW TO DISPLAY SUBPLOT CODES

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

select p.projectname, l.authorlocationcode as plotcode, 

sub_locationevent.authoreventcode as subplotCode

from project p join source s 

on p.source_id=s.source_id

join locationevent le on p.project_id=le.project_id

join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id

join location l on le.location_id=l.location_id

where s.shortname=:ds and (p.projectname IN (:pio)  or p.projectname IN (:pai))

order by p.projectname, l.authorlocationcode;

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

-- Quantitative validation queries on the BIEN database

-- 

-- Applies to SALVIAS, or any datasource with all of the following attributes:

-- - Plots

-- - Comprised of multiple datasets organized as "projects"

-- - Maximum 2-level nesting (subplots within plots)

-- - One or more plots contain individual observations with stem measurements

-- - One or more plots contain aggregate observations of counts of individuals within species

-- - One or more plots contain aggregate observations of cover by species

-- 

-- Requires list of projects from source db manager, in three groups: (1) individual observation

--   plot, (2) aggregate individual count plots, (3) aggregate percent cover plots

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

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

-- Set variables specific to this source

-- Currently configured for SALVIAS

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

-- datasource

\set ds '\'SALVIAS\'' 

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

-- 1. Count of projects

-- Check: identical count in source db

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

SELECT COUNT(*) AS projects

FROM project p JOIN source s 

ON p.source_id=s.source_id

WHERE s.shortname=:ds; 

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

-- 2. List of project names

-- Check: join to source db returns same number of rows

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

SELECT p.projectname

FROM project p JOIN source s 

ON p.source_id=s.source_id

WHERE s.shortname=:ds; 

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

-- 3. Count of all plots in this source

-- Check: identical count in source db

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

SELECT COUNT(*) AS plots

FROM location l JOIN locationevent le

ON l.location_id=le.location_id

JOIN project p

ON p.project_id=le.project_id

JOIN source s

ON p.source_id=s.source_id

WHERE s.shortname=:ds;

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

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

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

SELECT p.projectname, COUNT(DISTINCT l.location_id) AS plots

FROM location l JOIN locationevent le

ON l.location_id=le.location_id

JOIN project p

ON p.project_id=le.project_id

JOIN source s

ON p.source_id=s.source_id

WHERE s.shortname=:ds

GROUP BY p.projectname;

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

-- 5.List of plot codes by project

-- Check: join to source db by all columns returns same number of rows

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

SELECT p.projectname, l.authorlocationcode AS plotCode

FROM location l JOIN locationevent le

ON l.location_id=le.location_id

JOIN project p

ON p.project_id=le.project_id

JOIN source s

ON p.source_id=s.source_id

WHERE s.shortname=:ds;

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

-- 6. List of plots with stem measurements

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

SET search_path TO "SALVIAS_VegBIEN";

SELECT

authorlocationcode

FROM (SELECT * FROM location WHERE parent_id IS NULL) top_plot

WHERE source_id = source_by_shortname('SALVIAS')

AND EXISTS(

	SELECT NULL

	FROM location

	JOIN locationevent USING (location_id)

	JOIN taxonoccurrence USING (locationevent_id)

	JOIN aggregateoccurrence USING (taxonoccurrence_id)

	JOIN plantobservation USING (aggregateoccurrence_id)

	JOIN stemobservation USING (plantobservation_id)

	WHERE location.top_plot = top_plot.location_id

	LIMIT 1

)

ORDER BY authorlocationcode

;

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

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

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

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

-- 8.List of plots which use percent cover

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

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

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

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

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

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

--

-- Method: count taxonObservations

-- Note 1: `individuals` should be NULL for plots which use percent cover

-- or line-intercept methods.

-- Note 2: Does this method requires that plots have 2-level nesting of

--       subplots within plots?

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

select p.projectname, l.authorlocationcode as plotcode, sum(ao.count) as individuals

from project p join source s 

on p.source_id=s.source_id

join locationevent le on p.project_id=le.project_id

join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id

join location l on le.location_id=l.location_id

join taxonoccurrence o on sub_locationevent.locationevent_id=o.locationevent_id

join aggregateoccurrence ao on o.taxonoccurrence_id=ao.taxonoccurrence_id

where s.shortname=:ds

group by p.projectname, l.authorlocationcode

order by p.projectname, l.authorlocationcode;

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

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

-- 

-- Method: count records in stemobservation table

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

select p.projectname, l.authorlocationcode as plotcode, count(distinct stemobservation_id) as stems

from project p join source s 

on p.source_id=s.source_id

join locationevent le on p.project_id=le.project_id

join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id

join location l on le.location_id=l.location_id

join taxonoccurrence o on sub_locationevent.locationevent_id=o.locationevent_id

join aggregateoccurrence ao on o.taxonoccurrence_id=ao.taxonoccurrence_id

join plantobservation po on po.aggregateoccurrence_id=ao.aggregateoccurrence_id

join stemobservation so on so.plantobservation_id=po.plantobservation_id

where s.shortname=:ds 

group by p.projectname, l.authorlocationcode

order by p.projectname, l.authorlocationcode;

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

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

-- Check: join to source db by all columns, returns same number of rows

-- Note: Must perform equivalent concatenation of taxonomic field in source db.

-- NOT YET READY, NEED TO INCLUDE SUBSPECIES, IF ANY

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

select p.projectname, l.authorlocationcode as plotcode, 

count(distinct trim('' || coalesce(family::text,'') || ' ' || coalesce(genus::text,'') || ' ' || coalesce(specific_epithet::text,'') || ' ' ||  coalesce(morphospecies::text,'')))

as taxa

from project p join source s 

on p.source_id=s.source_id

join locationevent le on p.project_id=le.project_id

join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id

join location l on le.location_id=l.location_id

join taxonoccurrence o on sub_locationevent.locationevent_id=o.locationevent_id

join taxondetermination td on td.taxonoccurrence_id=o.taxonoccurrence_id

join taxonverbatim tv on td.taxonverbatim_id=tv.taxonverbatim_id

where s.shortname=:ds and td.isoriginal='t'

group by p.projectname, l.authorlocationcode

order by p.projectname, l.authorlocationcode 12;

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

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

-- 

-- Note: Must perform equivalent concatenation of taxonomic field in source db.

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

select distinct p.projectname, l.authorlocationcode as plotcode, 

trim('' || coalesce(family::text,'') || ' ' || coalesce(genus::text,'') || ' ' || coalesce(specific_epithet::text,'') || ' ' ||  coalesce(morphospecies::text,'')) as taxon

from project p join source s 

on p.source_id=s.source_id

join locationevent le on p.project_id=le.project_id

join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id

join location l on le.location_id=l.location_id

join taxonoccurrence o on sub_locationevent.locationevent_id=o.locationevent_id

join taxondetermination td on td.taxonoccurrence_id=o.taxonoccurrence_id

join taxonverbatim tv on td.taxonverbatim_id=tv.taxonverbatim_id

where s.shortname=:ds and td.isoriginal='t'

order by p.projectname, l.authorlocationcode, taxon;

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

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

-- 

-- Note: Must do equivalent concatenation of taxonomic field in source db.

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

select distinct p.projectname, l.authorlocationcode as plotcode, 

trim('' || coalesce(family::text,'') || ' ' || coalesce(genus::text,'') || ' ' || coalesce(specific_epithet::text,'') || ' ' ||  coalesce(morphospecies::text,'')) as taxon,

sum(ao.count) as individuals

from project p join source s 

on p.source_id=s.source_id

join locationevent le on p.project_id=le.project_id

join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id

join location l on le.location_id=l.location_id

join taxonoccurrence o on sub_locationevent.locationevent_id=o.locationevent_id

join taxondetermination td on td.taxonoccurrence_id=o.taxonoccurrence_id

join taxonverbatim tv on td.taxonverbatim_id=tv.taxonverbatim_id

join aggregateoccurrence ao on o.taxonoccurrence_id=ao.taxonoccurrence_id

where s.shortname=:ds and td.isoriginal='t'

group by p.projectname, l.authorlocationcode, taxon

order by p.projectname, l.authorlocationcode, taxon;

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

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

-- Applies to: aggregate-cover plots only

-- Method: sums percent cover in aggregateoccurrence (as recorded in coverindex)

-- Check: join to source db by all columns, returns same number of rows

-- Note: Must do equivalent concatenation of taxonomic field in source db.

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

select distinct p.projectname, l.authorlocationcode as plotcode, 

trim('' || coalesce(family::text,'') || ' ' || coalesce(genus::text,'') || ' ' || coalesce(specific_epithet::text,'') || ' ' ||  coalesce(morphospecies::text,'')) as taxon,

sum(ci.coverpercent) as totalpercentcover

from project p join source s 

on p.source_id=s.source_id

join locationevent le on p.project_id=le.project_id

join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id

join location l on le.location_id=l.location_id

join taxonoccurrence o on sub_locationevent.locationevent_id=o.locationevent_id

join taxondetermination td on td.taxonoccurrence_id=o.taxonoccurrence_id

join taxonverbatim tv on td.taxonverbatim_id=tv.taxonverbatim_id

join aggregateoccurrence ao on o.taxonoccurrence_id=ao.taxonoccurrence_id

join coverindex ci on ao.coverindex_id=ci.coverindex_id

where s.shortname=:ds and td.isoriginal='t'

and ci.coverpercent is not null

group by p.projectname, l.authorlocationcode, taxon

order by p.projectname, l.authorlocationcode, 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 origina db

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

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

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

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

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

select p.projectname, l.authorlocationcode as plotcode, 

count(distinct sub_locationevent.locationevent_id) as subplots

from project p join source s 

on p.source_id=s.source_id

join locationevent le on p.project_id=le.project_id

join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id

join location l on le.location_id=l.location_id

where s.shortname=:ds

group by p.projectname, l.authorlocationcode

order by p.projectname, l.authorlocationcode;

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

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

-- DOESN'T YET WORK PROPERLY

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

select p.projectname, 

l.authorlocationcode as plotcode, 

sub_locationevent.authoreventcode as subplotCode

from project p join source s 

on p.source_id=s.source_id

join locationevent le on p.project_id=le.project_id

join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id

join location l on sub_locationevent.location_id=l.location_id

where s.shortname=:ds

order by p.projectname, l.authorlocationcode;

mailto:bboyle@email.arizona.edu?Brad_Boyle.2013-12-10-14:57.Quantative+validations+for+SALVIAS

mailto:bboyle@email.arizona.edu?Brad_Boyle.2014-1-17-13:28.SALVIAS+quantitative+validation+scripts.(bien3_validations_salvias_vegbien.sql)

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

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

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

-- 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,')

');

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,')

');

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,')

');

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

');

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,')

');

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

');

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

');

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

');

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

');

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

');

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

');

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

");

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 

");

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 

");

PREPARE stmt FROM @sql;