...
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" is null and "WEIGHT" is null and "NUMPOUCH" is null and "LENGTH" is null)
Convert 'none detected' value in NUMOBSERVED to 0
The following variables are to be ingested
STRATANR, MACROHABNR, species_name, NUMOBSERVED, MICROHABNR, SPCOMM, SEX, ACTIVITY, PLANTEDRELEASED, NUMNIGHTS, NUMELLIOTTRAP, NUMELLIOTNIGHTS, NUMPITTRAP, NUMPITNIGHTS, NUMCAGETRAP, NUMCAGENIGHTS, ISHARPTRAP, ISMISTNET, ISHAIRTUBE, ISBATDETECTOR
For Fish
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. 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.
...