In Metropolitan-Council/councilR: Functions and Templates for the Metropolitan Council

knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>",
  eval = FALSE
)

library(councilR)

Database connections are a critical part of the work we do at the Metropolitan Council.

See vignette("Credentials", package = "councilR") to review credential management.

All uid and pwd parameters are keyring::key_get("councilR.uid") and keyring::key_get("councilR.pwd") by default.

For additional information on database configuration, consult the onboarding guide from our friends over in Metro Transit Strategic Initiatives. You must be on the Council network to access the guide.

FRED

FRED contains most of CD Research's data tables, including tables for regional parks use estimates, population estimates, group quarters, and much more. Once you have the connection, you can use SQL queries to fetch only the data you need.

library(DBI)
fred <- fred_connection()

DBI::dbListTables(fred, )[1:20]

head(DBI::dbGetQuery(fred, "SELECT * FROM GQ_UNIT WHERE UNIT_ZIP = 55104"))

DBI::dbDisconnect(fred)

The database connection is also useful if you want to pull multiple tables from a single connection with {purrr}.

library(purrr)
library(dplyr)

fred <- fred_connection()

park_tables <- purrr::map(
  # park table names
  list(
    "PARK_UNIT",
    "PARK_TYPE",
    "PARK_IMPL_AGENCY"
  ),
  function(x) {
    # for each table name, locate and collect data
    dplyr::tbl(fred, x) %>%
      dplyr::collect()
  }
)

park_tables

DBI::dbDisconnect(fred)

If you know the name of the table you need, you can pull it directly with import_from_fred(). This uses SQL SELECT * FROM ___.

head(import_from_FRED(table_name = "GQ_UNIT", prod = FALSE))

FRED-Oracle

FRED-Oracle is our older Oracle version of FRED. We are migrating all tables from FRED-Oracle to FRED, but some tables are still only available in FRED-Oracle.

You will need Oracle drivers and specific credentials to access FRED-Oracle. See ?FRED_oracle_connection() for more information.

FRED-Oracle functions include FRED_oracle_connection() and import_from_FRED_oracle(). Both "FRED" and "fred" capitalization are supported.

fred_oracle <- FRED_oracle_connection()

DBI::dbListTables(fred_oracle, schema = "RESEARCH_WEB")[1:20]

head(DBI::dbGetQuery(fred_oracle, "SELECT * FROM RESEARCH_WEB.RES_PERMIT_TYPE"))

DBI::dbDisconnect(fred_oracle)

GISLibrary

GISLibrary is an ESRI SQL database that contains essential geospatial datasets and complementary tables.

Generally, if the Council has published a dataset on Minnesota Geospatial Commons, it is available in GISLibrary.

# create connection
gis <- gis_connection()

# fetch with SQL
DBI::dbGetQuery(gis, "select *, Shape.STAsText() as wkt from GISLibrary.dbo.COUNTIES where CO_NAME = 'ANOKA'")

# remember to disconnect!
DBI::dbDisconnect(gis)

# import a specific table as an sf object, with no additional SQL
# returns an sf object
counties <- import_from_gis(
  query = "GISLibrary.dbo.COUNTIES",
  .quiet = TRUE
)

# if a table does not have any spatial data,
# it will return a data frame
lookup_table <- import_from_gis(
  query = "GISLibrary.dbo.CountyCTULookupTable",
  .quiet = TRUE
)

Emissions

The emissions database is used for GHG inventory and scenario planning projects.

# create connection
emissions <- emissions_connection()

# fetch with SQL
DBI::dbGetQuery(emissions, "SELECT * FROM metro_energy.vw_electricity_residential_ctu")

# remember to disconnect!
DBI::dbDisconnect(emissions)

# import a specific table with no additional SWL
import_from_emissions("metro_energy.vw_electricity_residential_ctu")

Metropolitan-Council/councilR documentation built on Jan. 28, 2025, 8:33 p.m.