Processing and Analysis of Condensation Trails Data Using Database Systems

This article describes the causes and behavior of aircraft condensation trails, aircraft radar technology, communication between aircraft and ground control stations, description of the function, analysis and processing of the ADS-B receivers and registers data. It also deals with the design and creation of a suitable web application with a simple user interface and a database for storing and processing this data. The work was based mainly on academical materials and sources, focusing on the issue of condensation trails, ADS-B functionality and programming of web applications.


Introduction
The article is focused on the formation of condensation trails, airborne radar, ADS-B system, web application structure and programming languages that are used in created application. The aim is to create a web application for data processing. These data come from ADS-B system. Data from this system contains information about the aircraft (its location and other data) and the application allows the students and staff of the Department of Air Transport to process and supplement the data with information about condensation trails. The web application is currently deployed at https://3219046688.adsbprojekt-vv.cz, but will later be moved to internal servers, so it can be used by CTU employees safely.
1. The right combination of humidity and ambient air temperature; 2. Temperature below freezing (minus 40 • C to 60 • C).

The formation of condensation trails
The actual formation of condensation lines is approximately as follows: aircraft is flying at the correct height and is equipped with engines that burn fossil fuels while releasing very hot exhaust gases at temperatures of hundreds of degrees Celsius [2,3]. These gases also contain a relatively large amount of water vapor with a similar temperature as other exhaust gasses. Also included in the exhaust gas are microparticles of solid matter (remnants of unburned fuel, debris, burning remains and others), which can be considered condensation nuclei. These ice cores are gradually encased in freezing water vapor and since their position corresponds directly to the path left behind by the plane, they are visible to the ordinary observer in the form of sky lines. However, they do not arise immediately after they are discharged from the exhaust system as the condensation cores are too hot. However, by melting the emerging ice, the condensation core cools down to the temperature at which the icing core is held and visible condensation lines are formed. Other important conditions for the formation of condensation lines include ambient air humidity -relative humidity [3].
The Primary Surveillance Radar (PSR) operates on the simple principle of reflecting radio waves from the target, while the Secondary Surveillance Radar (SSR) does not rely on reflections from the target, but actively communicates with transponders, located on board aircraft and their individual modes [5,4].

Primary Surveillance Radar
The radar transmitter emits a radio beam and records reflections from aircraft (the term "targets" is used) and then reflects these reflections to the air traffic controllers screen. PSR also has several species, all of which vary in use, range, and other details. PSR is used for activities such as taxiing at major airports, military tracking of targets, or possibly as AWR (Airborne Weather Radar) [5].
This type of radar has relatively large disadvantages. For example the need for a powerful transmitter. Also, the standard PSR can only display target position data, its speed and direction in which it flies, but no other information about the airplane's identification and altitude [5].

Secondary Surveillance Radar
The SSR transmitter operates in the UHF band at 1030 MHz. The transmitter sends a query to its surroundings, the air stations accept the query, unpack it and send back their reply. The answer may vary, depending on the type of query and radar and transponder work mode. Message is also sent back to the transmitter in the UHF band, more precisely at 1090 MHz. The receiver receives, reads, and displays the resulting data on an air traffic controller screen [5,4].
The responses transmitted by the aircraft are all three modes: A, C, S. Each mode has different waveforms, species, and response data. A distinction is made between addressed and unaddressed communication -addressed communication is in Mode S and the aircraft is addressed directly by the radar. In unaddressed communication, the distinction between airplanes is not taken into account and we only distinguish the airplanes on the screen according to the direction and distance from which we will respond [4,6].

ADS-B
ADS-B is a radio communication system that is used to transmit accurate location and other flight information, especially identification data. ADS-B stands for 'Automatic Dependent Surveillance: Broadcast'. It is therefore a radiocommunication system that uses 978 MHz and 1090 MHz frequencies and the Pulse Position Modulation (PPM) modulation scheme to communicate and process data. It usually used the band 1090 MHz, however, due to the high traffic on this and around this band, the 978 MHz band is also sometimes used. It belongs among the so-called third generation systems -systems that greatly increase air safety. The big advantage is the relative low cost, mainly because of the simple and cheap flight planning and the necessity of minimal ground equipment [7].
The entire system is fully automatic, requiring no pilot intervention in normal condition thereby significantly reducing the error rate caused by the human factor [7].
It operates on the principle of detecting the current position of the aircraft and this information is sent to aircrafts in the vicinity, while greatly reducing positioning errors, inaccuracies and time delays as seen in radar-plane communication.
The recipient of the ADS-B data transmission may be both an aircraft, equipped with an appropriate receiving system and a ground station. Figure 1 below shows graphically the ADS-B communication between ground and air stations [7].
ADS-B consists of two services: ADS-B OUT, ADS-B IN.

Description of processed data
Data can be described as a set of information that we use to describe the behavior, property, state, or phenomenon, connected to a selected object. CTU receives air traffic data from its ADS-B receivers located in Prague. This data is in the format of ".csv" tables, and one such table is generated for each aircraft passing through the monitored space. This table is the database input, that the user uploads it to it. The data is of several kinds and it is originally an information from the on-board system ADS-B, supplemented with meteorological information (not available from most aircraft) [7].

Databases and the web application design
The database is a system of files (information, data, packages...) that are linked together in one system. They represent the technological advancement of computer systems and, unlike their predecessors -paper filing cabinets and written technical data bases -a highly flexible way to work with many types and volumes of data. Their use is considerable, almost every website, computer system and computational component have their own, or are attached to some kind of database [9].
The application's layers interconnection is shown on Figure 2 bellow. The created application has four main functions: 1. Upload of new data tables (described in chapter 2.4); 2. Filtration of results from uploaded tables by a specified date; 3. The ability to add condensation trail information to these filtered records; 4. Export data from database in pre-defined manner.

