R: ggplot2 map of some European airports

.gitignore

.Rproj.user
.Rhistory
.RData
.Ruserdata

airport-apdf.png

airport-ir390-date.csv

airport.csv

airport.R

#
# export to CSV the list of APDF airports
#

library(ROracle)
library(dplyr)
library(readr)

export_apdf_airports <- function(on_date = Sys.Date()) {
  on_date <- format(on_date)
  usr <- Sys.getenv("PRU_DEV_USR")
  pwd <- Sys.getenv("PRU_DEV_PWD")
  dbn <- Sys.getenv("PRU_DEV_DBNAME")

  withr::local_envvar(c("TZ" = "UTC",
                        "ORA_SDTZ" = "UTC"))

  con <- withr::local_db_connection(
    ROracle::dbConnect(
      DBI::dbDriver("Oracle"),
      usr, pwd,
      dbname = dbn,
      timezone = "UTC")
  )

  query <- "
    SELECT
      apt.APT_ICAO, pru.LON AS LONGITUDE, pru.LAT AS LATITUDE,
      apt.APT_IN_EAPN AS IS_EAPN,
      DECODE(apt.APT_ICAO,'LCLK','N','Y') IS_IMPED
    FROM
      PRU_AIRPORT.STAT_AIRPORT_INFO apt, PRU_AIRPORT pru
    WHERE
          apt.APT_IS_POSTING = 'Y'
      AND apt.APT_ICAO = pru.ICAO_CODE
      AND (pru.WEF <= TO_DATE(?ON_DATE, 'YYYY-MM-DD')
           AND TO_DATE(?ON_DATE, 'YYYY-MM-DD') < pru.TILL)
  "

  query <- DBI::sqlInterpolate(con, query, ON_DATE = on_date)
  ROracle::dbSendQuery(con, query) %>%
    ROracle::fetch(n = -1) %>%
    janitor::clean_names()
}

export_apdf_airports() %>%
  mutate_at(.vars = vars(matches('^is_')), .funs = ~ . == 'Y') %>%
  write_csv("airport.csv")


# IR390 first processed date
# NO SQL but directly from Thierry
tibble::tribble(
  ~ICAO, ~IR390_DATE,
  "LSZH",      "25-11-2014 18:46",
  "LSGG",      "16-12-2014 15:17",
  "EGBB",      "20-01-2015 14:53",
  "EGCC",      "26-01-2015 10:21",
  "GCLP",      "28-01-2015 10:03",
  "GCTS",      "28-01-2015 10:45",
  "LEMD",      "28-01-2015 11:25",
  "LEBB",      "28-01-2015 12:37",
  "LEBL",      "28-01-2015 13:05",
  "LEIB",      "28-01-2015 13:34",
  "LEMG",      "28-01-2015 14:03",
  "GCFV",      "02-02-2015 11:12",
  "GCXO",      "12-02-2015 12:06",
  "LIME",      "18-02-2015 14:28",
  "EIDW",      "18-02-2015 15:21",
  "EGLL",      "18-02-2015 15:59",
  "EKCH",      "19-02-2015 12:18",
  "LEAL",      "24-02-2015 16:02",
  "LIPZ",      "02-03-2015 09:50",
  "EGKK",      "08-03-2015 15:11",
  "LOWW",      "19-03-2015 10:51",
  "LFPG",      "19-03-2015 13:36",
  "LEVC",      "31-03-2015 10:49",
  "EPWA",      "31-03-2015 12:49",
  "EDDP",      "31-03-2015 13:39",
  "LBSF",      "31-03-2015 15:39",
  "LJLJ",      "01-04-2015 13:51",
  "LIRF",      "15-04-2015 10:33",
  "EDDL",      "24-04-2015 14:01",
  "EDDF",      "06-05-2015 15:06",
  "GCRR",      "08-05-2015 18:42",
  "EGSS",      "21-05-2015 10:51",
  "EDDK",      "26-06-2015 09:19",
  "EDDW",      "27-08-2015 12:41",
  "EGGD",      "27-08-2015 18:06",
  "LFPO",      "01-09-2015 15:49",
  "LGAV",      "01-09-2015 16:38",
  "LHBP",      "02-09-2015 10:57",
  "LICC",      "16-10-2015 18:59",
  "EBCI",      "21-10-2015 13:59",
  "EETN",      "04-11-2015 17:52",
  "LIMC",      "18-11-2015 14:41",
  "EDDN",      "01-12-2015 10:11",
  "EDDE",      "02-12-2015 16:27",
  "EVRA",      "08-12-2015 09:37",
  "EDDV",      "14-12-2015 10:32",
  "EBBR",      "16-12-2015 11:09",
  "EDDM",      "17-12-2015 10:20",
  "ESSA",      "11-01-2016 09:13",
  "LFSB",      "18-01-2016 12:25",
  "LIPE",      "19-01-2016 20:09",
  "EICK",      "27-01-2016 11:21",
  "EDDH",      "12-02-2016 13:28",
  "LIML",      "23-02-2016 16:33",
  "EDDR",      "09-03-2016 13:35",
  "EGGW",      "07-06-2016 12:37",
  "EFHK",      "17-06-2016 06:21",
  "ENGM",      "19-07-2016 02:45",
  "LPPR",      "22-12-2016 10:38",
  "EDDG",      "22-12-2016 10:58",
  "ELLX",      "09-01-2017 18:43",
  "LIRN",      "11-01-2017 19:42",
  "LEPA",      "11-01-2017 21:32",
  "EDDB",      "11-01-2017 22:41",
  "EDDT",      "11-01-2017 22:52",
  "LKPR",      "12-01-2017 00:10",
  "EGPH",      "12-01-2017 03:41",
  "ESGG",      "12-01-2017 04:49",
  "LFLL",      "12-01-2017 14:13",
  "LPPT",      "13-01-2017 02:05",
  "EDDS",      "20-02-2017 08:55",
  "LEZL",      "05-04-2017 13:04",
  "LMML",      "05-04-2017 13:44",
  "EHAM",      "29-08-2017 11:02",
  "LIMF",      "09-11-2017 17:33",
  "EGNT",      "21-01-2018 01:45",
  "LDZA",      "15-02-2018 11:34",
  "EGPD",      "20-02-2018 16:21",
  "EGPF",      "20-02-2018 22:41",
  "LIRA",      "18-05-2018 21:34",
  "LROP",      "29-08-2018 11:33",
  "LZIB",      "28-09-2018 12:54",
  "EDDC",      "03-04-2019 21:09",
  "LPFR",      "03-04-2019 23:14",
  "EGLC",      "25-04-2019 13:29",
  "LFML",      "07-06-2019 12:09",
  "LFBO",      "26-06-2019 12:35",
  "LFMN",      "06-09-2019 16:08",
  "EYVI",      "22-09-2019 06:08",
  "LCLK",                      NA
) %>%
  janitor::clean_names() %>%
  write_csv("airport-ir390-date.csv")

