Dataset link

Create a new database “opensky”, then use your SQL editor to create the MobilityDB extension as follows:

CREATE EXTENSION MobilityDB CASCADE;
        

The CASCADE command will additionally create the PostGIS extension.

Now create a table in which the CSV file will be loaded:

CREATE TABLE flights(
    et              bigint,
    icao24          varchar(20),
    lat             float,
    lon             float,
    velocity        float,
    heading         float,
    vertrate        float,
    callsign        varchar(10),
    onground        boolean,
    alert           boolean,
    spi             boolean,
    squawk          integer,
    baroaltitude    numeric(7,2),
    geoaltitude     numeric(7,2),
    lastposupdate   numeric(13,3),
    lastcontact     numeric(13,3)
);

Load the data into the database using the following command. Replace the <path_to_file> with the actual path of the CSV file. Do this for all files.

COPY flights(et, icao24, lat, lon, velocity, heading,
		 vertrate, callsign, onground, alert, spi, squawk,
		 baroaltitude, geoaltitude, lastposupdate, lastcontact)
FROM '<path_to_file>' DELIMITER  ',' CSV HEADER;

All the times in this dataset are in unix timestamp (an integer) with timezone being UTC. So we need to convert them to PostgreSQL timestamp type.

ALTER TABLE flights
    ADD COLUMN et_ts timestamp,
    ADD COLUMN lastposupdate_ts timestamp,
    ADD COLUMN lastcontact_ts timestamp;

UPDATE flights
    SET et_ts = to_timestamp(et),
        lastposupdate_ts = to_timestamp(lastposupdate),
        lastcontact_ts = to_timestamp(lastcontact);

You can check the size of the database with:

SELECT pg_size_pretty( pg_total_relation_size('flights') );
        

Delete all icao24 that have all NULL latitudes

-- icao24_with_null_lat is used to provide a list of rows that will be deleted
WITH icao24_with_null_lat AS (
    SELECT icao24, COUNT(lat)
    FROM flights
    GROUP BY icao24
    HAVING COUNT(lat) = 0
      )
DELETE
FROM flights
WHERE icao24 IN
-- this SELECT statement is needed for the IN statement to compare against a list
(SELECT icao24 FROM icao24_with_null_lat);
            

Note: This data cleaning is not comprehensive. It was just to highlight that before creating trajectories, it may be very important to have a look at the data and do some cleaning as that will directly impact the quality of mobilityDB trajectories being created. If there as NULLs in mobilityDB trajectories, some operation on it can give error.

Data for the workshop is loaded into a MobilityDB database hosted on Azure, with all login information provided in the Sign-in and Connect to Data Source section below.

The workshop is using the following settings in Grafana to connect to the postgres server on Azure. More detailed instruction to set up Grafana can be found in section 2.3 to 2.5 of the Dashboard and Visualization of Ship Trajectories (AIS) workshop.

The data used for this workshop provided by The OpenSky Network. This is data from a 24hr period from June 1, 2020 (dataset link). The raw data is originally provided in separate CSV documents for each hour of the day.

Open a new browser and go to http://localhost:3000/ to work in your instance of Grafana. With a new dashboard we can start creating the panels below.