3. Data Model
Sq1090 data model consists of four entities
- message
Raw Mode S and ADS-B messages captured by a receiver at specific time.
- callsign
Aircraft callsign and category information decoded from a raw message.
- position
Aircraft position, position error, and altitude information decoded from a raw message.
- assembly
View of latest aircraft callsign and position information.
The entities are available in the following contexts
Entity |
Stream |
Database |
Export |
|---|---|---|---|
message |
Y |
Y |
Y |
callsign |
Y |
Y |
Y |
position |
Y |
Y |
Y |
assembly |
Y |
N |
N |
The definition of each context is
- stream
Streams of records using RabbitMQ Streams broker. Each record is a list as defined in the next subsections. Stream data is available in a stream named
sq1090.{entity}, for examplesq1090.message.Description of data model uses data types as defined in AMQP 1.0 standard.
- database
Data is stored in PostgreSQL database tables using Timescale and PostGIS extensions. Table schema is described in the next subsections. Data is available in a table named
{entity}, for examplemessage.Description of data model uses data types as defined by PostgreSQL RDBMS system.
- export
Data is exported from PostgreSQL database tables into Parquet files. Parquet file schema is defined in the next subsections. Files are stored in
{entity}/{YYYY-MM-DD}.parquetfile, for examplemessage/2025-03-21.parquetfor aircraft raw messages for2025-03-21date.Description of data model uses (logical) data types as defined by Parquet standard.
3.1. Raw Messages
Raw Mode S and ADS-B messages captured by a receiver at specific time.
The records are available in sq1090.message RabbitMQ stream.
Each raw message record is a tuple of two fields
time: doubleTime of capturing a Mode S or an ADS-B raw message by a receiver.
Number of seconds since epoch.
data: binaryMode S or ADS-B raw message in a binary format. It has 7 or 14 bytes.
The records are available in message PostgreSQL database table.
The table containing raw message records has the schema
time: timestamp with time zoneTime of capturing a Mode S or an ADS-B raw message by a receiver.
Expected time zone of timestamps is UTC.
data: byteaMode S or ADS-B raw message in a binary format. It has 7 or 14 bytes.
The records are available in message/{YYYY-MM-DD}.parquet Parquet
files, exported for a specific day.
The file containing raw message records has two columns
time: timestamp[us, tz=UTC]Time of capturing a Mode S or an ADS-B raw message by a receiver.
Expected time zone of timestamps is UTC.
data: binaryMode S or ADS-B raw message in a binary format. It has 7 or 14 bytes.
3.2. Aircraft Callsign and Category
Aircraft callsign and category information decoded from a raw message.
The records are available in sq1090.callsign RabbitMQ stream.
Each callsign record is a list of 5 values
time: doubleTime of receiving a raw message with aircraft callsign and category information.
Number of seconds since epoch.
icao: uintICAO address of an aircraft.
Use a function to convert to a hexadecimal string.
category: ushortCategory of aircraft as defined by ICAO.
type: ushortType code of raw message helping to identify category of an aircraft.
callsign: stringAircraft callsign.
The records are available in callsign PostgreSQL database table.
The table containing callsign and category data has the schema
time: timestamp with time zoneTime of receiving a raw message with aircraft callsign and category information.
icao: integerICAO address of an aircraft.
Use a function to convert to a hexadecimal string.
category: smallintCategory of aircraft as defined by ICAO. An integer value.
type: smallintType code of raw message helping to identify category of an aircraft. An integer value.
callsign: varchar(8)Aircraft callsign.
The records are available in callsign/{YYYY-MM-DD}.parquet Parquet
files, exported for a specific day.
The file containing callsign and category records has 5 columns
time: timestamp[us, tz=UTC]Time of receiving a raw message with aircraft callsign and category information.
Expected time zone of timestamps is UTC.
icao: int32ICAO address of an aircraft.
Use a function to convert to a hexadecimal string.
category: int16Category of aircraft as defined by ICAO.
type: int16Type code of raw message helping to identify category of an aircraft.
callsign: stringAircraft callsign.
3.3. Aircraft Position
Aircraft position, position error, and altitude information decoded from a raw message.
The records are available in sq1090.position RabbitMQ stream.
Each position record is a list of 6 values
time: doubleTime of receiving a raw message with aircraft location and altitude.
Number of seconds since epoch.
icao: uintICAO address of an aircraft.
Use a function to convert to a hexadecimal string.
hpl: doubleHorizontal protection limit of the aircraft location (radius around reported aircraft position).
rcu: doubleContainment radius on horizontal position error.
rcv: doubleContainment radius on vertical position error.
location: list[double, double, double]Position and altitude of an aircraft.
List of longitude, latitude, and altitude values.
The records are available in position PostgreSQL database table.
The table containing aircraft position, altitude, and position error data has the schema
time: timestamp with time zoneTime of receiving a raw message with aircraft location and altitude.
Expected time zone of timestamps is UTC.
icao: integerICAO address of an aircraft.
Use a function to convert to a hexadecimal string.
hpl: numeric(6, 1)Horizontal protection limit of the aircraft location (radius around reported aircraft position).
rcu: numeric(5, 0)Containment radius on horizontal position error.
rcv: numeric(2, 0)Containment radius on vertical position error.
location: geometry(PointZ, 4326)Position and altitude of an aircraft.
The location and altitude is stored using PostGIS extension.
The records are available in position/{YYYY-MM-DD}.parquet
Parquet files, exported for a specific day.
The file containing aircraft position, altitude, and position error has 6 columns
time: timestamp[us, tz=UTC]Time of receiving a raw message with aircraft location and altitude.
Expected time zone of timestamps is UTC
icao: uintICAO address of an aircraft.
Use a function to convert to a hexadecimal string.
hpl: decimal128(6, 1)
Horizontal protection limit of the aircraft location (radius around reported aircraft position).
rcu: decimal128(5, 0)Containment radius on horizontal position error.
rcv: decimal128(2, 0)Containment radius on vertical position error.
location: binaryPosition and altitude of an aircraft.
WKB data of 3D points.
3.4. Aircraft Data View
View of latest aircraft callsign and position information.
The records are available in sq1090.assembled RabbitMQ stream.
Each record is a list of values
time: doubleTime of receiving a raw message with aircraft location and altitude.
Number of seconds since epoch.
icao: uintICAO address of an aircraft.
Use a function to convert to a hexadecimal string.
callsign: list[ushort, ushort, string]List of category, type code, and callsign. See callsign entity definition.
location: list[double, double, double]Tuple of longitude, latitude, and altitude. See position entity definition.
nuc: list[double, double, double]Tuple of horizontal protection limit, containment radius on horizontal and vertical position error. See position entity definition.
The aircraft data view is not available in the database.
The aircraft data view is not available in the exported data.