FlightAware Discussions

Dump1090 MySQL Database Script - E-Mail-Alert Lat/Lon/Alt/Hex/Flight-Filter

hmmm - i’ll try to help if you would explain a little more what you did, what settings you made in the script, what database you use etc … the inserted message is a bit unprecise as i was too lazy to fetch the database-request-result and just used the number of rows that match the filter because the script output was ment only for my debugging while coding :slight_smile: so - in other words that just tells you how many entries to database would have been done. as you don’t have any data found in your db something with the db or db connection seems to be wrong. e.g. if you use mysql - did you set field ‘id’ to auto-increment?

Doh! Yes, it was the ID column, once I set it to auto_increment, away it went! Cool, thanks.

perfect! welcome :slight_smile:

go to latest version here: Dump1090 MySQL Database Script - E-Mail-Alert Lat/Lon/Alt/Hex/Flight-Filter

  • the alert email now contains a link with alert position to google maps
  • added a minimal e-mail-script to submit alert message
  • terminal output now says ‘xxx actual datasets match filter’ instead ‘xxx actual datasets inserted’
  • added more precise timing for aircraft.json load
  • name the files radar.php and mailer.php

some short notes to the code:

  • my intention was to send an html-email with embedded map but first i noticed google today asks for (free) registering for an api-key. no sorry i don’t like google and their behavior - i had a solution with openstreetmaps that worked perfect. but at the end i found that nearly no email-client today allows iframes. so finally it’s just a link.

  • the data is submitted via ‘get’ and not ‘post’ as post takes too much time and would slowdown the script the moment an alert is triggered.

  • mailer.php: the ‘key’ is just a minimum security against funster abusing your mailer.php to send you hundrets of mails. but real abuse isn’t easy possible anyways (besides always possible code-injection) as the email-to-adress is hardcoded.

  • here ‘$sent_alert_messages < 1000’ in radar.php i did set a max number of alerts that will be sent out. you can set it to a billion or just 1 - this is just an ‘airbag’ preventing your email-inbox from exploding if something with your settings 'alert rectangle/height was tooooo wide :slight_smile:

  • here ‘$outdated_entry = time() - 900;’ in radar.php you maybe have to tweak the’900’. that’s the time in seconds the script ignores an aicraft when an alert-message already was sent out to avoid sending alerts over and over for the same aircraft. 900 is a good starting point - but it depends on how wide and high you set the alert area and how fast and which direction the aircraft moves through the alert-area.

  • for what it is worth - added more precise timing that tries to keep refresh-rate for loading a new aircraft.json exactly every second. as the script grew and processing-time grew the static 1 second loop was too much while sending alert-request or adding large numbers of data to database. now the script measures already used time for the actual loop and adds only the amount of sleep to come as close as possible to 1 second in total. in (at least my) theory this should help to miss the minimum number of aircraft.json files and still work without moire effect like side-effects. anyways - biggest advantage is always when the script is under pressure time-wise e.g. other demanding processes on the machine, large writes to database and especially while sending alert-messages. now with the ‘adaptive-sleep-timer’ it does not loose unwanted additional fixed time with sleeping while a new aircraft.json is already prepared.

btw. if someone has problems to post code here i found a workaround as the built-in tool most often does not work and splits the code in code and no code parts. open your code in your standard text-editor (e.g. bbedit or ultraedit) -> highlight the whole code -> then ident 4 spaces to right -> copy the now already idented code -> and insert in forum editor -> save edit

radar.php

<?php

#phpinfo();
#var_dump(ini_get_all());
#ini_set('error_reporting', E_ALL);

// below a sample create statement for database table - mysql set id-fild to auto-increment
// CREATE TABLE aircrafts (id INTEGER PRIMARY KEY, date TEXT, now TEXT, hex TEXT, flight TEXT, altitude TEXT, lat TEXT, lon TEXT, track TEXT, speed TEXT, vert_rate TEXT, seen_pos TEXT, seen TEXT, rssi TEXT, messages TEXT, category TEXT, squawk TEXT, nucp TEXT, mlat TEXT, tisb TEXT)

$max_lat = 50.000000; $min_lat = 47.000000; $max_lon = 13.000000; $min_lon = 10.000000; $max_alt = 10000; // set the rectangle and altitude to store aircraft-data in database
$alert_max_lat = 49.000000; $alert_min_lat = 49.000000; $alert_max_lon = 12.000000; $alert_min_lon = 12.000000; $alert_max_alt = 1000; // set the rectangle and altitude to send alert message
$db_name = "adsb.db"; $db_host = "127.0.0.1"; $db_user = "root"; $db_pass = "password"; // set parameters database connection
$url = "http://127.0.0.1/dump1090/data/aircraft.json"; // set path to aircraft.json file
$secret_email_key = "YOUR_SECRET_KEY"; // set a key (letters/numbers only) according to mailer.php
date_default_timezone_set("Europe/Berlin"); // set your timezone

$i = 0;
$alert_trigger_array = array("", "");
$alert_message = "";
$sent_alert_messages = 0;
$start_time = time();
while (true) {

	$x = 0;
	$sql = "";
	$start_loop_microtime = microtime(true);
	$json_data_array = json_decode(file_get_contents($url),true);

		// loop through aircraft.json file
		foreach ($json_data_array['aircraft'] as $row) {
			isset($json_data_array['now']) ? $ac_now = $json_data_array['now'] : $ac_now = "";
			isset($row['hex']) ? $ac_hex = $row['hex'] : $ac_hex = "";
			isset($row['flight']) ? $ac_flight = trim($row['flight']) : $ac_flight = "";
			isset($row['altitude']) ? $ac_altitude = $row['altitude'] : $ac_altitude = "";
			isset($row['lat']) ? $ac_lat = $row['lat'] : $ac_lat = "";
			isset($row['lon']) ? $ac_lon = $row['lon'] : $ac_lon = "";
			isset($row['track']) ? $ac_track = $row['track'] : $ac_track = "";
			isset($row['speed']) ? $ac_speed = $row['speed'] : $ac_speed = "";
			isset($row['vert_rate']) ? $ac_vert_rate = $row['vert_rate'] : $ac_vert_rate = "";
			isset($row['seen_pos']) ? $ac_seen_pos = $row['seen_pos'] : $ac_seen_pos = "";
			isset($row['seen']) ? $ac_seen = $row['seen'] : $ac_seen = "";
			isset($row['rssi']) ? $ac_rssi = $row['rssi'] : $ac_rssi = "";
			isset($row['messages']) ? $ac_messages = $row['messages'] : $ac_messages = "";
			isset($row['category']) ? $ac_category = $row['category'] : $ac_category = "";
			isset($row['squawk']) ? $ac_squawk = $row['squawk'] : $ac_squawk = "";
			isset($row['nucp']) ? $ac_nucp = $row['nucp'] : $ac_nucp = "";
			isset($row['mlat']) ? $ac_mlat = implode(" ", $row['mlat']) : $ac_mlat = "";
			isset($row['tisb']) ? $ac_tisb = implode(" ", $row['tisb']) : $ac_tisb = "";

			// generate sql insert statement per aircraft in aircraft.json
			if ($ac_altitude < $max_alt and $ac_lat < $max_lat and $ac_lat > $min_lat and $ac_lon < $max_lon and $ac_lon > $min_lon) {
				$sql .= "INSERT INTO aircrafts VALUES (NULL, '" . date("Y-m-d G:i:s l", $ac_now) . "', '$ac_now', '$ac_hex', '$ac_flight', ";
				$sql .= "'$ac_altitude', '$ac_lat', '$ac_lon', '$ac_track', '$ac_speed', '$ac_vert_rate', '$ac_seen_pos', '$ac_seen', ";
				$sql .=  "'$ac_rssi', '$ac_messages', '$ac_category', '$ac_squawk', '$ac_nucp', '$ac_mlat', '$ac_tisb');\n";
				$x++;
			}

			// set and modify alert trigger array
			if ($ac_altitude < $alert_max_alt and $ac_lat < $alert_max_lat and $ac_lat > $alert_min_lat and $ac_lon < $alert_max_lon and $ac_lon > $alert_min_lon) {
				if (!array_key_exists($ac_hex, $alert_trigger_array)) {
					$alert_message_subject = urlencode("### STRAFER-ALERT ### " . $ac_flight  . " " . $ac_hex . " : " . $ac_lat . " " . $ac_lon . " : " . $ac_altitude . "ft @ " . date("Y-m-d G:i:s l", $ac_now));
					$alert_message_body = urlencode($ac_flight  . " " . $ac_hex . " : <a href='http://www.google.com/maps/place/" . $ac_lat . "," . $ac_lon . "/@" . $ac_lat . "," . $ac_lon . ",12z'>" . $ac_lat . " " . $ac_lon . "</a> : " . $ac_altitude . "ft @ " . date("Y-m-d G:i:s l", $ac_now));
					$alert_message = "key=" . $secret_email_key . "&subject=" . $alert_message_subject . "&body=" . $alert_message_body;
				}
				if ($ac_hex) {
					$alert_trigger_array["$ac_hex"] = time();
					#var_dump($alert_trigger_array); // show array for debug
				}
			}

			// delete aircraft after 15min from already-message-sent-trigger
			$outdated_entry = time() - 900;
			foreach ($alert_trigger_array as $key => $value) {
				if ($value < $outdated_entry) {
					unset($alert_trigger_array[$key]);
				}
			}

			// send alert message, set limit for maximum number of messages and reset message content
			if ($alert_message != "" and $sent_alert_messages < 1000) {
				$sent_alert_messages = $sent_alert_messages + 1;

				// set your alert-action here e.g. get-request to your email-script or message service e.g. https://pushover.net
				file_get_contents("http://127.0.0.1/mailer.php?$alert_message");

				$alert_message = "";
			}
		}

		// write to database and display actual state
		$db = new PDO("mysql:host=$db_host;dbname=$db_name", "$db_user", "$db_pass");
		#$db = new SQLite3("$db_name"); // uncomment to use sqlite and comment-out the mysql connection above
		if ($sql) { $db->exec($sql); }
		$db = NULL;
		$runtime = sprintf("%d days %02d:%02d:%02d", (time() - $start_time)/60/60/24,((time() - $start_time)/60/60)%24,((time() - $start_time)/60)%60,(time() - $start_time)%60);
		print("since " . $runtime . " - runs completed " . $i . " -> " . $x . " actual dataset(s) match filter\n");

	unset($sql);
	// compute time since last aircraft.json was fetched and set sleep timer to get minimum a full second until next aircraft.json is ready to get fetched
	$sleep_microtime = (1000000 - (round(1000000 * (microtime(true) - $start_loop_microtime))));
	// if your system does not support microtime comment out above line and uncomment below line
	#sleep(1);
	if ($sleep_microtime > 0) { usleep($sleep_microtime); }
	$i++;
}

