Daily Log/E-Mail Aircraft Report directly on your RaspberryPi

another simple script that might be useful for others …

counts all unique aircrafts per UTC day and automatically sends you an email report with number of aircrafts and all hex-codes along with number of messages, first and last seen position per aircraft/hex. no database needed, no sd-card writing needed. if you want more data you can additionally use this script: Dump1090 MySQL Database Script with Alert and Filter

edit 2017_09_10 17:49 UTC: now discards hex 000000
edit 2017_10_11 00:10 UTC: added daily excel/csv report file
edit 2017_10_16 19:40 UTC: added a little workaround for excel reformats hex to scientific notation behavior
edit 2017_10_18 22:10 UTC: fixed a bug and added some fields
edit 2017_10_19 17:45 UTC: added option to write daily zipped logfile and/or email report
edit 2017_10_21 22:25 UTC: fixed a bug
edit 2017_10_22 20:20 UTC: added mysql database option
edit 2017_11_02 13:20 UTC: added start/stop data cache functionality

one line sample database output using an inner join to basestation.sqb:

sql: select * from daily_report inner join basestation on daily_report.transponder = basestation.ModeS

id    report_date    transponder    messages    flight    category    squawk    first_seen                    first_latitude    first_longitude    first_altitude    last_seen                    last_latitude    last_longitude    last_altitude    low_dist    high_dist    low_rssi    high_rssi    mlat    AircraftID    ModeS    ModeSCountry    Country    Registration    Status    Manufacturer    ICAOTypeCode    Type    SerialNo    RegisteredOwners    OperatorFlagCode
1     20171022       4b19e8         478         SWR117G   A0          3024      2017-10-22 20:47:21 Sunday    47.468851         8.820261           13750             2017-10-22 21:02:03 Sunday   48.658447        9.533169          4525             74.2        102.5        -30.7       -9.6                 1282          4B19E8   Switzerland     HB         HB-JVC          A         Fokker          F100            F100    11501       Helvetic Airways    OAW

or simply count all aircrafts/messages per day:

select count(report_date) as seen_aircrafts, sum(messages) as received_messages, report_date as count_date from daily_report group by report_date order by count_date desc

seen_aircrafts    received_messages    count_date
3705              6304047              20171026
3617              6376172              20171025
3291              5900191              20171024
3233              5822322              20171023

given raspbian jessie or stretch install with dump1090:

sudo apt-get update

install sendmail (only needed for email option):
sudo apt-get install sendmail

php install - raspbian jessie only:
sudo apt-get install php5-common php5-cgi php5-mysql php5-sqlite php5-curl php5

php install - raspbian stretch only:
sudo apt-get install php7.0-common php7.0-cgi php7.0-mysql php7.0-sqlite php7.0-curl php7.0

do the needed settings at top of ac_counter.php - then place the script in /home/pi/ and follow below instructions …

setup script system service:

sudo chmod 755 /home/pi/ac_counter.php
sudo nano /etc/systemd/system/ac_counter.service

→ in nano insert the following lines

[Unit]
Description=ac_counter.php

[Service]
ExecStart=/home/pi/ac_counter.php
Restart=always
RestartSec=10
StandardOutput=null
StandardError=null

[Install]
WantedBy=multi-user.target

save and exit nano ctrl+x → ctrl+y → enter

sudo chmod 644 /etc/systemd/system/ac_counter.service
sudo systemctl enable ac_counter.service
sudo systemctl start ac_counter.service
sudo systemctl status ac_counter.service

alternative but not reccomended you can run the script via cron:

setup crontab to auto-run script:
sudo crontab -e
@reboot sleep 10 && /usr/bin/php /home/pi/ac_counter.php > /dev/null

ac_counter.php

#!/usr/bin/php
<?php

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

