Import Aircraft Data
====================
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

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

- `PostgreSQL <https://www.postgresql.org/>`_
- `PostGIS <https://postgis.net/>`_ extension
- `TimescaleDB <https://timescaledb.org/>`_ extension

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

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

    https://archive.org/search?query=subject%3Aads-b+creator%3Awrobell&sort=title

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 <https://en.wikipedia.org/wiki/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.

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)

.. vim: sw=4:et:ai