Content Comparison

...

The following variables need to be ingested: METHODNR, SPCOMM, SEX, WEIGHT, PLANTEDRELEASED, total_length, svlength, fork, FYKENETDUR, FYKENETNR

Occurrence

For birds the following join is For invertebrates:

The following tables are needed:

JOINfrom bdbsa.supatch sp ON sv."PATCHID" = sp."PATCHID" 
suspecies spp
join bdbsa.suspecies sppanalyses_length_wide alw on svalw."VISITNR" = spp."VISITNR" joinand bdbsa.vssp vs on vs."NSXCODEalw."SPECIESTYPE"= spp."SPECIESTYPE" and alw."SPSEQNR" = spp."NSXCODESPSEQNR" fulland alw."NSXCODE" =spp."NSXCODE"
left join bdbsa.sutrap str on str."VISITNR" = spp."VISITNR"
left join bdbsa.suvisit sv on sv."VISITNR" = spp."VISITNR" 
JOIN bdbsa.supatch sp ON sp."PATCHID" = sv."PATCHID"
where (spp."SPECIESTYPE" = 'B')

and the following columns will be ingested:

...

I';

The following variables need to be ingested: METHODNR, SPCOMM, ACTIVITY, PLANTEDRELEASED, total_length, mantle_length, carapace_length, FYKENETDUR, FYKENETNR, BAITTRAPHRS, BAITTRAPNUM

Occurrence

For birds the following join is needed:

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" = '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

1. For mammals, the following joins and logic are required

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

2. Convert 'none detected' value in NUMOBSERVED to 0

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

...

Most of the variables after PLANTEDRELEASEDmay be removed, this needs to be confirmed.

For mammals

1. For mammals, the following joins and logic are required

   Code BlockFROM bdbsa.suvisit sv JOIN bdbsa.supatch sp ON sv."PATCHID" = sp."PATCHID"

For Fish

1. The following logic is required for fish

   1. Code Block

      from suspecies spp left join bdbsa.suspeciessutrap sppstr on svstr."VISITNR" = spp."VISITNR" left join bdbsa.vssp vs on vs."NSXCODE" = spp."NSXCODE" full join bdbsa.sutrap str on str."VISITNR" =suvisit sv on sv."VISITNR" = where (spp."SPECIESTYPEVISITNR" = 'M'left andJOIN "TEATS" is null and "VAGINA" is null and "POUCH" is null and "TESTES" is null and "WEIGHT" is null and "NUMPOUCHbdbsa.supatch sp ON sp."PATCHID" = sv."PATCHID" where (spp."SPECIESTYPE" = 'F' and "LENGTH" is null and "LENGTHWEIGHT" is null)

   Convert 'none detected' value in NUMOBSERVED to 0

   1. ;

2. The following variables are to will be ingestedSTRATANR, MACROHABNR, : species_name, NUMOBSERVED, MICROHABNRPRESENCE, SPCOMM, SEX, ACTIVITY, PLANTEDRELEASED, NUMNIGHTSFYKENETDUR, NUMELLIOTTRAPFYKENETNR, NUMELLIOTNIGHTS, NUMPITTRAP, NUMPITNIGHTS, NUMCAGETRAP, NUMCAGENIGHTS, ISHARPTRAP, ISMISTNET, ISHAIRTUBE, ISBATDETECTOR

...

1. BAITTRAPHRS, BAITTRAPNUM

For Reptiles

1. The following logic is /joins are required for fish

   1. Code Block
      from suspecies spp left join bdbsa.sutrap str on str."VISITNR" = spp."VISITNR" left join bdbsa.suvisit sv on sv."VISITNR" = spp."VISITNR" left JOIN bdbsa.supatch sp ON sp."PATCHID" = sv."PATCHID" where (spp."SPECIESTYPE" = 'FR' and "LENGTH" is null and "WEIGHT" is null);

2. The following variables will be ingested: ingested: STRATANR, METHODNR, MACROHABNR, MICROHABNR, MACROHABNR, species_name, NUMOBSERVED, PRESENCE, SPCOMM, SEX, ACTIVITYISGRAVID, PLANTEDRELEASED, FYKENETDUR, FYKENETNR, BAITTRAPHRS, BAITTRAPNUM

...

1. , NUMNIGHTS, NUMELLIOTTRAP, NUMELLIOTNIGHTS, NUMPITTRAP, NUMPITNIGHTS, NUMCAGETRAP, NUMCAGENIGHTS

For Amphibians

1. The following logic /and joins are required

   1. Code Block
      from suspecies spp left join bdbsa.sutrap str on str."VISITNR" = spp."VISITNR" left join bdbsa.suvisit sv on sv."VISITNR" = spp."VISITNR" left JOIN bdbsa.supatch sp ON sp."PATCHID" = sv."PATCHID" where (spp."SPECIESTYPE" = 'RA' and "LENGTH" is null and "WEIGHT" is null);

2. The following variables will be ingested: STRATANRNUMOBSERVED, METHODNR, STRATANR, MACROHABNR, MICROHABNR, MACROHABNR, species_name, NUMOBSERVED, PRESENCE, SPCOMM, SEX, ISGRAVID, PLANTEDRELEASED, NUMNIGHTS, NUMELLIOTTRAP, NUMELLIOTNIGHTS, NUMPITTRAP, NUMPITNIGHTS, NUMCAGETRAP, NUMCAGENIGHTS, FYKENETDUR, FYKENETNR, BAITTRAPHRS, BAITTRAPNUM, DIPNETHRS, dipnetused

For AmphibiansInvertebrates

1. The following logic and joins are required

   1. Code Block
      from suspecies spp left join bdbsa.sutrap str on str."VISITNR" = spp."VISITNR" left join bdbsa.suvisit sv on sv."VISITNR" = spp."VISITNR" left JOIN bdbsa.supatch sp ON sp."PATCHID" = sv."PATCHID" where (spp."SPECIESTYPE" = 'AI' and "LENGTH" is null and "WEIGHT" is null);

2. The following variables will be ingested: NUMOBSERVED, METHODNR, STRATANR, MACROHABNR, MICROHABNR, SPCOMMSPCOMM, ACTIVITY, PLANTEDRELEASED, NUMNIGHTS, NUMELLIOTTRAP, NUMELLIOTNIGHTS, NUMPITTRAP, NUMPITNIGHTS, NUMCAGETRAP, NUMCAGENIGHTS, FYKENETDUR, FYKENETNR, BAITTRAPHRS, BAITTRAPNUM, DIPNETHRS, dipnetused