SET search_path = bien2_traits, pg_catalog;

--

--

 SELECT count(*) AS totalrecords

   FROM "TraitObservation"

  WHERE (((("TraitObservation"."taxonName" IS NOT NULL) AND ("TraitObservation"."taxonName" <> ''::text)) AND ("TraitObservation"."measurementName" IS NOT NULL)) AND ("TraitObservation"."measurementName" <> ''::text));

--

--

 SELECT count(DISTINCT "TraitObservation"."measurementName") AS traits

   FROM "TraitObservation"

  WHERE (((("TraitObservation"."taxonName" IS NOT NULL) AND ("TraitObservation"."taxonName" <> ''::text)) AND ("TraitObservation"."measurementName" IS NOT NULL)) AND ("TraitObservation"."measurementName" <> ''::text));

--

--

 SELECT DISTINCT "TraitObservation"."measurementName" AS trait

   FROM "TraitObservation"

  WHERE (((("TraitObservation"."taxonName" IS NOT NULL) AND ("TraitObservation"."taxonName" <> ''::text)) AND ("TraitObservation"."measurementName" IS NOT NULL)) AND ("TraitObservation"."measurementName" <> ''::text))

--

--

 SELECT "TraitObservation"."measurementName" AS trait, 

    count(*) AS measurements

   FROM "TraitObservation"

--

--

 SELECT count(DISTINCT btrim(concat_ws(' '::text, util.ifnull("TraitObservation"."taxonName", ''::text), util.ifnull("TraitObservation"."scientificNameAuthorship", ''::text)))) AS taxa

   FROM "TraitObservation"

  WHERE (((("TraitObservation"."taxonName" IS NOT NULL) AND ("TraitObservation"."taxonName" <> ''::text)) AND ("TraitObservation"."measurementName" IS NOT NULL)) AND ("TraitObservation"."measurementName" <> ''::text));

--

--

 SELECT DISTINCT btrim(concat_ws(' '::text, util.ifnull("TraitObservation"."taxonName", ''::text), util.ifnull("TraitObservation"."scientificNameAuthorship", ''::text))) AS taxonwithauthor

   FROM "TraitObservation"

  WHERE (((("TraitObservation"."taxonName" IS NOT NULL) AND ("TraitObservation"."taxonName" <> ''::text)) AND ("TraitObservation"."measurementName" IS NOT NULL)) AND ("TraitObservation"."measurementName" <> ''::text))

--

--

 SELECT "TraitObservation"."measurementName" AS trait, 

    "TraitObservation"."measurementValue" AS value, 

    "TraitObservation"."measurementUnit" AS units

--

--

 SELECT btrim(concat_ws(' '::text, util.ifnull("TraitObservation"."taxonName", ''::text), util.ifnull("TraitObservation"."scientificNameAuthorship", ''::text))) AS taxonwithauthor, 

    "TraitObservation"."measurementName" AS trait, 

    "TraitObservation"."measurementValue" AS value

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

--

SET search_path = public_validations, pg_catalog;

--

--

 SELECT count(*) AS projects

   FROM (public.project p

   JOIN public.source s ON ((p.source_id = s.source_id)))

--

--

 SELECT p.projectname

   FROM (public.project p

   JOIN public.source s ON ((p.source_id = s.source_id)))

--

--

 SELECT count(*) AS plots

   FROM (((public.location l

   JOIN public.locationevent le ON ((l.location_id = le.location_id)))

--

--

 SELECT p.projectname, 

    count(DISTINCT l.location_id) AS plots

   FROM (((public.location l

--

--

 SELECT p.projectname, 

    l.authorlocationcode AS "plotCode"

   FROM (((public.location l

--

--

 SELECT top_plot.authorlocationcode

   FROM public.top_plot

  WHERE ((top_plot.source_id = public.source_by_shortname(("current_schema"())::text)) AND (EXISTS ( SELECT NULL::unknown AS unknown

--

--

 SELECT p.projectname, 

    l.authorlocationcode AS plotcode, 

    sum(ao.count) AS individuals

--

--

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, 

    count(DISTINCT so.stemobservation_id) AS stems

--

--

';

--

--

 SELECT p.projectname, 

    l.authorlocationcode AS plotcode, 

    count(DISTINCT btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text)))) AS taxa

--

--

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

NOT YET READY, NEED TO INCLUDE SUBSPECIES, IF ANY

';

--

--

 SELECT DISTINCT p.projectname, 

    l.authorlocationcode AS plotcode, 

    btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text))) AS taxon

--

--

';

--

--

 SELECT DISTINCT p.projectname, 

    l.authorlocationcode AS plotcode, 

    btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text))) AS taxon, 

--

--

';

--

--

 SELECT DISTINCT p.projectname, 

    l.authorlocationcode AS plotcode, 

    btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text))) AS taxon, 

