Planedar - Your Flights, Our Noise

by WildGardenGnome in Circuits > Raspberry Pi

118 Views, 0 Favorites, 0 Comments

Planedar - Your Flights, Our Noise

It's quite easy to get a good feel for the amount of cars, screaming kids, or people yelling into their phones which pass by each day. Flights however is a different ballgame. These winged busses fly high, are often invisible, but do create a tremendous amount of noise pollution at all hours of the day.

To shine a light on this hidden highway we're going to build Planedar. This project will spot planes within hearing distance, store their information, and provide a snazzy dashboard. We are also adding an inbuild passenger address system (PA) to provide a complete experience.

Supplies

Hardware

Raspberry Pi 4 Starter Kit
Bluetooth Speaker

Apps & Services

OpenSky
Flightradar24
Google Maps
OpenStreetMap
Sound Meter

Software

MySQL
Apache Webserver
FileZilla
Putty
Visual Studio Code
PhpMyAdmin

Frameworks

Tailwind CSS
Flask

Languages

Python
SQL
HTML/CSS/Javascript

Data Flow

In the above picture you can see the data flow of our project.

Our script sends a filtered request for all audible planes to the openSky API.
The openSky API responds with raw data if one is caught.
Our script formats and stores this data in a local database.
The script then generates audiofiles and plays them via the bluetooth speaker (PA).
When the dashboard is accessed, it requests the data from the backend.
The backend fetches the relevant records from the database.
The database sends them over to the backend.
The backend now passes them on to the dashboard.
Which does some styling and displays it all.

Setup

It all starts with a Raspberry Pi. Our Pi will not only run the code for data collection, storage and analysis. It will also communicate with the bluetooth speaker, and provide infrastructure for our dashboard and database.

To get our Pi up and running, we can follow the getting started tutorial provided by The Raspberry Pi Foundation themselves.

To work with the pie themed computer, I usually use a combination of Visual Studio Code, FileZilla and PuTTY. This is my personal setup, there are many alternatives to choose from.

Raw Data Collection

Planedar

This step starts with a massive thank you to Conor Mclaughlin for his great project article. Follow along and you will also have a script for identifying planes flying overhead with Python using the OpenSky REST API.

To only detect planes within earshot, I've spent an evening listening, tracking planes on Flightradar24, and using Google Maps to pinpoint the distance at which they are audible. In my case that's 3.5 km, give or take. This means all planes within a range of 3.5km are audible and we can draw our radius box accordingly.

Noise Index

We're also adding an extra data field. Based on speed, distance, and the vertical movement we calculate our very own Noise Index. The AI has generated this calculation, so to make sure it's somewhat accurate, I've spent another evening with a decibel meter app verifying.

Based on a sample size of 25, the index works well enough. It usually underestimates the noise by 2 decibel points(~5%). So, instead of the 63 Decibels the app indicated, the Noise Index estimated 61. This makes it a good approximation, which is good enought for our project.

Full Code

Make sure to replace all 'XX' placeholders in the script with your specific coordinates, and ensure your OpenSky API and database credentials are saved in a .env file for the script to access. If this is unfamiliar, here's an explanation.

This code already contains the database connection, explained in our next step.

import requests

import time

import string

import json

import datetime

from dotenv import load_dotenv

from math import radians, sin, cos, atan2, sqrt

import os

import math

import mysql.connector

load_dotenv()

mydb = mysql.connector.connect(

host=os.getenv("db_host"),

user=os.getenv("db_user_radar"),

password=os.getenv("db_password_radar"),

db=os.getenv("db")

)

def get_haversine_distance(lat1, lng1, lat2, lng2):

# Approximate radius of earth in km

R = 6373.0

lat1 = radians(lat1)

lon1 = radians(lng1)

lat2 = radians(lat2)

lon2 = radians(lng2)

dlon = lon2 - lon1

dlat = lat2 - lat1

a = sin(dlat / 2)**2 + cos(lat1) * cos(lat2) * sin(dlon / 2)**2

c = 2 * atan2(sqrt(a), sqrt(1 - a))

distance = R * c

return round(distance,2)

def checkForDuplicates(callSignToBeChecked):

mycursor = mydb.cursor()

lookUpQuery = "SELECT call_sign FROM `flights` ORDER BY date_time DESC LIMIT 10;"

mycursor.execute(lookUpQuery)

result = mycursor.fetchall();

mycursor.close()

passedDuplicateCheck = True

for callSign in result:

if callSignToBeChecked == callSign[0]:

passedDuplicateCheck = False

return passedDuplicateCheck

def estimateNoiseLevel(data):

YOUR_COORD_LAT = # Your Latitude

YOUR_COORD_LON = # Your longitude

EARTH_RADIUS_KM = 6371.0

lat_rad_obs = math.radians(YOUR_COORD_LAT)

lon_rad_obs = math.radians(YOUR_COORD_LON)

lat_rad_plane = math.radians(data['latitude'])

lon_rad_plane = math.radians(data['longitude'])

# Haversine formula for horizontal distance

delta_lon = lon_rad_plane - lon_rad_obs

central_angle = math.acos(

math.cos(lat_rad_obs) * math.cos(lat_rad_plane) * math.cos(delta_lon) +

math.sin(lat_rad_obs) * math.sin(lat_rad_plane)

)

horizontal_distance_km = EARTH_RADIUS_KM * central_angle

vertical_distance_km = data['geo_altitude'] / 1000.0 # Convert meters to kilometers

total_distance_km = math.sqrt(

math.pow(horizontal_distance_km, 2) +

math.pow(vertical_distance_km, 2)

)

min_squared_distance_for_log = 0.001

distance_contribution_denominator = max(math.pow(total_distance_km, 2), min_squared_distance_for_log)

distance_contribution = 10000000.0 / distance_contribution_denominator

min_altitude_meters = 10.0

altitude_contribution = 100000.0 / max(data['geo_altitude'], min_altitude_meters)

