In einarhjorleifsson/pgnapes: Connection to the PGNAPES database

knitr::opts_chunk$set(
  collapse = TRUE,
  message = FALSE,
  warning = FALSE,
  eval = TRUE,
  comment = "#>",
  fig.path = "man/figures/README-",
  out.width = "100%"
)

pgnapes

The goal of {pgnapes} is to:

• Establish R-connection to PGNAPES Oracle database
• Provide convenient functions to PGNAPES tables and views
• R-code examples for data analysis and graphical presentations

Installation

You can install the development version of pgnapes from GitHub with:

install.packages("devtools")
remotes::install_github("einarhjorleifsson/pgnapes")

The backbone of {pgnapes} is {dplyr} which allows for generation of simple or complex sequence of codes that are automatically translated to SQL via the {dbplyr}-package. Since the PGNAPES database is Oracle 11g one can not use {dbplyr} versions >2.0. Hence we need to install an older version:

remotes::install_github("tidyverse/dbplyr@v1.4.4", force = TRUE)

Side effects: Installing an earlier version of {dbplyr} means that one can not load the {tidyverse} package only its child packages (dplyr, ggplot2, tidyr, tibble, ….). Reinstalling the latest version of {dbplyr} is done by install.packages("dbplyr").

Connection

All functions in the {pgnapes} package have a prefix pgn_. The core function in {pgnapes} is the pgn_connect that provides a connection to the PGNAPES database. The arguments of the function are username, password and use_odbc. The two first arguments are self-expanatory. The arguement use_odbc (default TRUE) provides a control if connection is made via {odbc} or {ROracle}. The latter is generally faster but the {ROracle}-package setup is more difficult (see furhter notes below)

At the start of an R-sesssion load some packages:

library(dplyr)
library(tidyr)
library(ggplot2)
library(maps)
library(pgnapes)

Connect to the database:

The first thing to do is to establish a connection to the PGNAPES-database by:

con <- pgn_connect(username = "your_user_name", password = "your_password")

load("leyndo.RData")
con <- pgn_connect(my_user_name, my_password, use_odbc = TRUE)

You should now get something like the following:

con

The PGNAPES tables

The functions below are a convenient wrapper to access all the tables in pgnapes:

pgn_acoustic(con)
pgn_acousticvalues(con)
pgn_biology(con)
pgn_catch(con)
pgn_countries(con)
pgn_hydrography(con)
pgn_icessquares(con)
pgn_icessquares_big(con)
pgn_igoss(con)
pgn_plankton(con)
pgn_logbook(con)
pgn_species(con)
pgn_stationtypes(con)
pgn_survey(con)
pgn_vessels(con)

Examples

Get and "standardize" mackerel catch

d <-
  pgn_logbook(con) %>%
  filter(year >= 2013,
         month %in% 7:8,
         sttype %in% c("PTRAWL", "TRAWL", "Multpelt", "PTrawl_Straight",
                       "DeepTrawl", "Multpelt 832")) %>%
  arrange(cruise, year, month, day, hour, min) %>%
  left_join(pgn_catch(con) %>% 
              filter(species %in% "MAC")) %>%
  collect(n = Inf) %>% 
  mutate(catch = catch / 1000 / (towtime / 60),
         catch = replace_na(catch, 0))
glimpse(d)

Map catch:

xlim <- range(d$lon)
ylim <- range(d$lat)
m <- map_data("world", xlim = xlim, ylim = ylim)
d %>% 
  ggplot(aes(lon, lat)) +
  theme_bw() +
  geom_polygon(data = m, aes(long, lat, group = group), fill = "grey") +
  geom_path(aes(group = cruise), colour = "grey", lwd = 0.25, alpha = 0.5) +
  geom_point(data = d %>% filter(catch > 0),
             aes(size = catch), colour = "red", alpha = 0.35) +
  scale_size_area(max_size = 5) +
  geom_point(size = 0.01, alpha = 0.2) +
  facet_wrap(~ year, ncol = 2) +
  #coord_quickmap(xlim = xlim, ylim = ylim) +
  coord_map(projection = "azequalarea",
            xlim = xlim, ylim = ylim) +
  scale_x_continuous(NULL, NULL) +
  scale_y_continuous(NULL, NULL)

Intalling ROracle

Your IT-department may be of help here, details on intallments are here. Information on the installation of the needed Oracle Instant Client are here.

einarhjorleifsson/pgnapes documentation built on July 12, 2022, 8:43 p.m.