...
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
| Code Block |
|---|
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
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)
Survey columns individuals
Select data for survey only
| Code Block |
|---|
bdbsa.supatch."SITETYPE" = 'SU' |
2. Select data for individuals using DBH as key
| Code Block |
|---|
"DBH" is not null |
3 . For individuals the following variables from the suspecies table will be ingested:
...
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
| Code Block |
|---|
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
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)
Survey columns individuals
Select data for survey only
| Code Block |
|---|
bdbsa.supatch."SITETYPE" = 'SU' |
2. Select data for individuals using DBH as key
| Code Block |
|---|
"DBH" is not null |
3 . For individuals the following variables from the suspecies table will be ingested:
PLANTEDRELEASED_individual, CROWN_EXTENT_PERC, CROWN_EXTENT_SCORE, CROWN_DENSITY_PERC, CROWN_DENSITY_SCORE, DBH, NEW_TIP_GROWTH, EPICORMIC_GROWTH, EXTENT_REPRODUCTION, MISTLETOE_LOAD, LEAF_DIE_OFF, LEAF_DAMAGE, EXTENT_BARK_CRACKING
...
The suspecies tables for animals will be divided into 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.
All records have to be filtered by ISCERTAIN = Y
Separate NUMOBSERVED column into the following parameters when none detectedwill be divided into 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.
All records have to be filtered by ISCERTAIN = Y
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.
Individuals
Pivot length and lengthmethod columns
...