README.md

In MPCA-data/mpcadb: R connections to MPCA's databases: TEMPO, DELTA, EQUIS, CEDR

mpcadb

:electric_plug: :floppy_disk: Easy R connections to MPCA’s databases: DELTA, TEMPO, EQUIS, RAPIDS, ONBASE.

Functions

• open_delta : Open a connection to DELTAW, MPCA’s oracle database that houses TEMPO, EQUIS, RAPIDS, ONBASE and more.
• get_ai : Get all Agency Interest (AI) names from TEMPO.

Install

Install mpcadb from GitHub:

# First install the 'remotes' package
install.packages("remotes")

# Install the `mpcadb` development pkg from GitHub
remotes::install_github("MPCA-data/mpcadb")

Setup

1. Click the 🔎 magnifying glass search icon in the windows taskbar
2. Type ODBC in the search bar
3. Select ODBC Data Source Administrator (64-bit)
4. Click the [Add...] button in the menu that pops up
5. Choose Oracle OraClient11g_home2 11. ...
6. Click [ Finish ]

Enter these options

• Data Source Name: deltaw
• Description: deltaw
• TNS Service Name: deltaw
• User ID: leave blank

Voila! You should see deltaw in the "User Data Sources" with a platform of 64-bit and for the Driver "Oracle in OraClient...".

Use

:warning: You may need to set RStudio to open the 64-bit version of R to connect to delta.

Open an RODBC connection to DELTA with the open_delta() function. It will add the credentials for you.

library(mpcadb)

# Open DELTA connection and set to `deltaw`
deltaw <- open_delta()

## Connection successful!

List all tables

tbls_all <- RODBC::sqlTables(deltaw)

|TABLE_CAT |TABLE_SCHEMA |TABLE_NAME |TABLE_TYPE |REMARKS | |:---------|:-----------|:------------------------------|:-----------|:-------| |NA |TEMPOBP |MTB_EL_LIC_TYPE_EXAM_TYPE_XREF |SYNONYM |NA | |NA |SYS |KU$_PROCACT_SCHEMA_PKG_VIEW |SYSTEM VIEW |NA | |NA |SYS |USER_SEGMENTS |SYSTEM VIEW |NA | |NA |PUBLIC |APEX_APPL_PLUGINS |SYNONYM |NA | |NA |TEMPOAP |ADET_QUERY |SYNONYM |NA |

List all tables in specific Schema

tbls_rapids <- RODBC::sqlTables(deltaw, schema = "SUPERAPIDS")

|TABLE_CAT |TABLE_SCHEM |TABLE_NAME |TABLE_TYPE |REMARKS | |:---------|:-----------|:---------------------------|:----------|:-------| |NA |SUPERAPIDS |GEO_STATES |SYNONYM |NA | |NA |SUPERAPIDS |REF_FACTOR_SOURCE_CODES |SYNONYM |NA | |NA |SUPERAPIDS |REF_CEDR_CUSTOM_THROUGHPUT |SYNONYM |NA | |NA |SUPERAPIDS |REF_SCC_DATA_CATEGORY_CODES |SYNONYM |NA | |NA |SUPERAPIDS |GEO_ACTIVITIES |SYNONYM |NA |

TEMPO AI’s

Get a complete list of the Agency Interests from TEMPO with get_ai():

ai_names <- get_ai(keep_alt_names = TRUE)

ai_names