?>

mailer.php

<?php

#phpinfo();
#var_dump(ini_get_all());
#ini_set('error_reporting', E_ALL);

isset($_GET["key"]) ? $key = $_GET["key"] : $key = "";
isset($_GET["subject"]) ? $subject = urldecode($_GET["subject"]) : $subject = "";
isset($_GET["body"]) ? $body = urldecode($_GET["body"]) : $body = "";

if ($key == "YOUR_SECRET_KEY") { // set key (letters/numbers only) according to the key you set in radar.php
	$header  = "MIME-Version: 1.0\r\n";
	$header .= "Content-type: text/html; charset=iso-8859-1\r\n";
	$header .= "From: ADS-B RADAR\r\n";
	$header .= "Reply-To: YOUR_EMAIL@EMAIL.COM\r\n"; // set your email
	$header .= "X-Mailer: PHP ". phpversion();
	mail("YOUR_EMAIL@EMAIL.COM", $subject, $body, $header); // set your email
}

?>
1 Like

here is the result running old version (db + terminal window left side) vs new version (db + terminal window right side) - it shows after running for one hour about 5% higher rate of fetched aircraft.json files and comes fairly close to 100%:

edit: all theory is grey :slight_smile: what looked like a success first - in reality were often duplicates in the database and a script that heavily started to swing around 1hz like a badly constructed bridge in the storm. so this was wasted time but on the other hand i learned a little how hard it can be just to tame 1hz and how hard it may be to tame the internal dump1090 code at muuuch higher frequency …

edit2: finally found out by setting up multiple micro-second-timers in different stages of my script what the reason for these delays and all the swinging force and back was. i created the code and made all test runs on my notebook - and my notebook clearly uses wifi not ethernet. so - when the script runs and tries to get about every second a new aircraft.json from my raspberry where dump1090 runs - it always takes 5 times long than over a ethernet connection (50milliseconds vs 10milliseconds). just this difference is 10-20times more than the whole other script-processing takes. moreover sometimes over wifi it takes about 1000 miliseconds or longer (aka 1 second) to get the new aircraft.json. that is 100 times longer than via ethernet!!! when my script above tried to get the script with ‘adaptive timing’ it aggressively tried over and over to get the fresh file and by doing so the wifi-connection reacted somewhat pissed and it just took more and more time. that’s why my script started shaking like crazy :grinning:

edit3: that’s why i switched back to the fixed sleep timer. the biggest problem is not the processing time of the script but to get the fresh aircraft.json via network. and here the more aggressive polling is simply contra-productive.

go to latest version here: Dump1090 MySQL Database Script - E-Mail-Alert Lat/Lon/Alt/Hex/Flight-Filter

go to latest version here: Dump1090 MySQL Database Script - E-Mail-Alert Lat/Lon/Alt/Hex/Flight-Filter

  • switched back to fixed sleep time as precision of write-timing aircraft.json is not determinable in millisecond range (at least for me)
  • added basic database insert feedback
  • now all user settings at top of script
  • code clean-up
  • more comments

notice: i wrote the script because my main interest is in the aircrafts flying in my neighborhood which are mostly small aircrafts and some military. that’s why i’ve chosen the aircraft.json to be the best data-source for my needs. it holds lots of very precise data combined from received messages in human readable format and is updated at a high frequency (every second). moreover the aircraft.json contains the mlat-results flightaware computed partly with the data from the site. so please do not abuse my script to generate and publish the flightaware-mlat-data anywhere - this data is only meant for your personal use. you find additional information here: FlightAware MLAT Network Announcement

radar.php

<?php

#phpinfo();
#var_dump(ini_get_all());
#ini_set('error_reporting', E_ALL);

// below a sample create statement for database table - mysql set id-field to auto-increment
// CREATE TABLE aircrafts (id INTEGER PRIMARY KEY, date TEXT, now TEXT, hex TEXT, flight TEXT, altitude TEXT, lat TEXT, lon TEXT, track TEXT, speed TEXT, vert_rate TEXT, seen_pos TEXT, seen TEXT, rssi TEXT, messages TEXT, category TEXT, squawk TEXT, nucp TEXT, mlat TEXT, tisb TEXT)

// user settings only the 9 lines below - see comments at line-ends
$max_lat = 50.000000; $min_lat = 47.000000; $max_lon = 12.000000; $min_lon = 9.000000; $max_alt = 4000; // set the rectangle and altitude to store aircraft-data in database
$alert_max_lat = 49.000000; $alert_min_lat = 48.000000; $alert_max_lon = 11.000000; $alert_min_lon = 10.000000; $alert_max_alt = 2000; // set the rectangle and altitude to send alert message
$db_name = "adsb.db"; $db_host = "127.0.0.1"; $db_user = "USERNAME"; $db_pass = "PASSWORD"; // set parameters database connection
$url_json = "http://127.0.0.1/dump1090/data/aircraft.json"; // set path to aircraft.json file
$url_mailer = "http://127.0.0.1/mailer.php"; // set path to your mailer.php file
$secret_email_key = "KEY"; // set a key (letters/numbers only) according to mailer.php
$mailer_limit = 1000; // set the absolute limit of alert-messages this script is allowed to send over its whole runtime
$aircraft_suspend_time = 900; // default is 900 - change only if needed - time in seconds an aicraft is suspended from alert-messages after sending an alert-message for this aircraft
date_default_timezone_set("Europe/Berlin"); // set your timezone

$i = 0;
$alert_trigger_array = array("", "");
$alert_message = "";
$sent_alert_messages = 0;
$start_time = time();

