Database plots with dbplot

Setup

library(DBI)
library(tidyverse)
library(dbplot)
conf <- config::get()
con <- dbConnect(odbc::odbc(),
                 Driver = "SQLServer",
                 Database = conf$database,
                 Port = conf$port,
                 Server = conf$server,
                 UID = conf$UID,
                 PWD = conf$PWD)
airports <- tbl(con, "airports")
flights <- tbl(con, "flights2")

Histogram

dbplot_histogram(airports, alt, binwidth = 700) + 
  labs(title = "Airports Altitude") +
  theme_minimal()

Compute bins

db_compute_bins(airports, alt)

db_bin(x)

(((max(x, na.rm = TRUE) - min(x, na.rm = TRUE))/(30)) * ifelse((as.integer(floor(((x) - 
    min(x, na.rm = TRUE))/((max(x, na.rm = TRUE) - min(x, na.rm = TRUE))/(30))))) == 
    (30), (as.integer(floor(((x) - min(x, na.rm = TRUE))/((max(x, 
    na.rm = TRUE) - min(x, na.rm = TRUE))/(30))))) - 1, (as.integer(floor(((x) - 
    min(x, na.rm = TRUE))/((max(x, na.rm = TRUE) - min(x, na.rm = TRUE))/(30))))))) + 
    min(x, na.rm = TRUE)

Raster

dbplot_raster(flights, lon, lat) +
  labs(title = "Arrivals") +
  theme_minimal()

Line plot

dbplot_line(flights, month, mean(arr_delay)) +
  labs(title = "Arrival delays by month") +
  theme_minimal()

Missing values are always removed in SQL.
Use `AVG(x, na.rm = TRUE)` to silence this warning

Barplot

dbplot_bar(flights, origin, mean(dep_delay)) +
  labs(title = "Departure delays by airport") +
  theme_minimal() +
  coord_flip()

Missing values are always removed in SQL.
Use `AVG(x, na.rm = TRUE)` to silence this warning