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 flvnonsynnotren tables
join bdbsa.flvnonsynnotren 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 populations which are observed on plots
survey based data for plant individuals which are observed on plots
opportunistic samples of plant occurrences that are plot-less
Because of this and rows in the mapping have a 1:1 relationship with an FoI (i.e. a parameter cannot belong to more than one FoI), the data will be separated into three tables for processing: survey-plant population, survey-plant individual and opportunistic-plant occurrence.
Columns from other tables shared across FOI’s
suplantlfstage has to be join to the suspecies table
join bdbsa.suplantlfstage s on s."SPSEQNR"= spp."SPSEQNR" and s."VISITNR" = sv."VISITNR"
This table contains two parameters that are shared across plant opportunistic, population and individual FOI’s.
Notice that the following column need to be created for occurrence, individuals and populations
“LIFESTAGECODE”. The following conversion needs to be done for the “LIFESTAGECODE” included in the database
DE category has to be converted to D
N category has to be converted to V
SP needs to be converted to X
RE to V (Note while the RE category can become either Vegetative or Regenerating the plants listed as RE in the Database only contain many herbaceous annual species suggesting that they must be in a vegetative state, N of record under RE = 15)
Survey columns individuals
Select data for survey only
bdbsa.supatch."SITETYPE" = 'SU'
2. Select data for individuals using DBH as key
"DBH" is not null
3 . For individuals the following variables from the suspecies table will be ingested:
PLANTEDRELEASED_individual, CROWN_EXTENT_PERC, CROWN_EXTENT_SCORE, CROWN_DENSITY_PERC, CROWN_DENSITY_SCORE, DBH, NEW_TIP_GROWTH, EPICORMIC_GROWTH, EXTENT_REPRODUCTION, MISTLETOE_LOAD, LEAF_DIE_OFF, LEAF_DAMAGE, EXTENT_BARK_CRACKING
Notice that the following columns need to be created for both individuals and populations “PLANTEDRELEASED” and species_name columns into “PLANTEDRELEASED_tree” and “species_name_tree”and “PLANTEDRELEASED_population” and “species_name_population”
4. The scientific name of the species comes from matchin the NSXCODE column from suspecies to flvnonsynnotren table.
Opportunistic columns
Select data for opportunistic only
bdbsa.supatch."SITETYPE" = 'OP'
Opportunistic records may or may not have a site name (parent site). These records do not belong to a quadrat. All of these records have been assigned a “PATCHID” however a site sensu TERN data ontology does not exist. In terms of the ontology, these records are plot-less
2. For these FOI, a “tern_opportunistic_suspecies” data has to be created and the following columns from the suspecies table need to be included:
NUMOBSERVED,PLANTEDRELEASED_occurence and species_occurrence
3. LOCMETHODNR and RELIABNR are attributes of the coordinates. In the first case it is an instrument that varies by latitude,longitude combination. In the second case is a reliability value for the coordinates that comes in a lookuptable lureliab
4. Only two site attributes have enough information for this ingestion: GENLOC, LOCCOMM. PROPERTY and VISITCOMM may also be added but more than 76% are null.
Add Comment