circler_of_sorts.py

library(RSQLite)
library(ggplot2)
library(leaflet)
library(htmlwidgets)

# Connect to the Europe SQLite database
europe_con <- dbConnect(SQLite(), "/path/to/database.db")

# Query to retrieve timestamp, latitude, and longitude
query_location_europe <- "SELECT timestamp_u, lat, lon
                          FROM flights
                          WHERE nic >= 1 AND nic <= 6
                            AND (lat > 35.0 OR lat < 71.0 OR lon > -28.0 OR lon < 40.0)"

# Execute the query and store results in a dataframe
location_df_europe <- dbGetQuery(europe_con, query_location_europe)

# Close the Europe database connection
dbDisconnect(europe_con)

# Convert timestamp_u to datetime
location_df_europe$timestamp_u <- as.POSIXct(location_df_europe$timestamp_u, format = "%Y-%m-%d %H:%M:%S")

# Create a new column for 15-minute intervals
location_df_europe$interval <- cut(location_df_europe$timestamp_u, breaks = "15 min")

# Connect to the global SQLite database
global_con <- dbConnect(SQLite(), "/path/to/database.db")

# Query to retrieve timestamp, latitude, and longitude for global dataset
query_location_global <- "SELECT timestamp_u, lat, lon
                          FROM flights
                          WHERE nic >= 1 AND nic <= 6
                            AND (lat < 35.0 OR lat > 71.0 OR lon < -28.0 OR lon > 40.0)"

# Execute the query and store results in a dataframe
location_df_global <- dbGetQuery(global_con, query_location_global)

# Close the global database connection
dbDisconnect(global_con)

# Convert timestamp_u to datetime
location_df_global$timestamp_u <- as.POSIXct(location_df_global$timestamp_u, format = "%Y-%m-%d %H:%M:%S")

# Create a new column for 15-minute intervals
location_df_global$interval <- cut(location_df_global$timestamp_u, breaks = "15 min")

# Create and save ggplot summary plot for both Europe and Global datasets
output_directory <- "/path/to/output"
dir.create(output_directory, showWarnings = FALSE)

count_table_europe <- table(location_df_europe$interval)
count_df_europe <- data.frame(interval = as.POSIXct(names(count_table_europe)), count = as.numeric(count_table_europe))

count_table_global <- table(location_df_global$interval)
count_df_global <- data.frame(interval = as.POSIXct(names(count_table_global)), count = as.numeric(count_table_global))

plot_combined <- ggplot() +
  geom_line(data = count_df_europe, aes(x = interval, y = count, color = "Europe"), size = 1) +
  geom_line(data = count_df_global, aes(x = interval, y = count, color = "Global"), size = 1) +
  scale_x_datetime(breaks = "3 hours", date_labels = "%Y-%m-%d %H:%M") +
  labs(title = "Aircrafts affected by suspected GPS-jamming, Europe and outside Europe",
       subtitle = "Count: ADS-B-reports. Processing: CC-BY-SA @auonsson Data: airplanes.live",
       x = "Datetime",
       y = "Count NIC 1-6") +
  theme_minimal() +
  theme(legend.position = "bottom", axis.text.x = element_text(angle = 45, hjust = 1))

# Set a white background and save the summary plot for both datasets
plot_filename_combined <- paste(output_directory, "Combined_Summary_Plot.png", sep = "/")
ggsave(plot_filename_combined, plot_combined, width = 13, height = 6, bg = "white")

# Create leaflet map with CircleMarkers for both Europe and Global datasets
leaflet_map <- leaflet() %>%
  addTiles() %>%
  addCircleMarkers(
    data = location_df_europe,
    lng = ~lon,
    lat = ~lat,
    color = "#f8766d",
    radius = 5,
    label = "Europe"
  ) %>%
  addCircleMarkers(
    data = location_df_global,
    lng = ~lon,
    lat = ~lat,
    color = "#00bfc4",
    radius = 5,
    label = "Global"
  ) %>%
  addLegend(
    position = "bottomright",
    colors = c("#f8766d", "00bfc4"),
    labels = c("Europe", "Global")
  )

# Save the leaflet map
map_filename <- paste(output_directory, "Combined_Leaflet_Map.html", sep = "/")
saveWidget(leaflet_map, file = map_filename)