Noise mapping with KSQL, Raspberry Pi and radio

At first glance, this story has everything to earn the status of a romantic fast on the eve of March 8: airplanes, love, a bit of espionage, and, finally, a cat (more precisely, a cat). It is hard to imagine that all this is directly related to Kafka, KSQL, and the experiment “to find the noisiest airplane at home using information technologies.” It is difficult, but it will be necessary: ​​just such an experiment was conducted by Simon Oburi, and we translated an article of his authorship describing all the details of the process.

Our new cat named Snowflake wakes up early. She is awakened by the sounds of airplanes flying over our house. And what if I, using Apache Kafka, KSQL and Raspberry Pi, were able to determine which plane does not allow my cat to sleep? It would be nice to create an amusing tracking panel to which the cat could turn its attention - and give me a little more sleep.

In outline

Transfer airplanes from the sky to graphics using Kafka and KSQL
')
Airplanes determine their location using GPS receivers. The airborne transmitter periodically reports the location, identification number, altitude and speed of the ship using short radio broadcasts. These broadcast automatic dependent surveillance ( APN-B ) transmissions are essentially data packets open for access from ground stations.

One microcomputer, such as the Raspberry Pi, and several auxiliary components are all that are required to receive messages from onboard transmitters of planes that scurry over my house.

Aircraft airplanes look like a tangled mess of messages and require systematization. Recognizing these chaotic data streams is like listening to a conversation at a noisy party. Therefore, in order to find the plane that disturbs my cat, I decided to use a combination of Kafka and KSQL.


Awakened Cat and Raspberry Pi

Collecting AZN-B readings with the Raspberry Pi

To collect onboard broadcasts, I used the Raspberry Pi and RTL2832U - a USB modem, which was originally sold as a device for watching digital TV on a computer. On Raspberry Pi, I installed dump1090 - a program that receives data from AZN-B via RTL2832U using a small antenna.


My software radio from Raspberry Pi and RTL2832U

Let's transform signals AZN-IN in themes Kafka

Now that I have received a stream of AZN-B raw signals, we should pay attention to the traffic. Raspberry Pi does not have enough power for serious calculations, so I had to transfer the data processing to my local cluster on Kafka.



Received messages are divided either into location reports or bebor identification messages . The location looks like a message of the form: “the board 7c6db8 flies at an altitude of 6250 feet in the coordinate -33.8,151.0” . Information about the identification of the board will look like: "the board 7c451c flies along the route QJE1726" .

A small Python script for my Raspberry Pi splits all incoming AZN-B messages. I used the Confluent Rest Proxy proxy server to distribute data from the Raspberry Pi to the location-topic and ident-topic topics on Kafka. The proxy server provides a RESTful interface for the Kafka cluster, which makes it easy to create messages using a simple REST call on Pi.



I wanted to understand which planes fly over my roof and which routes. The OpenFlights database allows you to compare the airborne code, for example, 7C6DB8, assigned by the International Civil Aviation Organization (ICAO), to the type of aircraft - in our case, the Boeing 737. I downloaded the data of my mapping into the icao-to-aircraft topic.

KSQL provides the “SQL engine”, which allows you to process data in real time on Apache Kafka topics. For example, to find the onboard code 7C6DB8, we can write the following query:

CREATE TABLE icao_to_aircraft WITH (KAFKA_TOPIC='ICAO_TO_AIRCRAFT_REKEY', VALUE_FORMAT='AVRO', KEY='ICAO'); ksql> SELECT manufacturer, aircraft, registration \ FROM icao_to_aircraft \ WHERE icao = '7C6DB8'; Boeing | B738 | VH-VYI 

Similarly, I downloaded callsigns in the theme callsign-details (i.e. QFA563, this is Qantas flight from Brisbane to Sydney).

 CREATE TABLE callsign_details WITH (KAFKA_TOPIC='CALLSIGN_DETAILS_REKEY', VALUE_FORMAT='AVRO', KEY='CALLSIGN'); ksql> SELECT operatorname, fromairport, toairport \ FROM callsign_details \ WHERE callsign = 'QFA563'; Qantas | Brisbane | Sydney 

Now let's take a look at the location-topic data stream. Here we can observe a constant stream of incoming messages about the location of the flying plane.

 kafka-avro-console-consumer --bootstrap-server localhost:9092 --property --topic location-topic {"ico":"7C6DB8","height":"6250","location":"-33.807724,151.091495"} 

The query for kSQL will look like this:

 ksql> SELECT TIMESTAMPTOSTRING(rowtime, 'yyyy-MM-dd HH:mm:ss'), \ ico, height, location \ FROM location_stream \ WHERE ico = '7C6DB8'; 2018-09-19 07:13:33 | 7C6DB8 | 6250.0 | -33.807724,151.091495 

KSQL: Thread Harmonization ...

KSQL's real value lies in the ability to merge incoming location data streams with the original data topics (see 03_ksql.sql ) —that is, add useful information to the raw data stream. This is very similar to “left join” in a traditional database. The result is another Kafka theme created without a single line of Java code!

source> CREATE STREAM location_and_details_stream AS \
SELECT l.ico, l.height, l.location, t.aircraft \
FROM location_stream l \
LEFT JOIN icao_to_aircraft t ON l.ico = t.icao;
In addition, you receive a KSQL query. The data flow will look like this:

 ksql> SELECT TIMESTAMPTOSTRING(rowtime, 'yy-MM-dd HH:mm:ss') \ , manufacturer \ , aircraft \ , registration \ , height \ , location \ FROM location_and_details_stream; 18-09-27 09:53:28 | Boeing | B738 | VH-YIA | 7225 | -33.821,151.052 18-09-27 09:53:31 | Boeing | B738 | VH-YIA | 7375 | -33.819,151.049 18-09-27 09:53:32 | Boeing | B738 | VH-YIA | 7425 | -33.818,151.048 

In addition, we can combine incoming callsign calls with a fixed topic callsign_details :

 CREATE STREAM ident_callsign_stream AS \ SELECT i.ico \ , c.operatorname \ , c.callsign \ , c.fromairport \ , c.toairport \ FROM ident_stream i \ LEFT JOIN callsign_details c ON i.indentification = c.callsign; ksql> SELECT TIMESTAMPTOSTRING(rowtime, 'yy-MM-dd HH:mm:ss') \ , operatorname \ , callsign \ , fromairport \ , toairport \ FROM ident_callsign_stream ; 18-09-27 13:33:19 | Qantas | QFA926 | Sydney | Cairns 18-09-27 13:44:11 | China Eastern | CES777 | Kunming | Sydney 18-09-27 14:00:54 | Air New Zealand | ANZ110 | Sydney | Auckland 

Now we have two informative topics:

1. location_and_details_stream , which provides a stream of updated information on the position and speed of the aircraft;
2. ident_callsign_stream , which describes flight details, including airline and destination.

With these constantly updated themes, we can create several excellent overview panels. I used Kafka Connect to upload the Kafka threads, filled in by KSQL, to Elasticsearch (full scripts here ).

Kibana overview panel

Here is an example of a survey panel showing the location of the aircraft on the map. In addition, you can see a chart by airline, a graph of flight altitude and word clouds for major destinations. The heat map shows the areas of concentration of the aircraft, that is, the area with the highest noise level.

Back to cat

Today the cat woke me up around 6 o'clock in the morning. Can KSQL help me find the plane that flew at this time over my house at an altitude of less than 3,500 feet?

 select timestamptostring(rowtime, 'yyyy-MM-dd HH:mm:ss') , manufacturer , aircraft , registration , height from location_and_details_stream where height < 3500 and rowtime > stringtotimestamp('18-09-27 06:10', 'yy-MM-dd HH:mm') and rowtime < stringtotimestamp('18-09-27 06:20', 'yy-MM-dd HH:mm'); 2018-09-27 06:15:39 | Airbus | A388 | A6-EOD | 2100.0 2018-09-27 06:15:58 | Airbus | A388 | A6-EOD | 3050.0 

Awesome! I can identify the plane that was above my roof at 6:15 in the morning. It turns out that Snowflake was awakened by the Airbus A380 (a huge liner, by the way), which flew to Dubai.

Just a couple of days off, and you have a streaming system with KSQL. Which, moreover, allows you to quickly find interesting data events. Although Snowflake may be skeptical.