Skip to content

OAG database

Jump to bottom
Ian Ross edited this page Aug 25, 2025 · 3 revisions

This page describes the schema and some queries for the SQLite database we're going to use to store OAG flight schedule data.

Schema

In SQLite, foreign key constraints are enabled at the connection level, so this needs to be done every time we connect to the database.

PRAGMA foreign_keys = ON;

This table has one row per entry in the OAG input and mostly stores metadata about flights. The actual flight schedules are stored separately.

CREATE TABLE flights (
  id INTEGER PRIMARY KEY NOT NULL,

  -- IATA carrier designator, 2 characters.
  carrier TEXT NOT NULL,

  -- Flight number, up to 4 digits, possibly with leading zeros.
  flight_number TEXT NOT NULL,

  -- Origin and destination airport IDs.
  origin INTEGER NOT NULL REFERENCES airports(id),
  destination INTEGER NOT NULL REFERENCES airports(id),

  -- Monday = 1, Sunday = 7: mask = sum_{d \in days} 1 << (d - 1)
  day_of_week_mask INTEGER NOT NULL,

  -- Integer minutes from midnight. Long flights that depart on one day and
  -- arrive the next have arrival_time < departure_time.
  departure_time INTEGER NOT NULL,
  arrival_time INTEGER NOT NULL,

  -- TODO: Separate table for equipment details?
  aircraft_type TEXT NOT NULL,
  engine_type TEXT,

  -- This needs to be converted to kilometers, which makes it a floating point
  -- value. (We could omit it and calculate it from the airport coordinates,
  -- but we want to be able to filter on distance, so it's probably better to
  -- have it pre-computed here.)
  distance REAL NOT NULL,

  seat_capacity INTEGER NOT NULL,

  -- Dates as YYYY-MM-DD.
  effective_from TEXT NOT NULL,
  effective_to TEXT NOT NULL,

  -- Calculated from schedules as the table is being populated.
  number_of_flights INTEGER NOT NULL,

  -- Concatenation of origin and destination IATA codes, ordered
  -- alphabetically for use in most frequent flight calculations.
  od_pair TEXT NOT NULL
);

CREATE INDEX flights_carrier_idx ON flights(carrier);
CREATE INDEX flights_origin_idx ON flights(origin);
CREATE INDEX flights_destination_idx ON flights(destination);
CREATE INDEX flight_distance_idx ON flights(distance);
CREATE INDEX flight_seat_capacity_idx ON flights(seat_capacity);
CREATE INDEX flights_aircraft_type_idx ON flights(aircraft_type);
CREATE INDEX flights_od_pair_idx ON flights(od_pair);

The schedules table includes one row for each actual flight represented by the entries in the OAG data.

CREATE TABLE schedules (
  id INTEGER PRIMARY KEY NOT NULL,

  -- Seconds from epoch (1970-01-01 00:00:00 UTC).
  departure_timestamp INTEGER NOT NULL,
  arrival_timestamp INTEGER NOT NULL,

  -- Days from epoch (1970-01-01). Allows easy implementation of "every 5
  -- days", etc.
  day INTEGER NOT NULL,

  flight_id INTEGER NOT NULL REFERENCES flights(id)
);

CREATE INDEX schedules_time_idx ON schedules(departure_timestamp);
CREATE INDEX schedules_flight_id_idx ON schedules(flight_id);

Some supplementary data is needed, so we have a table for airports and countries, both populated from files downloaded from https://ourairports.com/data/

CREATE TABLE airports (
  id INTEGER PRIMARY KEY NOT NULL,

  iata_code TEXT NOT NULL,
  name TEXT NOT NULL,
  municipality TEXT,

  -- Use ISO 3166-1 alpha-2 codes.
  country TEXT NOT NULL REFERENCES countries(code),

  latitude REAL NOT NULL,
  longitude REAL NOT NULL,
  elevation REAL
);

CREATE UNIQUE INDEX airports_iata_code_idx ON airports(iata_code);
CREATE INDEX airports_country_idx ON airports(country);

CREATE VIRTUAL TABLE IF NOT EXISTS airport_location_idx USING rtree(
  id INTEGER PRIMARY KEY,
  min_latitude, max_latitude,
  min_longitude, max_longitude
);


CREATE TABLE countries (
  -- ISO 3166-1 alpha-2 code
  code TEXT PRIMARY KEY NOT NULL,
  name TEXT NOT NULL,
  continent TEXT NOT NULL
);

Queries

These cases are taken from Adi's slides from our meeting on 2025-08-21.

Case 1: Annual environmental assessment ​

  1. Load full year OAG data
  2. Filter based on​: range OR​ seats OR​ location (country, continent, region etc) OR ​AC Type (or list of AC codes)

First an example without filtering:

SELECT s.departure_timestamp, s.arrival_timestamp,
       f.id as flight_id, f.carrier, f.flight_number,
       ao.iata_code AS origin, ad.iata_code AS destination,
       f.aircraft_type, f.engine_type, f.distance, f.seat_capacity
  FROM schedules s
  JOIN flights f ON f.id = s.flight_id
  JOIN airports ao ON f.origin = ao.id
  JOIN airports ad ON f.destination = ad.id
 ORDER BY s.departure_timestamp;

And with filtering, using the same sort of approach:

