QueryDatabase: Query the National Water Information System Database

In jfisher-usgs/RNWIS: National Water Information System: R Interface

Description

This function acquire data from a single database table using a site selection criteria to constrain the number of sites selected.

Usage

QueryDatabase(
  channel,
  sqtable,
  sqvars = "*",
  site.no.var = NULL,
  site.no = NULL,
  site.tp.cd.var = NULL,
  site.tp.cd = NULL,
  agency.cd.var = NULL,
  agency.cd = NULL,
  lat.var = NULL,
  lat.lim = c(NA, NA),
  lng.var = NULL,
  lng.lim = c(NA, NA),
  alt.var = NULL,
  alt.lim = c(NA, NA),
  d.t.var = NULL,
  d.t.lim = c(NA, NA)
)

Arguments

channel	RODBC. A connection to a ODBC database.
sqtable	character. Name of the table from which data is to be retrieved.
sqvars	character. Vector of column names in queried table to be included in the final query results; its default is an asterisk ‘*’ and specifies that all columns will be returned.
site.no.var	character. Column name of table which shows the site identification number.
site.no	numeric. Vector of site identification numbers
site.tp.cd.var	character. Column name of queried table which shows the site type.
site.tp.cd	character. Vector of site type codes
agency.cd.var	character. Column name of queried table which shows the agency code.
agency.cd	character. Vector of agency codes
lat.var	character. Column name of queried table which shows the latitude.
lat.lim	numeric. Vector of minimum and maximum latitude values (WGS84).
lng.var	character. Column name of queried table which shows the longitude.
lng.lim	numeric. Vector of minimum and maximum longitude values (WGS84).
alt.var	character. Column name of queried table which shows the altitude.
alt.lim	numeric. Vector of minimum and maximum altitude values (NGVD29 or NAVD 88).
d.t.var	character. Column name of queried table which shows a date and time variable.
d.t.lim	POSIXt. Vector of minimum and maximum date values

Value

On success, returns an object of class data.frame or character. On failure, returns a vector of error message(s).

Author(s)

J.C. Fisher, U.S. Geological Survey, Idaho Water Science Center

See Also

sqlQuery

Examples

## Not run: 
  con <- RODBC::odbcConnect("NWIS Idaho", uid = "", pwd = "")

  d <- QueryDatabase(con, sqtable = "sitefile_01",
                     sqvars = c("site_no", "alt_va"),
                     site.no.var = "site_no",
                     site.no = c(432700112470801, 435038112453401))

  d <- QueryDatabase(con, sqtable = "sitefile_01",
                     sqvars = c("site_no", "dec_lat_va", "dec_long_va"),
                     site.tp.cd.var = "site_tp_cd", site.tp.cd = "GW",
                     agency.cd.var = "agency_cd", agency.cd = c("USGS", "USEPA"),
                     lng.var = "dec_long_va", lng.lim = c(-114.00, -112.00),
                     lat.var = "dec_lat_va", lat.lim = c(43.00, 44.00))

  d <- QueryDatabase(con, sqtable = "gw_lev_01",
                     sqvars = c("site_no", "lev_dt", "lev_va"),
                     site.no.var = "site_no", site.no = 432700112470801)

  close(con)

## End(Not run)

jfisher-usgs/RNWIS documentation built on Aug. 26, 2020, 6:43 p.m.