---
title: "STAT 540 Lec 17 SQL in R"
format: html
jupyter: python3
---


```{r}
library(mdsr)
db <- dbConnect_scidb('airlines')
```


How do we get our SQL queries into R

```{r}
library(DBI) # install.packages('DBI')
```

This package has a function called `dbGetQuery()`.

```{sql connection = db}
SELECT faa, alt, lat, lon
FROM airports
WHERE alt > 5280
LIMIT 20
```


```{r}

query <- "
SELECT faa, alt, lat, lon
FROM airports
WHERE alt > 5280
"

output <- dbGetQuery(db,query)

```


Make a map of the US showing the locations of these mile-high airports.

```{r}
library(maps) # install.packages('maps')
map('state') # plot the continental US
points(x = output$lon, y = output$lat, pch = 19)
text(x = output$lon, y = output$lat, labels = output$faa, cex = .5, pos = 3)
```


Get a table showing which of these mile-high airports had a flight going to Miami.

```{sql connection = db}
SELECT f.origin, f.dest, COUNT(*) AS departures, a.lat, a.lon
FROM flights AS f JOIN airports AS a ON f.origin = a.faa
WHERE a.alt > 5280 AND f.dest = 'MIA' AND f.year = 2015
GROUP BY f.origin
```

```{r}

query <- "
SELECT f.origin, f.dest, COUNT(*) AS departures, a.lat, a.lon
FROM flights AS f JOIN airports AS a ON f.origin = a.faa
WHERE a.alt > 5280 AND f.dest = 'MIA' AND f.year = 2015
GROUP BY f.origin
"
toMiami <- dbGetQuery(db, query)
```

```{r}
query <-"
SELECT lat, lon
FROM airports
WHERE faa = 'MIA'
"
Miami <- dbGetQuery(db,query)

```

Make the cool plot:


```{r}
map('state')
library(geosphere) # install.packages('geosphere')
den <- c(toMiami$lon[1],toMiami$lat[1])
ege <- c(toMiami$lon[2],toMiami$lat[2])
mia <- c(Miami$lon,Miami$lat)

points(x = den[1], y = den[2])
points(x = ege[1], y = ege[2])
points(x = mia[1], y = mia[2])
geo1 <- gcIntermediate(p1 = den, p2 = mia) # line between den and miami
lines(geo1)

geo2 <- gcIntermediate(p1 = ege, p2 = mia) # line between den and miami
lines(geo2)
```