Animate Aircraft Traffic
========================
Introduction
------------
Sq1090 project decodes ADS-B messages into aircraft position data. Use the
data to animate 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 an SQL query to retrieve aircraft locations within
  the geographical area covering SORIN holding fix and Dublin Airport;
  we will exclude KERAV holding fix traffic for a cleaner animation
- prepare geographical data for the holding fix and the airport with
  `sf R library <https://r-spatial.github.io/sf/>`_
- transform aircraft locations to create animation data with
  `dplyr R library <https://dplyr.tidyverse.org/>`_
- animate aircraft traffic with
  `tmap R library <https://r-tmap.github.io/tmap/>`_

The outcome of the tutorial is presented in the video below. The animation
shows airplanes in `holding pattern
<https://en.wikipedia.org/wiki/Holding_(aeronautics)>`_ at SORIN holding
fix. The airport is marked with 5-kilometre buffer to display number of
aircrafts in that area.

.. video:: images/traffic-on-hold-anim.mp4
   :loop:
   :width: 640
   :align: center
   :caption: Aircraft traffic at SORIN holding fix near Dublin Airport on
             31 October, 2025.

.. note::

   This tutorial is similar to the one in the :numref:`sect-traffic-vis`.

   The other tutorial plots flight data in 15-minute time intervals over
   at SORIN and KERAV holding fixes near Dublin Airport. That visualisation
   can help in choosing an aircraft traffic time for this tutorial.

Assumptions
-----------
At a technical level, this tutorial assumes familiarity with

1. `Structured Query Language (SQL)
   <https://www.postgresql.org/docs/current/sql.html>`_
   and `Common Table Expressions (CTEs)
   <https://www.postgresql.org/docs/current/queries-with.html>`_ within
   PostgreSQL relational database system.
2. `TimescaleDB hyperfunctions
   <https://www.tigerdata.com/docs/reference/timescaledb/hyperfunctions>`_
   and `PostGIS spatial functions
   <https://postgis.net/docs/PostGIS_Special_Functions_Index.html>`_.
3. `R <https://www.r-project.org/>`_ programming language for statistical
   analysis.
4. `tmap <https://r-tmap.github.io/tmap/>`_,
   `sf <https://r-spatial.github.io/sf/>`_ , and
   `dplyr <https://dplyr.tidyverse.org/>`_ R language libraries.

Find Aircraft Locations
-----------------------
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 <https://en.wikipedia.org/wiki/World_Geodetic_System>`_
    coordinate system.

Query the ``position`` table to find positions of each aircraft, including
its bearing, within the geographical area covering SORIN holding fix and
Dublin Airport on October 31, 2025

