Content Comparison

...

2. For individuals the following joins and logic are required, this step differs across species. Invertebrates and birds are excluded as they only comprise 3 records for this FOI.

For mammals :

There should be 13,445 records

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

...

For birds: there are no individuals.

For reptilesfish:

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

There should be 4792 records

Variables to be ingested are

...

as follow:

species_name, METHODNR, SPCOMM, SEX, WEIGHT,

...

ACTIVITY, PLANTEDRELEASED, total_length,

...

disc_length,

...

Occurrence

For birds the following join is needed:

...

fork, lowerjawfork, FYKENETDUR, FYKENETNR, BAITTRAPHRS, BAITTRAPNUM

For reptiles:

The following tables are needed

from suspecies spp
join bdbsa.analyses_length_wide alw on alw."VISITNR"= spp."VISITNR" joinand bdbsa.vssp vs on vs."NSXCODEalw."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" = spp."VISITNR" 
JOIN bdbsa.supatch sp ON sp."PATCHID" = sv."PATCHID"
where (spp."SPECIESTYPE" = 'BR')

...

;

1. Variables to be ingested are the following

...

1. :

...

1. METHODNR,

...

1. STRATANR,

...

1. MACROHABNR, MICROHABNR,

...

1. SPCOMM, SEX,

...

1. WEIGHT, ISGRAVID, PLANTEDRELEASED, total_length, SNOUT, tail_length, turtle_carapace_length, NUMNIGHTS, NUMELLIOTTRAP, NUMELLIOTNIGHTS, NUMPITTRAP, NUMPITNIGHTS, NUMCAGETRAP, NUMCAGENIGHTS, ISHARPTRAP, ISMISTNET,

...

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" join bdbsa.suspecies spp on sv."VISITNR"

   FYKENETDUR, FYKENETNR

For amphibian:

The following tables are needed:

str."FYKENETHRS", str."BAITTRAPHRS", str."BAITTRAPNUM", str."DIPNETHRS"
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" = 'A';

The following variables need to be ingested: METHODNR, SPCOMM, SEX, WEIGHT, PLANTEDRELEASED, total_length, svlength, fork, 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" = spp."VISITNR" left join bdbsa.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)

   Convert 'none detected' value in NUMOBSERVED to 0

   1. );

2. 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 bdbsa.suvisit sv JOIN bdbsa.supatch sp ON sv."PATCHID" = sp."PATCHID" join bdbsa.from suspecies spp on sv."VISITNR" = spp."VISITNR" left join bdbsa.vsspsutrap vsstr on vsstr."NSXCODEVISITNR" = spp."NSXCODEVISITNR" fullleft join bdbsa.analyses_length_wide alwsuvisit sv on alwsv."VISITNR"= spp."VISITNR" and alw."SPECIESTYPE"= spp."SPECIESTYPE" and alw."NSXCODE" = spp."NSXCODEVISITNR" fullleft joinJOIN bdbsa.sutrapsupatch strsp onON strsp."VISITNRPATCHID" = sv."VISITNRPATCHID" where (spp."SPECIESTYPE" = 'F')A' and "LENGTH" is null and "WEIGHT" is null);

2. 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, PLANTEDRELEASEDMETHODNR, 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