SQLite Database - davewalker5/ADS-B-BaseStationReader GitHub Wiki

Database Schema

Database Schema

Live Tracking Data

  • Live tracking data, extracted from BaseStation messages, is written to the following tables:
Table Name Contents
TRACKED_AIRCRAFT Aircraft 24-bit ICAO address and latest tracking details for an aircraft
POSITION Timestamped position records, recorded as latitude, longitude and altitude change
  • Each aircraft tracked in a given session has a record in the TRACKED_AIRCRAFT table that is created when the aircraft is first seen and updated as further messages are received from that aircraft
  • New records are created in the POSITION table as changes are reported, providing a live track of the aircraft
  • Recording of position data can be enabled/disabled via the tracking application configuration file and/or command line arguments to the application

Sighting Lookup Data

Table Name Contents
AIRCRAFT Aircraft registration and manufacturing details
MODEL Aircraft model codes and name
MANUFACTURER Aircraft model manufacturer details
FLIGHT Flight codes, number and route
AIRLINE Airline codes and name
SIGHTING Records a sighting of an aircraft operating a given flight at a date and time

Database Management

  • The application uses Entity Framework Core and initial creation and management of the database is achieved using EF Core database migrations
  • Both the tracking application and lookup application run the latest migrations on startup
  • This will:
    • Create the database if it doesn't exist
    • Apply the latest database structure changes, if not already done

Data Import

  • The lookup tool provides options to import airline, manufacturer and model information ahead of time
  • This potentially reduces the number of API calls needed to fully populate the lookup data for an aircraft and flight
  • This has both performance and cost advantages, especially if a paid external API is used

Record Locking

  • The ICAO 24-bit address is used as the unique identifier for an aircraft when writing updates to the database
  • Consequently, if an aircraft goes out of range then comes back into range, the original record would be picked up again on the second pass, though that pass may represent a different flight on a different date
  • Further, from this article:

Mode S equipped aircraft are assigned a unique ICAO 24-bit address or (informally) Mode-S "hex code" upon national registration and this address becomes a part of the aircraft's Certificate of Registration. Normally, the address is never changed, however, the transponders are reprogrammable and, occasionally, are moved from one aircraft to another (presumably for operational or cost purposes), either by maintenance or by changing the appropriate entry in the aircraft's Flight management system

  • The record for a given address should only be updated while the aircraft in question remains in range
  • Once it passes out of range, or when a new tracking session is started, if the address is seen again it should result in a new tracking record
  • This is achieved using the "Locked" status on tracking records (see the screenshot, above):
    • When an aircraft moves out of range and is removed from the tracking collection, a notional "lock timer" starts
    • If it's seen again within the timout, the record remains unlocked to avoid duplication of aircraft records for the same flight
    • Once the timeout is reached, the record is locked and any further updates for that ICAO address result in a new record
    • When the ContinuousWrtier starts, it immediately queues updates to mark all records that are not currently locked as locked, before accepting any other updates into the queue
  • Records marked as "Locked" are not considered candidates for further updates

Serial Writing

  • SQLite has been chosen as an appropriate DBMS for storing the data
  • It allows multiple readers but, at any one time, there can only be a single writer
  • As indicated above, the AircraftTracker exposes multiple events that require updates to be written to the database
  • If the console application attempts to write to the database from the event handlers as soon as an event notification is received, at some point a conflict arises between multiple concurrent updates and a "database is locked" error is thrown
  • Asynchronous, queued writing to the tracking database is required to avoid these conflicts and this is what the FIFO queue and the ContinusousWriter implement
  • This architecture has the further advantage that database updates are separated from the subscribing application

Querying the Database

  • To avoid conflicts between readers and writers that may cause a "database is locked" error and halt the application, WAL journal mode should be used when querying the database if the application is running
  • The following is an example query that uses a PRAGMA to enable WAL mode then lists all aircraft in the database matching the specified ICAO 24-bit address:
PRAGMA journal_mode=WAL;

SELECT *
FROM AIRCRAFT
WHERE Address = '3949F8';