two weeks ago i was searching for a tool to save flights within a selectable range (lat, lon, altitude). because i did not find what i was looking for - so today wrote a short php-script. my coding skills a quite limited but anyways maybe somebody has use for it 
the script uses the aircraft.json file that every second drives the skyview map - extracts the data according to altitude, latitude and longitude settings and writes to database (mysql or sqlite).
go to latest version here: Dump1090 MySQL Database Script - E-Mail-Alert Lat/Lon/Alt/Hex/Flight-Filter - #23 by TomMuc
<?php
#phpinfo();
#var_dump(ini_get_all());
#ini_set('error_reporting', E_ALL);
# user settings
$max_lat = 40.000000; $min_lat = 50.000000; $max_lon = 12.000000; $min_lon = 10.000000; $max_alt = 40000;
$db_name = "adsb.db"; $db_host = "127.0.0.1"; $db_user = "root"; $db_pass = "password";
$url = "http://192.168.1.0/dump1090/data/aircraft.json";
date_default_timezone_set("Europe/Berlin");
$i = 0;
$start_time = time();
while (true) {
$x = 0;
$sql = "";
$json_data_array = json_decode(file_get_contents($url),true);
foreach ($json_data_array['aircraft'] as $row) {
if (isset($row['altitude']) and $row['altitude'] < $max_alt and isset($row['lat']) and $row['lat'] < $max_lat and $row['lat'] > $min_lat and isset($row['lon']) and $row['lon'] < $max_lon and $row['lon'] > $min_lon) {
$sql .= "INSERT INTO aircrafts VALUES (NULL, '";
$sql .= (isset($json_data_array['now']) ? date("Y-m-d G:i:s l",$json_data_array['now']) : NULL) . "', '";
$sql .= (isset($json_data_array['now']) ? $json_data_array['now'] : NULL) . "', '";
$sql .= (isset($row['hex']) ? $row['hex'] : NULL) . "', '";
$sql .= (isset($row['flight']) ? trim($row['flight']) : NULL) . "', '";
$sql .= (isset($row['altitude']) ? $row['altitude'] : NULL) . "', '";
$sql .= (isset($row['lat']) ? $row['lat'] : NULL) . "', '";
$sql .= (isset($row['lon']) ? $row['lon'] : NULL) . "', '";
$sql .= (isset($row['track']) ? $row['track'] : NULL) . "', '";
$sql .= (isset($row['speed']) ? $row['speed'] : NULL) . "', '";
$sql .= (isset($row['vert_rate']) ? $row['vert_rate'] : NULL) . "', '";
$sql .= (isset($row['seen_pos']) ? $row['seen_pos'] : NULL) . "', '";
$sql .= (isset($row['seen']) ? $row['seen'] : NULL) . "', '";
$sql .= (isset($row['rssi']) ? $row['rssi'] : NULL) . "', '";
$sql .= (isset($row['messages']) ? $row['messages'] : NULL) . "', '";
$sql .= (isset($row['category']) ? $row['category'] : NULL) . "', '";
$sql .= (isset($row['squawk']) ? $row['squawk'] : NULL) . "', '";
$sql .= (isset($row['nucp']) ? $row['nucp'] : NULL) . "', '";
$sql .= (isset($row['mlat']) ? implode(" ", $row['mlat']) : NULL) . "', '";
$sql .= (isset($row['tisb']) ? implode(" ", $row['tisb']) : NULL) . "');\n";
$x++;
}
}
$db = new PDO("mysql:host=$db_host;dbname=$db_name", "$db_user", "$db_pass");
#$db = new SQLite3("$db_name");
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 datasets inserted\n");
unset($sql);
sleep(1);
$i++;
}
?>
p.s.
here is a sample to create the table (for mysql you have to set ‘id’ 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)
here is what i found about aircraft.json on github. the description of ‘category’, ‘mlat’ and ‘tisb’ datafields is missing. dump1090-mutability Oliver Jowett @ GitHub - mutability/dump1090: Dump1090 is a simple Mode S decoder for RTLSDR devices
This file contains dump1090's list of recently seen aircraft. The keys are:
* now: the time this file was generated, in seconds since Jan 1 1970 00:00:00 GMT (the Unix epoch).
* messages: the total number of Mode S messages processed since dump1090 started.
* aircraft: an array of JSON objects, one per known aircraft. Each aircraft has the following keys. Keys will be omitted if data is not available.
* hex: the 24-bit ICAO identifier of the aircraft, as 6 hex digits. The identifier may start with '~', this means that the address is a non-ICAO address (e.g. from TIS-B).
* squawk: the 4-digit squawk (octal representation)
* flight: the flight name / callsign
* lat, lon: the aircraft position in decimal degrees
* nucp: the NUCp (navigational uncertainty category) reported for the position
* seen_pos: how long ago (in seconds before "now") the position was last updated
* altitude: the aircraft altitude in feet, or "ground" if it is reporting it is on the ground
* vert_rate: vertical rate in feet/minute
* track: true track over ground in degrees (0-359)
* speed: reported speed in kt. This is usually speed over ground, but might be IAS - you can't tell the difference here, sorry!
* messages: total number of Mode S messages received from this aircraft
* seen: how long ago (in seconds before "now") a message was last received from this aircraft
* rssi: recent average RSSI (signal power), in dbFS; this will always be negative.
if some want to run this direct on raspi - here is the php install:
sudo apt-get update
sudo apt-get install php5-common php5-cgi php5-mysql php5-sqlite php5
sudo lighty-enable-mod fastcgi
sudo lighty-enable-mod fastcgi-php
sudo service lighttpd force-reload
sudo shutdown -r now
php -i
good gui-interfaces to sqlite and mysql:
sqlite browser: http://sqlitebrowser.org
mysql sequelpro: https://www.sequelpro.com
mysql workbench (i’m not a too big fan of): MySQL :: Download MySQL Workbench
cheers
tom
p.p.s. here running on my mac-mini media-server
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?
