Content Comparison

...

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

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

...

1. survey based data for plant populations which are observed on plots

2. survey based data for plant individuals which are observed on plots

3. opportunistic samples of plant occurrences that are plot-less

4. 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.

Columns from other tables shared across FOI’s

suplantlfstage has to be join to the suspecies table

join bdbsa.suplantlfstage s on s."SPSEQNR"= spp."SPSEQNR" and s."VISITNR" = sv."VISITNR"

This table contains two parameters that are shared across plant opportunistic, population and individual FOI’s.

Notice that the following column need to be created for occurrence, individuals and populations

“LIFESTAGECODE”. The following conversion needs to be done for the “LIFESTAGECODE” included in the database

...

DE category has to be converted to D

...

N category has to be converted to V

...

SP needs to be converted to X

...

NUMOBSERVED column

This column whenever present will be separated into two variables:

NUMOBSERVED: all numeric values >0 will be kept in NUMOBSERVED. 0 values and Non-numeric values will be transformed to NULL values

PRESENCE: This column will have two values “none detected“ and “present”. The “present” value will be all numeric values > 0 in the NUMOBSERVED column as well as the several, present but not countedand Present but not counted values. “none detected” will be all the 0 and none detected values in the NUMOBSERVED column.

Columns from other tables shared across FOI’s

suplantlfstage has to be join to the suspecies table

join bdbsa.suplantlfstage s on s."SPSEQNR"= spp."SPSEQNR" and s."VISITNR" = sv."VISITNR"

This table contains two parameters that are shared across plant opportunistic, population and individual FOI’s.

Notice that the following column need to be created for occurrence, individuals and populations

“LIFESTAGECODE”. The following conversion needs to be done for the “LIFESTAGECODE” included in the database

1. DE category has to be converted to D

2. N category has to be converted to V

3. SP needs to be converted to X

4. RE to V (Note while the RE category can become either Vegetative or Regenerating the plants listed as RE in the Database only contain many herbaceous annual species suggesting that they must be in a vegetative state, N of record under RE = 15)

...

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 ISFIREYRCERTAIN(note that ? values need to be remapped to Y)

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

STRFORMATIONNR

Plant functional type ingestion

...

All records have to be filtered by ISCERTAIN = YSeparate

NUMOBSERVED column

...

Individuals

1. Pivot length and lengthmethod columns

First, the length and length method have to be pivot to a wider format.

Expand (pivot wider) column LENGTHNR as column names and LENGTH column as values to fill these columns. The column names for the LENTHNR column are below, as well as an example of the query:

...

This column whenever present will be separated into two variables:

NUMOBSERVED: all numeric values >0 will be kept in NUMOBSERVED. 0 values and Non-numeric values will be transformed to NULL values

PRESENCE: This column will have two values “none detected“ and “present”. The “present” value will be all numeric values > 0 in the NUMOBSERVED column as well as the several, present but not countedand Present but not counted values. “none detected” will be all the 0 and none detected values in the NUMOBSERVED column.

Individuals

1. Pivot length and lengthmethod columns

First, the length and length method have to be pivot to a wider format.

Expand (pivot wider) column LENGTHNR as column names and LENGTH column as values to fill these columns. The column names for the LENTHNR column are below, as well as an example of the query:

1 body length
10 snout to vent length (does not need to be ingested as it already exist as SNOUT)
12 tail Length
14 mantle Length
15 invertebrate carapace length (if species type I)

...

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

SELECT *
FROM   crosstab(
   $$SELECT "VISITNR", "SPECIESTYPE","NSXCODE","LENGTHMETHOD", "LENGTH" 
    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 :

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

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

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 :

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"
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"

...

...

1. 1. join bdbsa.

...

1. 1. suspecies spp on

...

1. 1. sv."VISITNR" = spp."VISITNR"

...

1. 1. join

...

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

...

1. 1. TEATS" is

...

1. 1. null

...

1. 1. and "

...

1. 1. VAGINA" is

...

1. 1. null

...

1. 1. and "

...

1. 1. POUCH" is

...

1. 1. null

...

1. 1. and "

...

1. 1. TESTES" is

...

1. 1. null

...

1. 1. and "

...

1. 1. WEIGHT" is

...

1. 1. null

...

1. 1. and "

...

1. 1. NUMPOUCH" is

...

1. 1. null

...

1. 1. and "

...

1. 1. LENGTH" is

...

1. 1. null

...

1. 1. )

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

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

4. Variables to be ingested are the following (note: NUMOBSERVED is not ingested as this FOI by definition is one individual): METHODNRConvert 'none detected' value in NUMOBSERVED to 0

5. The following variables are to be ingested STRATANR, MACROHABNR, species_name, tail_length, pes_length, forearm_lengthNUMOBSERVED, MICROHABNR, SPCOMM, SEX, TEATS, VAGINA, POUCH, TESTES, WEIGHT, ACTIVITY, PLANTEDRELEASED, NUMNIGHTS, NUMELLIOTTRAPANUMELLIOTTRAP, NUMELLIOTNIGHTS, NUMPITTRAPANUMPITTRAP, NUMPITNIGHTS, NUMCAGETRAPANUMCAGETRAP, NUMCAGENIGHTS, ISHARPTRAP, ISMISTNET, NUMELLIOTTRAPB, NUMPITTRAPB, NUMCAGETRAPB, ISHAIRTUBE, ISBATDETECTOR

For birds: there is no individuals.

Occurrence

For birds the following join is needed:

...

Fish

1. The following logic is required for fish

   1. Code Block
      from suspecies spp left join bdbsa.sutrap str on str."VISITNR" =

...

1. 1. spp."

...

1. 1. VISITNR" left join bdbsa.

...

1. 1. suvisit

...

1. 1. sv on sv."VISITNR" = spp."VISITNR"

...

1. 1. left JOIN bdbsa.

...

1. 1. supatch

...

1. 1. sp

...

1. 1. ON

...

1. 1. sp."

...

1. 1. PATCHID" =

...

1. 1. sv."

...

1. 1. PATCHID"

...

and the following columns will be ingested:

...

1. 1. 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,

...

1. FYKENETDUR,

...

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" = spp."VISITNR"

   FYKENETNR, BAITTRAPHRS, BAITTRAPNUM

For Reptiles

1. The following logic/joins are required

   1. 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)

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 bdbsa.suvisit sv JOIN bdbsa.supatch sp ON sv."PATCHID" = sp."PATCHID" join bdbsa.suspecies spp on sv."VISITNR" = spp."VISITNR"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 bdbsa.sutrap str on str."VISITNR left JOIN bdbsa.supatch sp ON sp."PATCHID" = 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, PLANTEDRELEASEDNUMOBSERVED, METHODNR, STRATANR, MACROHABNR, MICROHABNR, SPCOMM, PLANTEDRELEASED, NUMNIGHTS, NUMELLIOTTRAP, NUMELLIOTNIGHTS, NUMPITTRAP, NUMPITNIGHTS, NUMCAGETRAP, NUMCAGENIGHTS, FYKENETDUR, FYKENETNR, BAITTRAPHRS, BAITTRAPNUM, DIPNETHRS, dipnetused

For ReptilesInvertebrates

1. The following logic /and joins 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"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);

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