Gathering & Displaying ADSB and ACARS Data – Part III [Writing Code]

In part II the hardware was installed, and local map software was running. The map software is nice, but it’s very real time. I wanted to collect data for historical reference. I wanted to know the data coming in about a plane, and then extrapolate from that any public data about each aircraft, also linking out to public databases that might have visual route data, photos of the aircraft, FAA information and more.

I decided the best view would be to have a data grid, like a web version of Excel, where each column of data could be filtered/searched and clicked to change the sort order. To get there I would need a way of pulling the data off the dongle and pass it to my own publicly hosted webserver.

It seemed that since Dump1090 was already parsing the dongle’s data to JSON that all I needed to do was listen in on that. Each time I got a hit on an aircraft I would send the JSON to a webserver which would store it to a database. That would take care of storing data from the local aircraft to a public database.

Displaying data would require frontend software (in my case I used Vue) be used to hook to server side endpoints that pull data from the database as JSON, then display per my wishes (such as a data grid).

Phase I – Saving Data to a Public Database

Database

I created a database through my host provider. After creating the mysql database, I created a table with the expected columns. I also set up permissions for the database.

Local Python Code to Parse Dump1090

It took a bit of digging around to figure out the path to the JSON data in dump1090-carammato0. Turns out it dumps json to /data/aircraft.json Knowing that I wrote a bit of python to listen to that feed:

%MINIFYHTMLfbe5173a4b0a655718d50db3eec84a7123%
import urllib.request, json import time from bs4 import BeautifulSoup import requests from icao_nnumber_converter_us import icao_to_n from datetime import datetime def get_n_number(icao): return icao_to_n(icao) def plane_data(n_number): # FAA - N number flights URL = f"https://registry.faa.gov/AircraftInquiry/Search/NNumberResult?nNumberTxt={n_number}" print(URL) page = requests.get(URL) soup = BeautifulSoup(page.content, "html.parser") if soup.find("td", attrs={"data-label": "Manufacturer Name"}) is not None: manufacturer_name = soup.find("td", attrs={"data-label": "Manufacturer Name"}).text else: manufacturer_name='' if soup.find("td", attrs={"data-label": "Model"}) is not None: model = soup.find("td", attrs={"data-label": "Model"}).text else: model = '' if soup.find("td", attrs={"data-label": "Expiration Date"}) is not None: expiration_date = soup.find("td", attrs={"data-label": "Expiration Date"}).text else: expiration_date = '' if soup.find("td", attrs={"data-label": "Name"}) is not None: owner_name = soup.find("td", attrs={"data-label": "Name"}).text else: owner_name = '' if soup.find("td", attrs={"data-label": "Street"}) is not None: owner_street = soup.find("td", attrs={"data-label": "Street"}).text else: owner_street = '' if soup.find("td", attrs={"data-label": "City"}) is not None: owner_city = soup.find("td", attrs={"data-label": "City"}).text else: owner_city = '' if soup.find("td", attrs={"data-label": "State"}) is not None: owner_state = soup.find("td", attrs={"data-label": "State"}).text else: owner_state = '' if soup.find("td", attrs={"data-label": "County"}) is not None: owner_county = soup.find("td", attrs={"data-label": "County"}).text else: owner_county = '' if soup.find("td", attrs={"data-label": "Zip Code"}) is not None: owner_zip = soup.find("td", attrs={"data-label": "Zip Code"}).text else: owner_zip = '' return [manufacturer_name.strip(), model.strip(), expiration_date.strip(), owner_name.strip(), owner_street.strip(), owner_city.strip(), owner_state.strip(), owner_county.strip(), owner_zip.strip()] dump1090_api = "http://localhost:8000/data/aircraft.json" airDictionary = { } def kt_mph(kt): return round(kt * 1.151) with urllib.request.urlopen(dump1090_api) as url: data = json.loads(url.read().decode()) for i in data['aircraft']: now = datetime.now() formatted_date = now.strftime('%m-%d-%Y %H:%M') if "flight" in i and "lon" in i and "lat" in i: # print(i) flight = i['flight'] if "track" in i: heading = i['track'] else: heading = '' if "alt_geom" in i: alt = i['alt_geom'] else: alt = '' if "lat" in i: lat = i['lat'] else: lat = '' if "lon" in i: lon = i['lon'] else: lon = '' if "hex" in i: icao = i['hex'] else: icao ='' if "squawk" in i: squawk = i['squawk'] else: squawk = '' if "tas" in i: speed = i["tas"] else: speed = 400 nnumber = get_n_number(icao.strip()) pd = plane_data(nnumber) mph = kt_mph(speed) airDictionary.update({"aircraft":{"flight": flight.strip(), "nnumber": nnumber.strip(), "heading": heading, "alt":alt, "lon":lon, "lat":lat, "manufacturer": pd[0], "model": pd[1], "expiration": pd[2], "owner_name":pd[3], "owner_street":pd[4], "owner_city":pd[5], "owner_state":pd[6], "owner_zip":pd[8], "squawk": squawk, "mph": mph, "spotted": formatted_date }}) send_json(airDictionary) time.sleep(2)
Code language: PHP (php)

