Content Comparison

...

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:

JOIN bdbsa.supatch sp ON sv."PATCHID" = sp."PATCHID" str."FYKENETHRS", str."BAITTRAPHRS", str."BAITTRAPNUM", str."DIPNETHRS"
from suspecies spp
join bdbsa.suspecies sppanalyses_length_wide alw on svalw."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."SPECIESTYPEVISITNR" 
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

For invertebrates:

The following tables are needed:

from suspecies spp
join bdbsa.analyses_length_wide alw on alw."VISITNR"= spp."VISITNR" and alw."SPECIESTYPE"= spp."SPECIESTYPE" and alw."SPSEQNR" = spp."SPSEQNR" and 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" = '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

For Fish

1. The following logic is 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" = 'F' and "LENGTH" is null and "WEIGHT" is null);

2. The following variables will be ingested: species_name, NUMOBSERVED, PRESENCE, SPCOMM, SEX, ACTIVITY, PLANTEDRELEASED, FYKENETDUR, FYKENETNR, BAITTRAPHRS, BAITTRAPNUM

For Reptiles

1. The following logic/joins are required

   1. Code Block

      from suspecies spp left join bdbsa.sutrap str on str."VISITNR" = svspp."VISITNR" left wherejoin (spp."SPECIESTYPE" = 'M' and "TEATS" is null and "VAGINA" is null and "POUCH" is null and "TESTES" is null and "WEIGHT" is null and "NUMPOUCHbdbsa.suvisit sv on sv."VISITNR" = spp."VISITNR" JOIN bdbsa.supatch sp ON sp."PATCHID" = sv."PATCHID" where (spp."SPECIESTYPE" = 'R' and "LENGTH" is null and "LENGTHWEIGHT" is null)

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

3. 1. ;

4. The following variables are to will be ingested: STRATANR, METHODNR, MACROHABNR, MICROHABNR, MACROHABNR, species_name, NUMOBSERVED, MICROHABNRPRESENCE, SPCOMM, SEX, ACTIVITYISGRAVID, PLANTEDRELEASED, NUMNIGHTS, NUMELLIOTTRAP, NUMELLIOTNIGHTS, NUMPITTRAP, NUMPITNIGHTS, NUMCAGETRAP, NUMCAGENIGHTS, ISHARPTRAP, ISMISTNET, ISHAIRTUBE, ISBATDETECTOR

For FishAmphibians

1. The following logic is and 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" = 'FA' and "LENGTH" is null and "WEIGHT" is null);

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

For Reptiles

There should be 131,147 records

Invertebrates

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" = 'RI' and "LENGTH" is null and "WEIGHT" is null);

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