vertical_rate_contribution = abs(data['vertical_rate']) * 50.0

intensity_score_sum = distance_contribution + altitude_contribution + vertical_rate_contribution

final_intensity_score = max(intensity_score_sum, 1.0)

noise_level = round(10 * math.log10(final_intensity_score))

return noise_level

def formatAndSaveData(data):

YOUR_COORD = [XX, XX]

dateTime = datetime.datetime.now().replace(second=0, microsecond=0) # Primary Key

for item in data['states']:

temp = {}

temp['date_time'] = dateTime # Primary Key

temp['call_sign'] = item[1].strip() # Primary key

temp['plane_category'] = item[17]

temp['longitude'] = item[5]

temp['latitude'] = item[6]

temp['baro_altitude'] = item[7] # meters

temp['geo_altitude'] = item[13] # meters

temp['distance'] = get_haversine_distance(YOUR_COORD[0], YOUR_COORD[1], item[6], item[5]) # km

temp['speed'] = round(item[9] * 3600 /1000) # km/h

temp['vertical_rate'] = item[11] # m/s

temp['noise_level'] = estimateNoiseLevel(temp)

if checkForDuplicates(temp['call_sign']):

savePlaneDataToDB(temp)

else:

print("-- Duplicate found -- " + temp['call_sign'])

def checkForOverheadPlanes():

# Real bbox

params = {

'lomin': 'XX',

'lamin': 'xX',

'lomax': 'XX',

'lamax': 'XX',

'extended': 1

}

response = requests.get('https://opensky-network.org/api/states/all', params=params, auth=(os.getenv("open_sky_user"), os.getenv("open_sky_password")))

rawData = response.json()

if rawData['states'] is not None:

formattedData = formatAndSaveData(response.json())

def savePlaneDataToDB(dataObject):

mycursor = mydb.cursor()

date_time = dataObject["date_time"] # Primary Key

call_sign = dataObject["call_sign"] # Primary Key

plane_category = dataObject["plane_category"]

longitude = dataObject["longitude"]

latitude = dataObject["latitude"]

baro_altitude = dataObject['baro_altitude'] # meters

geo_altitude = dataObject['geo_altitude'] # meters

distance = dataObject["distance"] # km

speed = dataObject["speed"] # km/h

vertical_rate = dataObject['vertical_rate'] # m/s

noise_level = dataObject['noise_level'] # Decibel

insertQuery = f"INSERT INTO `flights` (`date_time`, `call_sign`,`plane_category`, `longitude`, `latitude`, `baro_altitude`, `geo_altitude`, `distance`, `speed`,`vertical_rate`,`noise_level` ) VALUES ('{date_time}', '{call_sign}', '{plane_category}', '{longitude}', '{latitude}', '{baro_altitude}','{geo_altitude}','{distance}', '{speed}', '{vertical_rate}', '{noise_level}')"

try:

mycursor.execute(insertQuery)

mydb.commit()

mycursor.close()

except Exception as e:

print(e)

time.sleep(1)

while True:

try:

checkForOverheadPlanes()

# 4000 calls per day --> 30 seconds of sleep per call minimum, take 30 te be safe

time.sleep(30)

except Exception as e:

print(e)

time.sleep(30)

Data Storage

Database Setup

For a permanent record of all this activity, and some extra data analysis, we create a MySQL database. Luckily, PiMyLifeUp has a fantastic tutorial, installing and configuring a MySQL server on your Pi.

In order to save our plane related data, we create a dedicated database, table and user by running some SQL commands.

-- 1. Create the Database

CREATE DATABASE IF NOT EXISTS planedar;

-- 2. Select the Database to Use

USE planedar;

-- 3. Create the 'flights' Table