--

--

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 p.projectname, 

    l.authorlocationcode AS plotcode, 

    count(DISTINCT sub_locationevent.locationevent_id) AS subplots

--

--

 SELECT p.projectname, 

    l.authorlocationcode AS plotcode, 

    sub_locationevent.authoreventcode AS "subplotCode"

--

--

';

--

--

 SELECT count(*) AS "totalRecords"

   FROM (public.trait

   JOIN public.taxonoccurrence USING (taxonoccurrence_id))

--

--

 SELECT count(DISTINCT trait.name) AS traits

   FROM (public.trait

   JOIN public.taxonoccurrence USING (taxonoccurrence_id))

--

--

 SELECT DISTINCT trait.name AS trait

   FROM (public.trait

   JOIN public.taxonoccurrence USING (taxonoccurrence_id))

--

--

 SELECT trait.name AS trait, 

    count(*) AS measurements

   FROM (public.trait

--

--

 SELECT count(DISTINCT ROW(taxonverbatim.taxonname, taxonverbatim.author)) AS taxa

   FROM public.taxonverbatim

  WHERE (taxonverbatim.source_id = public.source_by_shortname(("current_schema"())::text));

--

--

';

--

--

 SELECT DISTINCT concat_ws(' '::text, taxonverbatim.taxonname, taxonverbatim.author) AS taxonwithauthor

   FROM public.taxonverbatim

  WHERE (taxonverbatim.source_id = public.source_by_shortname(("current_schema"())::text))

--

--

Note 2: Note formation of taxonCorrected: includes family ONLY if taxon is not determined at least to genus

';

--

--

 SELECT trait.name AS trait, 

    trait.value, 

    trait.units

--

--

 SELECT concat_ws(' '::text, taxonverbatim.taxonname, taxonverbatim.author) AS taxonwithauthor, 

    trait.name AS trait, 

    trait.value

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

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

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

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

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

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

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

-- 6. List of plots with stem measurements

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

SELECT authorlocationcode

FROM top_plot

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

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

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

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

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

-- 

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

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

-- 

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

-- 

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

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

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

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

-- 1. Count of total records (specimens) in source db

-- Check: full join against equivalent query on BIEN3 db should return 1 row

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

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

-- 2. Count of unique (verbatim) non-null families

-- Check: Full inner join to equivalent query on BIEN3 db should return 1 row

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

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

-- 3. List of verbatim families

-- Check: Full inner join to equivalent query on BIEN3 db should return same number of rows

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

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

-- 4. Count of unique (verbatim) non-null species, without author

-- Check: Full inner join to equivalent query on BIEN3 db should return 1 row

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

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

-- 5. List of verbatim species, excluding author

-- Check: Full inner join to equivalent query on BIEN3 db should return 3335 rows

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

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

-- 6. Count of unique (verbatim) non-null subspecific taxa, without author

-- Check: Full inner join to equivalent query on BIEN3 db should return 1 row

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

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

-- 7. List of verbatim subspecific taxa, without author

-- Check: Full inner join to equivalent query on BIEN3 db should return 40145 rows

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

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

-- 8. Count of unique (verbatim) taxa including author, for all taxa identified at least to genus

-- Check: Full inner join to equivalent query on BIEN3 db should return 1 row

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

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

-- 9. List of unique (verbatim) taxa including author, for all taxa identified at least to genus

-- Check: Full inner join to equivalent query on BIEN3 db should return 45997 rows

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

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

-- 10. Count number of records by institution

-- Check: Full inner join to equivalent query on BIEN3 db should return 6 rows

-- Note: Majority should be from 'NY'; these are standard herbarium acronyms

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

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

-- 11. List of three standard political divisions

-- Note: character set issues may cause mis-matches. This query is a good way to reveal

-- character set issues, either in source db or in BIEN

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

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

-- 12. Check distinct Collector names + collection numbers + collection dates, 

-- plus total records

-- Check: Full inner join to equivalent query on BIEN3 db should return 309396 rows

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

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

-- 13. Count of all  verbatim Latitude and Longitude values, as well as all 

-- latitude and longitude values that are decimals numbers

-- Check: full join to equivalent query against BIEN3 should return 1 row

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

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

-- 14. Count of all verbatim Latitude and Longitude values that are not valid values

-- of decimal latitude or decimal longitude

-- Check: full join to equivalent query against BIEN3 should return 1 row

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

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

-- 15. List distinct non-null locality descriptions

-- Check: full join to equivalent query against BIEN3 should return 125592 records

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

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

-- 16. List distinct non-null specimen descriptions

-- Check: full join to equivalent query against BIEN3 should return 158460 records

-- Note: specimens descriptions in nybg extract is in column PlantFungusDescription

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

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

-- 1. Count records

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

SELECT COUNT(*) AS "totalRecords"

FROM trait

JOIN taxonoccurrence USING (taxonoccurrence_id)

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

-- 2. Count trait names

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

SELECT COUNT(DISTINCT name) AS traits

FROM trait

JOIN taxonoccurrence USING (taxonoccurrence_id)

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

-- 3. List trait names

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

SELECT DISTINCT name AS trait

FROM trait

JOIN taxonoccurrence USING (taxonoccurrence_id)

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

-- 4. Count records per trait

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

SELECT name AS trait, COUNT(*) AS measurements

FROM trait

JOIN taxonoccurrence USING (taxonoccurrence_id)

-- Note: No morphospecies in trait table, therefore count

-- 		taxon + authority only

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

SELECT COUNT(DISTINCT (taxonname, author)) AS taxa

FROM taxonverbatim

WHERE source_id = source_by_shortname(current_schema)

-- Note 2: Note formation of taxonCorrected: includes family ONLY if

-- taxon is not determined at least to genus

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

SELECT DISTINCT concat_ws(' ', taxonname, author) AS taxonwithauthor

FROM taxonverbatim

WHERE source_id = source_by_shortname(current_schema)

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

-- 7. Trait, value and units for first 5000 records 

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

SELECT name AS trait, value, units

FROM trait

JOIN taxonoccurrence USING (taxonoccurrence_id)

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

-- 8. Taxon, trait and value for first 5000 records 

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

SELECT concat_ws(' ', taxonname, author) AS taxonwithauthor, name AS trait, value

FROM taxonverbatim

JOIN taxondetermination ON taxondetermination.taxonverbatim_id = taxonverbatim.taxonverbatim_id AND taxondetermination.iscurrent

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

-- 1. Count records

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

SELECT COUNT(*) AS totalrecords

FROM "TraitObservation"

WHERE "taxonName" IS NOT NULL AND "taxonName"<>'' AND "measurementName" IS NOT NULL AND "measurementName"<>''

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

-- 2. Count trait names

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

SELECT COUNT(DISTINCT "measurementName") AS traits

FROM "TraitObservation"

WHERE "taxonName" IS NOT NULL AND "taxonName"<>'' AND "measurementName" IS NOT NULL AND "measurementName"<>''

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

-- 3. List trait names

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

SELECT DISTINCT "measurementName" AS trait

FROM "TraitObservation"

WHERE "taxonName" IS NOT NULL AND "taxonName"<>'' AND "measurementName" IS NOT NULL AND "measurementName"<>''

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

-- 4. Count records per trait

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

SELECT "measurementName" AS trait, COUNT(*) AS measurements

FROM "TraitObservation"

WHERE "taxonName" IS NOT NULL AND "taxonName"<>'' AND "measurementName" IS NOT NULL AND "measurementName"<>''

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

-- 5. Count taxa

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

SELECT COUNT(DISTINCT

TRIM(CONCAT_WS(' ',IFNULL("taxonName",''),IFNULL("scientificNameAuthorship",'')))

) AS taxa

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

-- 6. List distinct taxa

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

SELECT DISTINCT

TRIM(CONCAT_WS(' ',IFNULL("taxonName",''),IFNULL("scientificNameAuthorship",'')))

AS taxonwithauthor

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

-- 7. Trait, value and units

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

SELECT "measurementName" AS trait, "measurementValue" AS value, "measurementUnit" AS units

FROM "TraitObservation"

WHERE "taxonName" IS NOT NULL AND "taxonName"<>'' AND "measurementName" IS NOT NULL AND "measurementName"<>''

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

-- 8. "taxonName", trait and value for first 5000 records

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

SELECT

TRIM(CONCAT_WS(' ',IFNULL("taxonName",''),IFNULL("scientificNameAuthorship",'')))

AS taxonwithauthor,

{

	echo_func

	#need explicit schema so matching tables from other schemas are not included

}

fi