4. Animate Aircraft Traffic

4.1. 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

  • transform aircraft locations to create animation data with dplyr R library

  • animate aircraft traffic with tmap R library

The outcome of the tutorial is presented in the video below. The animation shows airplanes in holding pattern at SORIN holding fix. The airport is marked with 5-kilometre buffer to display number of aircrafts in that area.

Aircraft traffic at SORIN holding fix near Dublin Airport on 31 October, 2025.

Note

This tutorial is similar to the one in the Section 3.

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.

4.2. 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, sf , and dplyr R language libraries.

4.3. 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 Section 3 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 positions of each aircraft, including its bearing, within the geographical area covering SORIN holding fix and Dublin Airport on October 31, 2025

 1with timeline as (
 2    select
 3        date_trunc('second', time) as tb,
 4        to_hex(icao) as icao,
 5        st_astext(st_centroid(st_collect(location))) as location
 6    from position
 7    where st_within(location, st_makeenvelope(-6.375, 52.96, -5.37, 53.475, 4326))
 8        and time between '2025-10-31 10:45Z' and '2025-10-31 11:30Z'
 9    group by tb, icao
10)
11select
12    tb,
13    icao,
14    location,
15    degrees(st_azimuth(location, lead(location) over (partition by icao order by tb))) as angle
16from timeline

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

4.4. 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. It integrates with sf package, which in conjunction with dplyr library, enables creating and manipulating geographical data (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

$ 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

1library(dplyr)
2library(grid)
3library(RPostgreSQL)
4library(sf)
5library(tidyr)
6library(tmap)
7library(urltools)

Parse database connection and output file arguments

1args = commandArgs(trailingOnly=T)
2db = url_parse(args[1])
3output = args[2]

Connect to Sq1090 database and execute SQL query (QUERY variable is the query defined in previous section)

1pg_drv = dbDriver('PostgreSQL')
2conn = dbConnect(pg_drv, dbname=db$path, host=db$domain, port=db$port)
3data = dbGetQuery(conn, QUERY)

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.

1airport.pos = st_point(c(-6.27, 53.4285)) |> st_sfc(crs=4326)
2airport.sf = (airport.pos |> st_buffer(dist=5000))
3
4data.hold = st_sf(name=c('SORIN'),
5                  location=list(st_point(c(-5.806250, 53.141472))) |> st_sfc(crs=4326))

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)

 1data.base = (data
 2             |> group_by(icao)
 3             |> summarise(start_time=min(tb), end_time=max(tb))
 4             |> mutate(end_time=end_time + TIME_END)
 5             |> group_by(icao)
 6             |> reframe(tb=seq(start_time, end_time, by=1))
 7             |> left_join(data, join_by(tb, icao))
 8             |> mutate(location=vec_fill_missing(location, max_fill=TIME_END),
 9                       angle=vec_fill_missing(angle, max_fill=TIME_END))
10             |> drop_na()
11             |> mutate(location=st_as_sfc(location, crs=4326),
12                       angle=-angle,
13                       at_airport=lengths(st_within(location, airport.sf)) > 0)
14             |> mutate(xmod=1.5 * sin(angle * (pi / 180)),
15                       ymod=-1.5 * cos(angle * (pi / 180)),
16                       icon=intToUtf8(0x01f6e7)))

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

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

1data.plane = st_sf(data.base |> filter(!at_airport))

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

 1data.airport = st_sf(data.base
 2                     |> filter(at_airport)
 3                     |> select(tb, icao)
 4                     |> group_by(tb)
 5                     |> summarise(icon=n())
 6                     |> mutate(icao='DUB',
 7                               angle=0,
 8                               xmod=0,
 9                               ymod=-1.25,
10                               icon=as.character(icon),
11                               location=airport.pos))

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.

 1selector = vars(tb, icao, angle, xmod, ymod, icon, location)
 2data.tf = (bind_rows(data.plane |> select(!!!selector),
 3                     data.airport |> select(!!!selector))
 4           |> arrange(tb, icao))
 5
 6bbox = data.tf |> st_transform(25830) |> st_buffer(dist=100) |> st_bbox()
 7
 8p.base = (tm_basemap('CartoDB.Positron')
 9          + tm_shape(airport.sf, bbox=bbox)
10          + tm_sf(col='orange', col_alpha=0.5, fill='orange', fill_alpha=0.1)
11          + tm_shape(data.hold)
12          + tm_dots('location', fill='red')
13          + tm_text('name', ymod=-0.75)
14          + tm_layout(frame=F))
15
16p.base.grob = tmap_grob(p.base)

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

 1frame_dir = sprintf('%s/frames', tempdir())
 2dir.create(frame_dir, recursive=T)
 3data.time.index = unique(data.tf$tb)
 4
 5invisible(lapply(seq_along(data.time.index), function(i) {
 6    data.tf.i = subset(data.tf, tb == data.time.index[i])
 7
 8    p = (tm_shape(data.tf.i, bbox=bbox)
 9         + tm_text('icon', angle='angle', size=2.25)
10         + tm_text('icao',
11                   angle='angle',
12                   xmod='xmod',
13                   ymod='ymod',
14                   bgcol='white',
15                   bgcol_alpha=0.5,
16                   options=opt_tm_text(bg.padding=0.2))
17         + tm_credits(as.character(data.time.index[i]),
18                      position=c('right', 'bottom'),
19                      size=1.2,
20                      bg=T,
21                      bg.color='white',
22                      bg.alpha=0.75)
23        + tm_credits(paste('Data by Sq1090, under ODbL.',
24                           'Map tiles by Carto, under CC BY 3.0.',
25                           'Data by OpenStreetMap, under ODbL.',
26                           sep='\n'),
27                     position=c('left', 'bottom'),
28                     size=1.2,
29                     bg=T,
30                     bg.color='white',
31                     bg.alpha=0.75)
32        + tm_layout(bg.color='transparent', frame=F))
33
34    png(sprintf('%s/frame-%06d.png', frame_dir, i), width=640, height=640, unit='px')
35    grid.newpage()
36    grid.draw(p.base.grob)
37    grid.draw(tmap_grob(p))
38    dev.off()
39}))
40
41files = list.files(path=frame_dir, pattern='frame-[0-9]+\\.png$', full.names=T)
42invisible(av_encode_video(files, output=output, framerate=60, verbose=F))
43unlink(frame_dir, recursive=T)

Execute the script to create the animation in MP4 format

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

4.5. 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

You can study Sq1090 project data model by reading its reference documentation, the Section 3.