Now we are ready to construct airframe or airplane trajectories out of their individual observations. Each “icao24” in our dataset represents a single airplane.

We can create a composite index on icao24 (unique to each plane) and et_ts (timestamps of observations) to help improve the performance of trajectory generation.

CREATE INDEX icao24_time_index
    ON flights (icao24, et_ts);

We create trajectories for a single airframe because:

CREATE TABLE airframe_traj(icao24, trip, velocity, heading, vertrate, callsign, squawk,
                           geoaltitude) AS
SELECT icao24,
       tgeompoint_seq(array_agg(tgeompoint_inst(geom, et_ts) ORDER BY et_ts)
                      FILTER (WHERE geom IS NOT NULL)),
       tfloat_seq(array_agg(tfloat_inst(velocity, et_ts) ORDER BY et_ts)
                  FILTER (WHERE velocity IS NOT NULL)),
       tfloat_seq(array_agg(tfloat_inst(heading, et_ts) ORDER BY et_ts)
                  FILTER (WHERE heading IS NOT NULL)),
       tfloat_seq(array_agg(tfloat_inst(vertrate, et_ts) ORDER BY et_ts)
                  FILTER (WHERE vertrate IS NOT NULL)),
       ttext_seq(array_agg(ttext_inst(callsign, et_ts) ORDER BY et_ts)
                 FILTER (WHERE callsign IS NOT NULL)),
       tint_seq(array_agg(tint_inst(squawk, et_ts) ORDER BY et_ts)
                FILTER (WHERE squawk IS NOT NULL)),
       tfloat_seq(array_agg(tfloat_inst(geoaltitude, et_ts) ORDER BY et_ts)
                  FILTER (WHERE geoaltitude IS NOT NULL))
FROM flights
GROUP BY icao24;
                

Here we create a new table for all the trajectories. We select all the attributes of interest that change over time. We can follow the transformation from the inner call to the outer call:

Right now we have, in a single row, an airframe’s (where an airframe is a single physical airplane) entire day’s trip information. We would like to segment that information per flight (an airframe flying under a specific callsign). This query segments the airframe trajectories (in temporal columns) based on the time period of the callsign. Below we explain the query and the reason behind segmenting the data this way.

-- Each row from airframe will create a new row in flight_traj depending on when the
-- callsign changes, regardless of whether a plane repeats the same flight multiple
-- times in any period

-- Airplane123 (airframe_traj) |-------------------------|
-- Flightpath1 (flight_traj)   |-----|
-- Flightpath2 (flight_traj)         |--------|
-- Flightpath1 (flight_traj)                  |-------|
-- Flightpath3 (flight_traj)                          |--|

CREATE TABLE flight_traj(icao24, callsign, flight_period, trip, velocity, heading,
                         vertrate, squawk, geoaltitude)
AS
    -- callsign sequence unpacked into rows to split all other temporal sequences.
WITH airframe_traj_with_unpacked_callsign AS
	(SELECT icao24, 
            trip, 
            velocity, 
            heading, 
            vertrate, 
            squawk, 
            geoaltitude, 
            startValue(unnest(segments(callsign))) AS start_value_callsign,
		    unnest(segments(callsign))::tstzspan AS callsign_segment_period
	FROM airframe_traj)
SELECT icao24 AS icao24, 
        start_value_callsign AS callsign, 
        callsign_segment_period AS flight_period, 
        atTime(trip, callsign_segment_period) AS trip, 
        atTime(velocity, callsign_segment_period) AS velocity, 
        atTime(heading, callsign_segment_period) AS heading, 
        atTime(vertrate, callsign_segment_period) AS vertrate, 
        atTime(squawk, callsign_segment_period) AS squawk, 
        atTime(geoaltitude, callsign_segment_period) AS geoaltitude
FROM airframe_traj_with_unpacked_callsign;
            

Note: We could have tried to create the above (table ”flight_traj”) per flight trajectories by simply including “callsign” in the GROUP BY statement in the query used to create the previous airframe_traj table (GROUP BY icao24, callsign;).

The problem with this solution: This approach would put the trajectory data of 2 distinct flights where that airplane and flight number are the same in a single row, which is not correct.

MobilityDB functions helped us avoid the use of several hardcoded conditions that depend on user knowledge of the data. This approach is very generic and can be applied anytime we want to split a trajectory by the inflection points in time of some other trajectory.

We can easily combine results from multiple queries in the same visualization in Grafana, simplifying the queries themselves. Here we apply some domain knowledge of sport pilot aircraft license limits for altitude and speed to provide an estimated count of each.

The final statistics visualization will look like this:

Figure 3.13. Statistic visualization of number of flights by license type