Estimate Number of Flights ========================== Introduction ------------ Sq1090 project decodes ADS-B messages into aircraft position data. Use the data to estimate the number of flights during specific period of time and over a defined geographical area. In this tutorial you will - create SQL query to identify and count flights during COVID-19 pandemic over Dublin airspace - visualise the data with `ggplot2 R library `_ The outcome of the tutorial is presented on the :numref:`fig-covid-19-flights`. .. figure:: images/covid-19-flights.* :name: fig-covid-19-flights :align: center :width: 640 :target: images/covid-19-flights.pdf Number of flights during the COVID-19 pandemic over Dublin airspace. Assumptions ----------- On technical level, the tutorial requires familiarity with 1. `Structured Query Language (SQL) `_, `Common Table Expressions (CTEs) `_, and `window functions `_ within PostgreSQL relational database system. 2. `TimescaleDB hyperfunctions `_ and `PostGIS spatial functions `_. 3. `R `_ programming language for statistical analysis and `ggplot2 `_ library. For the purpose of aircraft position data analysis, let's define 1. The Dublin airspace is the circle within 50 km radius of the Dublin Airport. 2. When an aircraft leaves the airspace and returns after 30 minutes, then the return is counted as an another flight. 3. COVID-19 time period is - from 2020-02-29 - first case of the virus in Republic of Ireland - to 2022-04-29 - daily reporting of new cases ended See also `COVID-19 in Ireland: Timeline 2020-2023 `_. Identify and Count Flights -------------------------- Sq1090 project decodes ADS-B messages into aircraft positions and stores the data in the ``position`` database table. The table has time, aircraft ICAO address, and aircraft location columns (see :numref:`sec-data-model` for details) The table has time, aircraft ICAO address, and aircraft location columns ``time`` Time of receiving a raw message with aircraft location and altitude. ``icao`` ICAO address of an aircraft. It is an integer number displayed as a hexadecimal number. ``location`` Location and altitude of an aircraft. The positions are stored using `WGS 84 `_ coordinate system. Query the ``position`` table to establish flight timeline of each aircraft during the pandemic and over Dublin airpsace .. literalinclude:: ../scripts/covid-19-flights :language: sql :lines: 32-41 :dedent: :linenos: :append: order by icao, time The query - ``[6]`` filters the data leveraging TimescaleDB time index to optimise bucketing and finding aircraft positions based on the specified time interval - ``[7-10]`` filters the positions over the geographical area using a PostGIS spatial index to optimise execution of the predicate function - ``[3]`` combines data of each aircraft, orders it by time, and finally, for each time of an aircraft position, identifies time of previous position The query can return data like .. code-block:: :linenos: icao | prev_time | time --------+-------------------------------+------------------------------- 1003e | | 2021-04-13 05:13:10.747583+01 1003e | 2021-04-13 05:13:10.747583+01 | 2021-04-13 05:13:12.33053+01 1003e | 2021-04-13 05:13:12.33053+01 | 2021-04-13 05:13:12.92841+01 ... 1003e | 2021-04-13 05:13:19.318988+01 | 2021-04-13 05:13:19.706155+01 100a2 | | 2021-06-26 05:39:39.91335+01 100a2 | 2021-06-26 05:39:39.91335+01 | 2021-06-26 05:39:53.672599+01 100a2 | 2021-06-26 05:39:53.672599+01 | 2021-06-26 05:39:54.764552+01 ... 100a2 | 2021-06-26 05:43:15.742002+01 | 2021-06-26 05:43:16.889032+01 100a3 | | 2020-07-29 11:38:03.443719+01 100a3 | 2020-07-29 11:38:03.443719+01 | 2020-07-29 11:38:04.043477+01 100a3 | 2020-07-29 11:38:04.043477+01 | 2020-07-29 11:38:04.427198+01 ... 100a3 | 2020-07-29 11:39:00.622831+01 | 2020-07-29 11:39:01.059635+01 100a3 | 2020-07-29 11:39:01.059635+01 | 2020-07-30 05:01:09.011887+01 100a3 | 2020-07-30 05:01:09.011887+01 | 2020-07-30 05:01:10.10184+01 ... 100a3 | 2020-07-30 05:06:24.186686+01 | 2020-07-30 05:06:25.280184+01 100a3 | 2020-07-30 05:06:25.280184+01 | 2020-08-05 12:51:07.580152+01 100a3 | 2020-08-05 12:51:07.580152+01 | 2020-08-05 12:51:13.31518+01 ... 100a3 | 2020-08-05 12:54:38.547477+01 | 2020-08-05 12:54:39.58337+01 ... The output of the query displays flight timeline for three aircrafts. Aircrafts 1003e and 100a2 have one flight each (lines 3-7 and 8-12 respectively). Aircraft 100a3 has three flights (lines 13-17, 18-21, and 22-25). New flight can be identified when the aircraft left Dublin airspace and returned after 30 minutes or more. Having flight timeline, identify each flight of an aircraft .. literalinclude:: ../scripts/covid-19-flights :language: sql :lines: 44-50 :dedent: :linenos: :append: order by icao, day The query - ``[5-6]`` assigns sequence for each flight of an aircraft - ``[2]`` reduces an aircraft flight timeline into individual flights The query, based on the previous sample, returns .. code-block:: :linenos: icao | day | icao_flight_seq --------+------------+----------------- 1003e | 2021-04-13 | 0 100a2 | 2021-06-26 | 0 100a3 | 2020-07-29 | 0 100a3 | 2020-07-30 | 1 100a3 | 2020-08-05 | 2 ... Finally, put the two queries together, and count number of flights for each day .. literalinclude:: ../scripts/covid-19-flights :language: sql :lines: 31-54 :dedent: :linenos: The final query returns number of flights for a date .. code-block:: :linenos: day | count ------------+------- 2020-02-29 | 684 2020-03-01 | 861 2020-03-02 | 814 2020-03-03 | 724 2020-03-04 | 744 ... Read and Visualise Data ----------------------- Sq1090 database can be queried with SQL by many applications and programming languages. This tutorial uses R programming language to read and visualise the data. In R, use `RPostgreSQL` library to execute queries against Sq1090 database. There are many options to visualise the data in R. `ggplot2` library is a popular choice. Additionally, use `scales` library to control apperance of time axis, and `urltools` library to pass database system parameters. Install the libraries in R language environment, for example using its shell and ``install.packages`` function .. code-block:: $ R > install.packages(c('RPostgreSQL', 'ggplot2', 'scales', 'urltools')) In R IDE or a text editor, create file with name ``covid-19-flights``. We will populate the R script with commands to read aircraft flight summary from database, visualise it on a graph, and save it into a PDF file. Import the libraries .. literalinclude:: ../scripts/covid-19-flights :language: R :lines: 25-29 :dedent: :linenos: Parse database connection arguments .. literalinclude:: ../scripts/covid-19-flights :language: R :lines: 58-60 :dedent: :linenos: Connect to Sq1090 database and execute SQL query (``QUERY`` variable is the query defined in previous section) .. literalinclude:: ../scripts/covid-19-flights :language: R :lines: 62-64 :dedent: :linenos: Plot the number of flights during the pandemic over Dublin airspace (see :numref:`fig-covid-19-flights` in the introduction) .. literalinclude:: ../scripts/covid-19-flights :language: R :lines: 66-74 :dedent: :linenos: Execute the script to create the plot in PDF format .. code-block:: Rscript covid-19-flights postgresql://localhost:5432/sq1090 covid-19-flights.pdf Conclusions ----------- Sq1090 decodes ADS-B messages into aircraft position data. The position data is stored in Sq1090 database ``position`` table. Query the table to identify and count flights during a specific period of time and over a defined geographical area. In this tutorial, we implemented R script to plot number of flights during COVID-19 pandemic over Dublin airspace. The source code of the script can be found in Sq1090 project repository https://gitlab.com/sq1090/sq1090/-/blob/master/scripts/covid-19-flights You can study Sq1090 project data model by reading its reference documentation, :numref:`sec-data-model`. .. vim: sw=4:et:ai