Advertisement
auonss0n

circler_of_sorts.py

Jan 27th, 2024 (edited)
203
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
R 3.90 KB | None | 0 0
  1. library(RSQLite)
  2. library(ggplot2)
  3. library(leaflet)
  4. library(htmlwidgets)
  5.  
  6. # Connect to the Europe SQLite database
  7. europe_con <- dbConnect(SQLite(), "/path/to/database.db")
  8.  
  9. # Query to retrieve timestamp, latitude, and longitude
  10. query_location_europe <- "SELECT timestamp_u, lat, lon
  11.                          FROM flights
  12.                          WHERE nic >= 1 AND nic <= 6
  13.                            AND (lat > 35.0 OR lat < 71.0 OR lon > -28.0 OR lon < 40.0)"
  14.  
  15. # Execute the query and store results in a dataframe
  16. location_df_europe <- dbGetQuery(europe_con, query_location_europe)
  17.  
  18. # Close the Europe database connection
  19. dbDisconnect(europe_con)
  20.  
  21. # Convert timestamp_u to datetime
  22. location_df_europe$timestamp_u <- as.POSIXct(location_df_europe$timestamp_u, format = "%Y-%m-%d %H:%M:%S")
  23.  
  24. # Create a new column for 15-minute intervals
  25. location_df_europe$interval <- cut(location_df_europe$timestamp_u, breaks = "15 min")
  26.  
  27. # Connect to the global SQLite database
  28. global_con <- dbConnect(SQLite(), "/path/to/database.db")
  29.  
  30. # Query to retrieve timestamp, latitude, and longitude for global dataset
  31. query_location_global <- "SELECT timestamp_u, lat, lon
  32.                          FROM flights
  33.                          WHERE nic >= 1 AND nic <= 6
  34.                            AND (lat < 35.0 OR lat > 71.0 OR lon < -28.0 OR lon > 40.0)"
  35.  
  36. # Execute the query and store results in a dataframe
  37. location_df_global <- dbGetQuery(global_con, query_location_global)
  38.  
  39. # Close the global database connection
  40. dbDisconnect(global_con)
  41.  
  42. # Convert timestamp_u to datetime
  43. location_df_global$timestamp_u <- as.POSIXct(location_df_global$timestamp_u, format = "%Y-%m-%d %H:%M:%S")
  44.  
  45. # Create a new column for 15-minute intervals
  46. location_df_global$interval <- cut(location_df_global$timestamp_u, breaks = "15 min")
  47.  
  48. # Create and save ggplot summary plot for both Europe and Global datasets
  49. output_directory <- "/path/to/output"
  50. dir.create(output_directory, showWarnings = FALSE)
  51.  
  52. count_table_europe <- table(location_df_europe$interval)
  53. count_df_europe <- data.frame(interval = as.POSIXct(names(count_table_europe)), count = as.numeric(count_table_europe))
  54.  
  55. count_table_global <- table(location_df_global$interval)
  56. count_df_global <- data.frame(interval = as.POSIXct(names(count_table_global)), count = as.numeric(count_table_global))
  57.  
  58. plot_combined <- ggplot() +
  59.   geom_line(data = count_df_europe, aes(x = interval, y = count, color = "Europe"), size = 1) +
  60.   geom_line(data = count_df_global, aes(x = interval, y = count, color = "Global"), size = 1) +
  61.   scale_x_datetime(breaks = "3 hours", date_labels = "%Y-%m-%d %H:%M") +
  62.   labs(title = "Aircrafts affected by suspected GPS-jamming, Europe and outside Europe",
  63.        subtitle = "Count: ADS-B-reports. Processing: CC-BY-SA @auonsson Data: airplanes.live",
  64.        x = "Datetime",
  65.        y = "Count NIC 1-6") +
  66.   theme_minimal() +
  67.   theme(legend.position = "bottom", axis.text.x = element_text(angle = 45, hjust = 1))
  68.  
  69. # Set a white background and save the summary plot for both datasets
  70. plot_filename_combined <- paste(output_directory, "Combined_Summary_Plot.png", sep = "/")
  71. ggsave(plot_filename_combined, plot_combined, width = 13, height = 6, bg = "white")
  72.  
  73. # Create leaflet map with CircleMarkers for both Europe and Global datasets
  74. leaflet_map <- leaflet() %>%
  75.   addTiles() %>%
  76.   addCircleMarkers(
  77.     data = location_df_europe,
  78.     lng = ~lon,
  79.     lat = ~lat,
  80.     color = "#f8766d",
  81.     radius = 5,
  82.     label = "Europe"
  83.   ) %>%
  84.   addCircleMarkers(
  85.     data = location_df_global,
  86.     lng = ~lon,
  87.     lat = ~lat,
  88.     color = "#00bfc4",
  89.     radius = 5,
  90.     label = "Global"
  91.   ) %>%
  92.   addLegend(
  93.     position = "bottomright",
  94.     colors = c("#f8766d", "00bfc4"),
  95.     labels = c("Europe", "Global")
  96.   )
  97.  
  98. # Save the leaflet map
  99. map_filename <- paste(output_directory, "Combined_Leaflet_Map.html", sep = "/")
  100. saveWidget(leaflet_map, file = map_filename)
  101.  
  102.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement