Skip to contents

Accessing Bottom Trawl Survey Data

Source: vignettes/accessing-survdat-data.Rmd
accessing-survdat-data.Rmd

NEFSC Bottom Trawl Survey Data in {survdat}

The R package {survdat} allows you to pull and process NEFSC bottom trawl survey data. More information on the {survdat} package can be found here: https://noaa-edab.github.io/survdat/index.html

Before using this package, you will need to submit a number of ITD tickets: - Install of Oracle Instant Client - Creation of Oracle username and password. Also request access to NEFSCDB1 and NEFSC_USERS databases in this ticket. - Install of {ROracle} package

You must also be behind the NEFSC firewall (remote using VPN) for R to connect to Oracle databases.

The script below allows for a quick test of ROracle. The script will prompt for a username and password and then run the query string above to show all available tables your account can see.

library(ROracle)
library(DBI)
library(dplyr)
library(rstudioapi)

drv <- dbDriver("Oracle")

host <- "nefscdb1.nmfs.local"
port <- 1526
svc <- "NEFSC_USERS"

connect.string <- paste("(DESCRIPTION=",
                        "(ADDRESS=(PROTOCOL=tcp)(HOST=",host,")(PORT=",port,"))",
                        "(CONNECT_DATA=(SERVICE_NAME=",svc,")))",sep="")

user <- rstudioapi::showPrompt(title="Username", message="Please enter your Oracle username", default="")

passwd <- .rs.askForPassword("Please enter your Oracle password")

con <- dbConnect(drv,username=user,password=passwd, dbname=connect.string)

rs <- dbSendQuery(con, "SELECT owner,table_name FROM all_tables")

data <- fetch(rs)

print(data)

Installation

To install {survdat} directly from Github:

remotes::install_github("NOAA-EDAB/survdat",build_vignettes = TRUE)

The package {dbutils} must also be installed for R to connect to Oracle databases. More info on {dbutils} here: https://andybeet.github.io/dbutils/

remotes::install_github("andybeet/dbutils")

To connect to Oracle databases and pull survdat data

channel <- dbutils::connect_to_database("NEFSC_USERS","your username")
survdat::get_survdat_data(channel)

The function get_survdat_data() has parameters to include conversion factors (conversion.factor), species biological data (getBio), and length data (getLengths) which can be set = TRUE or FALSE.

POC for Troubleshooting

Contact Andy Beet () for issues with {dbutils} and {survdat} package help and data pulls.

If you do not have access to all the required tables in SVDBS to run {survdat} functions, the function ‘get_survdat_data’ will result in the following error:

“Error in .oci.GetQuery(conn, statment, data = data, prefetch = prefetch,…) ORA-00942: table or view does not exist”

If this occurs, contact Jakub Kircun with PEMAD () for SVDBS table access.