...
The following logic is required for fish
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.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" = 'F')
The following variables will be ingested:`
species_name, NUMOBSERVED, total_length, disc_length, mantle_length, fork_length, lower_jaw_fork_length, SPCOMM, SEX, WEIGHT, ACTIVITY, PLANTEDRELEASED, FYKENETDUR, FYKENETNR, BAITTRAPHRS, BAITTRAPNUM
Ignore me, working notes
occurrence and survey-based records. For Reptiles
The following
...
logic/joins 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.
...
analyses_length_wide alw on
...
alw."VISITNR"
...
=
...
spp."VISITNR"
...
and
...
alw."
...
SPECIESTYPE"
...
=
...
spp."SPECIESTYPE" and
...
alw."NSXCODE" =
...
Separate records into animal individuals and populations
The surveys contain data for animal individuals (tern_survey_suspecies_animal_individual) and populations (tern_survey_suspecies_animal_population). This needs to be separated into two tables.
The following filter can identify animal individuals
| Code Block |
|---|
where ("SEX" is not null or "TEATS" is not null or "VAGINA" is not null
or "POUCH" is not null or "NUMPOUCH" is not null or "TESTES" is not null or "WEIGHT" is not null
or "SNOUT" is not null or "ISGRAVID" is not null or "LENGTH" is not null) |
Animal populations can be identified by the complement of the filter above (i.e. where the values in the columns above are null).
The following variables will be ingested for individuals, see sections below as NUMTRAP and LENGTH variables need further processing. Also create dipnetused variable where the value is YES when DIPNETHRS is not null.
| Code Block |
|---|
spp."SPECIESTYPE", concat_ws(' ', vs."GENUS", vs."SP") as species_name,
spp."MACROHABNR",spp."MICROHABNR", spp."SPCOMM" ,
spp."SEX", spp."TEATS", spp."VAGINA" , spp."POUCH" , spp."NUMPOUCH" ,
spp."TESTES", spp."WEIGHT" , spp."SNOUT" , spp."ISGRAVID",
spp."LENGTH", spp."LENGTHMETHOD", spp."ACTIVITY",
spp."PLANTEDRELEASED", str."NUMNIGHTS", str."NUMELLIOTTRAPA",
str."NUMELLIOTNIGHTS", str."NUMPITTRAPA", str."NUMPITNIGHTS",
str."NUMCAGETRAPA", str."NUMCAGENIGHTS", str."ISHARPTRAP", str."ISMISTNET",
str."NUMELLIOTTRAPB", str."NUMPITTRAPB", str."NUMCAGETRAPB", str."ISHAIRTUBE",
str."ISBATDETECTOR", str."DIPNETHRS" |
For the animal population table, the following variables will be ingested
| Code Block |
|---|
spp."SPECIESTYPE", concat_ws(' ', vs."GENUS", vs."SP") as species_name,
spp."NUMOBSERVED", spp."MACROHABNR", spp."MICROHABNR", spp."SPCOMM" , spp."SIGHTINGNR" ,
spp."ACTIVITY", spp."PLANTEDRELEASED", str."NUMNIGHTS", str."NUMELLIOTTRAPA",
str."NUMELLIOTNIGHTS", str."NUMPITTRAPA", str."NUMPITNIGHTS",
str."NUMCAGETRAPA", str."NUMCAGENIGHTS", str."ISHARPTRAP", str."ISMISTNET",
str."NUMELLIOTTRAPB", str."NUMPITTRAPB", str."NUMCAGETRAPB", str."ISHAIRTUBE",
str."ISBATDETECTOR", str."DIPNETHRS" |
BIRDS
For individuals, a single record exists and won’t be ingested.
...
spp."NSXCODE" full join bdbsa.sutrap str on str."VISITNR" = sv."VISITNR" where (spp."SPECIESTYPE" = 'R')
The following variables will be ingested:
STRATANR, MACROHABNR, species_name, NUMOBSERVED, total_length, tail_length, SPCOMM, SEX, WEIGHT, ISGRAVID, PLANTEDRELEASED, NUMNIGHTS, NUMELLIOTTRAP, NUMELLIOTNIGHTS, NUMPITTRAP, NUMPITNIGHTS, NUMCAGETRAP, NUMCAGENIGHTS
...