The OpenSky data can be found in this 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. As before, if this command throws a permission error, you can use the \copy command from the psql shell.

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 24 hour 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.