iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
You can query the ADSBiq Caribbean sample in DuckDB, but first reconstruct its aircraft states: rows marked as diffs may contain only fields that changed, not a complete observation. The sample contains 100,000 positioned observations from 2026-06-29 within 5–30° N and 95–55° W. It is a bounded, single-day sample—not a complete record of Caribbean air traffic.
What the sample contains—and what it does not
The ADSBiq dataset card describes a 100,000-row educational sample drawn from its daily open ADS-B corpus. It is provided as a Parquet file, displayed on the card as 1.58 MB, with a DuckDB notebook described as memory-safe. Those are the publisher’s descriptions; no independent benchmark of the file or notebook is available here.
The positioned observations fall inside a rectangular window from 5° to 30° north and 95° to 55° west on 2026-06-29. The boundaries describe the sample’s stated geographic and date coverage, not a guarantee that every aircraft in that area was observed. ADSBiq says its observations come from a distributed community receiver network; coverage varies with receiver locations, antenna placement, terrain, aircraft altitude, traffic, and time. As the dataset card puts it, “Missing observations do not prove that no aircraft were present.”
Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallDo not present this sample as the complete ADSBiq corpus or as a census of Caribbean flights. Its coverage limitations also mean that counts and movement summaries describe observed records in this sample, not necessarily all real-world aircraft activity in the region.
#1 Best Overall
Understand the state-diff rows before querying
The central data-model detail is is_snapshot. A row with is_snapshot=true marks a full aircraft state; later rows can be partial updates containing only fields that changed. Common fields listed on the dataset card include ts, hex, flight, lat, lon, alt_baro, gs, track, squawk, and type.
Consequently, a null in a diff row may mean “this field was not updated,” rather than “the aircraft had no value.” Counting rows with a non-null latitude, for example, could omit later positions when the latitude was unchanged and not repeated. Reconstruct the per-aircraft state first, then calculate counts or movement summaries from the reconstructed rows.
Load and inspect the Parquet sample
Use a local copy of the sample file. DuckDB can read Parquet directly; the following queries are illustrative SQL using the documented field names, not a tested reproduction of the linked notebook.
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Scan for outdated or missing drivers - takes under a minute3Clear out junk files and repair common Windows errors-
Inspect the available columns and types:
DESCRIBE SELECT * FROM read_parquet('caribbean.parquet'); -
Check the row count and snapshot markers:
SELECT is_snapshot, count(*) AS rows FROM read_parquet('caribbean.parquet') GROUP BY is_snapshot ORDER BY is_snapshot; -
Preview records in timestamp order so you can see how snapshot and diff rows are represented:
SELECT ts, hex, is_snapshot, flight, lat, lon, alt_baro, gs, track, squawk, type FROM read_parquet('caribbean.parquet') ORDER BY hex, ts LIMIT 20;
Confirm the schema and timestamp ordering in your own file before adapting the examples. In particular, verify that ts sorts chronologically and that the columns you plan to reconstruct are nullable state fields.
Reconstruct each aircraft’s state
For each state field, carry its last non-null value forward within an aircraft’s ordered rows. DuckDB’s last_value window function with IGNORE NULLS expresses that pattern. This example reconstructs the listed fields; adjust the selection to match the schema you inspected.
Rank #3
WITH ordered AS (
SELECT
ts,
hex,
is_snapshot,
flight,
lat,
lon,
alt_baro,
gs,
track,
squawk,
type
FROM read_parquet('caribbean.parquet')
),
reconstructed AS (
SELECT
ts,
hex,
is_snapshot,
last_value(flight IGNORE NULLS) OVER w AS flight,
last_value(lat IGNORE NULLS) OVER w AS lat,
last_value(lon IGNORE NULLS) OVER w AS lon,
last_value(alt_baro IGNORE NULLS) OVER w AS alt_baro,
last_value(gs IGNORE NULLS) OVER w AS gs,
last_value(track IGNORE NULLS) OVER w AS track,
last_value(squawk IGNORE NULLS) OVER w AS squawk,
last_value(type IGNORE NULLS) OVER w AS type
FROM ordered
WINDOW w AS (
PARTITION BY hex
ORDER BY ts
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
)
SELECT *
FROM reconstructed
ORDER BY hex, ts;The window is partitioned by aircraft identifier (hex), so values are carried forward only within the same aircraft. The explicit frame includes all earlier rows for that aircraft up to the current row. A row before an aircraft’s first populated value for a field remains null for that field; do not treat it as a reconstructed complete state.
Free tools Windows power users keep installed
One-click scans. No signup required.
This forward-fill approach follows the dataset card’s described convention. It assumes that null means “unchanged or not supplied” in the diff representation. If the data can encode an explicit clearing of a field as null, a simple forward fill cannot distinguish that from an omitted update; check the dataset’s semantics before interpreting such fields. Also inspect ties in ts: if an aircraft has multiple updates at the same timestamp, a stable tie-breaker is needed to establish their order. The documented field list does not identify one.
Summarize only after reconstruction
Once you have reconstructed the state rows, apply filters and aggregate the values that answer your question. For example, count reconstructed rows with positioned coordinates:
SELECT count(*) AS positioned_rows
FROM reconstructed
WHERE lat IS NOT NULL
AND lon IS NOT NULL;To summarize the observed aircraft identifiers in the reconstructed result:
SELECT count(DISTINCT hex) AS aircraft_with_rows
FROM reconstructed;These are counts in the sample, not estimates of all aircraft in the Caribbean. A row count is also not automatically a flight count: one aircraft can contribute many observations, and a single aircraft identifier can appear across multiple records. Define the unit being counted before reporting a result.
When another historical source is a better fit
OpenSky’s historical state-vector service is a separate source, not the origin of this ADSBiq sample. Its documentation describes querying historical data through Trino and MinIO, with application-reviewed access for university-affiliated researchers, government organizations, and aviation authorities conducting aviation research or incident investigations. Private or commercial entities are directed to contact OpenSky about a license.
OpenSky’s documented state_vectors_data4 includes Unix time, hexadecimal icao24, WGS84 latitude and longitude, velocity, heading, vertical rate, callsign, ground status, altitude, last-contact time, receiver serials, and an hour partition. Its guide specifies units of metres, metres per second, and seconds. These fields and access conditions apply to OpenSky, not to the ADSBiq sample.
OpenSky requires state-vector queries to filter on the hour partition; its guide says unpartitioned scans are rejected under current enforcement. It gives time - lastcontact <= 15 as an example stale-data condition and notes that vectors may remain for up to 300 seconds after an aircraft leaves coverage. The guide reports unlimited retention for state_vectors_data4 since 2013 and for flights_data4 since 2016. Those retention statements do not expand the date coverage of the one-day ADSBiq sample.
OpenSky also catalogs other scientific datasets, including weekly state-vector snapshots from 2017–2022, an ADS-C dataset, aircraft metadata, and a March 2026 snapshot of Trino tables. Choose a source based on the coverage dates and region you need, its observation model, access eligibility, license, and whether its receiver coverage can support the inference you want to make.
Attribute the data correctly
The ADSBiq dataset card states an ODbL-1.0 license and requests attribution to the ADSB IQ feeder network. The card separately identifies example code in its API-demo repository as MIT-licensed; that code license does not replace or change the data license. Review the dataset card’s terms when redistributing or publishing results based on the sample.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
