Content Comparison

...

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.

...

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

SELECTCREATE *
FROM   crosstab(
   $$SELECTTABLE bdbsa.analysis_length AS
SELECT "VISITNR", "SPECIESTYPE","SPSEQNR","NSXCODE","LENGTHMETHOD", "LENGTH"
from bdbsa.suspecies s
where ("LENGTHMETHOD" FROMis not null bdbsa.analysis_length
    ORDER  BY 1,2$$and "LENGTH" is not null);
--drop TABLE if exists bdbsa.analysis_length;

--CREATE couldEXTENSION alsoIF justNOT beEXISTS "ORDERtablefunc;
BYcreate 1" here
table bdbsa.analyses_length_wide as  , $$select
distinctSELECT "LENGTHMETHOD"*
fromFROM bdbsa.analysis_length ORDER crosstab(
BY 1$$    ) AS$$SELECT ("VISITNR" float8, "SPECIESTYPE","SPSEQNR" text, "NSXCODE","LENGTHMETHOD" text,
   "total_lengthLENGTH" 
float8,    "svlength"FROM float8, "tail_length" float8,"disc bdbsa.analysis_length"
float8,    "mantlelength"ORDER float8, "carapace_length" float8, "fork" float8,
   "lowerjawfork" float8,"pes_length" float8,"head_length" float8 BY 5$$  
  , $$select distinct "LENGTHMETHOD"
from bdbsa.analysis_length
ORDER  BY 1$$
   ) AS ("VISITNR" float8, "SPECIESTYPE" text,"SPSEQNR" float8, "NSXCODE" text,
   "forearmtotal_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 :

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

...

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

There should be 4792 records

1. Variables to be ingested are the following: METHODNR, STRATANR, MACROHABNR, MICROHABNR, SPCOMM, SEX, WEIGHT, ISGRAVID, PLANTEDRELEASED, total_length, svlength, tail_length, carapace_length, NUMNIGHTS, NUMELLIOTTRAPA, NUMELLIOTNIGHTS, NUMPITTRAPA, NUMPITNIGHTS, NUMCAGETRAPA, NUMCAGENIGHTS, ISHARPTRAP, ISMISTNET, NUMELLIOTTRAPB, NUMPITTRAPB, NUMCAGETRAPB, ISHAIRTUBE, ISBATDETECTOR

...

1. , FYKENETDUR, FYKENETNR, FYKENETHRS

Occurrence

For birds the following join is needed:

...