Versions Compared

Version Old Version 34 New Version 35
Changes made by

Habacuc FM

Habacuc FM

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

  1. Variables to be ingested are the following: METHODNR, STRATANR, MACROHABNR, MICROHABNR, SPCOMM, SEX, WEIGHT, ISGRAVID, PLANTEDRELEASED, total_length, SNOUT, tail_length, turtle_carapace_length, NUMNIGHTS, NUMELLIOTTRAP, NUMELLIOTNIGHTS, NUMPITTRAP, NUMPITNIGHTS, NUMCAGETRAP, NUMCAGENIGHTS, ISHARPTRAP, ISMISTNET, FYKENETDUR, FYKENETNR

Occurrence

For birds the following join is For amphibian:

The following tables are needed:

Code Block
JOIN bdbsa.supatch sp ON sv."PATCHID" = sp."PATCHID" 
join bdbsa.suspecies spp on sv."VISITNR" str."FYKENETHRS", str."BAITTRAPHRS", str."BAITTRAPNUM", str."DIPNETHRS"
from suspecies spp
join bdbsa.analyses_length_wide alw on alw."VISITNR"= spp."VISITNR" joinand bdbsa.vssp vs on vsalw."SPECIESTYPE"= spp."SPECIESTYPE" and alw."SPSEQNR" = spp."SPSEQNR" and alw."NSXCODE" = spp."NSXCODE"
fullleft join bdbsa.sutrap str on str."VISITNR" = spp."VISITNR"
left join bdbsa.suvisit sv on sv."VISITNR" where= (spp."SPECIESTYPEspp."VISITNR" 
JOIN bdbsa.supatch sp ON sp."PATCHID" = 'B')

...

sv."PATCHID"
where spp."SPECIESTYPE" = 'A';

The following variables need to be ingested: METHODNR, species_name, STRATANR, NUMOBSERVED, MICROHABNR, MACROHABNR, SPCOMM, SEX, ACTIVITYWEIGHT, PLANTEDRELEASED, NUMNIGHTStotal_length, NUMELLIOTTRAPsvlength, NUMELLIOTNIGHTSfork, 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

...

FYKENETDUR, FYKENETNR

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

For Fish

  1. The following logic is required for fish

    1. Code Block
      from suspecies spp
left 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 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" = 'F' and "LENGTH" is null and "LENGTHWEIGHT" is null)

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

    1. ;

  4. 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: be ingested: STRATANR, METHODNR, MACROHABNR, MICROHABNR, MACROHABNR, species_name, NUMOBSERVED, PRESENCE, SPCOMM, SEX, ACTIVITYISGRAVID, PLANTEDRELEASED, FYKENETDUR, FYKENETNR, BAITTRAPHRS, BAITTRAPNUM

For Reptiles

There should be 131,147 records

  1. The following logic/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