...
2. For individuals the following joins and logic are required, this step is the same across species just changing the species type per animal group (i.e. M for mammals, R for reptiles, F for fish, A for amphibians)differs across species. Invertebrates and birds are excluded as they only comprise 3 records for this FOI.
For mammals :
| Code Block |
|---|
FROM bdbsa.suvisit sv 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.analyses_length_wide alw on alw."VISITNR"= spp."VISITNR" and alw."SPECIESTYPE"= spp."SPECIESTYPE" and alw."NSXCODE" =spp."NSXCODE" full join bdbsa.sutrap str on str."VISITNR" = sv."VISITNR" where (spp."SPECIESTYPE" = 'M' and "LENGTH" is not null) |
For mammals :
...
TEATS column: Values in category 3 need to be lumped into category 2.
...
TESTES column: Values in category 3 need to be lumped into category 2.
...
or "TEATS" is not null
or "VAGINA" is not null
or "POUCH" is not null
or "TESTES" is not null
or "WEIGHT" is not null
or "NUMPOUCH" is not null
) |
TEATS column: Values in category 3 need to be lumped into category 2.
TESTES column: Values in category 3 need to be lumped into category 2.
Variables to be ingested are the following (note: NUMOBSERVED is not ingested as this FOI by definition is one individual): METHODNR, species_name, tail_length, pes_length, forearm_length, SPCOMM, SEX, TEATS, VAGINA, POUCH, TESTES, WEIGHT, PLANTEDRELEASED, NUMNIGHTS, NUMELLIOTTRAPA, NUMELLIOTNIGHTS, NUMPITTRAPA, NUMPITNIGHTS, NUMCAGETRAPA, NUMCAGENIGHTS, ISHARPTRAP, ISMISTNET, NUMELLIOTTRAPB, NUMPITTRAPB, NUMCAGETRAPB, ISHAIRTUBE, ISBATDETECTOR
For birds: there is no individuals.
Populations
For birds, there is no populations
For populations the following joins and logic are required, this step is the same across species just changing the species type per animal group (i.e. B for Birds, M for mammals, R for reptiles, F for fish, A for amphibians).
...
, NUMELLIOTTRAPB, NUMPITTRAPB, NUMCAGETRAPB, ISHAIRTUBE, ISBATDETECTOR
For birds: there is no individuals.
Occurrence
For birds the following join is needed:
| 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" full join bdbsa.sutrap str on str."VISITNR" = sv."VISITNR" where (spp."VISITNR" = sv."VISITNR" where (spp."SPECIESTYPE" = 'B' and "LENGTH" is null) |
Occurrence
For birds the following join is needed:
| Code Block |
|---|
SPECIESTYPE" = 'B') |
and the following columns will be ingested:
METHODNR, species_name, STRATANR, NUMOBSERVED, MICROHABNR, MACROHABNR, SPCOMM, SEX, ACTIVITY, PLANTEDRELEASED, NUMNIGHTS, NUMELLIOTTRAP, NUMELLIOTNIGHTS, NUMPITTRAP, NUMPITNIGHTS, NUMCAGETRAP, NUMCAGENIGHTS, ISHARPTRAP, ISMISTNET, ISHAIRTUBE, ISBATDETECTOR
Most of the variables after PLANTEDRELEASEDmay be removed, this needs to be confirmed.
For mammals
For mammals, the following joins and logic are required
Code Block FROM bdbsa.suvisit sv 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" full
...
join bdbsa.
...
sutrap str on
...
str."VISITNR" = sv."VISITNR" where (spp.
...
"SPECIESTYPE" = 'M' and "TEATS" is null and "VAGINA" is null and
...
"
...
POUCH"
...
is null and
...
"
...
TESTES"
...
and the following columns will be ingested:
...
is null and "WEIGHT" is null and "NUMPOUCH" is null and "LENGTH" is null)
Convert 'none detected' value in NUMOBSERVED to 0
Ignore me, working notes
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.
...