Download Turtle Tagging data from WAMTRAM2.

Source: R/download_w2_data.R

This function requires an installed ODBC driver for MS SQL Server 2012. The database credentials are handled via environment variables.

download_w2_data(
  ord = c("YmdHMS", "Ymd"),
  tz = "Australia/Perth",
  db_drv = Sys.getenv("W2_DRV"),
  db_srv = Sys.getenv("W2_SRV"),
  db_name = Sys.getenv("W2_DB"),
  db_user = Sys.getenv("W2_UN"),
  db_pass = Sys.getenv("W2_PW"),
  db_port = Sys.getenv("W2_PT"),
  verbose = wastdr::get_wastdr_verbose(),
  dsn = Sys.getenv("W2_DSN"),
  use_rodbc = Sys.getenv("W2_RODBC", FALSE),
  save = NULL,
  compress = "xz"
)

Arguments

ord

Lubridate orders, default: c("YmdHMS", "Ymd").

tz

Timezone, default: "Australia/Perth".

db_drv

Database driver, default: Sys.getenv("W2_DRV") which should resolve to e.g. "/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so".

db_srv

Database server, default: Sys.getenv("W2_SRV"), which should resolve to a valid server hostname, e.g. "myserver.corporateict.domain".

db_name

The database name, default: Sys.getenv("W2_DB"), which should resolve to a valid database name, e.g. turtle_tagging.

db_user

The read-permitted database user, default: Sys.getenv("W2_UN").

db_pass

The database user's password, default: Sys.getenv("W2_PW").

db_port

The database port, default: Sys.getenv("W2_PT"), which should resolve to a numeric port, e.g. 1234.

verbose

Whether to show debug messages. Default: wastdr::get_wastdr_verbose()

dsn

The DSN for Windows systems, default: Sys.getenv("W2_DSN").

use_rodbc

Whether to use the RODBC library (if TRUE, best for Windows systems), or the odbc/DBI library (if FALSE, default, best for GNU/Linux systems).

save

If supplied, the filepath to save the data object to.

compress

The saveRDS compression parameter, default: "xz". Set to FALSE for faster writes and reads but larger filesize.

Value

A structure of class "wamtram_data" containing a named list of sanitised tables from the Turtle Tagging DB:

  • Metadata:

    • downloaded_on

    • w2_tables

  • Personnel:

    • persons

  • Data entry:

    • data_entry_batches "TRT_ENTRY_BATCHES"

    • data_entry

    • data_entry_operators

  • Lookups:

    • lookup_beach_positions

    • lookup_body_parts

    • lookup_conditions

    • lookup_damage_causes

    • lookup_damage_codes

    • lookup_datum_codes

    • lookup_egg_count_methods

    • lookup_id_types

    • lookup_measurement_types

    • lookup_pit_tag_states

    • lookup_sample_tissue_type

    • lookup_tag_states

  • Places:

    • sites

  • Encounters:

    • enc

      • location_code: the original rookery (where first encountered)

      • place_code: the place of the encounter (where currently found)

    • enc_qa

  • Observations:

    • obs_flipper_tags

    • obs_pit_tags

    • obs_damages

    • obs_measurements

    • obs_samples

  • Reconstructed:

    • reconstructed_pit_tags

    • reconstructed_tags

    • reconstructed_turtles

Details

In Windows systems, create a user defined DSN with settings

  • name WAMTRAMPROD

  • server kens-mssql-001-prod.corporateict.domain

  • SQL auth using login ID and password entered by user

  • trust server certificate (this is where odbc falls over)

Add to .Renviron: W2_RODBC=TRUE W2_DSN="WAMTRAMPROD"

See also

Other wamtram: map_wamtram(), w2_online()

Examples

if (FALSE) {
# Credentials are set in .Renviron

wamtram_data <- download_w2_data()


# develop:
ord <- c("YmdHMS", "Ymd")
tz <- "Australia/Perth"
verbose <- wastdr::get_wastdr_verbose()

con <- DBI::dbConnect(
  odbc::odbc(),
  Driver   = Sys.getenv("W2_DRV"),
  Server   = Sys.getenv("W2_SRV"),
  Database = Sys.getenv("W2_DB"),
  UID      = Sys.getenv("W2_UN"),
  PWD      = Sys.getenv("W2_PW"),
  Port     = Sys.getenv("W2_PT")
)
}