On survey, fish biology data are collected with the MFRI system HafVog (sometimes also referred to with the english monicker SeaScale) and stored in a local data base (DB) but are then incorporated in the institute's main DB after the survey. Here we show an example of how such data are extracted from the hafvog-scheme at the institute on Skúlagata, but also show the minor modifications needed to address an appropriate DB on survey. Further, since there are more ways then one to access MFRI-DBs from R, we show two alternative angles of attack.

DB access

We start with the package ROracle (and DBI implicitly) and deliberately use explicit set up of the DB-connection. Although more low-level than using the much used (at MFRI) package ora, in a way a wrapper around ROracle, our approach better illustrates the choice of DB that must be made on board (when scientists are removed from the office), and must also be changed on-shore after the survey if analysis is to be repeated there in the aftermath of the survey.

Currently, we have to authenicate us. Here we use (and distribute) the user and password combination of 'a databrowsing user' that is generally set up for the the HafVog/SeaScale-DBs (and shown in the manual for the system):

##library(ROracle)
##con <- dbConnect(dbDriver('Oracle'))
## previously it was necessary to authenticate e.g. with:
##
## user = 'hafvog_user', password = 'hafvog', dbname = 'mar')
## or:
## user = 'gagnasja', password = 'gagnasja', dbname = 'xe')
## 
## note that we would like a user/pass authentication 
## pair available on every installed HafVog and open for distribtution
## or alternatively the default user would have access to the hafvog-scheme
## dbname = 'xe') 
## on a machine with a local Oracle Extended Editoin receiving data from HafVog
## depending on setups, the user/pass combination could be skipped
## Further the hafvog manual mentions attempting:
## dbname = 'localhost:1521/xe') may be necessary

We collect station information in one query:

##stations <- dbGetQuery(con, "
##  select a.synis_id, stod, dags, 
##    to_char(togbyrjun,'yyyy-mm-dd HH24:MM:SS') upphaf, 
##    to_char(togendir,'yyyy-mm-dd HH24:MM:SS') endir,
##    dypi_hift, dypi_kastad, togdypi_hift, togdypi_kastad, 
##    vir_uti, larett_opnun, lodrett_opnun, grandaralengd,
##    togtimi, toglengd, toghradi,
##    kastad_n_breidd, kastad_v_lengd, hift_n_breidd, hift_v_lengd 
##  from hafvog.stodvar a, hafvog.togstodvar b
##  where a.synis_id = b.synis_id
##    and leidangur in ('A1-2016')
##  order by stod")
# change names to lowercase
##names(stations) <- casefold(names(stations))

and the fish biology information in another, renaming the fields according to the following scheme:

# Kvarnir
##fishData <- dbGetQuery(con, "
##  select a.synis_id, stod, nr, lengd l, aldur a, 
##    oslaegt w, kyn s, kynthroski kt
##  from hafvog.stodvar a, hafvog.kvarnir b
##  where a.synis_id = b.synis_id 
##    and leidangur in ('A1-2016') and tegund = 31
##  order by stod, nr")
##names(fishData) <- casefold(names(fishData))

A bit of data manipulation is necessary, we need to:

##library(geo)
# convert to decimal degrees
##stations$lat1 <- geoconvert(stations$kastad_n_breidd)
##stations$lon1 <- -geoconvert(stations$kastad_v_lengd)
##stations$lat2 <- geoconvert(stations$hift_n_breidd)
##stations$lon2 <- -geoconvert(stations$hift_v_lengd)
## find midpoint of tow
##stations$lat <- (stations$lat1 + stations$lat2)/2
##stations$lon <- (stations$lon1 + stations$lon2)/2
# maturity categorisation
##fishData$m <- ifelse(fishData$kt %in% c(NA, 1, 2), 0, 1)
# skip those with missing age
##id <- which(is.na(fishData$a))
##fishData <- fishData[-id,]

Optionally, output to a file:

# saveRDS(stations, file = "../data/stations_0.rds")
# saveRDS(fishData, file = "../data/fishData_0.rds")

Finally, exit cleanly

##dbDisconnect(con)

More ways than one to skin a cat

The github-package fishvice/mar, which provides functionality on top of dplyr and dplyrOracle connection to a DB, was introduced to haf_og_vatn/MFRI in November 2016.

Although it may not be available for all users and setups yet (e.g. on board resarch vesselss, or collected with HafVog on commercial vessels, port sampling from the branches, ...), we now show how this approach can be used and how it can recuce the verbosity of our code.

Similar to before we set up a connection to the DB, in this case the standard Skúlagata-DB (we make use of functionality built into mar and utilities newly built into our Oracle DB:

##library(dplyr)
##library(dplyrOracle)
##library(mar)
##mar <- src_oracle('mar')

We can either query the fiskar or hafvog versions of the data, we start with the final destination of the data, it is loaded onto schema fiskar, and the mar packages

##lesa_stodvar(mar) %>%
##  filter(leidangur == 'A1-2016') %>%
##  select(synis_id, stod, dags, 
##    togbyrjun, togendir, togtimi, toglengd, toghradi,
##    kastad_n_breidd, kastad_v_lengd,
##    hift_n_breidd, hift_v_lengd) %>%
##  mutate(lat1 = geoconvert1(kastad_n_breidd),
##    lon1 = -geoconvert1(kastad_v_lengd),
##    lat2 = geoconvert1(hift_n_breidd),
##    lon2 = -geoconvert1(hift_v_lengd)) %>%
##  collect() %>%
##  mutate(lat = (lat1 + lat2)/2,
##    lon = (lon1 + lon2)/2) -> stations2
# Kvarnir
##lesa_kvarnir(mar) %>%
##  filter(tegund == 31) %>%
##  collect(n=Inf) %>%
##  right_join(stations, by = "synis_id") -> fishData2

Vignette Info

Note the various macros within the vignette section of the metadata block above. These are required in order to instruct R how to build the vignette. Note that you should change the title field and the \VignetteIndexEntry to match the title of your vignette.

Styles

The html_vignette template includes a basic CSS theme. To override this theme you can specify your own CSS in the document metadata as follows:

output: 
  rmarkdown::html_vignette:
    css: mystyles.css

Figures

The figure sizes have been customised so that you can easily put two images side-by-side.

plot(1:10)
plot(10:1)

You can enable figure captions by fig_caption: yes in YAML:

output:
  rmarkdown::html_vignette:
    fig_caption: yes

Then you can use the chunk option fig.cap = "Your figure caption." in knitr.

More Examples

You can write math expressions, e.g. $Y = X\beta + \epsilon$, footnotes^[A footnote here.], and tables, e.g. using knitr::kable().

knitr::kable(head(mtcars, 10))

Also a quote using >:

"He who gives up [code] safety for [code] speed deserves neither." (via)



sigurdurthorjonsson/bionechi documentation built on Sept. 5, 2024, 4:11 a.m.