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 for plants
This single table contains all of the survey- and opportunistic-based measurements on plants and animals.
...
| Code Block |
|---|
select sp."SITEID", sp."QUADSITE", sp."PATCHID", sv."VISITNR", sv."VISITDATE",
svo."PREFIX", svo."VOUCHERNR",--unique identifiers
svo."MUSEUMHERBMNR", --used by ALA
-- svo."VOUCHERTYPE",svo."SPSEQNR", spp."SPECIESNR",
fl."GENUS",fl."SP",
fl."ISP1RANK" , fl."ISP1" , fl."SPAUTHOR",
svo."DETERMINERNR1" , --svo."DETERMINERNR2", determiner 2 is not used
svo."DETERMINEDATE", svo."ACCURACYCODE",--this is for the voucher?
svo."VOUCHERCOMM"
from bdbsa.suvoucher svo
join bdbsa.suspecies spp on spp."SPSEQNR" =svo."SPSEQNR" and spp."VISITNR" = svo."VISITNR"
join bdbsa.flsp fl on spp."SPECIESNR" = fl."SPECIESNR"
join bdbsa.suvisit sv on sv."VISITNR" =svo."VISITNR"
join bdbsa.supatch sp on sp."PATCHID" = sv."PATCHID"
where substring("MUSEUMHERBMNR" , '[A-Z]{1,}') = 'AD'
order by "VOUCHERTYPE" |
SUSPECIES for animals
The suspecies tables for animals will be divided into occurrence and survey-based records. The following joins are needed for this data (where SITETYPE = ‘OP’ for opportunistic and ‘SU’ for survey-based methods). Only records with certainty confirmed will be exposed, see where statement below.
| Code Block |
|---|
JOIN bdbsa.supatch sp ON sv."PATCHID" = sp."PATCHID"
join bdbsa.suspecies spp on sv."VISITNR" = spp."VISITNR"
join bdbsa.vssp vs on vs."NSXCODE" = spp."NSXCODE"
join bdbsa.sutrap str on str."VISITNR" = sv."VISITNR"
where (sp."SITETYPE" = 'OP' and ISCERTAIN = 'Y')
|
NUMTRAP variables
NUMTRAP columns, columns with the following syntax NUMXXXXTRAPA and NUMXXXXTRAPB need to be added by trap type by row, e.g. sum(NUMELLIOTTRAPA, NUMELLIOTTRAPB).
These variables are stored at the site-visit level. However, they apply only to particular animal groups,i.e. not all NUMTRAP columns apply to all animal observations done during a site visit.
The NUMTRAP variables are
Sum of NUMtrap A and B
Logic for which sampling belongs to which kind of animal
Expand LENGTH and LENGTHNR
Expand (pivot wider) column length method as column names and length column as their value. The column names for the LENTHNR column are below:
1 Total Length
10 Snout to Vent Length
12 Tail Length
14 Mantle Length
15 Carapace Length
2 Fork Length
3 Lower Jaw Fork Length (FISH)
5 PES Length(Mammal)
6 Head Length (Mammal)
7 Forearm Length (mammal
strata
34 < 0.5m
35 0.5m-5m
36 > 5m