Data layer, database and its implementation to the web application
Data layer is a data structure for storing data in a particular arrangement. This arrangement varies depending on the use of the database -some layouts are inappropriate for the specified task some, on the other hand, are appropriate, so it depends on what we want to use the database for [9,11]. Let's have a database that holds a number of tables. The table (also known as a session) contains entities and attributes in the relational model and is most often displayed in twodimensional view. An entity in such a view is then a line with the data itself and the table column attribute. Attribute names are different however, within the session the two same names are inadmissible. The data must also meet certain conditions -especially the same data type as the rest of the data and the column [9,11].
Thus, in the created application, the data layer has a storage function -the user uploads a table in the presentation layer, this table is processed by the application layer and stored in the database (data layer). The process of storing and subsequently retrieving data is performed by the application layer, using SQL. Figure 3 shows the SQL statement ("SE-LECT * FROM' 20180901 0447 3C5CB2' LIMIT 0, 100 "), which requests the database to display everything stored in the table" 20180901 0447 3C5CB2".

Application layer, back-end and its implementation to the web application
The application layer performs a functional role in the threelayer software architecture. It contains the business logic of the application, i.e. basically the definition of the behavior, function and functionality of the whole application. It is located between the data layer and the presentation layer and basically mediates the execution of commands and requirements of the individual layers that it processes, evaluates and executes [9,11].
Basically, the application layer contains a certain set of functions that it performs. It is often referred to as the backend, abbreviated as BE [9,11].
The back-end of the created application is written in PHP programming language, which is general-purpose language, suitable for use cases such as this. The example of this programming language (part of code, that exports ".csv" file, so it can be downloaded by the user) is visible on Figure 4.

Presentation layer, front-end and its implementation to the web application
The presentation layer is a user-based environment in a threetier application architecture. This layer is also often referred to as front-end (FE) and provides a link between user actions and application layer [9,11]. For presentation layer, which is fully connected to created back-end solution and then to the data layer, a combination of several programming languages was chosen and frameworks, namely:   Figure 6 shows the portion of the created HTML code for the same page.  One of the main features created is the ability to add condensation track information to the uploaded table. If a user filters out a period of time that contains saved flight information tables, he can add three kinds of information to them: "what happened to the condensation trail", "how long the condensation trail was visible", and "what plane created the condensation trail". This information is then stored in the database table via the application layer and is visible to the user the next time the search is performed. Figure 8 shows the search for the date 06.09.2018, for which two tables are available in the database. First table already has complete info about the table pre-filled, because it was previously saved. In the second table, the condensation information is not filled. These data are saved to table in the database and can be exported by the user, as the Figure 7 shows. Also, if the airplane type info was already saved and new table with the same airplane (from different date) is uploaded, the information about the airplane type (e.g. "A380") as is automatically pre-filled to this table.

SQL language
As mentioned above, SQL is the language used to handle the data layer and the database itself. The main element of the communication is the so-called "statement", which is sent in SQL to a database and it is processes and executed, if possible. We can then classify these commands according to their intended use and how they interact with the database and what actions they perform. Sorting them into so-called "sublanguages" according to their intended use, most often they are [9,11]: 1. data query language (DQL); 2. data definition language (DDL); 3. data control language (DCL); 4. data manipulation language (DML).

PHP language
PHP language (short for "Personal Home Page" or "Hypertext Preprocessor") is a language used to create dynamic web pages and web and desktop applications. PHP syntaxes are written directly into the web page code (function calls, working with variables etc.) but they are not visible to the ordinary user after displaying the web page code [11].
These commands are executed on the server side and only the result of these actions is transmitted to the user (for example, a different website design for users from a particular state) [11].
PHP also has a well-designed interconnection of various database systems and web protocols. From database systems, it supports virtually all of the most popular and widely used (MySQL, PostgreSQL. . . ) and the same is true for Internet protocols such as HTTP, SMTP, POP3, FTP, and others that are used for various purposes -from data transmission to receiving and sending email. It is also one of the most widely used scripting languages, with its share of this market in 2017 being over 82% (followed by ASP.NET), which means, among other things, that various custom modifications and add-ons were created, code errors are more easily repairable and that there is a large community of people who care about the maintenance and development of this language [11].

Discussion
The application created can be used for uploading, filtering, adding condensation trail's data and exporting these data to user. The entire process is slowed down by fact, that user has to manually upload data (in form of tables) to database. These tables are automatically generated from the ADS-B receivers, processed by the Matlab software and could therefore be automatically uploaded to the created database by another service. This solution would speed up the upload process by removing the need to pack and upload the tables to the database manually.

Conclusion
A web application was created with a database for storing, processing, managing, replenishing and exporting information, generated from the received data on the monitored aircraft and processed into tables. In addition, a manual for this web application was created that describes how the user should work with the application.
A three-tier application model was used to create the application, dividing the application into three separate layerspresentation, data and application. Only presentation layer is visible to the user and he can work with it. The calculations and work with data from the user and data from the database are then done on the application layer, which is neither visible nor accessible to the user and can only work with it using the presentation layer. For storing tables and the data records, about how the trails behaved, the data layer serves the user only through the data output to the presentation layer from the application layer. Programming languages HTML, PHP and SQL (HTML to create the basics of the website, PHP to execute user requests and to work with data and SQL for creating and editing data in the database) were used.