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