// below a sample create statement for database table
// CREATE TABLE daily_report (id INT NOT NULL AUTO_INCREMENT, report_date VARCHAR(100), transponder VARCHAR(100), messages VARCHAR(100), flight VARCHAR(100), category VARCHAR(100), squawk VARCHAR(100), first_seen VARCHAR(100), first_latitude VARCHAR(100), first_longitude VARCHAR(100), first_altitude VARCHAR(100), last_seen VARCHAR(100), last_latitude VARCHAR(100), last_longitude VARCHAR(100), last_altitude VARCHAR(100), low_dist VARCHAR(100), high_dist VARCHAR(100), low_rssi VARCHAR(100), high_rssi VARCHAR(100), mlat VARCHAR(100), PRIMARY KEY (id))

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

// set email and/or logfile and/or database option to true or false
$user_set_array['email'] = false;    $user_set_array['log'] = true;
$user_set_array['database'] = false;

// set path to directory where log files to store to
$user_set_array['log_directory'] = '/home/pi/ac_counter_log/';

// default path to temporary directory where tmp files to store to
$user_set_array['tmp_directory'] = '/run/ac_counter_tmp/';

// set your google email-address daily reports to send to
$user_set_array['email_address'] = 'YOUR_EMAIL@gmail.com';

// set the absolute limit of alert-messages default is 500
$user_set_array['mailer_limit'] = 500;

// set to true for units metric instead nautical
$user_set_array['metric'] = false;

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

// set only to true for script function test run -> will 3 times email/log/db after about 1/2/3 minutes
$user_set_array['test_mode'] = false;



// function to compute distance between receiver and aircraft
function func_haversine($lat_from, $lon_from, $lat_to, $lon_to, $earth_radius) {
    $delta_lat = deg2rad($lat_to - $lat_from);
    $delta_lon = deg2rad($lon_to - $lon_from);
    $a = sin($delta_lat / 2) * sin($delta_lat / 2) + cos(deg2rad($lat_from)) * cos(deg2rad($lat_to)) * sin($delta_lon / 2) * sin($delta_lon / 2);
    $c = 2 * atan2(sqrt($a), sqrt(1-$a));
    return $earth_radius * $c;
}

$i = 0;
$sent_messages = 0;
$tmp_write_trigger = 0;
$hex_array = array();
$start_time = time();
date_default_timezone_set('UTC');
$seconds_of_day = time() - strtotime('today');
$user_set_array['metric'] ? $earth_radius = 6371 : $earth_radius = 3440;

// fetch receiver.json and read receiver latitude and longitude
$json_receiver_location = json_decode(file_get_contents($user_set_array['url_json'] . 'receiver.json'), true);
isset($json_receiver_location['lat']) ? $rec_lat = $json_receiver_location['lat'] : $rec_lat = 0;
isset($json_receiver_location['lon']) ? $rec_lon = $json_receiver_location['lon'] : $rec_lon = 0;

// set csv-file column header names
$csv_header = '"Transponder"' . "\t" . '"Messages"' . "\t" . '"Flight"' . "\t" . '"Category"' . "\t" . '"Squawk"' . "\t" . '"First Seen"' . "\t" . '"First Latitude"' . "\t" . '"First Longitude"' . "\t" . '"First Altitude"' . "\t" . '"Last Seen"' . "\t" . '"Last Latitude"' . "\t" . '"Last Longitude"' . "\t" . '"Last Altitude"' . "\t" . '"Low Dist"' . "\t" . '"High Dist"' . "\t" . '"Low Rssi"' . "\t" . '"High Rssi"' . "\t" . '"Mlat"' . PHP_EOL . PHP_EOL;

// at script restart try to resume with already harvested data of this day
if (file_exists($user_set_array['tmp_directory'] . 'ac_counter.tmp') && date('Ymd') == date('Ymd', filemtime($user_set_array['tmp_directory'] . 'ac_counter.tmp'))) {
    $csv_array = json_decode(file_get_contents($user_set_array['tmp_directory'] . 'ac_counter.tmp'), true);
} else {
    $csv_array = array();
}

while (true) {

    $db_insert = '';
	$start_loop_microtime = microtime(true);

	// write about every 5 minutes a tmp-file to preserve already harvested data of this day
	if ($tmp_write_trigger == 300 && isset($csv_array)) {
	    if (!file_exists($user_set_array['tmp_directory'])) mkdir($user_set_array['tmp_directory'], 0755, true);
        file_put_contents($user_set_array['tmp_directory'] . 'ac_counter.tmp', json_encode($csv_array), LOCK_EX);
        $tmp_write_trigger = 0;
    }
    $tmp_write_trigger++;

    // at midnight generate csv-file and submit email and/or write log-file and/or database
    if ($seconds_of_day > time() - strtotime('today') || ($user_set_array['test_mode'] && ($i == 60 || $i == 120 || $i == 180))) {
        $csv = '';
        $csv .= $csv_header;
        foreach ($csv_array as $key => $value) {
            $csv .= "\"\t\0" . implode("\"\t\"", str_replace('.', ',', $value)) . "\"" . PHP_EOL;
        }
		if ($user_set_array['email'] == true && $sent_messages < $user_set_array['mailer_limit']) {
			$boundary = str_replace(' ', '.', microtime());
			$header = 'From: ' . $user_set_array['email_address'] . PHP_EOL;
			$header .= 'Reply-To: ' . $user_set_array['email_address'] . PHP_EOL;
			$header .= 'MIME-Version: 1.0' . PHP_EOL;
			$header .= 'Content-Type: multipart/mixed; boundary="' . $boundary . '"' . PHP_EOL . PHP_EOL;
			$body = '--' . $boundary . PHP_EOL;
			$body .= 'Content-type:text/plain; charset=iso-8859-1' . PHP_EOL;
			$body .= 'Content-Transfer-Encoding: 7bit' . PHP_EOL . PHP_EOL;
			$body .= number_format(count($csv_array), 0, ',', '.') . ' Aircrafts @ ' . number_format(array_sum(array_column($csv_array, 'msg')), 0, ',', '.') . ' Messages - Yesterday UTC' . PHP_EOL . PHP_EOL;
			$body .= '--' . $boundary . PHP_EOL;
			$body .= 'Content-Type: application/octet-stream; name="aircrafts.xls"' . PHP_EOL;
			$body .= 'Content-Transfer-Encoding: base64' . PHP_EOL;
			$body .= 'Content-Disposition: attachment; filename="aircrafts.xls"' . PHP_EOL . PHP_EOL;
			$body .= chunk_split(base64_encode($csv)) . PHP_EOL . PHP_EOL;
			$body .= '--' . $boundary . '--';
			mail($user_set_array['email_address'], 'Daily Aircraft Stats', $body, $header);
        }
        if ($user_set_array['log'] == true) {
			$file_to_write = gzencode($csv);
			$file_name_to_write = $user_set_array['log_directory'] . 'ac_' . date('Y_m_d_i', time() - 86400) . '.xls.zip';
			if (!file_exists($user_set_array['log_directory'])) mkdir($user_set_array['log_directory'], 0755, true);
            file_put_contents($file_name_to_write, $file_to_write, LOCK_EX);
		}
        if ($user_set_array['database'] == true) {
            $sql = '';
			foreach ($csv_array as $key => $value) {
				$sql .= "INSERT INTO daily_report VALUES (NULL, '" . date('Ymd', time() - 86400) . "', '" . implode("', '", $value) . "');" . PHP_EOL;
			}
			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 = PHP_EOL . $db_error->getMessage();
			}
		}
		if (!$user_set_array['test_mode']) $csv_array = array();
		$sent_messages++;
	}

	// fetch aircraft.json and read timestamp
	$json_data_array = json_decode(file_get_contents($user_set_array['url_json'] . 'aircraft.json'),true);
	isset($json_data_array['now']) ? $ac_now = date("Y-m-d G:i:s l", $json_data_array['now']) : $ac_now = '';

	// loop through aircraft section of aircraft.json file and generate csv_array that holds the data of whole day
	foreach ($json_data_array['aircraft'] as $row) {
		isset($row['hex']) ? $ac_hex = $row['hex'] : $ac_hex = '';
		isset($row['flight']) ? $ac_flight = trim($row['flight']) : $ac_flight = '';
		isset($row['category']) ? $ac_category = $row['category'] : $ac_category = '';
		isset($row['squawk']) ? $ac_squawk = $row['squawk'] : $ac_squawk = '';
		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['seen']) ? $ac_seen = $row['seen'] : $ac_seen = '';
		isset($row['rssi']) ? $ac_rssi = $row['rssi'] : $ac_rssi = '';
		isset($row['mlat']) ? $ac_mlat = implode(' ', $row['mlat']) : $ac_mlat = '';
		if ($ac_hex != '' && $ac_hex != '000000' && ($ac_seen != '' && $ac_seen < 1.2)) {
		    $csv_array[$ac_hex]['hex'] = $ac_hex;
		    isset($csv_array[$ac_hex]['msg']) ? $csv_array[$ac_hex]['msg']++ : $csv_array[$ac_hex]['msg'] = 1;
		    if (!isset($csv_array[$ac_hex]['flight']) && $ac_flight == '') { $csv_array[$ac_hex]['flight'] = ''; }
		    else if ($ac_flight != '') { $csv_array[$ac_hex]['flight'] = $ac_flight; }
		    if (!isset($csv_array[$ac_hex]['category']) && $ac_category == '') { $csv_array[$ac_hex]['category'] = ''; }
		    else if ($ac_category != '') { $csv_array[$ac_hex]['category'] = $ac_category; }
		    if (!isset($csv_array[$ac_hex]['squawk']) && $ac_squawk == '') { $csv_array[$ac_hex]['squawk'] = ''; }
		    else if ($ac_squawk != '') { $csv_array[$ac_hex]['squawk'] = $ac_squawk; }
            if (!isset($csv_array[$ac_hex]['f_see']) || $csv_array[$ac_hex]['f_see'] == '') $csv_array[$ac_hex]['f_see'] = $ac_now;
            if (!isset($csv_array[$ac_hex]['f_lat']) || $csv_array[$ac_hex]['f_lat'] == '') $csv_array[$ac_hex]['f_lat'] = $ac_lat;
            if (!isset($csv_array[$ac_hex]['f_lon']) || $csv_array[$ac_hex]['f_lon'] == '') $csv_array[$ac_hex]['f_lon'] = $ac_lon;
            if (!isset($csv_array[$ac_hex]['f_alt']) || $csv_array[$ac_hex]['f_alt'] == '') $csv_array[$ac_hex]['f_alt'] = $ac_altitude;
            if (!isset($csv_array[$ac_hex]['l_see']) && $ac_now == '') { $csv_array[$ac_hex]['l_see'] = ''; }
		    else if ($ac_now != '') { $csv_array[$ac_hex]['l_see'] = $ac_now; }
		    if (!isset($csv_array[$ac_hex]['l_lat']) && $ac_lat == '') { $csv_array[$ac_hex]['l_lat'] = ''; }
		    else if ($ac_lat != '') { $csv_array[$ac_hex]['l_lat'] = $ac_lat; }
		    if (!isset($csv_array[$ac_hex]['l_lon']) && $ac_lon == '') { $csv_array[$ac_hex]['l_lon'] = ''; }
		    else if ($ac_lon != '') { $csv_array[$ac_hex]['l_lon'] = $ac_lon; }
		    if (!isset($csv_array[$ac_hex]['l_alt']) && $ac_altitude == '') { $csv_array[$ac_hex]['l_alt'] = ''; }
		    else if ($ac_altitude != '') { $csv_array[$ac_hex]['l_alt'] = $ac_altitude; }
            $ac_lat && $ac_lon ? $ac_dist = round(func_haversine($rec_lat, $rec_lon, $ac_lat, $ac_lon, $earth_radius), 1) : $ac_dist = '';
		    if (!isset($csv_array[$ac_hex]['l_dist']) || $csv_array[$ac_hex]['l_dist'] == '') { $csv_array[$ac_hex]['l_dist'] = $ac_dist; }
		    else if ($ac_dist != '' && $csv_array[$ac_hex]['l_dist'] > $ac_dist) { $csv_array[$ac_hex]['l_dist'] = $ac_dist; }
		    if (!isset($csv_array[$ac_hex]['h_dist']) || $csv_array[$ac_hex]['h_dist'] == '') { $csv_array[$ac_hex]['h_dist'] = $ac_dist; }
		    else if ($ac_dist != '' && $csv_array[$ac_hex]['h_dist'] < $ac_dist) { $csv_array[$ac_hex]['h_dist'] = $ac_dist; }
		    if (!isset($csv_array[$ac_hex]['l_rssi'])) { $csv_array[$ac_hex]['l_rssi'] = $ac_rssi; }
		    else if ($ac_rssi != '' && $csv_array[$ac_hex]['l_rssi'] > $ac_rssi) { $csv_array[$ac_hex]['l_rssi'] = $ac_rssi; }
		    if (!isset($csv_array[$ac_hex]['h_rssi'])) { $csv_array[$ac_hex]['h_rssi'] = $ac_rssi; }
		    else if ($ac_rssi != '' && $csv_array[$ac_hex]['h_rssi'] < $ac_rssi) { $csv_array[$ac_hex]['h_rssi'] = $ac_rssi; }
		    if (!isset($csv_array[$ac_hex]['mlat']) && $ac_mlat == '') { $csv_array[$ac_hex]['mlat'] = ''; }
		    else if ($ac_mlat != '') { $csv_array[$ac_hex]['mlat'] = 'mlat'; }
            $last_run = time() - strtotime('today');
		}
	}
	$seconds_of_day = time() - strtotime('today');
	#var_dump($csv_array);

    // 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),6),6) : $loop_clock = number_format(1, 6);
    $process_microtime = (round(1000000 * (microtime(true) - $start_loop_microtime)));
    print('upt(us): ' . sprintf('%07d', $process_microtime) . ' - ' . $loop_clock . ' loops/s avg - since ' . $runtime_formatted . ' - run ' . $i . ' => ' . sprintf('%04d', count($csv_array)) . ' aircraft(s) @ ' . array_sum(array_column($csv_array, 'msg')) . ' msg today ' . $db_insert . PHP_EOL);
    sleep(1);
    $i++;

}

