Skip to content

v1.1

Latest
Latest
Compare
Choose a tag to compare
@eugenepik eugenepik released this 24 May 11:10
· 3 commits to main since this release
v1.1
3341cad
This commit was created on GitHub.com and signed with GitHub’s verified signature.
GPG key ID: B5690EEEBB952194
Learn about vigilant mode.

Release Description

ADS-B Data Analysis using OpenSky Trino Database

This release includes SQL scripts and Python code for analyzing ADS-B messages stored in the OpenSky Trino database. The analysis focuses on identifying and handling gaps in positional data and potential GPS spoofing incidents for airborne aircraft.

Included Files:

  1. flight_analysis.sql

    • This SQL script initializes a named prepared statement called flight_analysis to identify gaps in aircraft routes where either latitude or longitude information is missing, indicating possible data transmission issues in the ADS-B system. The script uses multiple Common Table Expressions (CTEs) to perform detailed analysis, validate geographic data points, and assess data quality using Navigation Integrity Category (NIC) values.

  2. spoofing_analysis.sql

    • This SQL script initializes a named prepared statement called spoofing_analysis to identify potential GPS spoofing incidents. It filters ADS-B messages for unusually large distances traveled between consecutive data points relative to the time elapsed, assuming a maximum plausible speed of 600 m/s for aircraft. The script uses multiple CTEs to organize the logic, calculate distances, and flag discrepancies that suggest positional anomalies.

  3. ADS-B_GPS_TrinoDB_Analysis.py

    • This Python script automates data extraction and analysis from the OpenSky Trino database, focusing on identifying gaps in aircraft ADS-B transmission data. It constructs and executes complex SQL queries iteratively for each day within a specified date range, processes the data into pandas DataFrames, and exports the results to dynamically named Excel files. The script includes logging and error handling mechanisms to ensure stable execution.

Key Features:

  • Comprehensive Analysis: The SQL scripts and Python code provide a detailed analysis of ADS-B data, identifying significant data gaps and potential GPS spoofing incidents.
  • Data Integrity Checks: Ensures data reliability by validating geographic data points, checking NIC values, and confirming valid icao24 aircraft identifiers.
  • Automated Execution: The Python script automates the execution of SQL queries, processes the results, and generates output files, facilitating large-scale data analysis.
  • Detailed Reporting: Outputs detailed information about identified data gaps and potential spoofing incidents, including validated coordinates, durations, and NIC values.

How to Use:

  1. SQL Scripts: Execute the flight_analysis.sql and spoofing_analysis.sql scripts in the Trino database to set up the prepared statements.
  2. Python Script: Run the ADS-B_GPS_TrinoDB_Analysis.py script to automate the data extraction and analysis process. Ensure to update the script with your Trino database connection details.

Author

Eugene Pik
eugene.pik@mevocopter.com

License

This project is licensed under the Apache License, Version 2.0.

Assets 2
Loading