...
The suspecies tables for animals will be divided into occurrence and survey-based records. The following joins are needed for this data (where SITETYPE = ‘OP’ for opportunistic and ‘SU’ for survey-based methods). Only records with certainty confirmed will be exposed, see where statement below.
| 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"
join bdbsa.sutrap str on str."VISITNR" = sv."VISITNR"
where (sp."SITETYPE" = 'OP' and ISCERTAIN = 'Y')
|
Separate records into animal individuals and populations
The surveys contain data for animal individuals (tern_survey_suspecies_animal_individual) and populations (tern_survey_suspecies_animal_population). This needs to be separated into two tables.
The following filter can identify animal individuals
| Code Block |
|---|
where ("SEX" is not null or "TEATS" is not null or "VAGINA" is not null
or "POUCH" is not null or "NUMPOUCH" is not null or "TESTES" is not null or "WEIGHT" is not null
or "SNOUT" is not null or "ISGRAVID" is not null or "LENGTH" is not null) |
Animal populations can be identified by the complement of the filter above (i.e. where the values in the columns above are null).
The following variables will be ingested for individuals, see sections below as NUMTRAP and LENGTH variables need further processing. Also create dipnetused variable where the value is YES when DIPNETHRS is not null.
| Code Block |
|---|
spp."SPECIESTYPE", concat_ws(' ', vs."GENUS", vs."SP") as species_name,
spp."MACROHABNR",spp."MICROHABNR", spp."SPCOMM" ,
spp."SEX", spp."TEATS", spp."VAGINA" , spp."POUCH" , spp."NUMPOUCH" ,
spp."TESTES", spp."WEIGHT" , spp."SNOUT" , spp."ISGRAVID",
spp."LENGTH", spp."LENGTHMETHOD", spp."ACTIVITY",
spp."PLANTEDRELEASED", str."NUMNIGHTS", str."NUMELLIOTTRAPA",
str."NUMELLIOTNIGHTS", str."NUMPITTRAPA", str."NUMPITNIGHTS",
str."NUMCAGETRAPA", str."NUMCAGENIGHTS", str."ISHARPTRAP", str."ISMISTNET",
str."NUMELLIOTTRAPB", str."NUMPITTRAPB", str."NUMCAGETRAPB", str."ISHAIRTUBE",
str."ISBATDETECTOR", str."DIPNETHRS" |
For the animal population table, the following variables will be ingested
| Code Block |
|---|
spp."SPECIESTYPE", concat_ws(' ', vs."GENUS", vs."SP") as species_name,
spp."NUMOBSERVED", spp."MACROHABNR", spp."MICROHABNR", spp."SPCOMM" , spp."SIGHTINGNR" ,
spp."ACTIVITY", spp."PLANTEDRELEASED", str."NUMNIGHTS", str."NUMELLIOTTRAPA",
str."NUMELLIOTNIGHTS", str."NUMPITTRAPA", str."NUMPITNIGHTS",
str."NUMCAGETRAPA", str."NUMCAGENIGHTS", str."ISHARPTRAP", str."ISMISTNET",
str."NUMELLIOTTRAPB", str."NUMPITTRAPB", str."NUMCAGETRAPB", str."ISHAIRTUBE",
str."ISBATDETECTOR", str."DIPNETHRS" |
NUMTRAP variables
NUMTRAP columns, columns with the following syntax NUMXXXXTRAPA and NUMXXXXTRAPB need to be added by trap type by row, e.g. NUMELLIOTTRAP AS sum(NUMELLIOTTRAPA, NUMELLIOTTRAPB).
These variables are stored at the site-visit level. However, they apply only to particular animal groups,i.e. not all NUMTRAP columns apply to all animal observations done during a site visit.
The NUMTRAP variables are
Sum of NUMtrap A and B
Logic for which sampling belongs to which kind of animal
Expand LENGTH and LENGTHNR
Expand (pivot wider) column LENGTHNR as column names and LENGTH column as values to fill these columns. Notice that number 15 has to be subdivided into invertebrate (when Species type = I) and turtle carapace length (when species type = R). The column names for the LENTHNR column are below, as well as example of 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)
15 turtle carapace length (if species type R)
2 fork length
3 lower jaw Fork Length
5 PES Length
6 Head Length
7 Forearm Length
...
individuals, populations and occurrences per animal group.
NUMTRAP columns, columns with the following syntax NUMXXXXTRAPA and NUMXXXXTRAPB need to be added by trap type by row, e.g. NUMELLIOTTRAP AS sum(NUMELLIOTTRAPA, NUMELLIOTTRAPB).
These variables are stored at the site-visit level. However, they apply only to particular animal groups,i.e. not all NUMTRAP columns apply to all animal observations done during a site visit. Also create dipnetused variable where the value is YES when DIPNETHRS is not null.
Individuals
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)
15 turtle carapace length (if species type R)
2 fork length
3 lower jaw Fork Length
5 PES Length
6 Head Length
7 Forearm Length
| Code Block |
|---|
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 is the same across species just changing the species type per animal group (i.e. M for mammals, R for reptiles, F for fish, A for amphibians). Invertebrates and birds are excluded as they only comprise 3 records.
| 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) |
For mammals :
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.
Populations
For birds, there is no populations
For populations the following joins and logic are required, this step is the same across species just changing the species type per animal group (i.e. B for Birds, M for mammals, R for reptiles, F for fish, A for amphibians).
| 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" = 'B' and "LENGTH" is null) |
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"
--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" = '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
Ignore me, working notes
occurrence and survey-based records. The following joins are needed for this data (where SITETYPE = ‘OP’ for opportunistic and ‘SU’ for survey-based methods). Only records with certainty confirmed will be exposed, see where statement below.
| 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"
join bdbsa.sutrap str on str."VISITNR" = sv."VISITNR"
where (sp."SITETYPE" = 'OP' and ISCERTAIN = 'Y') |
Separate records into animal individuals and populations
The surveys contain data for animal individuals (tern_survey_suspecies_animal_individual) and populations (tern_survey_suspecies_animal_population). This needs to be separated into two tables.
The following filter can identify animal individuals
| Code Block |
|---|
where ("SEX" is not null or "TEATS" is not null or "VAGINA" is not null
or "POUCH" is not null or "NUMPOUCH" is not null or "TESTES" is not null or "WEIGHT" is not null
or "SNOUT" is not null or "ISGRAVID" is not null or "LENGTH" is not null) |
Animal populations can be identified by the complement of the filter above (i.e. where the values in the columns above are null).
The following variables will be ingested for individuals, see sections below as NUMTRAP and LENGTH variables need further processing. Also create dipnetused variable where the value is YES when DIPNETHRS is not null.
| Code Block |
|---|
spp."SPECIESTYPE", concat_ws(' ', vs."GENUS", vs."SP") as species_name,
spp."MACROHABNR",spp."MICROHABNR", spp."SPCOMM" ,
spp."SEX", spp."TEATS", spp."VAGINA" , spp."POUCH" , spp."NUMPOUCH" ,
spp."TESTES", spp."WEIGHT" , spp."SNOUT" , spp."ISGRAVID",
spp."LENGTH", spp."LENGTHMETHOD", spp."ACTIVITY",
spp."PLANTEDRELEASED", str."NUMNIGHTS", str."NUMELLIOTTRAPA",
str."NUMELLIOTNIGHTS", str."NUMPITTRAPA", str."NUMPITNIGHTS",
str."NUMCAGETRAPA", str."NUMCAGENIGHTS", str."ISHARPTRAP", str."ISMISTNET",
str."NUMELLIOTTRAPB", str."NUMPITTRAPB", str."NUMCAGETRAPB", str."ISHAIRTUBE",
str."ISBATDETECTOR", str."DIPNETHRS" |
For the animal population table, the following variables will be ingested
| Code Block |
|---|
spp."SPECIESTYPE", concat_ws(' ', vs."GENUS", vs."SP") as species_name,
spp."NUMOBSERVED", spp."MACROHABNR", spp."MICROHABNR", spp."SPCOMM" , spp."SIGHTINGNR" ,
spp."ACTIVITY", spp."PLANTEDRELEASED", str."NUMNIGHTS", str."NUMELLIOTTRAPA",
str."NUMELLIOTNIGHTS", str."NUMPITTRAPA", str."NUMPITNIGHTS",
str."NUMCAGETRAPA", str."NUMCAGENIGHTS", str."ISHARPTRAP", str."ISMISTNET",
str."NUMELLIOTTRAPB", str."NUMPITTRAPB", str."NUMCAGETRAPB", str."ISHAIRTUBE",
str."ISBATDETECTOR", str."DIPNETHRS" |
BIRDS
For individuals, a single record exists and won’t be ingested.
...
For occurrence, the list is METHODNR, STRATANR, MACROHABNR, species_name, NUMOBSERVED, MICROHABNR, SEX, ACTIVITY, PLANTEDRELEASED.
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.