?>
9 Likes

Tom,

Instead of sending an email, is it possible to set this up to update a spreadsheet on google drive using IFTTT?? Similar to this. Track Internet Dropouts and Notify Your ISP with RPi | Make:

ant73,

of course - you can use/alter/adapt the script in every way you want :slight_smile:

added your desired functionality - but with email as this is more standard and i cannot support each and every place where data should go to :wink:

Cool! I’ll check it out

Sendmail not include mail command, or I´m wrong? mail($user_set_array[‘email_address’], ‘Your Daily Aircraft Stats’, $body, $header);

“bash: mail: command not found”

php mail-function PHP: mail - Manual

Is there way to test out if mailing works, without waiting 24h?

comment out line 28 and 54 with a # and run the script in terminal for 3-5 seconds then it should send out some messages … another option is to start the mail test at 23:59 utc :)))

1 Like
sudo crontab -e
@reboot sleep 10 && /usr/bin/php /home/pi/ac_counter.php > /dev/null

this path has to match your setup

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

means running the script on a different pi than the one that is connected to antenna you have to set the ip-address to that pi. if you use fa sd-card or any differnt flavor of dump1090 then you have to use the dircetory path to ‘aircraft.json’ that matches your install e.g. for dump1090 flightaware sd-card

$user_set_array['url_json'] = 'http://127.0.0.1/dump1090-fa/data/aircraft.json';

