Content Comparison

...

select sp."SITEID", sp."QUADSITE", sp."PATCHID", sv."VISITNR", sv."VISITDATE", 
 svo."PREFIX", svo."VOUCHERNR",--unique identifiers
 svo."MUSEUMHERBMNR", --used by ALA
-- svo."VOUCHERTYPE",svo."SPSEQNR", spp."SPECIESNR", 
 fl."GENUS",fl."SP",
 fl."ISP1RANK" , fl."ISP1" , fl."SPAUTHOR", 
 svo."DETERMINERNR1" , --svo."DETERMINERNR2", determiner 2 is not used
 svo."DETERMINEDATE", --svo."ACCURACYCODE",--this is for the voucher?date
 svo."VOUCHERCOMM" 
 from bdbsa.suvoucher svo
 join bdbsa.suspecies spp on spp."SPSEQNR" =svo."SPSEQNR" and
  spp."VISITNR" = svo."VISITNR" 
 join bdbsa.flsp fl on spp."SPECIESNR" = fl."SPECIESNR"
 join bdbsa.suvisit sv on sv."VISITNR" =svo."VISITNR" 
 join bdbsa.supatch sp on sp."PATCHID" = sv."PATCHID" 
 where substring("MUSEUMHERBMNR" , '[A-Z]{1,}') = 'AD'
 order by "VOUCHERTYPE" 

...

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

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.

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

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.

...

Expand (pivot wider) column length method LENGTHNR as column names and length column as their valueLENGTH 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 Total Lengthbody length
10 Snout to Vent Length
12 Tail snout to vent length (does not need to be ingested as it already exist as SNOUT)
12 tail Length
14 Mantle Length
15 Carapace Length2 Fork Length
3 Lower Jaw Fork Length (FISH)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(Mammal)
6 Head Length (Mammal)
7 Forearm Length (mammal

strata

34 < 0.5m

35 0.5m-5m

36 > 5m

...

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

BIRDS

For individuals, a single record exists and won’t be ingested.

For populations, the following variables 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

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.