Here’s what’s happening here:

  • Core of the code is the with statement. It listens to the dump1090 endpoint and gathers the info – saved to the data variable as a JSON object.
  • A for loop iterates over the data objected created in the step above. I make a few modifications to this data before packaging it into my own dictionary:
  • I change knots to MPH, and save that to a key/value in the dictionary.
  • Using the top method, I convert ICAO numbers to NNumbers. An ICAO is a hex value assigned to individual plans, but if we convert it back to non-hex it’s the Tail number (FAA calls it the N Number). With that we can grab more information. Just so happens that what is picked up in the JSON from dump1090 is ICAO, which isn’t as useful to me as the NNUMBER…. There’s a python library that does this calculation for me. I’m calling to return the tail number.
  • Now that I have the N Number, I call the top method to query the FAA pubic database for the tail number of the plane (N Number), what was converted in the previous step. Using Beautiful Soup I grab data on the result and pass that into the Dictionary, getting manufacture, model and owner name.
  • Once the Dictionary is complete, I submit it to the public backend system. I’ve omitted the send_json method because it has some details on the endpoint. Basically it sends (using requests) headers, and the post body to my endpoint

Backend Save Endpoint

Using PHP because it’s free for me, I grab listen for requests, then iterate over the JSON object assigning each JSON value to a variable. These variables are used to build a save to the local database.

// Listen for input.... //Attempt to decode the incoming RAW post data from JSON. $data = json_decode($content, true); // build string to save to local db...
Code language: PHP (php)

After grabbing the raw data, the iteration is handled like this in PHP:

$items = $data; foreach ($items as $i) { $flight = $i['flight']; $nnumber = $i['nnumber']; $heading = $i['heading']; $altitude = $i['alt']; $lon = $i['lon']; $lat = $i['lat']; $manufacturer = $i['manufacturer']; $model = $i['model']; $expiration = $i['expiration']; $owner_name = $i['owner_name']; $owner_street = $i['owner_street']; $owner_city = $i['owner_city']; $owner_state = $i['owner_state']; $owner_zip = $i['owner_zip']; $squawk = $i['squawk']; $mph = $i['mph']; $spotted = $i['spotted']; // Attempt insert query execution $sql = "INSERT INTO [[ MY TABLE ]] (flight, nnumber, lon, lat, alt, heading, manufacturer, model, expiration, owner_name, owner_address, owner_city, owner_state, owner_zip, squawk, mph, spotted) VALUES ('$flight', '$nnumber', $lon, $lat, $altitude, $heading, '$manufacturer', '$model', '$expiration', '$owner_name', '$owner_street', '$owner_city', '$owner_state', '$owner_zip', '$squawk', '$mph', '$spotted')"; if(mysqli_query($connection, $sql)){ echo "Records inserted successfully."; } else{ echo "ERROR: Could not able to execute $sql. " . mysqli_error($connection); } mysqli_close($connection);
Code language: PHP (php)

If all goes well, after a few tests it should populate the database with entries of aircraft!

Once we have a database of entries we can create the frontend.

Phase II – Creating/Supporting a Frontend to the Data

Backend Endpoint – Supporting Frontend

To support the front end, I need a backend endpoint that can query the database and return JSON. Again I’m using PHP because it’s free for me through my hosting provider:

$sth = mysqli_query($connection, "SELECT * FROM [MY TABLE]"); $rows = array(); while($r = mysqli_fetch_assoc($sth)) { $rows[] = $r; } print json_encode($rows); mysqli_close($connection);
Code language: PHP (php)

Hitting this endpoint should query the database and return a response of the query as JSON. Great.

Vue Frontend

My vision wasn’t something like a realtime map, but more of a data grid. I wanted a way to view the data collected. There happens to be a great Javascript library called AG Grid that does just this. The full documentation on AG Grid is available at: https://www.ag-grid.com

The final result would end up as: https://avairspace.com To get there I had to first get the grid working. I started with a generic Vue application. In the main App.vue file I added the following:

<template> <h2 style="font-family:'Trebuchet MS', 'Lucida Sans Unicode', 'Lucida Grande', 'Lucida Sans', Arial, sans-serif;color:white;padding-left: 2px;font-size: 1em;">Planes spotted: Antelope Valley, California</h2> <ag-grid-vue class="ag-theme-alpine" style="width: 1420px;height: 450px" :columnDefs="columnDefs.value" :paginationAutoPageSize="true" :pagination="true" :rowData="rowData.value" :defaultColDef="defaultColDef" rowSelection="multiple" animateRows="true" @cell-clicked="cellWasClicked" @grid-ready="onGridReady" > </ag-grid-vue> </template> <script> import "ag-grid-community/styles/ag-grid.css"; import "ag-grid-community/styles/ag-theme-alpine.css"; import "./assets/override.css" import { AgGridVue } from "ag-grid-vue3"; import { reactive, onMounted, ref } from "vue"; export default { name: "App", components: { AgGridVue, AcarsAircraft, }, setup() { const gridApi = ref(null); // Optional - for accessing Grid's API // Obtain API from grid's onGridReady event const onGridReady = (params) => { gridApi.value = params.api; }; const cellClassRules = { "fast": params => params.value >= 530, }; const lowAlt = { "low": params => params.value < 4000 } const rowData = reactive({}); const columnDefs = reactive({ value: [ { field: "spotted", sort: 'desc', width: 180, maxWidth: 180}, { field: "flight", headerName:"Flight#", width:90, maxWidth: 155, cellRenderer: function(params) { return `<a class="fa" href="https://flightaware.com/live/flight/${params.value}" target="_blank" rel="noopener">`+ params.value+'</a> - '+`<a class="rb" href="https://www.radarbox.com/data/flights/${params.value}" target="_blank">`+' [RB]</a>' } }, { field: "nnumber",headerName: "Tail #",width:100, maxWidth: 190, cellRenderer: function(params) { return `<a class="jp" href="https://www.jetphotos.com/registration/${params.value}" target="_blank" rel="noopener">`+ params.value+'</a> - '+`<a class="faa" href="https://registry.faa.gov/AircraftInquiry/Search/NNumberResult?nNumberTxt=${params.value}" target="_blank">`+' [FAA]</a>' } }, { field: "mph", headerName: "MPH",width: 30, maxWidth: 90 ,cellClassRules: cellClassRules}, { field: "alt", headerName: "Alt.", width: 110, maxWidth: 110, cellClassRules: lowAlt, cellRenderer: function(params) { return params.value + " ft" }}, { field: "owner_name", headerName: "Owner", cellRenderer: function(params) { return `<a class="owner" href="https://google.com/search?q=${params.value}" target="_blank" rel="noopener">`+ params.value+'</a>' } } , { field: "manufacturer", width: 200, maxWidth:250 }, { field: "model", width:100, maxWidth:130, cellRenderer: function(params) { return `<a class="model" href="https://google.com/search?q=${params.value}" target="_blank" rel="noopener">`+ params.value+'</a>' } }, ], }); // DefaultColDef sets props common to all Columns const defaultColDef = { sortable: true, filter: true, flex: 1, resizable: true, }; // Example load data from sever onMounted(() => { fetch("[[ MY BACKEND ENDPOINT URL TO GET RESULTS AS JSON ]]") .then((result) => result.json()) .then((remoteRowData) => (rowData.value = remoteRowData)); setInterval(function () { fetch(" [[MY BACKEND ENDPOINT URL TO GET RESULTS AS JSON ]]") .then((result) => result.json()) .then((remoteRowData) => (rowData.value = remoteRowData)); }, 120000) }) return { onGridReady, columnDefs, rowData, defaultColDef, }, deselectRows: () =>{ gridApi.value.deselectAll() }, }; }, }; </script>
Code language: HTML, XML (xml)

The above Vue code is the base application. The grid accepts the data coming in, parses it into the columns and each column is filterable. Within the columnDefs, I set some column overrides to make unique responsive data. Examples are were use string interpolation to make dynamic links, so the data that is in the cell is also interpolated into a link to different sites like Google.

Some columnDefs make calls to cellClassRules and lowAlt. These methods are referenced in the CSS for styling. For example, for the MPH column, when a plane’s speed is above 530MPH, the cell background is turned red. When a plane’s altitude is below 4,000 ft the background is turned yellow. This allows me to quickly spot planes that are flying fast, as well as landing/taking off (or helicopters, which often fly below 4,000 ft.)

AV Airspace screenshot

Above is the final result: A data grid that is searchable by mousing over a column, clicking on the = stack icon and then inputing a value to search for; it is also sortable (ascending and descending). The grid can also be searched negatively, such as removing all results with “airline” in the owner name, to remove airlines.

Finding patterns with the planes can interesting, such as who flies most often over the area on repeat trips. Other interesting projects might be to graph out the data to find interesting insights, for now this is where I’m at and where I intend to leave it.

Part I – Introduction

Part II – Hardware

Part III – Writing Code (this page)

About Author /

Leave a Comment

Your email address will not be published.

Start typing and press Enter to search