then reboot the pi

1 Like

/run/dump1090-fa/aircraft.json
Still doesn’t work. I give up.
For now.

1 Like

why don’t you just do the steps above?

no idea what ‘/run/dump1090-fa/aircraft.json’ should give
no idea what ‘*/1 * * * * /usr/bin/php ac_counter.ph > /dev/null’ is meant to be
the script name is ‘ac_counter.php’ and not ‘ac_counter.ph’. the script is placed in home/pi not root. calling a script that endless runs every minute => tries to start an additional instance every minute???

there are just two steps - both you did different from howto and wrong …

That’s where my aircraft.json is located.
$user_set_array[‘url_json’] = ‘http://127.0.0.1/run/dump1090-fa/aircraft.json’;

I tried;
sudo crontab -e
@reboot sleep 10 && /usr/bin/php /home/pi/ac_counter.php > /dev/null

nothing happened.

*/1 * * * * /usr/bin/php ac_counter.php was to try and generate an email every minute to see if it worked.

the script send out ONE EMAIL PER DAY AT MIDNIGHT UTC what to happen in between you are waiting for?

if you want to try whether it will run the right way on your pi simply type in terminal

php /home/pi/ac_counter.php

for trial email - read above in thread Daily Log/E-Mail Aircraft Report directly on your RaspberryPi - #9 by TomMuc