while (true) {

	$x = 0;
	$sql = "";
	$start_loop_microtime = microtime(true);
	$json_data_array = json_decode(file_get_contents($url_json),true);

	// loop through aircraft.json file
	foreach ($json_data_array['aircraft'] as $row) {
		isset($json_data_array['now']) ? $ac_now = $json_data_array['now'] : $ac_now = "";
		isset($row['hex']) ? $ac_hex = $row['hex'] : $ac_hex = "";
		isset($row['flight']) ? $ac_flight = trim($row['flight']) : $ac_flight = "";
		isset($row['altitude']) ? $ac_altitude = $row['altitude'] : $ac_altitude = "";
		isset($row['lat']) ? $ac_lat = $row['lat'] : $ac_lat = "";
		isset($row['lon']) ? $ac_lon = $row['lon'] : $ac_lon = "";
		isset($row['track']) ? $ac_track = $row['track'] : $ac_track = "";
		isset($row['speed']) ? $ac_speed = $row['speed'] : $ac_speed = "";
		isset($row['vert_rate']) ? $ac_vert_rate = $row['vert_rate'] : $ac_vert_rate = "";
		isset($row['seen_pos']) ? $ac_seen_pos = $row['seen_pos'] : $ac_seen_pos = "";
		isset($row['seen']) ? $ac_seen = $row['seen'] : $ac_seen = "";
		isset($row['rssi']) ? $ac_rssi = $row['rssi'] : $ac_rssi = "";
		isset($row['messages']) ? $ac_messages = $row['messages'] : $ac_messages = "";
		isset($row['category']) ? $ac_category = $row['category'] : $ac_category = "";
		isset($row['squawk']) ? $ac_squawk = $row['squawk'] : $ac_squawk = "";
		isset($row['nucp']) ? $ac_nucp = $row['nucp'] : $ac_nucp = "";
		isset($row['mlat']) ? $ac_mlat = implode(" ", $row['mlat']) : $ac_mlat = "";
		isset($row['tisb']) ? $ac_tisb = implode(" ", $row['tisb']) : $ac_tisb = "";

		// generate sql insert statement per aircraft in aircraft.json
		if ($ac_altitude < $max_alt and $ac_lat < $max_lat and $ac_lat > $min_lat and $ac_lon < $max_lon and $ac_lon > $min_lon) {
			$sql .= "INSERT INTO aircrafts VALUES (NULL, '" . date("Y-m-d G:i:s l", $ac_now) . "', '$ac_now', '$ac_hex', '$ac_flight', ";
			$sql .= "'$ac_altitude', '$ac_lat', '$ac_lon', '$ac_track', '$ac_speed', '$ac_vert_rate', '$ac_seen_pos', '$ac_seen', ";
			$sql .= "'$ac_rssi', '$ac_messages', '$ac_category', '$ac_squawk', '$ac_nucp', '$ac_mlat', '$ac_tisb');\n";
			$x++;
		}

		// set and modify alert-trigger-array and build alert-message
		if ($ac_altitude < $alert_max_alt and $ac_lat < $alert_max_lat and $ac_lat > $alert_min_lat and $ac_lon < $alert_max_lon and $ac_lon > $alert_min_lon) {
			if (!array_key_exists($ac_hex, $alert_trigger_array)) {
				$alert_message_subject = urlencode("### STRAFER-ALERT ### " . $ac_flight  . " " . $ac_hex . " : " . $ac_lat . " " . $ac_lon . " : " . $ac_altitude . "ft @ " . date("Y-m-d G:i:s l", $ac_now));
				$alert_message_body = urlencode($ac_flight  . " " . $ac_hex . " : <a href='http://www.google.com/maps/place/" . $ac_lat . "," . $ac_lon . "/@" . $ac_lat . "," . $ac_lon . ",12z'>" . $ac_lat . " " . $ac_lon . "</a> : " . $ac_altitude . "ft @ " . date("Y-m-d G:i:s l", $ac_now));
				$alert_message = "key=" . $secret_email_key . "&subject=" . $alert_message_subject . "&body=" . $alert_message_body;
			}
			if ($ac_hex) {
				$alert_trigger_array["$ac_hex"] = time();
				#var_dump($alert_trigger_array); // show array for debug
			}
		}

		// delete aircraft after user set seconds from already-message-sent-trigger
		$outdated_entry = time() - $aircraft_suspend_time;
		foreach ($alert_trigger_array as $key => $value) {
			if ($value < $outdated_entry) {
				unset($alert_trigger_array[$key]);
			}
		}

		// send alert-message, set absolute limit for maximum number of messages and reset alert-message
		if ($alert_message != "" and $sent_alert_messages < $mailer_limit) {
			file_get_contents($url_mailer . "?" . $alert_message);
			$sent_alert_messages++;
			$alert_message = "";
		}

	}

// write to database
$db = new PDO("mysql:host=$db_host;dbname=$db_name", "$db_user", "$db_pass"); $db_insert = "";
if ($sql) { $db->exec($sql); $db->lastInsertId() ? $db_insert = "inserted" : $db_insert = "db-error"; }
$db = null;

// generate terminal output and set sleep timer to get minimum a full second until next aircraft.json is ready to get fetched
$runtime = (time() - $start_time);
$runtime_formatted = sprintf("%d days %02d:%02d:%02d", $runtime/60/60/24,($runtime/60/60)%24,($runtime/60)%60,$runtime%60);
($runtime > 0) ? $loop_clock = number_format(round(($i / $runtime),12),12) : $loop_clock = number_format(1, 12);
$process_microtime = (round(1000000 * (microtime(true) - $start_loop_microtime)));
print("process-time(us): " . sprintf("%07d", $process_microtime) . " - " . $loop_clock . " loops/s avg - since " . $runtime_formatted . " - run(s) " . $i . " -> " . sprintf("%03d", $x) . " dataset(s) => " . $db_insert . "\n");
usleep(1000000);
$i++;

}

?>

mailer.php

<?php

#phpinfo();
#var_dump(ini_get_all());
#ini_set('error_reporting', E_ALL);

isset($_GET["key"]) ? $key = $_GET["key"] : $key = "";
isset($_GET["subject"]) ? $subject = urldecode($_GET["subject"]) : $subject = "";
isset($_GET["body"]) ? $body = urldecode($_GET["body"]) : $body = "";

if ($key == "YOUR_SECRET_KEY") { // set key (letters/numbers only) according to the key you set in radar.php
	$header  = "MIME-Version: 1.0\r\n";
	$header .= "Content-type: text/html; charset=iso-8859-1\r\n";
	$header .= "From: ADS-B RADAR\r\n";
	$header .= "Reply-To: YOUR_EMAIL@EMAIL.COM\r\n"; // set your email
	$header .= "X-Mailer: PHP ". phpversion();
	mail("YOUR_EMAIL@EMAIL.COM", $subject, $body, $header); // set your email
}

?>

@TomMuc

I’ve taken your original script and commented out the alert content because in it’s current form of lat, lon and altitude it doesn’t meet my needs for alerting. So I’m databasing the json data and adjusted the script to run every 10 seconds as I think if my ‘radar’ sees an aircraft it will appear in the json file for at least that amount of time and saves writing duplicate entries to my sql server. This all appears to be working very well, I started the script three days ago and it hasn’t yet faltered using my FA formatted Rpi3.

My next inclination is to see if the alerting code can be adjusted to alert on hex and/or flight. This is where the power of an email alerter come in for me. I’m very keen to be alerted when specific aircraft or flights are received by my ‘radar’.

I think your script holds much promise of fulfilling this requirement for me but as someone who doesn’t ‘get’ computer code very well it’s very much a ‘try and see it’ approach unless the application is specifically created for my purpose which is seldom!

fine that all is runing stable :slight_smile: anyways i would recommend to use the latest version of the script. no big deal to use the alert with hex instead geographic position. in line 64 (actual version) you have to alter this

go to latest version here: Dump1090 MySQL Database Script - E-Mail-Alert Lat/Lon/Alt/Hex/Flight-Filter

