go to latest version here: Dump1090 MySQL Database Script - E-Mail-Alert Lat/Lon/Alt/Hex/Flight-Filter - #23 by TomMuc
- 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
-
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
}
?>