1. Import Aircraft Data

1.1. Introduction

Sq1090 project publishes raw and decoded aircraft data to the Internet Archive. The data can be imported into a database and queried with SQL.

In this tutorial you will

  • create Sq1090 database

  • download aircraft data from Internet Archive

  • populate the database with the data

1.2. Assumptions

At a technical level, this tutorial assumes familiarity with PostgreSQL database system and its extensions.

Before moving on to the next step, install the following software

1.3. Create Database

Once PostgreSQL system and required extensions are available, Sq1090 database can be created.

Sq1090 database definition is part of the main software component of the Sq1090 project. It can be installed with the command:

$ pip install -U sq1090

Use PostgreSQL createdb command to create Sq1090 database named sq1090:

$ createdb sq1090

Create tables and load reference data into sq1090 database with command:

$ n23-db sq1090.db | psql sq1090
CREATE EXTENSION
CREATE EXTENSION
...
INSERT 0 32
INSERT 0 1

1.4. Download Data

The data published by Sq1090 project can be found on the Internet Archive

Internet Archive allows downloading individual files or whole dataset using BitTorrent protocol.

For example, to download a set of individual files to sq1090-data directory with GNU Wget:

$ wget -P sq1090-data/ -x -nH --cut-dirs=2 https://archive.org/download/sq1090-b20-2026/{callsign,message,position}/2026-01-{01,02}.parquet
...
FINISHED --2026-07-29 20:20:50--
Total wall clock time: 1h 19m 14s
Downloaded: 6 files, 462M in 1h 19m 2s (99.7 KB/s)

$ find sq1090-data -type f | sort
sq1090-data/callsign/2026-01-01.parquet
sq1090-data/callsign/2026-01-02.parquet
sq1090-data/message/2026-01-01.parquet
sq1090-data/message/2026-01-02.parquet
sq1090-data/position/2026-01-01.parquet
sq1090-data/position/2026-01-02.parquet

Note

Downloading individual files from Internet Archive can be slow. It is recommended to download the files using BitTorrent protocol.

1.5. Import Data

After downloading data files from Internet Archive, import them to Sq1090 database.

Install Sq1090 Import/Export sub-project:

$ pip install -U sq1090-imex

Use sq1090-import command to import the data files from sq1090-data directory into sq1090 database:

$ sq1090-import postgresql://localhost/sq1090 sq1090-data/*/*.parquet
INFO:sq1090imex.db:metadata table: table=callsign, version=1.0.0
INFO:sq1090-import:file imported: file=sq1090-data/callsign/2026-01-01.parquet, table=callsign
INFO:sq1090imex.db:metadata table: table=callsign, version=1.0.0
INFO:sq1090-import:file imported: file=sq1090-data/callsign/2026-01-02.parquet, table=callsign
INFO:sq1090imex.db:metadata table: table=message, version=1.0.0
INFO:sq1090-import:file imported: file=sq1090-data/message/2026-01-01.parquet, table=message
INFO:sq1090imex.db:metadata table: table=message, version=1.0.0
INFO:sq1090-import:file imported: file=sq1090-data/message/2026-01-02.parquet, table=message
INFO:sq1090imex.db:metadata table: table=position, version=1.0.0
INFO:sq1090-import:file imported: file=sq1090-data/position/2026-01-01.parquet, table=position
INFO:sq1090imex.db:metadata table: table=position, version=1.0.0
INFO:sq1090-import:file imported: file=sq1090-data/position/2026-01-02.parquet, table=position

Above command takes target database and files to import as its arguments. The metadata in the files specifies what database tables the data belongs to.

Check that data is loaded into sq1090 database:

$ psql sq1090
sq1090=# select time_bucket('1d', time) as tb, count(*) from callsign group by tb order by tb;
           tb           | count
------------------------+--------
 2026-01-01 00:00:00+00 | 510574
 2026-01-02 00:00:00+00 | 546404
(2 rows)

sq1090=# select time_bucket('1d', time) as tb, count(*) from message group by tb order by tb;
           tb           |  count
------------------------+----------
 2026-01-01 00:00:00+00 | 29987053
 2026-01-02 00:00:00+00 | 33136346
(2 rows)

sq1090=# select time_bucket('1d', time) as tb, count(*) from position group by tb order by tb;
           tb           |  count
------------------------+---------
 2026-01-01 00:00:00+00 | 2390241
 2026-01-02 00:00:00+00 | 2624875
(2 rows)