if ($ac_altitude < $alert_max_alt and $ac_lat < $alert_max_lat and $ac_lat > $alert_min_lat and $ac_lon < $alert_max_lon and $ac_lon > $alert_min_lon) {

to something like this

if (in_array($ac_hex, $alert_hex_array)) {

and e.g. line 20 you make a new line like this:

$alert_hex_array = array("hex1", "hex2", "hex3");

do you run the script directly on a raspberry? if so you should use an external hdd as the sdcards aren’t too happy about so much data written. btw. the same way like above you can easily setup the database-writing to only special hex aircrafts when they come in range …

edit: if the number of aircrafts you want to observe is large or often changes you could simply write it in a comma separated text file which is placed anywhere in your network. then radar.php could read this file on startup or in a given interval and automatically load this into the $alert_hex_array

Hi Tom, hehe, yes I’ve had that problem with MySQL and SD Cards! At the moment I run the script on the rpi but dump the data to my Ubuntu laptop on the ten second cycle. Given the consistency of the script running I will move it to the laptop once I have my archiving working nicely so that when I query current json data it not having to trawl through days worth of data. Being able to extract a particular flights track data to include signal strength enables me to see blind spots etc. in my hardware setup, just one of the uses of my data! I’m just a geek!!

Great work Tom and I’ll have a play with the script to see what alerts I can get it to generate so thanks for the tips.

… and the new script-version now complains e.g. if you forget to set id to autoincrement :smiley:

This program will send an email alert based upon call sign or ICAO. It does not create a database though, so I may not meet your needs.
https://www.dropbox.com/s/2awljgkict5jjas/flights.py?dl=0

go to latest version here: Dump1090 MySQL Database Script - E-Mail-Alert Lat/Lon/Alt/Hex/Flight-Filter

as some of you want to track only particular aircrafts - here is the new version where you can set additionally hex for aicrafts to write to database and/or alerts. place a plain text-file in the same directory where radar.php is placed. the text file should contain one line with your hex codes comma-separated. in script settings line19/20/21 just set $hex_mode_alert and/or $hex_mode_database to true and set path to your hex_code_array.txt.

had no time for real testing - but should work …

edit 2017_09_05 18:39 UTC: there was a minor bug - a missing blank in hex array filter - preventing to match the given hex-values. in addition i reformatted the user-settings for more clarity and altered the mysql-table-create sample. now basic gmail support for alert-emails is built-in. if you run the script on the raspberry pi and want to use gmail instead of the mailer.php (that you just would have to upload to your webspace/webserver/isp) you need first an email-server running e.g. sendmail.

sudo apt-get install sendmail-bin

over the last days i did some tests. i run the radar.php on my mac-mini server and it connects every second to my raspi that runs dump1090. this runs smooth and without any issus since over a week now. even if i delete the hex_code_array.txt file the script does not stop but just compains a little. as soon as it finds the file again it stops complaining an automatically resumes to its task. same happens if i shut down the raspberry where dump1090 is running - radar.php starts heavy complaining - but as soon as the raspi and dump1090 runs again radar.php automatically resumes to its task.

radar.php

<?php

#phpinfo();
#var_dump(ini_get_all());
#ini_set('error_reporting', E_ALL);

// below a sample create statement for database table
// CREATE TABLE aircrafts (id INT NOT NULL AUTO_INCREMENT, date VARCHAR(100), now VARCHAR(100), hex VARCHAR(100), flight VARCHAR(100), altitude VARCHAR(100), lat VARCHAR(100), lon VARCHAR(100), track VARCHAR(100), speed VARCHAR(100), vert_rate VARCHAR(100), seen_pos VARCHAR(100), seen VARCHAR(100), rssi VARCHAR(100), messages VARCHAR(100), category VARCHAR(100), squawk VARCHAR(100), nucp VARCHAR(100), mlat VARCHAR(100), tisb VARCHAR(100), PRIMARY KEY (id))

// set the rectangle and altitude to store aircraft-data in database
$user_set_array['max_lat'] = 50.000000;    $user_set_array['min_lat'] = 46.000000;    $user_set_array['max_alt'] = 10000;
$user_set_array['max_lon'] = 14.000000;    $user_set_array['min_lon'] = 10.000000;

// set the rectangle and altitude to send alert message
$user_set_array['alert_max_lat'] = 49.000000;    $user_set_array['alert_min_lat'] = 47.000000;    $user_set_array['alert_max_alt'] = 5000;
$user_set_array['alert_max_lon'] = 13.000000;    $user_set_array['alert_min_lon'] = 11.000000;

// set to true and your google email-address if for alert-messages you want to use gmail instead own mailer.php file
$user_set_array['gmail'] = false; $user_set_array['email_address'] = 'YOUR_EMAIL@gmail.com';

// set parameters for database connection
$user_set_array['db_name'] = 'adsb.db'; $user_set_array['db_host'] = '127.0.0.1'; $user_set_array['db_user'] = 'USERNAME'; $user_set_array['db_pass'] = 'PASSWORD';

// set path to aircraft.json file
$user_set_array['url_json'] = 'http://127.0.0.1/dump1090/data/aircraft.json';

// set path to your mailer.php file
$user_set_array['url_mailer'] = 'http://127.0.0.1/mailer.php';

// set a key (letters/numbers only) according to mailer.php
$user_set_array['secret_email_key'] = 'YOUR_USER_KEY';

// set the absolute limit of alert-messages (default is 1000) this script is allowed to send over its whole runtime
$user_set_array['mailer_limit'] = 1000;

// set aircraft suspend time (default is 900) - change only if needed - time in seconds an aicraft is suspended from alert-messages after sending an alert-message for this aircraft
$user_set_array['aircraft_suspend_time'] = 900;

// set this to true if you want only alerts and/or database writes from those aircrafts matching your hex_code_array.txt file
$user_set_array['hex_mode_alert'] = false;    $user_set_array['hex_mode_database'] = false;

// set path to your hex_code_array.txt file
$user_set_array['hex_file_path'] = 'hex_code_array.txt';

// set your timezone see http://php.net/manual/en/timezones.php
$user_set_array['time_zone'] = 'America/Chicago';



$i = 0;
$hex_code_array = array();
$alert_trigger_array = array();
$alert_message = '';
$sent_alert_messages = 0;
$start_time = time();
date_default_timezone_set($user_set_array['time_zone']);

while (true) {

	$x = 0;
	$sql = '';
	$start_loop_microtime = microtime(true);
	$json_data_array = json_decode(file_get_contents($user_set_array['url_json']),true);
	if ($user_set_array['hex_mode_alert'] or $user_set_array['hex_mode_database']) { $hex_code_array = explode(', ', file_get_contents($user_set_array['hex_file_path'])); }

	// loop through aircraft.json file
	foreach ($json_data_array['aircraft'] as $row) {
		isset($json_data_array['now']) ? $ac_now = $json_data_array['now'] : $ac_now = '';
		isset($row['hex']) ? $ac_hex = $row['hex'] : $ac_hex = '';
		isset($row['flight']) ? $ac_flight = trim($row['flight']) : $ac_flight = '';
		isset($row['altitude']) ? $ac_altitude = $row['altitude'] : $ac_altitude = '';
		isset($row['lat']) ? $ac_lat = $row['lat'] : $ac_lat = '';
		isset($row['lon']) ? $ac_lon = $row['lon'] : $ac_lon = '';
		isset($row['track']) ? $ac_track = $row['track'] : $ac_track = '';
		isset($row['speed']) ? $ac_speed = $row['speed'] : $ac_speed = '';
		isset($row['vert_rate']) ? $ac_vert_rate = $row['vert_rate'] : $ac_vert_rate = '';
		isset($row['seen_pos']) ? $ac_seen_pos = $row['seen_pos'] : $ac_seen_pos = '';
		isset($row['seen']) ? $ac_seen = $row['seen'] : $ac_seen = '';
		isset($row['rssi']) ? $ac_rssi = $row['rssi'] : $ac_rssi = '';
		isset($row['messages']) ? $ac_messages = $row['messages'] : $ac_messages = '';
		isset($row['category']) ? $ac_category = $row['category'] : $ac_category = '';
		isset($row['squawk']) ? $ac_squawk = $row['squawk'] : $ac_squawk = '';
		isset($row['nucp']) ? $ac_nucp = $row['nucp'] : $ac_nucp = '';
		isset($row['mlat']) ? $ac_mlat = implode(' ', $row['mlat']) : $ac_mlat = '';
		isset($row['tisb']) ? $ac_tisb = implode(' ', $row['tisb']) : $ac_tisb = '';

		// generate sql insert statement per aircraft in range of user set altitude/latitude/longitude and optionally according only to hex numbers in hex_code_array.txt
		#var_dump($hex_code_array); // show array for debug
		if ($user_set_array['hex_mode_database']) {
			if ($ac_altitude < $user_set_array['max_alt'] and $ac_lat < $user_set_array['max_lat'] and $ac_lat > $user_set_array['min_lat'] and $ac_lon < $user_set_array['max_lon'] and $ac_lon > $user_set_array['min_lon'] and in_array($ac_hex, $hex_code_array)) {
				$sql .= "INSERT INTO aircrafts VALUES (NULL, '" . date("Y-m-d G:i:s l", $ac_now) . "', '$ac_now', '$ac_hex', '$ac_flight', ";
				$sql .= "'$ac_altitude', '$ac_lat', '$ac_lon', '$ac_track', '$ac_speed', '$ac_vert_rate', '$ac_seen_pos', '$ac_seen', ";
				$sql .= "'$ac_rssi', '$ac_messages', '$ac_category', '$ac_squawk', '$ac_nucp', '$ac_mlat', '$ac_tisb');";
				$sql .= PHP_EOL;
				$x++;
			}
		} else {
			if ($ac_altitude < $user_set_array['max_alt'] and $ac_lat < $user_set_array['max_lat'] and $ac_lat > $user_set_array['min_lat'] and $ac_lon < $user_set_array['max_lon'] and $ac_lon > $user_set_array['min_lon']) {
				$sql .= "INSERT INTO aircrafts VALUES (NULL, '" . date("Y-m-d G:i:s l", $ac_now) . "', '$ac_now', '$ac_hex', '$ac_flight', ";
				$sql .= "'$ac_altitude', '$ac_lat', '$ac_lon', '$ac_track', '$ac_speed', '$ac_vert_rate', '$ac_seen_pos', '$ac_seen', ";
				$sql .= "'$ac_rssi', '$ac_messages', '$ac_category', '$ac_squawk', '$ac_nucp', '$ac_mlat', '$ac_tisb');";
				$sql .= PHP_EOL;
				$x++;
			}
		}

		// set and modify alert-trigger-array and build alert-message optionally according only to hex numbers in hex_code_array.txt
		if ($user_set_array['hex_mode_alert']) {
			if ($ac_altitude < $user_set_array['alert_max_alt'] and $ac_lat < $user_set_array['alert_max_lat'] and $ac_lat > $user_set_array['alert_min_lat'] and $ac_lon < $user_set_array['alert_max_lon'] and $ac_lon > $user_set_array['alert_min_lon'] and in_array($ac_hex, $hex_code_array)) {
				if (!array_key_exists($ac_hex, $alert_trigger_array)) {
					$alert_message_subject = urlencode('### STRAFER-ALERT ### ' . $ac_flight  . ' ' . $ac_hex . ' : ' . $ac_lat . ' ' . $ac_lon . ' : ' . $ac_altitude . 'ft @ ' . date('Y-m-d G:i:s l', $ac_now));
					$alert_message_body = urlencode($ac_flight  . ' ' . $ac_hex . ' : <a href="http://www.google.com/maps/place/' . $ac_lat . ',' . $ac_lon . '/@' . $ac_lat . ',' . $ac_lon . ',12z">' . $ac_lat . ' ' . $ac_lon . '</a> : ' . $ac_altitude . 'ft @ ' . date('Y-m-d G:i:s l', $ac_now));
					$alert_message = 'key=' . $user_set_array['secret_email_key'] . '&subject=' . $alert_message_subject . '&body=' . $alert_message_body;
				}
				if ($ac_hex) {
					$alert_trigger_array[$ac_hex] = time();
					#var_dump($alert_trigger_array); // show array for debug
				}
			}
		} else {
			if ($ac_altitude < $user_set_array['alert_max_alt'] and $ac_lat < $user_set_array['alert_max_lat'] and $ac_lat > $user_set_array['alert_min_lat'] and $ac_lon < $user_set_array['alert_max_lon'] and $ac_lon > $user_set_array['alert_min_lon']) {
				if (!array_key_exists($ac_hex, $alert_trigger_array)) {
					$alert_message_subject = urlencode('### STRAFER-ALERT ### ' . $ac_flight  . ' ' . $ac_hex . ' : ' . $ac_lat . ' ' . $ac_lon . ' : ' . $ac_altitude . 'ft @ ' . date('Y-m-d G:i:s l', $ac_now));
					$alert_message_body = urlencode($ac_flight  . ' ' . $ac_hex . ' : <a href="http://www.google.com/maps/place/' . $ac_lat . ',' . $ac_lon . '/@' . $ac_lat . ',' . $ac_lon . ',12z">' . $ac_lat . ' ' . $ac_lon . '</a> : ' . $ac_altitude . 'ft @ ' . date('Y-m-d G:i:s l', $ac_now));
					$alert_message = 'key=' . $user_set_array['secret_email_key'] . '&subject=' . $alert_message_subject . '&body=' . $alert_message_body;
				}
				if ($ac_hex) {
					$alert_trigger_array[$ac_hex] = time();
					#var_dump($alert_trigger_array); // show array for debug
				}
			}
		}

		// delete aircraft after user set seconds from already-message-sent-trigger
		$outdated_entry = time() - $user_set_array['aircraft_suspend_time'];
		foreach ($alert_trigger_array as $key => $value) {
			if ($value < $outdated_entry) {
				unset($alert_trigger_array[$key]);
			}
		}

		// send alert-message, set absolute limit for maximum number of messages and reset alert-message
		if ($alert_message != '' and $sent_alert_messages < $user_set_array['mailer_limit']) {
			if ($user_set_array['gmail']) {
				$email = $user_set_array['email_address'];
				$header  = 'MIME-Version: 1.0' . PHP_EOL;
				$header .= 'Content-type: text/html; charset=iso-8859-1' . PHP_EOL;
				$header .= 'From: ' . $user_set_array['email_address'] . PHP_EOL;
				$header .= 'Reply-To: ' . $user_set_array['email_address'] . PHP_EOL;
				$header .= 'X-Mailer: PHP ' . phpversion();
				mail($user_set_array['email_address'], urldecode($alert_message_subject), urldecode($alert_message_body), $header);
			} else {
				file_get_contents($user_set_array['url_mailer'] . '?' . $alert_message);
			}
			$sent_alert_messages++;
			$alert_message = '';
		}

	}

// write selected aircraft data to database
$db = new PDO('mysql:host=' . $user_set_array['db_host'] . ';dbname=' . $user_set_array['db_name'] . '', $user_set_array['db_user'], $user_set_array['db_pass']); $db_insert = '';
if ($sql) { $db->exec($sql); $db->lastInsertId() ? $db_insert = 'inserted' : $db_insert = 'db-error'; }
$db = null;

// generate terminal output and set sleep timer to get minimum a full second until next aircraft.json is ready to get fetched
$runtime = (time() - $start_time);
$runtime_formatted = sprintf('%d days %02d:%02d:%02d', $runtime/60/60/24,($runtime/60/60)%24,($runtime/60)%60,$runtime%60);
($runtime > 0) ? $loop_clock = number_format(round(($i / $runtime),12),12) : $loop_clock = number_format(1, 12);
$process_microtime = (round(1000000 * (microtime(true) - $start_loop_microtime)));
print('process-time(us): ' . sprintf('%07d', $process_microtime) . ' - ' . $loop_clock . ' loops/s avg - since ' . $runtime_formatted . ' - run(s) ' . $i . ' -> ' . sprintf('%03d', $x) . ' dataset(s) => ' . $db_insert . PHP_EOL);
usleep(1000000);
$i++;

}

?>	

mailer.php

<?php

#phpinfo();
#var_dump(ini_get_all());
#ini_set('error_reporting', E_ALL);

isset($_GET['key']) ? $key = $_GET['key'] : $key = '';
isset($_GET['subject']) ? $subject = urldecode($_GET['subject']) : $subject = '';
isset($_GET['body']) ? $body = urldecode($_GET['body']) : $body = '';

if ($key == 'YOUR_USER_KEY') { // set key (letters/numbers only) according to the key you set in radar.php
	$header  = 'MIME-Version: 1.0' . PHP_EOL;
	$header .= 'Content-type: text/html; charset=iso-8859-1' . PHP_EOL;
	$header .= 'From: ADS-B RADAR' . PHP_EOL;
	$header .= 'Reply-To: YOUR_EMAIL@EMAIL.COM' . PHP_EOL; // set your email
	$header .= 'X-Mailer: PHP ". phpversion();
	mail('YOUR_EMAIL@EMAIL.COM', $subject, $body, $header); // set your email
}

?>

sample hex_code_array.txt

hex1, hex2, hex3

So I am in the process of giving this a shot, but I don’t see anyplace where to authenticate to an email server, such as Google.

Am I missing something?

Mailer.php IS the ‘mail server’, and least as far as sending outbound mail is concerned. That’s why you don’t authenticate with another email server.

edit: all below this line is no longer needed - now basic gmail support is built-in - just copy the actual radar.php - see settings there

for gmail just replace line 138 (in actual version above):

file_get_contents($user_set["url_mailer"] . "?" . $alert_message);

with this (modify 3 times your email address):

$email = "YOUR_EMAIL@gmail.com";
$headers = "From: YOUR_EMAIL@gmail.com";
mail("YOUR_EMAIL@gmail.com", urldecode($alert_message_subject), urldecode($alert_message_body), $headers);

adsbhub.net does not work anymore. I found a new one - adsbhub.org, looks quite promising. Supports several exchange protocols Beast, SBS, VRS Compressed.

1 Like

go here - new thread Dump1090 MySQL Database Script with Alert and Filter

### Final Version - 5 Minute Express Install HowTo ###
writes Dump1090 data to database and e-mail alerts on special events. you can set how often the script writes to database and looks for alert condition. you can specify the area (lat/lon/alt) to be observed and filter for special hex and/or flight numbers.

please do not abuse my script to generate and publish the flightaware-mlat-data anywhere - this data is only meant for your personal use. you find additional information here: FlightAware MLAT Network Announcement

2017_09_12 23:54 UTC: one bug in the flight-filter, one that aircrafts weren’t deleted from already alert sent list, one where aircrafts without altitude but position were written to database and/or email-alert and one that allowed too many aircrafts to pass hex/flight filter - now debugged.

2017_09_21 19:40 UTC: added new wildcard-search for hex- and flight-numbers. you can now use % for each unknown character in addition upper/lower case does not matter anymore. in addition you can set now hex/flight filter to operate only within lat/lon/alt limit or within whole range of site. better database error handling added. script now does not quit even if database is shut down and automatically resumes db writing as soon as db is again up. added pushover.net alert type.

but be aware what you are doing with this filters! e.g. if you have a site with good reception you maybe see 300 aircrafts in parallel, then if you put 500 hex- and 500 flight-numbers in your text-files and set the frequency to load fresh aircraft.json every second - then you end up with 300,000 wildcard-match-tests per second. not a good idea on the raspi cpu wise. php7 that comes with raspbian stretch is about 3-10 times faster than php5 if someone wants to do really crazy things with the script.

summary:
i wrote this script first just to dump some of the aircrafts in my neighborhood and their data to a database. then somebody wanted an alert and later another hex/flight filtering. at my site the script and mysql server runs 24/7 on a mac-mini-server - but for those who want run this direct on a raspberry i made a short howto. but be aware that the raspi runs from a sd-card and does not have unlimited disk-space nor is the sd-card too happy with too extensive data-writing. at my site i have around 175 aircrafts simultaneously over the whole day. so if i write every second a dataset for each i get more than 15,000,000 table-rows a day! that means you should use the scripts filter, set an higher intervall and probably use an external usb-harddrive. e.g. if i filter an area where i have only 15 aircrafts around the day and then just write every 60 seconds to database then i get about 20.000 entries a day instead of more than 15 millions!

p.s. the howto below is dedicated to forum member abcd for his huge efforts to help others in this forum :slight_smile: hopefully he is fine with my first attempt …

git install:

git clone --depth=1 http://github.com/TomMuc1/Dump1090-MySQL-Alert-Filter.git

the script to do your own daily aircraft-count directly on the raspberry pi without database you find here: Daily Log/E-Mail Aircraft Report directly on your RaspberryPi

5 Minute Express Install HowTo

given raspbian jessie install with dump1090 with lighttpd


sudo apt-get update
sudo apt-get install sendmail
sudo apt-get install php5-common php5-cgi php5-mysql php5-sqlite php5-curl php5
sudo lighty-enable-mod fastcgi
sudo lighty-enable-mod fastcgi-php
sudo service lighttpd force-reload
sudo apt-get install mysql-server mysql-client

sudo shutdown -r now

mysql -u root -p

CREATE DATABASE adsb;

USE adsb;

CREATE TABLE `aircrafts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  `now` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  `hex` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  `flight` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  `altitude` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  `lat` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  `lon` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  `track` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  `speed` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  `vert_rate` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  `seen_pos` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  `seen` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  `rssi` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  `messages` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  `category` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  `squawk` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  `nucp` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  `mlat` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  `tisb` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11750 DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;

SHOW COLUMNS IN aircrafts;

ctrl + z

php radar.php

run the script for some seconds/minutes until it says xxx inserted then stop script with ctrl + z

mysql -u root -p

USE adsb;

SELECT * FROM aircrafts;

et voila :)

p.s. for raspbian stretch the php-install line is:
sudo apt-get install php7.0-common php7.0-cgi php7.0-mysql php7.0-sqlite php7.0-curl php7.0

p.p.s. the new mariadb (aka mysql) that comes with stretch is somewhat stupid with the root password.
these steps help to get back the old behavior:

sudo mysql -u root -p (leave password empty)
update mysql.user set password=password('YOUR_DB_PASSWORD') where user='root';
update mysql.user set plugin='' where user='root';
flush privileges;

radar.php

<?php

#phpinfo();
#var_dump(ini_get_all());
#ini_set('error_reporting', E_ALL);

// below a sample create statement for database table
// CREATE TABLE aircrafts (id INT NOT NULL AUTO_INCREMENT, date VARCHAR(100), now VARCHAR(100), hex VARCHAR(100), flight VARCHAR(100), altitude VARCHAR(100), lat VARCHAR(100), lon VARCHAR(100), track VARCHAR(100), speed VARCHAR(100), vert_rate VARCHAR(100), seen_pos VARCHAR(100), seen VARCHAR(100), rssi VARCHAR(100), messages VARCHAR(100), category VARCHAR(100), squawk VARCHAR(100), nucp VARCHAR(100), mlat VARCHAR(100), tisb VARCHAR(100), PRIMARY KEY (id))

// set the rectangle and altitude to store aircraft-data in database - if your lon is negative be aware to use the right values for max and min
$user_set_array['max_lat'] = 50.000000;    $user_set_array['min_lat'] = 46.000000;    $user_set_array['max_alt'] = 10000;
$user_set_array['max_lon'] = 14.000000;    $user_set_array['min_lon'] = 10.000000;

// set the rectangle and altitude to send alert message - if your lon is negative be aware to use the right values for max and min
$user_set_array['alert_max_lat'] = 49.000000;    $user_set_array['alert_min_lat'] = 47.000000;    $user_set_array['alert_max_alt'] = 5000;
$user_set_array['alert_max_lon'] = 13.000000;    $user_set_array['alert_min_lon'] = 11.000000;

// set lookup-interval default is 1 (must be integer between 1 - 900) this is the frequency the script runs and writes to database or looks for alerts
$user_set_array['sleep'] = 1;

// set your google email-address for alert-messages - if you want to use mailer.php instead gmail set method 'gmail' to 'webmail' or 'pushover' and a key according to mailer.php
$user_set_array['alert_method'] = 'gmail'; $user_set_array['email_address'] = 'YOUR_EMAIL@gmail.com'; $user_set_array['secret_email_key'] = 'YOUR_USER_KEY';

// set parameters for database connection
$user_set_array['db_name'] = 'adsb'; $user_set_array['db_host'] = '127.0.0.1'; $user_set_array['db_user'] = 'USERNAME'; $user_set_array['db_pass'] = 'PASSWORD';

// set path to aircraft.json file
$user_set_array['url_json'] = 'http://127.0.0.1/dump1090/data/aircraft.json';

// set path to your mailer.php file
$user_set_array['url_mailer'] = 'http://YOUR_WEBSPACE.COM/mailer.php';

// set the absolute limit of alert-messages (default is 1000) this script is allowed to send over its whole runtime
$user_set_array['mailer_limit'] = 1000;

// set aircraft suspend time (default is 900) - change only if needed - time in seconds an aicraft is suspended from alert-messages after sending an alert-message for this aircraft
$user_set_array['aircraft_suspend_time'] = 900;

// set this to true if you want alerts and/or database writes from those aircrafts matching your hex_code_array.txt or flight_code_array.txt files within limited area or whole site-range, with or without wildcards
$user_set_array['filter_mode_alert'] = false;    $user_set_array['filter_mode_alert_limited'] = false;
$user_set_array['filter_mode_database'] = false;     $user_set_array['filter_mode_database_limited'] = false;
$user_set_array['filter_mode_wildcard'] = false;

// set path to your hex_code_array.txt and flight_code_array.txt files
$user_set_array['hex_file_path'] = '/home/pi/hex_code_array.txt';
$user_set_array['flight_file_path'] = '/home/pi/flight_code_array.txt';

// set your timezone see http://php.net/manual/en/timezones.php
$user_set_array['time_zone'] = 'America/Chicago';




function func_wildcard_search($code, $user_code_array, $wildcard_mode) {
	$match = false;
	$code = strtoupper($code);
	if ($wildcard_mode) {
		foreach ($user_code_array as $pattern) {
			if (preg_match('/^' . trim($pattern) . '$/', $code)) $match = true;
		}
	} else {
		$user_code_array = array_map('trim', $user_code_array);
		if (in_array($code, $user_code_array)) $match = true;
	}
	return $match;
}

$i = 0;
$alert_message = '';
$sent_alert_messages = 0;
$alert_trigger_array = array();
$start_time = time();
date_default_timezone_set($user_set_array['time_zone']);

while (true) {

	$x = 0;
	$sql = '';
	$start_loop_microtime = microtime(true);
	$json_data_array = json_decode(file_get_contents($user_set_array['url_json']),true);
	if ($user_set_array['filter_mode_alert'] || $user_set_array['filter_mode_database']) {
		$hex_code_array = explode(',', str_replace('%', '.', strtoupper(file_get_contents($user_set_array['hex_file_path']))));
		$flight_code_array = explode(',', str_replace('%', '.', strtoupper(file_get_contents($user_set_array['flight_file_path']))));
	}

	// loop through aircraft.json file
	foreach ($json_data_array['aircraft'] as $row) {
		isset($json_data_array['now']) ? $ac_now = $json_data_array['now'] : $ac_now = '';
		isset($row['hex']) ? $ac_hex = $row['hex'] : $ac_hex = '';
		isset($row['flight']) ? $ac_flight = trim($row['flight']) : $ac_flight = '';
		isset($row['altitude']) ? $ac_altitude = $row['altitude'] : $ac_altitude = '';
		isset($row['lat']) ? $ac_lat = $row['lat'] : $ac_lat = '';
		isset($row['lon']) ? $ac_lon = $row['lon'] : $ac_lon = '';
		isset($row['track']) ? $ac_track = $row['track'] : $ac_track = '';
		isset($row['speed']) ? $ac_speed = $row['speed'] : $ac_speed = '';
		isset($row['vert_rate']) ? $ac_vert_rate = $row['vert_rate'] : $ac_vert_rate = '';
		isset($row['seen_pos']) ? $ac_seen_pos = $row['seen_pos'] : $ac_seen_pos = '';
		isset($row['seen']) ? $ac_seen = $row['seen'] : $ac_seen = '';
		isset($row['rssi']) ? $ac_rssi = $row['rssi'] : $ac_rssi = '';
		isset($row['messages']) ? $ac_messages = $row['messages'] : $ac_messages = '';
		isset($row['category']) ? $ac_category = $row['category'] : $ac_category = '';
		isset($row['squawk']) ? $ac_squawk = $row['squawk'] : $ac_squawk = '';
		isset($row['nucp']) ? $ac_nucp = $row['nucp'] : $ac_nucp = '';
		isset($row['mlat']) ? $ac_mlat = implode(' ', $row['mlat']) : $ac_mlat = '';
		isset($row['tisb']) ? $ac_tisb = implode(' ', $row['tisb']) : $ac_tisb = '';

		// generate sql insert statement per aircraft in range of user set altitude/latitude/longitude and optionally according only to hex or flight numbers in hex_code_array.txt and flight_code_array.txt
		#var_dump($hex_code_array); var_dump($flight_code_array); // show arrays for debug
		if ($user_set_array['filter_mode_database'] && $user_set_array['filter_mode_database_limited']) {
			if (($ac_altitude != '' && $ac_altitude < $user_set_array['max_alt'] && $ac_lat < $user_set_array['max_lat'] && $ac_lat > $user_set_array['min_lat'] && $ac_lon < $user_set_array['max_lon'] && $ac_lon > $user_set_array['min_lon']) && (func_wildcard_search($ac_hex, $hex_code_array, $user_set_array['filter_mode_wildcard']) || ($ac_flight != '' && func_wildcard_search($ac_flight, $flight_code_array, $user_set_array['filter_mode_wildcard'])))) {
				$sql .= "INSERT INTO aircrafts VALUES (NULL, '" . date("Y-m-d G:i:s l", $ac_now) . "', '$ac_now', '$ac_hex', '$ac_flight', ";
				$sql .= "'$ac_altitude', '$ac_lat', '$ac_lon', '$ac_track', '$ac_speed', '$ac_vert_rate', '$ac_seen_pos', '$ac_seen', ";
				$sql .= "'$ac_rssi', '$ac_messages', '$ac_category', '$ac_squawk', '$ac_nucp', '$ac_mlat', '$ac_tisb');";
				$sql .= PHP_EOL;
				$x++;
			}
		} else if ($user_set_array['filter_mode_database']) {
			if (($ac_altitude != '' && $ac_altitude < $user_set_array['max_alt'] && $ac_lat < $user_set_array['max_lat'] && $ac_lat > $user_set_array['min_lat'] && $ac_lon < $user_set_array['max_lon'] && $ac_lon > $user_set_array['min_lon']) || (func_wildcard_search($ac_hex, $hex_code_array, $user_set_array['filter_mode_wildcard']) || ($ac_flight != '' && func_wildcard_search($ac_flight, $flight_code_array, $user_set_array['filter_mode_wildcard'])))) {
				$sql .= "INSERT INTO aircrafts VALUES (NULL, '" . date("Y-m-d G:i:s l", $ac_now) . "', '$ac_now', '$ac_hex', '$ac_flight', ";
				$sql .= "'$ac_altitude', '$ac_lat', '$ac_lon', '$ac_track', '$ac_speed', '$ac_vert_rate', '$ac_seen_pos', '$ac_seen', ";
				$sql .= "'$ac_rssi', '$ac_messages', '$ac_category', '$ac_squawk', '$ac_nucp', '$ac_mlat', '$ac_tisb');";
				$sql .= PHP_EOL;
				$x++;
			}
		} else {
			if ($ac_altitude != '' && $ac_altitude < $user_set_array['max_alt'] && $ac_lat < $user_set_array['max_lat'] && $ac_lat > $user_set_array['min_lat'] && $ac_lon < $user_set_array['max_lon'] && $ac_lon > $user_set_array['min_lon']) {
				$sql .= "INSERT INTO aircrafts VALUES (NULL, '" . date("Y-m-d G:i:s l", $ac_now) . "', '$ac_now', '$ac_hex', '$ac_flight', ";
				$sql .= "'$ac_altitude', '$ac_lat', '$ac_lon', '$ac_track', '$ac_speed', '$ac_vert_rate', '$ac_seen_pos', '$ac_seen', ";
				$sql .= "'$ac_rssi', '$ac_messages', '$ac_category', '$ac_squawk', '$ac_nucp', '$ac_mlat', '$ac_tisb');";
				$sql .= PHP_EOL;
				$x++;
			}
		}

		// set and modify alert-trigger-array and build alert-message optionally according only to hex or flight numbers in hex_code_array.txt and flight_code_array.txt
		if ($user_set_array['filter_mode_alert'] && $user_set_array['filter_mode_alert_limited']) {
			if (($ac_altitude != '' && $ac_altitude < $user_set_array['alert_max_alt'] && $ac_lat < $user_set_array['alert_max_lat'] && $ac_lat > $user_set_array['alert_min_lat'] && $ac_lon < $user_set_array['alert_max_lon'] && $ac_lon > $user_set_array['alert_min_lon']) && (func_wildcard_search($ac_hex, $hex_code_array, $user_set_array['filter_mode_wildcard']) || ($ac_flight != '' && func_wildcard_search($ac_flight, $flight_code_array, $user_set_array['filter_mode_wildcard'])))) {
				if (!array_key_exists($ac_hex, $alert_trigger_array)) {
					$alert_message_subject = urlencode('### STRAFER-ALERT ### ' . $ac_flight  . ' ' . $ac_hex . ' : ' . $ac_lat . ' ' . $ac_lon . ' : ' . $ac_altitude . 'ft @ ' . date('Y-m-d G:i:s l', $ac_now));
					$alert_message_body = urlencode($ac_flight  . ' ' . $ac_hex . ' : <a href="http://www.google.com/maps/place/' . $ac_lat . ',' . $ac_lon . '/@' . $ac_lat . ',' . $ac_lon . ',12z">' . $ac_lat . ' ' . $ac_lon . '</a> : ' . $ac_altitude . 'ft @ ' . date('Y-m-d G:i:s l', $ac_now));
					$alert_message = 'key=' . $user_set_array['secret_email_key'] . '&subject=' . $alert_message_subject . '&body=' . $alert_message_body;
					if ($ac_hex) {
						$alert_trigger_array[$ac_hex] = time();
						#var_dump($alert_trigger_array); // show array for debug
					}
				}
			}
		} else if ($user_set_array['filter_mode_alert']) {
			if (($ac_altitude != '' && $ac_altitude < $user_set_array['alert_max_alt'] && $ac_lat < $user_set_array['alert_max_lat'] && $ac_lat > $user_set_array['alert_min_lat'] && $ac_lon < $user_set_array['alert_max_lon'] && $ac_lon > $user_set_array['alert_min_lon']) || (func_wildcard_search($ac_hex, $hex_code_array, $user_set_array['filter_mode_wildcard']) || ($ac_flight != '' && func_wildcard_search($ac_flight, $flight_code_array, $user_set_array['filter_mode_wildcard'])))) {
				if (!array_key_exists($ac_hex, $alert_trigger_array)) {
					$alert_message_subject = urlencode('### STRAFER-ALERT ### ' . $ac_flight  . ' ' . $ac_hex . ' : ' . $ac_lat . ' ' . $ac_lon . ' : ' . $ac_altitude . 'ft @ ' . date('Y-m-d G:i:s l', $ac_now));
					$alert_message_body = urlencode($ac_flight  . ' ' . $ac_hex . ' : <a href="http://www.google.com/maps/place/' . $ac_lat . ',' . $ac_lon . '/@' . $ac_lat . ',' . $ac_lon . ',12z">' . $ac_lat . ' ' . $ac_lon . '</a> : ' . $ac_altitude . 'ft @ ' . date('Y-m-d G:i:s l', $ac_now));
					$alert_message = 'key=' . $user_set_array['secret_email_key'] . '&subject=' . $alert_message_subject . '&body=' . $alert_message_body;
					if ($ac_hex) {
						$alert_trigger_array[$ac_hex] = time();
						#var_dump($alert_trigger_array); // show array for debug
					}
				}
			}
		} else {
			if ($ac_altitude != '' && $ac_altitude < $user_set_array['alert_max_alt'] && $ac_lat < $user_set_array['alert_max_lat'] && $ac_lat > $user_set_array['alert_min_lat'] && $ac_lon < $user_set_array['alert_max_lon'] && $ac_lon > $user_set_array['alert_min_lon']) {
				if (!array_key_exists($ac_hex, $alert_trigger_array)) {
					$alert_message_subject = urlencode('### STRAFER-ALERT ### ' . $ac_flight  . ' ' . $ac_hex . ' : ' . $ac_lat . ' ' . $ac_lon . ' : ' . $ac_altitude . 'ft @ ' . date('Y-m-d G:i:s l', $ac_now));
					$alert_message_body = urlencode($ac_flight  . ' ' . $ac_hex . ' : <a href="http://www.google.com/maps/place/' . $ac_lat . ',' . $ac_lon . '/@' . $ac_lat . ',' . $ac_lon . ',12z">' . $ac_lat . ' ' . $ac_lon . '</a> : ' . $ac_altitude . 'ft @ ' . date('Y-m-d G:i:s l', $ac_now));
					$alert_message = 'key=' . $user_set_array['secret_email_key'] . '&subject=' . $alert_message_subject . '&body=' . $alert_message_body;
					if ($ac_hex) {
						$alert_trigger_array[$ac_hex] = time();
						#var_dump($alert_trigger_array); // show array for debug
					}
				}
			}
		}

		// delete aircraft after user set seconds from already-message-sent-trigger
		$outdated_entry = time() - $user_set_array['aircraft_suspend_time'];
		foreach ($alert_trigger_array as $key => $value) {
			if ($value < $outdated_entry) {
				unset($alert_trigger_array[$key]);
			}
		}

		// send alert-message, set absolute limit for maximum number of messages and reset alert-message
		if ($alert_message != '' && $sent_alert_messages < $user_set_array['mailer_limit']) {
			if ($user_set_array['alert_method'] == 'gmail') {
				$email = $user_set_array['email_address'];
				$header  = 'MIME-Version: 1.0' . PHP_EOL;
				$header .= 'Content-type: text/html; charset=iso-8859-1' . PHP_EOL;
				$header .= 'From: ' . $user_set_array['email_address'] . PHP_EOL;
				$header .= 'Reply-To: ' . $user_set_array['email_address'] . PHP_EOL;
				$header .= 'X-Mailer: PHP ' . phpversion();
				mail($user_set_array['email_address'], urldecode($alert_message_subject), urldecode($alert_message_body), $header);
			} else if ($user_set_array['alert_method'] == 'pushover') {
				file_get_contents($user_set_array['url_mailer'] . '?mode=pushover&' . $alert_message);
			} else if ($user_set_array['alert_method'] == 'webmail') {
				file_get_contents($user_set_array['url_mailer'] . '?mode=webmail&' . $alert_message);
			}
			$sent_alert_messages++;
			$alert_message = '';
		}

	}

// write selected aircraft data to database
try {
	$db = new PDO('mysql:host=' . $user_set_array['db_host'] . ';dbname=' . $user_set_array['db_name'] . '', $user_set_array['db_user'], $user_set_array['db_pass']); $db_insert = '';
	$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	if ($sql) { $db->exec($sql); $db_insert = 'inserted'; }
	$db = null;
} catch (PDOException $db_error) {
	$db_insert = 'db-error' . PHP_EOL . $db_error->getMessage();
}

// generate terminal output and set sleep timer to get minimum a full second until next aircraft.json is ready to get fetched
$runtime = (time() - $start_time);
$runtime_formatted = sprintf('%d days %02d:%02d:%02d', $runtime/60/60/24,($runtime/60/60)%24,($runtime/60)%60,$runtime%60);
($runtime > 0) ? $loop_clock = number_format(round(($i / $runtime),12),12) : $loop_clock = number_format(1, 12);
$process_microtime = (round(1000000 * (microtime(true) - $start_loop_microtime)));
print('process-time(us): ' . sprintf('%07d', $process_microtime) . ' - ' . $loop_clock . ' loops/s avg - since ' . $runtime_formatted . ' - run(s) ' . $i . ' -> ' . sprintf('%03d', $x) . ' dataset(s) => ' . $db_insert . PHP_EOL);
sleep($user_set_array['sleep']);
$i++;

}

?>

sample hex_code_array.txt

hex1, hex2, hex3

sample flight_code_array.txt

flight1, flight2, flight3

mailer.php

<?php

#phpinfo();
#var_dump(ini_get_all());
#ini_set('error_reporting', E_ALL);

// set key (letters/numbers only) according to the key you set in radar.php
$user_key = 'YOUR_USER_KEY';

// set your email address if you want to use email alert
$user_email = 'YOUR_EMAIL@EMAIL.COM';

// set your pushover token and key if you want to use pushover alert
$pushover_api_token = 'YOUR_API_TOKEN'; $pushover_user_key = 'YOUR_USER_KEY';

isset($_GET['key']) ? $key = $_GET['key'] : $key = '';
isset($_GET['mode']) ? $mode = $_GET['mode'] : $mode = '';
isset($_GET['subject']) ? $subject = urldecode($_GET['subject']) : $subject = '';
isset($_GET['body']) ? $body = urldecode($_GET['body']) : $body = '';

if ($key == $user_key && $mode == 'webmail') {
	$header  = 'MIME-Version: 1.0' . PHP_EOL;
	$header .= 'Content-type: text/html; charset=iso-8859-1' . PHP_EOL;
	$header .= 'From: ADS-B RADAR' . PHP_EOL;
	$header .= 'Reply-To: ' . $user_email . PHP_EOL;
	$header .= 'X-Mailer: PHP '. phpversion();
	mail($user_email, $subject, $body, $header);
} else if ($key == $user_key && $mode == 'pushover') {
	curl_setopt_array($ch = curl_init(), array(
		CURLOPT_URL => 'https://api.pushover.net/1/messages.json',
		CURLOPT_POSTFIELDS => array('token' => $pushover_api_token, 'user' => $pushover_user_key, 'message' => $body, 'html' => '1'),
		CURLOPT_SAFE_UPLOAD => true,
		CURLOPT_RETURNTRANSFER => true,
	));
	curl_exec($ch);
	curl_close($ch);
}

?>
3 Likes

GREAT!
A “How to” with easy to follow and clear step-by-step instructions. Well done Tom.

1 Like

haha - thanx :slight_smile: and now i have a little idea of how exhausting it is to make all these howtos like you do!

some short examples what you can do with your own harvested data

count the distinct number of aircrafts you got messages from:

select count(distinct hex) from aircrafts;

count how many messages you received per aircrafts:

SELECT hex, COUNT(hex) number FROM aircrafts GROUP BY hex

combine your data with e.g. basestation.sqb - see output below sql sample:

select * from aircrafts inner join basestation on aircrafts.hex = basestation.ModeS

sample output:

id	    date	                    now	            hex	    flight	altitude	lat	        lon	        track	speed	vert_rate	seen_pos	seen	rssi	messages	category	squawk	nucp	mlat	tisb	AircraftID	ModeS	ModeSCountry	Country	Registration	Status	Manufacturer	        ICAOTypeCode	Type	SerialNo	RegisteredOwners	        OperatorFlagCode
252186	2017-09-08 21:01:56 Friday	1504897316.1	a6bd32	EJM533	31000	    47.801234	9.799171	144	    510	    -64	         0.2	    0.1	    -3.8	13107	    A2	        0356	7			            42480	    A6BD32	United States	N	    N533SR	        A	    Gulfstream Aerospace	G450	        G450	4253	    Executive Jet Management	EJM