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("Options", package = "councilR") to review package options that make these functions run more smoothly. All uid and pwd parameters are getOption("councilR.uid") and getOption("councilR.pwd").

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))

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.AIRPORTS where APNAME ='Flying Cloud'")

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

# import a specific table as an sf object, with no additional SQL
# returns an sf object
airport <- import_from_gis(
  query = "GISLibrary.dbo.AIRPORTS",
  .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 March 30, 2024, 2:43 a.m.