.. literalinclude:: ../scripts/traffic-on-hold-anim
   :language: sql
   :start-at: with timeline as (
   :end-at: from timeline
   :dedent:
   :linenos:

The query

- ``[3-5]`` extracts positions of each aircraft at one-second intervals,
  calculating average position if multiple data points exist within the
  same second
- ``[7]`` finds aircrafts within the geographical area using a PostGIS
  spatial index to optimise execution of the predicate function
- ``[8]`` filters the data leveraging TimescaleDB time index to optimise
  bucketing and finding aircraft positions within the specified time
  interval
- ``[15]`` calculates bearing of an aircraft at each aircraft position; the
  bearing is measured in degrees

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 animate 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
<https://en.wikipedia.org/wiki/Wilkinson%27s_Grammar_of_Graphics>`_.
It integrates with `sf` package, which in conjunction with `dplyr` library,
enables creating and manipulating geographical data (`simple features
<https://en.wikipedia.org/wiki/Simple_Features>`_).

Additionally, use

- `av` library to create a video from a series of plots
- `urltools` library to pass database system parameters to the script
  developed in this tutorial

.. note::

   When writing the script for this tutorial, the author has found memory
   issues in `tmap` package animation code, see
   https://github.com/r-tmap/tmap/issues/1237.

   The problem forces us to abandon use of `tm_animate` function. Instead,
   at the price of the increasing complexity of the tutorial, we roll our
   custom code to generate plots for the video.

Install the libraries in R language environment, for example using its
shell and ``install.packages`` function

.. code-block::

   $ R
   > install.packages(c('av', 'dplyr', 'grid', 'RPostgreSQL', 'sf', 'tidyr', 'tmap', 'urltools', 'vctrs'))

In R IDE or a text editor, create file with name ``traffic-on-hold-anim``.
We will populate the R script with commands to read aircraft positions from
database, create animation data, animate the traffic on a map, and save it
into a MP4 video file.

Import the libraries

.. literalinclude:: ../scripts/traffic-on-hold-anim
   :language: R
   :start-at: library(dplyr)
   :end-at: library(urltools)
   :dedent:
   :linenos:

Parse database connection and output file arguments

.. literalinclude:: ../scripts/traffic-on-hold-anim
   :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-anim
   :language: R
   :start-at: pg_drv = dbDriver
   :end-at: data = dbGetQuery
   :dedent:
   :linenos:

Define geographical features for Dublin Airport and SORIN holding fix. The
airport's 5-kilometre buffer will be used later to count airplanes within
its area.

.. literalinclude:: ../scripts/traffic-on-hold-anim
   :language: R
   :start-at: airport.pos =
   :end-at: -5.806250, 53.141472
   :dedent:
   :linenos:

Create the base dataframe for animation, containing aircraft flight tracks
and indicator whether an aircraft is within the airport area (``TIME_END``
variable is set to 60 seconds)

.. literalinclude:: ../scripts/traffic-on-hold-anim
   :language: R
   :start-at: data.base =
   :end-at: icon=intToUtf8(0x01f6e7)
   :dedent:
   :linenos:

When creating the base dataframe

- ``[2-4]`` identtify start and end of a flight of each aircraft, extend
  every flight by 60 seconds (``TIME_END``) to show an airplane on a map for
  context
- ``[5-6]`` create aircrafts' flight timeline, second-by-second
- ``[7-10]`` merge the aircrafts' flight timeline with the original dataset;
  fill missing aircraft location and bearing with last known data to avoid
  gaps in the animation
- ``[11]`` convert aircraft location fetched from database to `sf` vector
- ``[12]`` change sign of aircraft bearing to allow rotation of aircraft
  symbol on the plot
- ``[13]`` identify if a location is within the airport area
- ``[14-15]`` calculate text position under aircraft symbol on the map
- ``[16]`` define symbol for aircraft position using `Unicode up-pointing
  airplane <https://unicodeplus.com/U+1F6E7>`_

Prepare data for aircrafts in-flight (outside the airport area)

.. literalinclude:: ../scripts/traffic-on-hold-anim
   :language: R
   :start-at: data.plane =
   :end-at: data.plane =
   :dedent:
   :linenos:

Count aircrafts within the airport area and prepare data to be displayed on
the map

.. literalinclude:: ../scripts/traffic-on-hold-anim
   :language: R
   :start-at: data.airport =
   :end-at: location=airport.pos
   :dedent:
   :linenos:

Prepare the basemap for the animation. The basemap uses positron tiles from
CartoDB project, and displays both the SORIN holding fix and the area of
the airport. The rendered basemap is stored as R's grid graphical object
(grob) to enable efficient generation of frames of the animation.

.. literalinclude:: ../scripts/traffic-on-hold-anim
   :language: R
   :start-at: selector = vars
   :end-at: p.base.grob = tmap_grob(p.base)
   :dedent:
   :linenos:

Create frame for each second of the aircrafts' flight timeline and encode
them at 60 frames per second into a video file

.. literalinclude:: ../scripts/traffic-on-hold-anim
   :language: R
   :start-at: frame_dir = sprintf
   :end-at: unlink(frame_dir, recursive=T)
   :dedent:
   :linenos:

Execute the script to create the animation in MP4 format

.. code-block::

   Rscript traffic-on-hold-anim postgresql://localhost:5432/sq1090 traffic-on-hold-anim.mp4

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 animate aircraft
traffic at SORIN holding fix 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-anim

You can study Sq1090 project data model by reading its reference
documentation, the :numref:`sec-data-model`.

.. vim: sw=4:et:ai