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,

...

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

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

...

1. The following logic is required for fish

   Code BlockFROM bdbsa.suvisit sv JOIN bdbsa.supatch sp ON sv."PATCHID" = sp."PATCHID" join bdbsa.suspecies spp on sv."VISITNR" = spp."VISITNR"

   1. Code Block

      from suspecies spp 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' 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, PRESENCE, SPCOMM, SEX, WEIGHT, ACTIVITY, PLANTEDRELEASED, FYKENETDUR, FYKENETNR, BAITTRAPHRS, BAITTRAPNUM

...