FlightAware Discussions

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

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

I assume adding and removing entries from the hex and flight text files will require a restart of the radar.php?

Also, can the script handle wildcards? For example ANZ12%, C87F% etc.

I have set emailed alerts up for my GMail account and set the variables in the script accordingly. Subsequently an aircraft of interest that is in my hex text file has not been emailed to me. Having rescanned the radar.php file I see no variable for my GMail password or other GMail account settings, does there need to be?

Setting my mail for internal and setting the URL for the mailer to be http://192.168.1.2:8082/mailer.php gives me the following error when starting radar.php:

PHP Warning: file_get_contents(http://192.168.1.2:8082/mailer.php?key=xxxxxx&subject=%23%23%23+STRAFER-ALERT+%23%23%23+JST231+7c6b13+%3A+-42.212592+172.864188+%3A+7818.12m+%40+2017-09-11+12%3A33%3A56+Monday&body=JST231+7c6b13+%3A+<a+href%3D"http%3A%2F%2Fwww.google.com%2Fmaps%2Fplace%2F-42.212592%2C172.864188%2F%40-42.212592%2C172.864188%2C12z">-42.212592+172.864188<%2Fa>+%3A+7818.12m+%40+2017-09-11+12%3A33%3A56+Monday): failed to open stream: HTTP request failed! HTTP/1.0 500 Internal Server Error
in /var/www/html/aircraft.php on line 157

Key has been hidden for this post and port 8082 is my web forward port for the machine hosting radar.php and mailer.php

OK, I found them all in my Junk folder! GMail works just fine!

OK I’m sure I had this running OK this morning but now it seems after a total refresh of both radar.php and mailer.php that I am not getting any emailed alerts.

If I set Gmail option to true and enter my GMail address radar.php runs without error but also without any alerts, but database writes are OK.

Switching to the mailer it seems that I get a:

HTTP request failed! HTTP/1.0 500 Internal Server Error message when radar.php tries to locate the mailer.php

Any help appreciated.

Edit: This may be related to the fact I am trying to run the radar.php and mailer.php (when enabled) on a Ubuntu platform. I’ve just moved the scripts back to my RPi where the radar.php used to be until I started ‘messing’ with alerts! Having installed sendmail on the RPi I think the GMail option is now working… sort of.

@invergordon

if you use the script with google-mail configuration it should run without any problems on any raspian jessie install out of the box and mailer.php is not needed.

don’t know which version you are using? and if you messed too much around - just copy radar.php again - do the few needed settings again …

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

@invergordon

did a short test - downloaded the radar.php from fa-website - setup googlemail to true, my google-address, path to aircraft.json, lat/lon/alt for db, lat/lon/alt for alert, timezone and password/user/db/path-to-db. then copied to a fresh jessy setup.

runs instantly without any issue … sends alerts and writes to database.

in my fresh raspian-jessie installs there is sendmail installed - but when i download jessie from raspbians website today and did an install ther was no sendmail at all …

i add to install howto the sendmail install - no settings needed - just:

sudo apt-get update
apt-get install sendmail

but again if you simply use and upload mailer.php to your webspace all this isn’t needed

restart isn’t needed - the script every loop checks both .txt files

you have to input the full flight or hex

Thanks To, I’ve done this a few times now in case I’ve missed anything but that’s the process I’ve been through.

While the database writing goes well and I have no issues here the alerts are not really working.

Can you explain what the ‘upload’ mailer to my ‘webspace’ means and does? I’m a little confused with what the mailer/php should be doing.

Thanks.

what does ‘not really working’ mean?
webspace - where you have to put the mailer.php: https://www.ntchosting.com/encyclopedia/internet/web-space/