map-airports-apdf.Rproj

Version: 1.0

RestoreWorkspace: Default
SaveWorkspace: Default
AlwaysSaveHistory: Default

EnableCodeIndexing: Yes
UseSpacesForTab: Yes
NumSpacesForTab: 2
Encoding: UTF-8

RnwWeave: Sweave
LaTeX: pdfLaTeX

AutoAppendNewline: Yes
StripTrailingWhitespace: Yes

plot-airport-map.R

library(readr)
library(sf)
library(units)
library(rnaturalearth)
library(ggplot2)
library(ggrepel)
library(ggsflabel)

apts <- read_csv("airport.csv") %>%
  # define groups ...
  mutate(group = case_when(
    !is_eapn & !is_imped ~ "ONGOING",
    !is_eapn &  is_imped ~ "NOT_EAPN",
    is_eapn & !is_imped ~ "NOTHING",
    is_eapn &  is_imped ~ "EAPN"
  )) %>%
  # ... and get rid of the meaningless one
  filter(group != "NOTHING") %>%
  # make it a geographic object but keep the other the coords anyway
  sf::st_as_sf(
    coords = c("longitude","latitude"),
    crs = 4326,
    remove = FALSE,
    agr = "identity") %>%
  # use LAEA projection
  st_transform(crs = 3035)



# global bounding box
bbox_3035 <- apts %>%
  # put all buffers together...
  sf::st_union() %>%
  # ...take the polygon enclosing all of them ...
  sf::st_convex_hull() %>%
  # st_transform(3035) %>%
  # ...with a 50 NM buffer around ...
  st_buffer(set_units(150, km)) %>%
  sf::st_bbox() %>%
  # TODO: use 'round away from zero' eventually
  #       https://shrektan.com/post/2018/04/25/human-round-round-to-nearest-ties-away-from-zero/
  # round() %>%
  as.numeric() %>%
  `names<-`(c("left", "bottom", "right", "top"))


# just plot them (for fun)
world <- rnaturalearth::ne_countries(scale = "medium", returnclass = "sf") %>%
  st_transform(crs = 3035)

ggplot2::ggplot(data = world) +
  ggplot2::geom_sf(colour = "gray70", size = 0.5 / .pt) +
  ggplot2::geom_sf(data = apts, mapping = aes(fill = group), shape = 21, size = 2.5) +
  scale_fill_manual(
    breaks = c( "EAPN", "NOT_EAPN", "ONGOING"),
    values = c("green",     "blue",     "red")) +
  ggplot2::coord_sf(xlim = c(bbox_3035["left"], bbox_3035["right"]),
                    ylim = c(bbox_3035["bottom"], bbox_3035["top"]),
                    expand = TRUE) +
  ggplot2::theme_minimal() +
  ggplot2::theme(panel.background = ggplot2::element_rect(fill = "aliceblue"),
                 legend.position = "none")
ggsave("airport-apdf.png", width = 12)