Query 100,000 Caribbean Aircraft State Changes with DuckDB

Real-world streaming data rarely arrives as perfectly reconstructed rows. To
save bandwidth and storage, ADS-B archives can record a complete aircraft state
followed by sparse rows containing only fields that changed.

This tutorial uses a free, bounded Caribbean aircraft-data sample to explore
that model directly with DuckDB. The file contains 100,000 observations, is
only about 1.6 MB compressed, and has a permanent DOI.

Disclosure: I am affiliated with ADSBiq, the community-powered project that
produced this dataset. The sample is free under ODbL-1.0, and no hardware is
required.

What is in the sample?

The sample covers positioned observations between 5-30 degrees north and
95-55 degrees west on June 29, 2026. Common columns include:

• ts: observation timestamp
• hex: ICAO 24-bit aircraft address
• flight: transmitted callsign
• lat, lon: position
• alt_baro: barometric altitude
• gs: ground speed
• track: direction of travel
• type: aircraft type designator
• is_snapshot: whether the row contains a complete state
• is_removed: whether the aircraft left coverage

Rows after a snapshot can be sparse diffs. A null value therefore often means
"unchanged," not "unknown forever."

Install DuckDB

Use the DuckDB CLI or its Python package:

python -m pip install duckdb

The data is small enough for a laptop, but DuckDB can query Parquet lazily and
push filters into the scan. There is no reason to load every column into a
large dataframe first.

Query the Parquet file directly from Zenodo

import duckdb

url = (
    "https://zenodo.org/api/records/22062551/files/"
    "caribbean_sample_2026-06-29.parquet/content"
)

con = duckdb.connect()
con.execute("INSTALL httpfs")
con.execute("LOAD httpfs")

summary = con.execute("""
    SELECT
        count(*) AS rows,
        count(DISTINCT hex) AS aircraft,
        min(ts) AS first_observation,
        max(ts) AS last_observation
    FROM read_parquet(?)
""", [url]).fetchdf()

print(summary)

Count positioned observations by one-degree grid cell

Grid aggregation gives a quick, privacy-safe view of where the sample contains
observations:

grid = con.execute("""
    SELECT
        floor(lat) AS latitude_cell,
        floor(lon) AS longitude_cell,
        count(*) AS observations,
        count(DISTINCT hex) AS aircraft
    FROM read_parquet(?)
    WHERE lat IS NOT NULL
      AND lon IS NOT NULL
    GROUP BY ALL
    ORDER BY observations DESC
    LIMIT 25
""", [url]).fetchdf()

print(grid)

This measures observations in the sample. It does not measure all aircraft
activity. Receiver locations, antenna height, terrain, interference, aircraft
altitude, traffic, and time all affect crowdsourced coverage.

Start reconstruction with snapshots

For analyses that do not need every intermediate state, begin with complete
snapshot rows:

snapshots = con.execute("""
    SELECT hex, ts, flight, lat, lon, alt_baro, gs, track, type
    FROM read_parquet(?)
    WHERE is_snapshot
      AND lat BETWEEN 17 AND 19
      AND lon BETWEEN -68.5 AND -65
    ORDER BY ts
    LIMIT 1000
""", [url]).fetchdf()

For a complete trajectory, partition by hex, order by ts, and forward-fill
nullable state fields after a snapshot. Keep removal rows so that separate
coverage sessions are not accidentally joined into one continuous flight.

Turn it into a student or club project

Useful extensions include:

1. Compare observation density near different Caribbean airports.
2. Visualize altitude against reception distance.
3. Build a DuckDB or Polars pipeline that reconstructs selected aircraft.
4. Design a live island aviation dashboard using the REST API.
5. Measure how receiver or antenna changes affect subsequent observations.

The Caribbean STEM project page
collects the API, dataset, coding examples, and optional receiver path. Regional
versions are also available for Jamaica, Puerto Rico, Key West, and the Cayman
Islands.

Resources

• Citable dataset: DOI 10.5281/zenodo.22062551
• Python examples and coverage notebook
• Complete daily Parquet corpus
• Live API documentation

Missing observations do not prove that no aircraft were present, and this data
must not be used for navigation, separation, enforcement, or safety-of-life
decisions.