.. _sec-data-model: 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 example ``sq1090.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 example ``message``. 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}.parquet`` file, for example ``message/2025-03-21.parquet`` for aircraft raw messages for ``2025-03-21`` date. Description of data model uses (logical) data types as defined by Parquet standard. Raw Messages ------------ Raw Mode S and ADS-B messages captured by a receiver at specific time. .. tabs:: .. group-tab:: stream The records are available in ``sq1090.message`` RabbitMQ stream. Each raw message record is a tuple of two fields ``time: double`` Time of capturing a Mode S or an ADS-B raw message by a receiver. Number of seconds since epoch. ``data: binary`` Mode S or ADS-B raw message in a binary format. It has 7 or 14 bytes. .. group-tab:: database The records are available in ``message`` PostgreSQL database table. The table containing raw message records has the schema ``time: timestamp with time zone`` Time of capturing a Mode S or an ADS-B raw message by a receiver. Expected time zone of timestamps is UTC. ``data: bytea`` Mode S or ADS-B raw message in a binary format. It has 7 or 14 bytes. .. group-tab:: export 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: binary`` Mode S or ADS-B raw message in a binary format. It has 7 or 14 bytes. Aircraft Callsign and Category ------------------------------ Aircraft callsign and category information decoded from a raw message. .. tabs:: .. group-tab:: stream The records are available in ``sq1090.callsign`` RabbitMQ stream. Each callsign record is a list of 5 values ``time: double`` Time of receiving a raw message with aircraft callsign and category information. Number of seconds since epoch. ``icao: uint`` ICAO address of an aircraft. Use a function to convert to a hexadecimal string. ``category: ushort`` Category of aircraft as defined by ICAO. ``type: ushort`` Type code of raw message helping to identify category of an aircraft. ``callsign: string`` Aircraft callsign. .. group-tab:: database The records are available in ``callsign`` PostgreSQL database table. The table containing callsign and category data has the schema ``time: timestamp with time zone`` Time of receiving a raw message with aircraft callsign and category information. ``icao: integer`` ICAO address of an aircraft. Use a function to convert to a hexadecimal string. ``category: smallint`` Category of aircraft as defined by ICAO. An integer value. ``type: smallint`` Type code of raw message helping to identify category of an aircraft. An integer value. ``callsign: varchar(8)`` Aircraft callsign. .. group-tab:: export 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: int32`` ICAO address of an aircraft. Use a function to convert to a hexadecimal string. ``category: int16`` Category of aircraft as defined by ICAO. ``type: int16`` Type code of raw message helping to identify category of an aircraft. ``callsign: string`` Aircraft callsign. Aircraft Position ----------------- Aircraft position, position error, and altitude information decoded from a raw message. .. tabs:: .. group-tab:: stream The records are available in ``sq1090.position`` RabbitMQ stream. Each position record is a list of 6 values ``time: double`` Time of receiving a raw message with aircraft location and altitude. Number of seconds since epoch. ``icao: uint`` ICAO address of an aircraft. Use a function to convert to a hexadecimal string. ``hpl: double`` Horizontal protection limit of the aircraft location (radius around reported aircraft position). ``rcu: double`` Containment radius on horizontal position error. ``rcv: double`` Containment radius on vertical position error. ``location: list[double, double, double]`` Position and altitude of an aircraft. List of longitude, latitude, and altitude values. .. group-tab:: database 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 zone`` Time of receiving a raw message with aircraft location and altitude. Expected time zone of timestamps is UTC. ``icao: integer`` ICAO 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. .. group-tab:: export 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: uint`` ICAO 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: binary`` Position and altitude of an aircraft. WKB data of 3D points. Aircraft Data View ------------------ View of latest aircraft callsign and position information. .. tabs:: .. group-tab:: stream The records are available in ``sq1090.assembled`` RabbitMQ stream. Each record is a list of values ``time: double`` Time of receiving a raw message with aircraft location and altitude. Number of seconds since epoch. ``icao: uint`` ICAO 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. .. group-tab:: database The aircraft data view is not available in the database. .. group-tab:: export The aircraft data view is not available in the exported data. .. vim: sw=4:et:ai