Ok. I’ll let it run until 11am my time tomorrow and see what happens.

php /home/pi/ac_counter.php
PHP Warning: file_get_contents(http://127.0.0.1/run/dump1090-fa/aircraft.json): failed to open stream: HTTP request failed! HTTP/1.0 404 Not Found
in /home/pi/ac_counter.php on line 57
PHP Warning: Invalid argument supplied for foreach() in /home/pi/ac_counter.php on line 60
PHP Notice: Undefined variable: csv_array in /home/pi/ac_counter.php on line 91
PHP Notice: Undefined variable: csv_array in /home/pi/ac_counter.php on line 91
PHP Warning: array_column() expects parameter 1 to be array, null given in /home/pi/ac_counter.php on line 91
PHP Warning: array_sum() expects parameter 1 to be array, null given in /home/pi/ac_counter.php on line 91
upt(us): 0019920 - 1.000000000000 loops/s avg - since 0 days 00:00:00 - run(s) 0 => 0000 aircraft(s) @ messages today (UTC)

Tried that earlier. It didn’t work.

who told you http://127.0.0.1/run/dump1090-fa/aircraft.json ???

is it really that hard to read and copy

$user_set_array['url_json'] = 'http://127.0.0.1/dump1090-fa/data/aircraft.json';

Because /dump1090-fa/data/aircraft.json doesn’t exist.

pi@zeropi:~ $ sudo find / -name aircraft.json
/run/dump1090-fa/aircraft.json
pi@zeropi:~ $
pi@zeropi:~ $ php /home/pi/ac_counter.php
PHP Warning:  file_get_contents(http://127.0.0.1/run/dump1090-fa/data/aircraft.json): failed to open stream: HTTP request failed! HTTP/1.0 404 Not Found
 in /home/pi/ac_counter.php on line 57
PHP Warning:  Invalid argument supplied for foreach() in /home/pi/ac_counter.php on line 60
PHP Notice:  Undefined variable: csv_array in /home/pi/ac_counter.php on line 91
PHP Notice:  Undefined variable: csv_array in /home/pi/ac_counter.php on line 91
PHP Warning:  array_column() expects parameter 1 to be array, null given in /home/pi/ac_counter.php on line 91
PHP Warning:  array_sum() expects parameter 1 to be array, null given in /home/pi/ac_counter.php on line 91
upt(us): 0010164 - 1.000000000000 loops/s avg - since 0 days 00:00:00 - run(s) 0 => 0000 aircraft(s) @  messages today (UTC)

use a browser and type the url

http://IP_OF_YOUR_RASPBERRY_PI_RECEIVER/dump1090-fa/data/aircraft.json

what happens?

1 Like