Content Comparison

...

2 fork length
3 lower jaw Fork Length
5 PES Length
6 Head Length
7 Forearm Length

--CREATE TABLE bdbsa.analysis_length AS
SELECT "VISITNR", "SPECIESTYPE","SPSEQNR","NSXCODE","LENGTHMETHOD", "LENGTH"
from bdbsa.suspecies s
where ("LENGTHMETHOD" is not null and "LENGTH" is not null);
--drop TABLE if exists bdbsa.analysis_length;

--CREATEselect EXTENSIONdistinct IF"LENGTHMETHOD"
NOT EXISTS tablefunc;
create table from bdbsa.analysesanalysis_length_wide
ORDER as BY 1;

SELECT *
FROM   crosstab(
   $$SELECT concat("VISITNR", '_', "SPECIESTYPE",'_',"SPSEQNR",'_',"NSXCODE")as row_id,"LENGTHMETHODVISITNR", "SPECIESTYPE","SPSEQNR","NSXCODE","LENGTHMETHOD", "LENGTH" 
    FROM   bdbsa.analysis_length
    ORDER  BY 5$$5;
   
   
 , $$select distinct "LENGTHMETHOD"
from--CREATE EXTENSION IF NOT EXISTS tablefunc;
--create table bdbsa.analysisanalyses_length
ORDER  BY 1$$
_wide as   
SELECT *
FROM   crosstab(
  ) AS$$SELECT concat("VISITNR", float8'_', "SPECIESTYPE" text,'_',"SPSEQNR" float8, ,'_',"NSXCODE") text,
   "total_length" float8,
   "svlength" float8, "tail_length" float8,"disc_length" float8,
   "mantlelength" float8, "carapace_length" float8, "fork" float8,
   "lowerjawfork" float8,"pes_length" float8,"head_length" float8,
   "forearm_length" float8);
--drop TABLE if exists bdbsa.analyses_length_wide;   

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')

1. TEATS column: Values in category 3 need to be lumped into category 2.

2. TESTES column: Values in category 3 need to be lumped into category 2.

3. Variables to be ingested are the following (note: NUMOBSERVED is not ingested as this FOI by definition is one individual): METHODNR, species_name, tail_length, pes_length, forearm_length, SPCOMM, SEX, TEATS, VAGINA, POUCH, TESTES, WEIGHT, PLANTEDRELEASED, NUMNIGHTS, NUMELLIOTTRAPA, NUMELLIOTNIGHTS, NUMPITTRAPA, NUMPITNIGHTS, NUMCAGETRAPA, NUMCAGENIGHTS, ISHARPTRAP, ISMISTNET, NUMELLIOTTRAPB, NUMPITTRAPB, NUMCAGETRAPB, ISHAIRTUBE, ISBATDETECTOR

For birds: there are no individuals.

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 stras row_id,"VISITNR", "SPECIESTYPE","SPSEQNR","NSXCODE","LENGTHMETHOD", "LENGTH" 
    FROM   bdbsa.analysis_length
    ORDER  BY 5$$  
  , $$select distinct "LENGTHMETHOD"
from bdbsa.analysis_length
ORDER  BY 1$$
   ) AS ("row_id" text,"VISITNR" float8, "SPECIESTYPE" text,"SPSEQNR" float8, "NSXCODE" text,
   "total_length" float8,
   "svlength" float8, "tail_length" float8,"disc_length" float8,
   "mantlelength" float8, "carapace_length" float8, "fork" float8,
   "lowerjawfork" float8,"pes_length" float8,"head_length" float8,
   "forearm_length" float8);
--drop TABLE if exists bdbsa.analyses_length_wide;   

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 :

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')

1. TEATS column: Values in category 3 need to be lumped into category 2.

2. TESTES column: Values in category 3 need to be lumped into category 2.

3. Variables to be ingested are the following (note: NUMOBSERVED is not ingested as this FOI by definition is one individual): METHODNR, species_name, tail_length, pes_length, forearm_length, SPCOMM, SEX, TEATS, VAGINA, POUCH, TESTES, WEIGHT, PLANTEDRELEASED, NUMNIGHTS, NUMELLIOTTRAPA, NUMELLIOTNIGHTS, NUMPITTRAPA, NUMPITNIGHTS, NUMCAGETRAPA, NUMCAGENIGHTS, ISHARPTRAP, ISMISTNET, NUMELLIOTTRAPB, NUMPITTRAPB, NUMCAGETRAPB, ISHAIRTUBE, ISBATDETECTOR

For birds: there are no individuals.

For fish:

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

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

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"
left
join bdbsa.suvisitvssp svvs on svvs."VISITNRNSXCODE" = spp."VISITNRNSXCODE"
full JOINjoin bdbsa.supatchsutrap spstr ONon spstr."PATCHIDVISITNR" = sv."PATCHIDVISITNR"
where (spp."SPECIESTYPE" = 'RB';

There should be 4792 records

...

)

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,

...

Occurrence

For birds the following join is needed:

...

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.

...

1. 1. vssp vs on vs."NSXCODE" = spp."NSXCODE" full join bdbsa.sutrap str on str."VISITNR" = sv."VISITNR" where (spp."

...

1. 1. SPECIESTYPE" =

...

and the following columns will be ingested:

...

1. 1. '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 mammals

For mammals, the following joins and logic are required

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 ONON sp."PATCHID" = sv."PATCHID" =where sp(spp."PATCHIDSPECIESTYPE" = 'F' and join bdbsa.suspecies spp on sv."VISITNR" = spp."VISITNR" join bdbsa.vssp vs on vs."NSXCODE" = spp."NSXCODE" full"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)

   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