Content Comparison

...

For filtering the records into survey and observation-based the following joins are needed, table name convention would be tern_survey_suspeciessuspeciesY

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

...

survey based data for plant populations which are observed on plots
survey based data for plant individuals which are observed on plots
opportunistic samples of plant occurrences that are plot-less
All records need to be filtered by ISCERTAIN = Y

Because of this and rows in the mapping have a 1:1 relationship with an FoI (i.e. a parameter cannot belong to more than one FoI), the data will be separated into three tables for processing: survey-plant population, survey-plant individual and opportunistic-plant occurrence.

...

Code Block

select "PATCHID" , sv."VISITNR" , "VISITDATE" ,
 "ISFIRESCARS" , "LASTFIREYEAR" , "ISFIREYRCERTAIN" ,
 sv."STRFORMATIONNR", lstr."STRFORMATIONDESC", 
 lstr."STRLIFEFORMNR" ,lstr."PROJFOLCOVERNR"
 from bdbsa.suvisit sv
 join bdbsa.lustrformation lstr ON lstr."STRFORMATIONNR" = sv."STRFORMATIONNR"

While all these variables will be ingested until review only Only the following variables will be mapped:

LASTFIREYEAR (note that ? values need to be remapped to Y)

ISFIREYRCERTAIN (note that ? values need to be remapped to Y)

STRFORMATIONNR

Plant functional type ingestion

...

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

Code Block

SELECT--CREATE *
FROM   crosstab(
   $$SELECT "VISITNR", "SPECIESTYPETABLE 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 FROMTABLE if exists bdbsa.analysis_length;

select distinct "LENGTHMETHOD"
from bdbsa.analysis_length
ORDER  BY 1,2$$;

-- could also just be "ORDER BY 1" here
  , $$select distinct "LENGTHMETHOD"
from bdbsa.analysis_length
ORDER  BY 1$$
   ) AS ("VISITNR" float8, "SPECIESTYPE" text, "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);

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 :

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"
join bdbsa.analyses_length_wide alw on alw."VISITNR"= spp."VISITNR" and alw."SPECIESTYPE"= spp."SPECIESTYPE" and alw."NSXCODE" =spp."NSXCODE" 
full join bdbsa.sutrap str on str."VISITNR" = sv."VISITNR"
where (spp."SPECIESTYPE" = 'M' and 
"LENGTH" is not null 
or "TEATS" is not null 
or "VAGINA" is not null 
or "POUCH" is not null
or "TESTES" is not null
or "WEIGHT" is not null
or "NUMPOUCH" is not null
)

TEATS column: Values in category 3 need to be lumped into category 2.
TESTES column: Values in category 3 need to be lumped into category 2.
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 is no individuals.

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:

...

SELECT concat("VISITNR", '_', "SPECIESTYPE",'_',"SPSEQNR",'_',"NSXCODE")as row_id,"VISITNR", "SPECIESTYPE","SPSEQNR","NSXCODE","LENGTHMETHOD", "LENGTH" 
    FROM   bdbsa.analysis_length
    ORDER  BY 5;
   
   
--CREATE EXTENSION IF NOT EXISTS tablefunc;
--create table bdbsa.analyses_length_wide as   
SELECT *
FROM   crosstab(
   $$SELECT concat("VISITNR", '_', "SPECIESTYPE",'_',"SPSEQNR",'_',"NSXCODE") as 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 :

Code Block

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

TEATS column: Values in category 3 need to be lumped into category 2.
TESTES column: Values in category 3 need to be lumped into category 2.
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:

Code Block

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

Code Block

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

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:

Code Block

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:

Code Block

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:

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

For mammals, the following joins and logic are required

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)

Convert 'none detected' value in NUMOBSERVED to 0
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

...

Most of the variables after PLANTEDRELEASEDmay be removed, this needs to be confirmed.

For mammals

For mammals, the following joins and logic are required
Code BlockFROM

For Fish

The following logic is required for fish

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" = sp."PATCHID"  
join bdbsa.suspecies spp on sv."VISITNR" = spp."VISITNR" 
where (spp."SPECIESTYPE" = 'F' and "LENGTH" is null and "WEIGHT" is null);

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

For Reptiles

The following logic/joins are required

Code Block

from suspecies spp
left join bdbsa.vsspsutrap vsstr on vsstr."NSXCODEVISITNR" = spp."NSXCODEVISITNR"

fullleft join bdbsa.sutrapsuvisit strsv on strsv."VISITNR" = svspp."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" 
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

;

The following variables are to will be ingestedSTRATANR: 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

The following logic is and joins are 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"

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."NSXCODE" 
full join
left JOIN bdbsa.sutrapsupatch strsp onON strsp."VISITNRPATCHID" = sv."VISITNRPATCHID"
where (spp."SPECIESTYPE" = 'F')A' and "LENGTH" is null and "WEIGHT" is null);

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, PLANTEDRELEASEDNUMOBSERVED, METHODNR, STRATANR, MACROHABNR, MICROHABNR, SPCOMM, PLANTEDRELEASED, NUMNIGHTS, NUMELLIOTTRAP, NUMELLIOTNIGHTS, NUMPITTRAP, NUMPITNIGHTS, NUMCAGETRAP, NUMCAGENIGHTS, FYKENETDUR, FYKENETNR, BAITTRAPHRS, BAITTRAPNUM, DIPNETHRS, dipnetused

For ReptilesInvertebrates

The following logic /and joins are required

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

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."NSXCODE" 
full join
left JOIN bdbsa.sutrapsupatch strsp onON strsp."VISITNRPATCHID" = sv."VISITNRPATCHID"
where (spp."SPECIESTYPE" = 'R')I' and "LENGTH" is null and "WEIGHT" is null);

The following variables will be ingested: STRATANR, MACROHABNR, species_name, NUMOBSERVED, total_length, tail_lengthMETHODNR, SPCOMM, SEXACTIVITY, WEIGHT, ISGRAVID, PLANTEDRELEASED, NUMNIGHTS, NUMELLIOTTRAP, NUMELLIOTNIGHTS, NUMPITTRAP, NUMPITNIGHTS, NUMCAGETRAP, NUMCAGENIGHTS, FYKENETDUR, FYKENETNR, BAITTRAPHRS, BAITTRAPNUM, DIPNETHRS

Version	Old Version 27	New Version Current
Changes made by	Habacuc FM (Unlicensed)	Habacuc FM (Unlicensed)
Saved on	19 Aug 2022	23 Aug 2022

Versions Compared

Key

Plant functional type ingestion

Occurrence

Occurrence