.. _sect-traffic-vis: Visualise Aircraft Traffic ========================== Introduction ------------ Sq1090 project decodes ADS-B messages into aircraft position data. Use the data to visualise aircraft traffic during specific period of time and over a defined geographical area. On October 31, 2025, some flights were delayed at Dublin Airport. Aircraft traffic was established in holiding patterns at KERAV and SORIN fixes near the airport. This lasted from about 09:45 to 12:30 UTC. In this tutorial you will - create SQL query to bin aircraft flights into 15 minutes intervals in the geographical area covering KERAV and SORIN holding fixes and Dublin Airport - prepare geographical data with `sf R library `_ - visualise data with `tmap R library `_ The outcome of the tutorial is presented on the :numref:`fig-traffic-on-hold`. .. figure:: images/traffic-on-hold.* :name: fig-traffic-on-hold :align: center :width: 640 :target: images/traffic-on-hold.pdf Aircraft traffic at KERAV and SORIN holding fixes near Dublin Airport on 31 October, 2025. Assumptions ----------- At a technical level, this tutorial assumes familiarity with 1. `Structured Query Language (SQL) `_ and `Common Table Expressions (CTEs) `_ within PostgreSQL relational database system. 2. `TimescaleDB hyperfunctions `_ and `PostGIS spatial functions `_. 3. `R `_ programming language for statistical analysis. 4. `tmap `_ and `sf `_ R language libraries. Bin Flights by Time Interval ---------------------------- Sq1090 project decodes ADS-B messages into aircraft positions and stores the data in the ``position`` database table. The table has time, aircraft ICAO address, and aircraft location columns (see the :numref:`sec-data-model` for details) ``time`` Time of receiving a raw message with aircraft location and altitude. ``icao`` ICAO address of an aircraft. It is an integer number displayed as a hexadecimal number. ``location`` Location and altitude of an aircraft. The positions are stored using `WGS 84 `_ coordinate system. Query the ``position`` table to find flight timeline of each aircraft within the geographical area covering KERAV and SORIN holding fixes and Dublin Airport on October 31, 2025 .. literalinclude:: ../scripts/traffic-on-hold :language: sql :start-after: with timeline as ( :end-at: group by tb_sec, icao :dedent: :linenos: The query - ``[2-4]`` extracts positions of each aircraft at one-second intervals, calculating average position if multiple data points exist within the same second - ``[6]`` finds aircrafts within the geographical area using a PostGIS spatial index to optimise execution of the predicate function - ``[7]`` filters the data leveraging TimescaleDB time index to optimise bucketing and finding aircraft positions based on the specified time interval Having flight timeline, bin flights into 15-minute time intervals and identify track of each aircraft .. literalinclude:: ../scripts/traffic-on-hold :language: sql :start-after: bin flights into 15-minute intervals :end-at: having count(location) > 1 :dedent: :linenos: The query - ``[2-3]`` buckets positions of each aircraft into 15-minute intervals - ``[4, 7]`` creates chronological sequence of aircraft positions; there needs to be at least one location The final data query is .. literalinclude:: ../scripts/traffic-on-hold :language: sql :start-at: with timeline as ( :end-at: having count(location) > 1 :dedent: :linenos: Read and Visualise Data ----------------------- Sq1090 database can be queried with SQL by many applications and programming languages. This tutorial uses R programming language to read and visualise the data. In R, use `RPostgreSQL` library to execute queries against Sq1090 database. There are many options to visualise the data on a map in R. `tmap` is an R package for drawing thematic maps using `grammar of graphics concept `_. It integrates with `sf` package, which in conjunction with `dplyr` library, enables creating and manipulating geographical data (`simple features `_). Additionally, use `urltools` library to pass database system parameters to the script developed in this tutorial. Install the libraries in R language environment, for example using its shell and ``install.packages`` function .. code-block:: $ R > install.packages(c('RPostgreSQL', 'dplyr', 'tmap', 'sf', 'urltools')) In R IDE or a text editor, create file with name ``traffic-on-hold``. We will populate the R script with commands to read aircraft positions from database, visualise traffic on a map, and save it into a PDF file. Import the libraries .. literalinclude:: ../scripts/traffic-on-hold :language: R :start-at: library(dplyr) :end-at: library(urltools) :dedent: :linenos: Parse database connection and output file arguments .. literalinclude:: ../scripts/traffic-on-hold :language: R :start-at: args = commandArgs :end-at: output = args :dedent: :linenos: Connect to Sq1090 database and execute SQL query (``QUERY`` variable is the query defined in previous section) .. literalinclude:: ../scripts/traffic-on-hold :language: R :start-at: pg_drv = dbDriver :end-at: data = dbGetQuery :dedent: :linenos: Define geographical features for Dublin Airport and KERAV and SORIN holding fixes .. literalinclude:: ../scripts/traffic-on-hold :language: R :start-at: airport.sf = :end-at: -5.765917, 53.62852 :dedent: :linenos: Convert data read from database into data with geographical features .. literalinclude:: ../scripts/traffic-on-hold :language: R :start-at: data.track = :end-at: icao=factor :dedent: :linenos: Plot the aircraft traffic for each 15-minute time interval in the geographical area covering Dublin Airport and KERAV and SORIN holding fixes (see the :numref:`fig-traffic-on-hold` in the introduction) .. literalinclude:: ../scripts/traffic-on-hold :language: R :start-at: credits = c :end-at: tmap_save(p :dedent: :linenos: Execute the script to create the plot in PDF format .. code-block:: Rscript traffic-on-hold postgresql://localhost:5432/sq1090 traffic-on-hold.pdf Conclusions ----------- Sq1090 decodes ADS-B messages into aircraft position data. The position data is stored in Sq1090 database ``position`` table. Query the table to determine flight timeline of each aircraft within a geographical area. In this tutorial, we have implemented the R script to visualise aircraft traffic at KERAV and SORIN holding fixes near Dublin Airport. The source code of the script can be found in Sq1090 project repository https://gitlab.com/sq1090/sq1090/-/blob/master/scripts/traffic-on-hold You can study Sq1090 project data model by reading its reference documentation, the :numref:`sec-data-model`. .. vim: sw=4:et:ai