Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement