Using KSQL, Apache Kafka, a Raspberry Pi and a software defined radio to find the plane that wakes my cat


Planes to graphs using Kafka & KSQL

ADS-B capture using a Raspberry Pi

To capture the aircraft transmissions I used a Raspberry Pi and a RTL2832U — a USB dongle originally sold to watch digital TV on a computer. On the Pi I installed Dump1090 — a program which accesses ADS-B data via the RTL2832U and a small antennae

Raspberry Pi and a RTL2832U as a software defined radio (SDR)

ADS-B signals to Kafka Topics

Now I’ve got a stream of raw ADS-B signals we need to have a look at the traffic. Kafka is a pretty awesome stream processing platform. I started by separating the incoming ADS-B signals into two kafka topics, location-topic (aircraft code with height and location) and callsign-topic (aircraft code and callsign).

create stream icao_to_aircraft_stream 
with (KAFKA_TOPIC='icao-to-aircraft', VALUE_FORMAT='AVRO');
select manufacturer, aircraft, registration
from icao_to_aircraft_stream
where icao = '7C6DB8';
Boeing | B738 | VH-VYI
create stream callsign_details_stream 
with (KAFKA_TOPIC='callsign-details', VALUE_FORMAT='AVRO');
select operatorname, fromairport, toairport
from callsign_details_stream
where callsign = 'QFA563';
Qantas | Brisbane | Sydney
kafka-avro-console-consumer --bootstrap-server localhost:9092 --property --topic location-topic{"ico":"7C6DB8","height":"6250","location":"-33.807724,151.091495"}
select timestamptostring(rowtime, 'yyyy-MM-dd HH:mm:ss')
, ico
, height
, location
from location_stream
where ico = '7C6DB8';
2018-05-19 07:13:33 | 7C6DB8 | 6250.0 | -33.807724,151.091495

KSQL — Crossing the streams …

The real power of KSQL comes from combining the incoming stream of location data against the static details topics (see 03_ksql.sq). That is, adding useful details to the raw data stream. This is very similar to a “left join” in database language. In fact, the syntax for KSQL is indeed a left join…

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;
select timestamptostring(rowtime, 'yy-MM-dd HH:mm:ss') 
, manufacturer
, aircraft
, registration
, height
, location
from location_and_details_stream;
18-05-27 09:53:28 | Boeing | B738 | VH-YIA | 7225 | -33.821,151.052
18-05-27 09:53:31 | Boeing | B738 | VH-YIA | 7375 | -33.819,151.049
18-05-27 09:53:32 | Boeing | B738 | VH-YIA | 7425 | -33.818,151.048
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;
select timestamptostring(rowtime, 'yy-MM-dd HH:mm:ss') \
, operatorname \
, callsign \
, fromairport \
, toairport \
from ident_callsign_stream ;
18-05-27 13:33:19 | Qantas | QFA926 | Sydney | Cairns
18-05-27 13:44:11 | China Eastern | CES777 | Kunming | Sydney
18-05-27 14:00:54 | Air New Zealand | ANZ110 | Sydney | Auckland

Kibana Dashboard

Here’s a sample of dashboards displaying aircraft location on a map. In addition a bar-chart of manufactures, an altitude line plot and a destination word-cloud.

Kibana Display

Findings & Conclusion

As best as I can remember, today my cat woke me at a little past 6am. I can use KSQL to find a low flying aircraft (below 3500ft) around this time …

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-05-27 06:10', 'yy-MM-dd HH:mm') and rowtime < stringtotimestamp('18-05-27 06:20', 'yy-MM-dd HH:mm');
2018-05-27 06:15:39 | Airbus | A388 | A6-EOD | 2100.0
2018-05-27 06:15:58 | Airbus | A388 | A6-EOD | 3050.0
Kibana map between 6:10 and 6:20

Ready to try it?

Got a similar pet problem; or just curious how Kafka and KSQL work together? You can find a combined docker setup, helper images, KSQL and Elastic setup at:-



Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Simon Aubury

Simon Aubury

Day job: data steaming & system architecture. Night gig: IoT and random project hacking