Skip to end of metadata
Go to start of metadata

You are viewing an old version of this content. View the current version.

Compare with Current View Version History

« Previous Version 7 Next »

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:

  1. survey based data for plant populations which are observed on plots

  2. survey based data for plant individuals which are observed on plots

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

Survey columns

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

Opportunistic columns

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

  • No labels