| MASTER_AI_ID | INT_DOC_ID | MASTER_AI_NAME | AI_TYPE_CODE | START_DATE | END_DATE | ALTERNATE_RECORD_SEQUENCE | USER_GROUP_ID | ALTERNATE_AI_ID | ALTERNATE_AI_NAME | ALTERNATE_AI_TYPE_CODE | START_DATE_ALT | END_DATE_ALT | COMMENTS | PROGRAM_CODE | | -------------: | -----------: | :-------------------------------------- | :------------- | :------------------ | :-------- | --------------------------: | :-------------- | :---------------- | :-------------------------------------- | :------------------------ | :------------------ | :------------- | :------- | :------------ | | 3462 | 0 | Hastings Bus Co | CON | 1992-07-23 19:11:05 | NA | 1 | HW+ | MND981784622 | Hastings Bus Co | CON | 1999-07-28 14:25:32 | NA | NA | HW | | 2489 | 0 | 3M Alexandria | CON | 1992-09-29 00:00:00 | NA | 1 | AQ+ | 04100003 | 3M - Alexandria | CON | 1995-07-11 10:21:24 | NA | NA | AQ | | 2492 | 0 | Chart Inc., New Prague | CON | 1992-09-17 00:00:00 | NA | 1 | IS+ | MNR0539M2 | Chart Inc., New Prague | CON | 2014-09-22 11:57:53 | NA | NA | IS | | 2504 | 0 | Virginia Department of Public Utilities | CON | 1995-07-07 09:28:44 | NA | 1 | AQ+ | 13700028 | Virginia Department of Public Utilities | CON | 1995-07-07 09:28:44 | NA | NA | AQ |

Get only a select list of TEMPO AI’s:

ai_names <- get_ai(ai = c(441, 288), keep_alt_names = FALSE)

ai_names

| MASTER_AI_ID | INT_DOC_ID | MASTER_AI_NAME | AI_TYPE_CODE | START_DATE | END_DATE | | -------------: | -----------: | :---------------------- | :------------- | :------------------ | :-------- | | 288 | 0 | Mountain Iron WWTP | CON | 1991-12-05 00:00:00 | NA | | 441 | 0 | Bryan Rock Products Inc | MOB | 1993-06-23 09:45:05 | NA |

Run a custom query

This query loads the reference table for the pollutant - CAS number table from the SUPERAPIDS air emissions inventory database.

pollutants <- RODBC::sqlQuery(deltaw, "Select * from SUPERAPIDS.REF_MATERIAL_CODES", max = 2000)

|MATERIAL_CODE |SHORT_DESC |CAS_NO |POLLUTANT_TYPE_CODE | |:-------------|:---------------------------------------------|:----------|:-------------------| |HFE-236ca |1-(DIFLUOROMETHOXY)-1,1,2,2-TETRAFLUOROETHANE |32778-11-3 |GHG | |COATING MIX |COATING MIX |NA |NA | |BENZO(E)PYRE |BENZO(E)PYRENE |192-97-2 |HAP | |ETGLMOPHETPR |ETHYLENEGLYCOL MONOPHENYL ETHER PROPIONATE |23495-12-7 |HAP | |PAH/POM |PAH/POM - Unspecified |NA |HAP |

EQUIS example

equis_tbls <- sqlTables(deltaw, schema = "EQUIS")

# Get first 1000 Well records
well_depth <- sqlQuery(deltaw, "select * from EQUIS.DT_WELL",
                       max = 1000)

well_depth

|SYS_LOC_CODE |WELL_ID |GEOLOGIC_UNIT_CODE |INSTALLATION_DATE |DRILLING_METHOD |WELL_PURPOSE | DEPTH_OF_WELL| |:------------|:-------|:------------------|:-----------------|:---------------------|:----------------|-------------:| |689971 |30014 |QWTA |2010-07-23 |Bucket Auger |Well-Observation | 23.5| |242292 |NA |QWTA |1984-07-10 |Auger (non-specified) |Well | 17.0| |244490 |NA |QWTA |1986-07-23 |NA |Well-Observation | 65.0| |104319 |NA |QBAA |1975-10-27 |Non-specified Rotary |Well-Domestic | 170.0|

Find deepest well

max(well_depth$DEPTH_OF_WELL, na.rm = T)

575 ft.

MPCA-data/mpcadb documentation built on March 29, 2022, 10:05 p.m.