Below is the overview of the alignment between TERN and BDBSA. The alignment will be broken by collection type, starting with plant records on the survey collection (appended with “SU” on tables). Individual data models and preprocessing steps will be displayed below.
SUSPECIES
This single table contains all of the survey- and opportunistic-based measurements on plants and animals.
For filtering the records into survey and observation-based the following joins are needed, table name convention would be tern_survey_suspecies
JOIN bdbsa.supatch sp ON sv."PATCHID" = sp."PATCHID" join bdbsa.suspecies spp on sv."VISITNR" = spp."VISITNR"
For getting the full scientific name
--This are the columns needed
concat_ws(' ', fl."GENUS",fl."SP",fl."ISP1RANK",fl."ISP1",fl."ISP2RANK",
fl."ISP2",fl."ISP3RANK",fl."ISP3",fl."SPAUTHOR",fl."ISP1AUTHOR",
fl."ISP2AUTHOR",fl."ISP3AUTHOR") as species_name,
--and the join is made on the suspecies and flsp tables
join bdbsa.flsp fl on fl."NSXCODE" = spp."NSXCODE"
Preingestion processing of targeted tables
The SUSPECIES table combines three different FOI’s and data types for plants:
survey based data for plant species which are observed on plots
opportunistic samples of plant occurrences which are plot-less
Because of this the data will be separated on two tables for processing.
Survey columns
Select data for survey only
bdbsa.supatch."SITETYPE" = 'SU'
2. Separate “PLANTEDRELEASED” and species_name columns into “PLANTEDRELEASED_tree” and “species_name_tree”
"DBH" is not null
and “PLANTEDRELEASED_population” and “species_name_population”
"DBH" is null
Add Comment