CREATE TABLE `flights` (

-- Timestamp of the observation. Using DATETIME for date and time.

-- Part of the composite primary key.

`date_time` DATETIME NOT NULL COMMENT 'Observation timestamp (GMT+2)',

-- Aircraft's unique call sign (e.g., flight number).

-- Part of the composite primary key.

`call_sign` VARCHAR(20) NOT NULL,

-- Category or type of aircraft (e.g., 1=Commercial Jet, 2=Private Propeller).

`plane_category` INT NOT NULL,

-- Geographical coordinates for location tracking. Precision is appropriate for flight data.

`longitude` DECIMAL(9, 6) DEFAULT NULL,

`latitude` DECIMAL(8, 6) DEFAULT NULL,

-- Altitude reported by the barometric altimeter (pressure-based).

`baro_altitude` FLOAT NOT NULL COMMENT 'Altitude in Meters (Barometric)',

-- Altitude reported by GPS or geometry (above the WGS84 ellipsoid or ground).

`geo_altitude` FLOAT NOT NULL COMMENT 'Altitude in Meters (Geometric/GPS)',

-- Distance from a fixed point (e.g., observer's location). NULLable if not calculated.

`distance` DECIMAL(10, 2) DEFAULT NULL COMMENT 'Distance from observer in Kilometers',

-- Ground speed of the aircraft. NULLable if data is missing.

`speed` DECIMAL(7, 2) DEFAULT NULL COMMENT 'Ground speed in km/h',

-- Rate of ascent (+) or descent (-). Critical for noise estimation.

`vertical_rate` FLOAT NOT NULL COMMENT 'Vertical rate in m/s',

-- The combination of time and call sign ensures a unique record per plane per observation.

PRIMARY KEY (`date_time`, `call_sign`)

)

-- InnoDB is the standard transactional storage engine.

ENGINE=InnoDB

-- Character set and collation are modern and support a wide range of characters.

DEFAULT CHARSET=utf8mb4

COLLATE=utf8mb4_general_ci;

Script / Database Connection

We already mentioned the SQL connection functions in the previous step, but here is the isolated code for better understanding.

import mysql.connector

from dotenv import load_dotenv

import os

import time

# Load environment variables from a .env file (for secure credentials)

load_dotenv()

# --- Database Connection Setup ---

try:

mydb = mysql.connector.connect(

host=os.getenv("DB_HOST"),

user=os.getenv("DB_USER_RADAR"),

password=os.getenv("DB_PASSWORD_RADAR"),

database=os.getenv("DB_NAME") # Renamed 'db' to 'database' for clarity

)

if mydb.is_connected():

print("Database connection successful.")

except Exception as e:

print(f"Error connecting to MySQL database: {e}")

def save_plane_data_to_db(data_object: dict):

"""

Saves a single flight observation record to the 'flights' table.

Args:

data_object: A dictionary containing all necessary flight data fields.

"""

try:

with mydb.cursor() as mycursor:

# 1. Define the SQL INSERT statement with placeholders (%s)

insert_query = """

INSERT INTO `flights` (

`date_time`, `call_sign`, `plane_category`, `longitude`, `latitude`,

`baro_altitude`, `geo_altitude`, `distance`, `speed`, `vertical_rate`,

`noise_level`

)

VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)

"""

# 2. Prepare the data tuple in the correct order

data_tuple = (

data_object["date_time"],

data_object["call_sign"],

data_object["plane_category"],

data_object["longitude"],

data_object["latitude"],

data_object['baro_altitude'],

data_object['geo_altitude'],

data_object["distance"],

data_object["speed"],

data_object['vertical_rate'],

data_object.get('noise_level')

)

# 3. Execute the query using the tuple for safe parameter passing

mycursor.execute(insert_query, data_tuple)

# Commit the transaction to make the changes permanent

mydb.commit()

except mysql.connector.Error as err:

# Catch specific MySQL errors

print(f"Database Error: {err}")

# Consider rolling back if an error occurred before commit

mydb.rollback()

time.sleep(1)

except Exception as e:

# Catch other general Python errors

print(f"General Error: {e}")

time.sleep(1)

def check_for_recent_entry(call_sign_to_be_checked: str):

"""

Checks if the given call sign is present in the 10 most recent observations.

Returns:

True if the call sign is NOT found (i.e., 'passed' the check).

False if the call sign IS found (i.e., a 'duplicate' recent entry).

"""

try:

with mydb.cursor() as mycursor:

look_up_query = "SELECT call_sign FROM `flights` ORDER BY date_time DESC LIMIT 10;"

mycursor.execute(look_up_query)

recent_call_signs = [row[0] for row in mycursor.fetchall()]

# The check is simply whether the call sign is NOT in the list

passed_duplicate_check = call_sign_to_be_checked not in recent_call_signs

return passed_duplicate_check

except mysql.connector.Error as err:

print(f"Database Error during duplicate check: {err}")

return True # Default to True (pass check) if database is unavailable

Data Analysis

With our basis covered, we can now let the app run for a while, and see the data come in. It's remarkably fun to browse, but it would be even better to have the most interesting information readily available. To make this happen, we are going to use SQL Views, converting the data into information.

Total flights per day

This view calculates the total number of flights observed on each day.

CREATE OR REPLACE VIEW v_flights_per_day AS

SELECT

DATE(date_time) AS observation_date,

COUNT(*) AS total_flights

FROM

flights

GROUP BY

observation_date

ORDER BY

observation_date;

Average flights per timeslot (1h)

This view calculates the average number of flights that occur during a specific hour across all recorded days.

CREATE OR REPLACE VIEW v_avg_flights_per_hour AS

SELECT

-- Formats the DATETIME into a 1-hour timeslot string (e.g., '14:00').

DATE_FORMAT(date_time, '%H:00') AS hour_of_day,

-- Calculates the total number of flights, divided by the number of distinct days recorded.

-- This gives the average frequency per hour bucket.

ROUND(

COUNT(*) / COUNT(DISTINCT DATE(date_time))

) AS avg_flights_per_hour

FROM

flights

GROUP BY

hour_of_day

ORDER BY

hour_of_day;

Highest total passovers

This view identifies the single call sign (plane) that has been observed the most.

CREATE OR REPLACE VIEW v_most_frequent_call_sign AS

SELECT

call_sign,

COUNT(call_sign) AS flight_count

FROM

flights

GROUP BY

call_sign

ORDER BY

flight_count DESC

LIMIT 1;

Fastest

This view retrieves the plane with the highest recorded ground speed.

CREATE OR REPLACE VIEW v_fastest_record AS

SELECT

FROM

flights

WHERE

speed IS NOT NULL -- Exclude records where speed data is missing

ORDER BY

speed DESC

LIMIT 1;

Closest

This view retrieves the record of the closest pass, prioritizing horizontal distance, then altitude.

CREATE OR REPLACE VIEW v_closest_pass AS

SELECT

FROM

flights

WHERE

distance IS NOT NULL -- Must have horizontal distance

AND (baro_altitude IS NOT NULL OR geo_altitude IS NOT NULL) -- Must have altitude data

ORDER BY

distance ASC, -- 1. Primary sort: Closest horizontal distance

geo_altitude ASC -- 2. Secondary sort: Lowest altitude

LIMIT 1;

Loudest

This SQL view is designed to easily find the single flight observation that registered the highest calculated noise level.

CREATE OR REPLACE VIEW v_loudest_pass AS

SELECT

FROM

flights

WHERE

noise_level IS NOT NULL -- Ensure the record has a calculated noise level

ORDER BY

noise_level DESC -- Sort by noise level, highest (loudest) first

LIMIT 1;

Hall Of Fame

This view acts as a master leaderboard by stacking the top results from four different categories; loudest, fastest, closest, and most frequent.

CREATE OR REPLACE VIEW `hall_of_fame` AS

-- 1. Grab the loudest plane

SELECT 'Highest Noise Impact' AS metric_type, date_time, call_sign, plane_category,

longitude, latitude, baro_altitude, geo_altitude, distance, speed,

vertical_rate, noise_level, NULL AS flight_count

FROM `v_loudest_pass`

UNION ALL

-- 2. Grab the fastest plane

SELECT 'Highest Speed' AS metric_type, date_time, call_sign, plane_category,

longitude, latitude, baro_altitude, geo_altitude, distance, speed,

vertical_rate, noise_level, NULL AS flight_count

FROM `v_fastest_record`

UNION ALL

-- 3. Grab the closest plane

SELECT 'Lowest Combined Distance' AS metric_type, date_time, call_sign, plane_category,

longitude, latitude, baro_altitude, geo_altitude, distance, speed,

vertical_rate, noise_level, NULL AS flight_count

FROM `v_closest_pass`

UNION ALL

-- 4. Grab the most frequent visitor

SELECT 'Highest Callsign Count' AS metric_type, NULL AS date_time, call_sign,

NULL AS plane_category, NULL AS longitude, NULL AS latitude, NULL AS baro_altitude,

NULL AS geo_altitude, NULL AS distance, NULL AS speed, NULL AS vertical_rate,

NULL AS noise_level, flight_count

FROM `v_most_frequent_call_sign`;

PA System

Audio Generation

We should not forget the most important part of all, the experience, this is exactly where our our PA System truly shines. It will shoot into action whenever a plane is detected, generate some audio and stream it directly to a Bluetooth speaker.

To get the feel right, we need to generate two audio segments and stitch them together, after which we add some rustic, slightly unpleasant, authenticity. The audio will consist of

A three tone intro and outro tune.
Spoken words from the relevant data.

The result is a tad too slow, slightly inhuman, and sounds like it is comes out of cheapest hardware available, perfect.

Below is the static code to generate this audio, which you can add to our previous script to make it dynamic. An example of the generated audio is also attached to this step as a .wav file, enjoy.

import librosa

import numpy as np

import soundfile as sf

from scipy.signal import butter, lfilter

from gtts import gTTS

import os

from playsound import playsound

from scipy.io.wavfile import write

def generate_pa_chime(filename="chime_generated.wav", duration_sec=2):

"""

Generates a slow, three-tone ascending PA announcement chime.

"""

sample_rate = 44100

# Calculate the duration for each of the three tones

tone_duration_sec = duration_sec / 3

num_samples_per_tone = int(sample_rate * tone_duration_sec)

# Time vector for one tone's duration

t = np.linspace(0., tone_duration_sec, num_samples_per_tone, endpoint=False)

# Define the three ascending frequencies

freq_low = 250 # First tone (Base)

freq_mid = 350 # Second tone

freq_high = 450 # Third tone (Highest)

# Generate the sine waves for each tone

tone1 = 0.5 * np.sin(2. * np.pi * freq_low * t)

tone2 = 0.5 * np.sin(2. * np.pi * freq_mid * t)

tone3 = 0.5 * np.sin(2. * np.pi * freq_high * t)

# Concatenate the three tones in sequence

chime_data = np.concatenate((tone1, tone2, tone3))

# Scale and convert to 16-bit integer format for WAV

chime_data = np.int16(chime_data * 32767)

# Save the array to a WAV file

write(filename, sample_rate, chime_data)

return filename

# --- Part 1: Initial TTS Generation (Mumbling Voice) ---

def generate_base_audio(text, filename="pilot_base.wav"):

"""Generates the base audio using gTTS."""

tts = gTTS(text=text, lang='en', slow=False, tld="co.uk")

tts.save(filename)

return filename

# --- Part 2: Digital Signal Processing (Mumble/Intercom Effect) ---

def butter_bandpass(lowcut, highcut, fs, order=5):

"""Cheap PA system cuts off both very low bass frequencies (muffling the voice) and very high treble frequencies (removing clarity)."""

nyq = 0.5 * fs

low = lowcut / nyq

high = highcut / nyq

b, a = butter(order, [low, high], btype='band')

return b, a

def apply_pilot_effect(input_file, output_file):

"""Applies frequency filtering and noise to simulate a poor PA system."""

# Load the audio signal

y, sr = librosa.load(input_file, sr=44100)

# 1. Bandpass Filtering (The 'Intercom/Tinny' Effect)

# Frequencies for cheap communication systems are typically 300-3000 Hz

lowcut = 300.0

highcut = 3000.0

b, a = butter_bandpass(lowcut, highcut, sr, order=4)

y_filtered = lfilter(b, a, y)

# 2. Add White Noise (The 'Static' Effect)

# The magnitude (0.005) controls how loud the noise is relative to the speech

noise_magnitude = 0.005

white_noise = np.random.normal(0, noise_magnitude, len(y))

y_noisy = y_filtered + white_noise

# 3. Apply Slight Pitch Shift (The 'Faulty Equipment' Effect)

# Shifts pitch down by 1 semitone for a deeper, slightly distorted voice.

semitone_shift = -1.0

y_final = librosa.effects.pitch_shift(y_noisy, sr=sr, n_steps=semitone_shift)

# 4. Save the final processed audio as a WAV file

sf.write(output_file, y_final, sr)

print(f"Pilot announcement saved to: {output_file}")

# Generate the chime before the main script runs

chime_file = generate_pa_chime()

print(f"Generated chime file: {chime_file}")

# --- Main Execution ---

if __name__ == "__main__":

call_sign = "BANANA"

geo_altitude = 1834

speed = 494

noise_level = 65

disturbance = noise_level - 55

disturbanceText = "which is luckily below the disturbance threshold."

if disturbance > 0:

disturbanceText = "which is well above the disturbance threshold."

text_announcement = (

f"Ladies and gentlemen this is your captain speaking."

f"Currently passing over is the {call_sign}, at a distance of about {geo_altitude} meters and with an estimated airspeed velocity of {speed} km/h."

f"As you can hear, it's producing a pleasant noise index of {noise_level}, {disturbanceText}"

f"Thank you for your attention, and please enjoy the rustic sounds of airplane travel."

)

base_file = "pilot_base.mp3"

processed_message_file = "old_pilot_mumble.wav"

# --- NEW: Generate the chime programmatically ---

chime_file = generate_pa_chime()

final_announcement_file = "final_announcement_with_chimes.wav"

# Step 1: Generate the base audio (saves to base_file.mp3)

tts = gTTS(text=text_announcement)

tts.save(base_file)

# Step 2: Apply the effects and save the final message audio (saves to processed_message_file.wav)

apply_pilot_effect(base_file, processed_message_file)

# --- Step 3: Stitch the Audio using SoX ---

print("Stitching generated chime and announcement...")

# The SoX command now uses the dynamically generated chime file

sox_command = f"sox {chime_file} {processed_message_file} {chime_file} {final_announcement_file}"

# Execute the command

os.system(sox_command)

# Step 4: Play the final stitched audio file!

print(f"Playing final audio file: {final_announcement_file}")

try:

playsound(final_announcement_file)

except Exception as e:

print(f"Error playing final audio: {e}")

Bluetooth Speaker

Now it's time for the connection between the Raspberry Pi and the bluetooth speaker. PyMyLifeUp has yet another great article with the instructions, Bluetooth on the Raspberry Pi.

With our PA setup, we can sit back, listen to the announcements, and get a real good feel of all the traffic far above our heads.

Downloads

final_announcement_with_chimes.wav

Backend

With the bulk of operations in working order we can move on to the displaying part, our dashboard. In order to display some statistics in a clean way we will first of all make the data from our SQL views accessible via an API. To do just this we use Flask, a lightweight python backend framework.

This python app will be nothing more then a serving hatch, accepting request for information from our soon te be created frontend, fetching it from our data storage and passing it back to the front end.

from flask import Flask, request, jsonify

import subprocess

import mysql.connector

from mysql.connector import Error, pooling

from dotenv import load_dotenv

import os

import json

import datetime

import decimal

from flask_cors import CORS

load_dotenv()

db_config = {

"host": os.getenv("db_host"),

"user": os.getenv("db_user_api"),

"password": os.getenv("db_password_api"),

"database": os.getenv("db"),

"pool_name": "flask_app_pool",

"pool_size": 5,

"pool_reset_session": True,

"raise_on_warnings": True,

"collation": "utf8mb4_unicode_ci",

"connect_timeout": 4

}

connection_pool = None

try:

connection_pool = pooling.MySQLConnectionPool(**db_config)

print(f"MySQL Connection Pool '{db_config['pool_name']}' created with size {db_config['pool_size']}.")

except Error as e:

print(f"CRITICAL ERROR: Failed to create MySQL connection pool: {e}")

def execute_query(query, params=None, is_select=True):

connection = None

cursor = None

result = None

try:

if connection_pool is None:

raise Error("Database connection pool is not initialized.")

connection = connection_pool.get_connection()

connection.ping(reconnect=True)

cursor = connection.cursor()

cursor.execute(query, params)

if is_select:

result = cursor.fetchall()

else:

connection.commit()

result = cursor.rowcount

except Error as e:

print(f"Database error during query '{query}': {e}")

if connection and connection.is_connected():

connection.rollback()

raise

finally:

if cursor:

cursor.close()

if connection:

connection.close()

return result

def getFlightsPerDayData():

lookUpQuery = "SELECT * FROM `flights_per_day` ORDER BY DATE(date) DESC;"

records = execute_query(lookUpQuery, is_select=True)

return records

def getFlightsPerHourData():

lookUpQuery = "SELECT * FROM `avg_flights_per_hour` ORDER BY hour_of_day ASC;"

records = execute_query(lookUpQuery, is_select=True)

return records

def getFlightsHeatmapData():

lookUpQuery = "SELECT * FROM `heat_map_data`;"

records = execute_query(lookUpQuery, is_select=True)

return records

def getNoiseLevelsData():

lookUpQuery = "SELECT * FROM `noise_levels`;"

records = execute_query(lookUpQuery, is_select=True)

return records

def getHallOfFameData():

lookUpQuery = "SELECT * FROM `hall_of_fame`;"

records = execute_query(lookUpQuery, is_select=True)

return records

app = Flask(__name__)

CORS(app)

@app.route('/getDataFlightsPerDay', methods=['GET'])

def getDataFlightsPerDay():

try:

data = getFlightsPerDayData()

response = jsonify({'data': data})

return response

except Error as e:

return jsonify({"error": str(e), "message": "Failed to retrieve flights per day data"}), 500

except Exception as e:

return jsonify({"error": str(e), "message": "An unexpected error occurred"}), 500

@app.route('/getDataFlightsHeatmap', methods=['GET'])

def getDataFlightsHeatmap():

try:

data = getFlightsHeatmapData()

response = jsonify({'data': data})

return response

except Error as e:

return jsonify({"error": str(e), "message": "Failed to retrieve flights per day data"}), 500

except Exception as e:

return jsonify({"error": str(e), "message": "An unexpected error occurred"}), 500

@app.route('/getDataFlightsPerHour', methods=['GET'])

def getDataFlightsPerHour():

try:

data = getFlightsPerHourData()

response = jsonify({'data': data})

return response

except Error as e:

return jsonify({"error": str(e), "message": "Failed to retrieve flights per hour data"}), 500

except Exception as e:

return jsonify({"error": str(e), "message": "An unexpected error occurred"}), 500

@app.route('/getDataNoiseLevels', methods=['GET'])

def getDataNoiseLevels():

try:

data = getNoiseLevelsData()

response = jsonify({'data': data})

return response

except Error as e:

return jsonify({"error": str(e), "message": "Failed to retrieve flights per hour data"}), 500

except Exception as e:

return jsonify({"error": str(e), "message": "An unexpected error occurred"}), 500

@app.route('/getDataHallOfFame', methods=['GET'])

def getDataHallOfFame():

try:

data = getHallOfFameData()

response = jsonify({'data': data})

return response

except Error as e:

return jsonify({"error": str(e), "message": "Failed to retrieve flights per hour data"}), 500

except Exception as e:

return jsonify({"error": str(e), "message": "An unexpected error occurred"}), 500

if __name__ == '__main__':

app.run(host="0.0.0.0", debug=True)

Our backend wil only require reading access to the SQL views we created earlier. It won't write any new data, hence we create a specific user with privileges to only read the summarised data.

CREATE USER 'user_api'@'%';

GRANT SELECT ON Planedar.flights_per_day TO 'user_api'@'%';

GRANT SELECT ON Planedar.heat_map_data TO 'user_api'@'%';

GRANT SELECT ON Planedar.avg_flights_per_hour TO 'user_api'@'%';

GRANT SELECT ON Planedar.call_sign_count TO 'user_api'@'%';

GRANT SELECT ON Planedar.highest_call_sign_count TO 'user_api'@'%';

GRANT SELECT ON Planedar.highest_noise_impact TO 'user_api'@'%';

GRANT SELECT ON Planedar.highest_speed TO 'user_api'@'%';

GRANT SELECT ON Planedar.lowerst_combined_distance TO 'user_api'@'%';

GRANT SELECT ON Planedar.hall_of_fame TO 'user_api'@'%';

FLUSH PRIVILEGES;

Dashboard

Webserver

Our dashboard is going to need a place to live, so we use the following guide to setup an apache webserver and a basic website an our Pi. Personally, I haven't done the Nginx revers proxy part of this tutorial.

Frontend

Once the server is setup we can create a basic dashboard by using the Tailwind CSS framework. The full webpage is added below.

Disturbance Threshold

In our dashboard we also added a disturbance threshold of 55 dB, this red line indicates when the noise is deemed a disturbance. The number was provided by our favorite somewhat sentient computer, in a very convincing way.

index.html

<!DOCTYPE html>

<head>

<title>Planedar</title>

</head>

<h2 class="titleTile">PLANEDAR</h2>

<p style="color:rgb(54 162 235);""> Your flights, my noise.</p>

</div>

<h3 class="dataTileHeader">Flights per day</h3>

</div>

<h3 class="dataTileHeader">Average Flights timeslot</h3>

</div>

<h3 class="dataTileHeader">Heatmap</h3>

</div>

<h3 class="dataTileHeader">Noise Impact Score (24h)</h3>

</div>

</div>

</main>

</body>

</html>

style.css

body {

font-family: 'Inter', sans-serif;

background-color: #F8FAFC;

}

.titleTile {

font-size: 3rem;

font-weight: 800;

color: rgb(54 162 235);

letter-spacing: 0.05em;

}

.dataTile{

background-color: white;

border-radius: 0.75rem;

box-shadow: 0 10px 15px -3px rgb(0 0 0 / 0.1), 0 4px 6px -4px rgb(0 0 0 / 0.1);

padding: 1.5rem;

border-width: 1px;

border-color: rgb(243 244 246);

display: flex;

flex-direction: column;

justify-content: space-between;

}

.dataTileHeader {

font-size: 1.5rem;

font-weight: 600;

color: rgb(54 162 235);

margin-bottom: 0.5rem;

}

.dataTileContent {

width: 100%;

flex-grow: 1;

display: flex;

align-items: center;

justify-content: center;

}

.metric-card {

background-color: #f7fafc;

border-radius: 8px;

padding: 16px;

box-shadow: 0 2px 6px rgba(0, 0, 0, 0.05);

border: 1px solid #e2e8f0;

display: flex;

flex-direction: column;

justify-content: space-between;

}

.metric-type {

font-size: 1.25rem;

font-weight: 600;

color: rgb(54 162 235);

margin-bottom: 8px;

border-bottom: 1px dashed #cbd5e0;

padding-bottom: 4px;

}

.metric-detail {

font-size: 0.95rem;

color: #4a5568;

margin-bottom: 4px;

}

.metric-detail span {

font-weight: 500;

color: rgb(54 162 235);

}

index.js

const lineColour = "rgb(54, 162, 235, 1)";

async function getFlightsPerDayData(){

url = "http://192.168.1.Xx:5000/getDataFlightsPerDay";

const response = await fetch(url);

const flightsPerDayData = await response.json();

return flightsPerDayData['data'];

}

async function drawFlightsPerDay(){

const flightsPerDayData = await getFlightsPerDayData();

const flightsData = [];

const dataDatePoints = [];

flightsPerDayData.forEach(dataPoint=> {

const date = new Date(dataPoint[0]);

const options = {

day: '2-digit',

month: '2-digit',

year: '2-digit'

};

const dateString = date.toLocaleDateString('en-GB',options);

dataDatePoints.unshift(dateString);

flightsData.unshift(dataPoint[1]);

});

const ctx = document.getElementById('flightsPerDayChart').getContext('2d');

const data = {

labels: dataDatePoints,

datasets: [{

label: 'Number of Flights',

data: flightsData,

fill: true, // Don't fill the area under the line

borderColor: lineColour, // Line color

tension: 0.3, // Smoothness of the line (0 for straight, 1 for very curved)

borderWidth: 2, // Thickness of the line

pointBackgroundColor: lineColour, // Color of data points

pointBorderColor: '#fff', // Border color of data points

pointHoverBackgroundColor: '#fff', // Hover background color for points

pointHoverBorderColor: lineColour, // Hover border color for points

pointRadius: 5, // Size of data points

pointHoverRadius: 7 // Size of data points on hover

}]

};

const config = {

type: 'line', // Type of chart

data: data,

options: {

responsive: true, // Make the chart responsive

maintainAspectRatio: true, // Allow canvas to resize freely within its container

plugins: {

legend: {

display: false, // Show the legend (e.g., 'Number of Flights')

position: 'top', // Position of the legend

labels: {

font: {

size: 14 // Font size for legend labels

}

title: {

display: false, // No separate title as we have H1 above

text: 'Flights Per Day'

}

scales: {

x: {

grid: {

display: false // This removes the vertical grid lines (for the x-axis)

title: {

display: false,

text: 'Date',

font: {

size: 16

color: '#4a5568'

ticks: {

color: '#4a5568',

display:false

}

y: {

title: {

display: false,

text: '# Flights',

font: {

size: 16

color: '#4a5568'

beginAtZero: false, // Start y-axis at 0

ticks: {

color: '#4a5568'

}

};

myLineChart = new Chart(ctx, config);

const container = document.getElementById('flights-per-day-container');

new ResizeObserver(() => {

if (myLineChart) {

myLineChart.resize();

}

}).observe(container);

};

async function getFlightsPerHourData(){

url = "http://192.168.1.XX:5000/getDataFlightsPerHour";

const response = await fetch(url);

const flightsPerDayData = await response.json();

return flightsPerDayData['data'];

}

async function drawFlightsPerHour(){

const flightsPerHourData = await getFlightsPerHourData();

const flightsData = [];

const dataHourPoints = [];

flightsPerHourData.forEach(dataPoint=> {

dataHourPoints.push(dataPoint[0]);

flightsData.push(dataPoint[1]);

});

const ctx = document.getElementById('flightsPerHourChart').getContext('2d');

const data = {

labels: dataHourPoints,

datasets: [{

label: 'Flights per hour',

data: flightsData,

fill: true, // Don't fill the area under the line

borderColor: lineColour, // Line color

tension: 0.3, // Smoothness of the line (0 for straight, 1 for very curved)

borderWidth: 2, // Thickness of the line

pointBackgroundColor: 'rgb(75, 192, 192)', // Color of data points

pointBorderColor: '#fff', // Border color of data points

pointHoverBackgroundColor: '#fff', // Hover background color for points

pointHoverBorderColor: 'rgb(75, 192, 192)', // Hover border color for points

pointRadius: 5, // Size of data points

pointHoverRadius: 7 // Size of data points on hover

}]

};

const config = {

type: 'bar', // Type of chart

data: data,

options: {

responsive: true, // Make the chart responsive

maintainAspectRatio: true, // Allow canvas to resize freely within its container

plugins: {

legend: {

display: false, // Show the legend (e.g., 'Number of Flights')

position: 'top', // Position of the legend

labels: {

font: {

size: 14 // Font size for legend labels

}

title: {

display: false, // No separate title as we have H1 above

text: 'Flights Per Day'

}

scales: {

x: {

grid: {

display: false // This removes the vertical grid lines (for the x-axis)

title: {

display: false,

text: 'Hour',

font: {

size: 16

color: '#4a5568'

ticks: {

color: '#4a5568'

}

y: {

title: {

display: false,

text: '# Flights',

font: {

size: 16

color: '#4a5568'

beginAtZero: true, // Start y-axis at 0

ticks: {

color: '#4a5568'

}

};

myLineChart = new Chart(ctx, config);

const container = document.getElementById('flights-per-hour-container');

new ResizeObserver(() => {

if (myLineChart) {

myLineChart.resize();

}

}).observe(container);

};

async function getFlightsHeatmapData(){

url = "http://192.168.1.XX:5000/getDataFlightsHeatmap";

const response = await fetch(url);

const flightsPerDayData = await response.json();

return flightsPerDayData['data'];

}

async function drawHeatMap(){

var map = L.map('mapid-container').setView([XX, XX], 13);

const myMarker = L.marker([XX, XX]);

myMarker.addTo(map);

L.tileLayer('https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png', {maxZoom: 13, attribution: '© <a href="http://www.openstreetmap.org/copyright">OpenStreetMap</a>'}).addTo(map);

var planeData = await getFlightsHeatmapData();

const formattedPlaneData = [];

var maxIntensity = 0;

planeData.forEach(dataPoint=> {

const formattedData = []

latitude = parseFloat(dataPoint[1])

longitude = parseFloat(dataPoint[2])

intensity = parseFloat(dataPoint[3])

if (intensity > maxIntensity){

maxIntensity = intensity;

}

formattedData.push(latitude);

formattedData.push(longitude);

formattedData.push(intensity);

formattedPlaneData.unshift(formattedData);

})

var heat = L.heatLayer(formattedPlaneData, {

radius: 15, // Radius of influence for each point (in pixels)

blur: 0, // Amount of blur (in pixels)

maxZoom: 13, // Zoom level where points reach maximum intensity

max: maxIntensity, // Max intensity value for data points (if your data values go higher, adjust this)

// Optional: Customize the color gradient

gradient: {

0.0: 'blue',

0.2: 'cyan',

0.4: 'lime',

0.6: 'yellow',

0.8: 'orange',

1.0: 'red'

}

}).addTo(map);

map.invalidateSize();

};

async function getNoiseLevelsData(){

url = "http://192.168.1.XX:5000/getDataNoiseLevels";

const response = await fetch(url);

const noiseLevelsData = await response.json();

return noiseLevelsData['data'];

}

async function drawNoiseLevels(){

const noiseLevelsData = await getNoiseLevelsData();

const noiseData = [];

const noiseTimePoints = [];

const noiseCallSignsLabels = [];

noiseLevelsData.forEach(dataPoint=> {

const date = new Date(dataPoint[0]);

const options = {

minute: '2-digit',

hour: '2-digit'

};

const timeString = date.getUTCHours() + ':' + date.getUTCMinutes();

noiseData.unshift(dataPoint[2]);

var labelString = "Callsign " + dataPoint[1] + " - " + dataPoint[0];

noiseCallSignsLabels.unshift(labelString);

});

const ctx = document.getElementById('noiseLevels').getContext('2d');

const data = {

labels: noiseCallSignsLabels,

datasets: [{

label: 'Noise Impact Score',

data: noiseData,

fill: true, // Don't fill the area under the line

borderColor: lineColour, // Line color

tension: 0.3, // Smoothness of the line (0 for straight, 1 for very curved)

borderWidth: 2, // Thickness of the line

pointBackgroundColor: 'rgb(75, 192, 192)', // Color of data points

pointBorderColor: '#fff', // Border color of data points

pointHoverBackgroundColor: '#fff', // Hover background color for points

pointHoverBorderColor: 'rgb(75, 192, 192)', // Hover border color for points

pointRadius: 4, // Size of data points

pointHoverRadius: 7 // Size of data points on hover

}]

};

const config = {

type: 'line', // Type of chart

data: data,

options: {

responsive: true, // Make the chart responsive

maintainAspectRatio: true, // Allow canvas to resize freely within its container

plugins: {

subtitle: { // Use subtitle for your footnote

display: true,

text: 'Note: Aircraft activity is represented by a synthetic impact score, calculated from proximity, altitude, and vertical rate.',

position: 'bottom', // This is key to position it below the title

font: {

size: 10,

style: 'italic',

weight: 'normal'

color: '#666' // A slightly muted color

annotation: {

annotations: {

// This is your red threshold line

myThreshold: { // You can name this anything unique

type: 'line',

mode: 'horizontal', // Makes it horizontal

scaleID: 'y', // Connects it to your Y-axis

value: 55, // The value where the line appears

borderColor: 'red', // Make it red!

borderWidth: 2, // How thick it is

borderDash: [6, 6], // Optional: makes it a dashed line

label: {

content: 'Distu', // Text on the line

enabled: true, // Show the text

backgroundColor: 'rgba(255, 0, 0, 0.7)',

color: 'white'

}

legend: {

display: false, // Show the legend (e.g., 'Number of Flights')

position: 'top', // Position of the legend

labels: {

font: {

size: 14 // Font size for legend labels

}

title: {

display: false, // No separate title as we have H1 above

text: 'Noise levels (24h)'

}

scales: {

x: {

grid: {

display: false // This removes the vertical grid lines (for the x-axis)

title: {

display: false,

text: 'Time',

font: {

size: 16

color: '#4a5568'

ticks: {

display: false,

color: '#4a5568'

}

y: {

title: {

display: false,

text: 'Decibels',

font: {

size: 16

color: '#4a5568'

beginAtZero: false, // Start y-axis at 0

ticks: {

color: '#4a5568'

}

};

myLineChart = new Chart(ctx, config);

const container = document.getElementById('noiseLevels-container');

new ResizeObserver(() => {

if (myLineChart) {

myLineChart.resize();

}

}).observe(container);

};

async function getHallOfFameData(){

url = "http://192.168.1.XX:5000/getDataHallOfFame";

const response = await fetch(url);

const hallOfFameData = await response.json();

return hallOfFameData['data'];

}

async function drawHallOfFame(){

const jsonData = await getHallOfFameData();

const container = document.getElementById('hallOfFame-container');

jsonData.forEach(item => {

const metricType = item[0];

const card = document.createElement('div');

card.className = 'metric-card';

const createDetail = (label, value, isCallsign = false) => {

if (value !== null && value !== undefined) {

const p = document.createElement('h2');

p.className = 'metric-detail';

if (isCallsign) {

const callsignLink = document.createElement('a');

callsignLink.href = `https://web7.radarbox.com/data/flights/${value}`;

callsignLink.target = "_blank"; // Opens in a new tab

callsignLink.textContent = value;

p.innerHTML = `${label}: <span></span>`; // Create a span to append the link

p.querySelector('span').appendChild(callsignLink);

} else {

p.innerHTML = `${label}: <span>${value}</span>`;

}

card.appendChild(p);

}

};

const typeHeader = document.createElement('h4');

typeHeader.className = 'metric-type';

if (metricType === 'Highest Noise Impact') {

typeHeader.textContent = "Noise Impact";

card.appendChild(typeHeader);

createDetail('Call Sign', item[2], true);

createDetail('Noise Level', item[11]);

} else if (metricType === 'Highest Speed') {

typeHeader.textContent = "Speed";

card.appendChild(typeHeader);

createDetail('Call Sign', item[2], true);

createDetail('Speed', item[9] ? `${item[9]} km/h` : null);

} else if (metricType === 'Lowest Combined Distance') {

typeHeader.textContent = "Distance";

card.appendChild(typeHeader);

createDetail('Call Sign', item[2], true);

createDetail('Distance', item[8] ? `${item[8]} km` : null);

createDetail('Altitude', item[6] ? `${item[6]} m` : null);

} else if (metricType === 'Highest Callsign Count') {

typeHeader.textContent = "Passovers";

card.appendChild(typeHeader);

createDetail('Call Sign', item[2], true);

createDetail('Flight Count', item[12]);

}

container.appendChild(card);

});

};

document.addEventListener('DOMContentLoaded', function() {

drawFlightsPerDay();

drawFlightsPerHour();

drawHeatMap();

drawNoiseLevels();

drawHallOfFame();

});

Note: In the code above, I have replaced my specific IP address and GPS coordinates with XX. To make this work for your own setup, simply replace:

192.168.1.XX with the IP address of your Raspberry Pi.
XX, XX with your own latitude and longitude coordinates.

Notes & Considerations

Blindspots

We have a limit of 4.000 calls per day when using the OpenSky API free tier, this means that our radar can only check every thirty seconds. Meaning that planes passing by during that period will be unaccounted for and so our current Planedar setup will miss quite a few flights.

We also check ten records back to make sure we don't save duplicates, however, this means we also don't pick up planes flying over multiple times within that 30 * 10 = 300 seconds timeframe.

Lastly, the planes won't be registered at their loudest, so we can assume these ratings are lower then the true impact.

AI Supported

Thanks to Gemini for image, moral, code and proofreading support.