SELECT s.departure_timestamp, s.arrival_timestamp,
       f.id as flight_id, f.carrier, f.flight_number,
       ao.iata_code AS origin, ad.iata_code AS destination,
       f.aircraft_type, f.engine_type, f.distance, f.seat_capacity
  FROM schedules s
  JOIN flights f ON f.id = s.flight_id
  JOIN airports ao ON f.origin = ao.id
  JOIN airports ad ON f.destination = ad.id
 WHERE f.distance > 1000 AND f.distance <= 5000
   AND f.seat_capacity >= 200
   AND (ao.country IN ('US') OR ad.country IN ('US'))
   AND f.aircraft_type IN ('777', '787', 'A350')
 ORDER BY s.departure_timestamp;

For spatial filtering, the simplest approach is to do a separate airport query first to get the list of possible origin and destination airports.

Here's an alternative way to do the filtering, which has more in common with Case 2:

WITH
  ids AS (
    SELECT s.id
      FROM schedules s
      JOIN flights f ON s.flight_id = f.id
      JOIN airports ao ON f.origin = ao.id
      JOIN airports ad ON f.destination = ad.id
     WHERE f.distance > 1000 AND f.distance <= 5000
       AND f.seat_capacity >= 200
       AND (ao.country IN ('US') OR ad.country IN ('US'))
       AND f.aircraft_type IN ('777', '787', 'A350'))
SELECT s.departure_timestamp, s.arrival_timestamp,
       f.id as flight_id, f.carrier, f.flight_number,
       ao.iata_code AS origin, ad.iata_code AS destination,
       f.aircraft_type, f.engine_type, f.distance, f.seat_capacity
  FROM schedules s
  JOIN flights f ON f.id = s.flight_id
  JOIN airports ao ON f.origin = ao.id
  JOIN airports ad ON f.destination = ad.id
 WHERE s.id IN (SELECT id FROM ids)
 ORDER BY s.departure_timestamp;

Case 2: Difference in environmental impact​

I think this is just a variation on the above, where we select a random sub-sample of the scheduled flights.

Here we select a random 5% sample of the filtered results:

WITH
  all_ids AS (
    SELECT s.id
      FROM schedules s
      JOIN flights f ON s.flight_id = f.id
      JOIN airports ao ON f.origin = ao.id
      JOIN airports ad ON f.destination = ad.id
     WHERE f.distance > 1000 AND f.distance <= 5000
       AND f.seat_capacity >= 200
       AND (ao.country IN ('US') OR ad.country IN ('US'))
       AND f.aircraft_type IN ('777', '787', 'A350')),
  ids AS (
    SELECT id FROM all_ids ORDER BY RANDOM()
     LIMIT floor((SELECT 0.05 * COUNT(*) FROM all_ids))
  )
SELECT s.departure_timestamp, s.arrival_timestamp,
       f.id as flight_id, f.carrier, f.flight_number,
       ao.iata_code AS origin, ad.iata_code AS destination,
       f.aircraft_type, f.engine_type, f.distance, f.seat_capacity
  FROM schedules s
  JOIN flights f ON f.id = s.flight_id
  JOIN airports ao ON f.origin = ao.id
  JOIN airports ad ON f.destination = ad.id
  WHERE s.id IN (SELECT id FROM ids)
  ORDER BY s.departure_timestamp;

Case 3: Capturing most frequent flights​

  1. Load full year OAG data​
  2. Filter by range​
  3. Get most frequent OD pairs ​(where JFKBOS is the same as BOSJFK)
WITH
  counts AS (
    SELECT COUNT(s.id) AS nflights, f.od_pair AS od_pair
      FROM schedules s
          JOIN flights f ON s.flight_id = f.id
    WHERE f.distance > 1000 AND f.distance <= 5000
    GROUP BY od_pair)
 SELECT substring(od_pair, 1, 3) AS airport1,
        substring(od_pair, 4) AS airport2,
        nflights
  FROM counts
 ORDER BY nflights DESC
 LIMIT 50;

Case 4: Capturing schedule seasonality ​

  1. Load full year OAG data​
  2. Filter by range​
  3. Sample by dates: for example, get every 8th day of flights in the year​.

This is the same as Case 1, but we only select every 8th day with an additional condition on the final SELECT. The start day and frequency can be adjusted by changing the modulus and offset in the condition. The same sort of thing can be used for selecting only weekday or weekend flights.

WITH
  ids AS (
    SELECT s.id
      FROM schedules s
      JOIN flights f ON s.flight_id = f.id
      JOIN airports ao ON f.origin = ao.id
      JOIN airports ad ON f.destination = ad.id
     WHERE f.distance > 1000 AND f.distance <= 5000)
SELECT s.departure_timestamp, s.arrival_timestamp,
       f.id as flight_id, f.carrier, f.flight_number,
       ao.iata_code AS origin, ad.iata_code AS destination,
       f.aircraft_type, f.engine_type, f.distance, f.seat_capacity
  FROM schedules s
  JOIN flights f ON f.id = s.flight_id
  JOIN airports ao ON f.origin = ao.id
  JOIN airports ad ON f.destination = ad.id
 WHERE s.id IN (SELECT id FROM ids) AND (s.day % 8) = 1
 ORDER BY s.departure_timestamp;

Clone this wiki locally