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

FlightAware ADS-B Flight Tracking
42

My network consists of an RPi3 hosting Piaware and a Ubuntu laptop hosting my webserver and a MySQL database.

I attempted to install the radar.php and mailer.php on the laptop, which went fine until I started to configure and test the email alerts.

After a failed attempt on the Ubuntu laptop I moved to the RPi3 but got similar result and no alerts via GMail. A search advised to install ssmtp as well as sendmail and once this was done and configures for GMail the email alerts started to work.

Having done the same config on the Ubuntu machine, that to also works.

Thanks for the scripts and your time and patience.

43

did you download the radar.php from 2017_09_12 23:54 UTC - because as i wrote i found another little bug with the bracketing - that was only on flightaware and github?

what i do not understand how just one email was sent out if there was missing software? as i wrote above i tried to reproduce your problems and downloaded a fresh raspbian jessie image → made all installs the way my howto describes - and the only missing software was sendmail …

anyways - happy that now anything is fine and works at your site too :slight_smile: especially as i added the hex/flight filter just for you

44

moved the code above in thread to final version.

go to latest version here: Dump1090 MySQL Database Script - E-Mail-Alert Lat/Lon/Alt/Hex/Flight-Filter - #23 by TomMuc

1 Like
45

So I have this installed but it is not catching any data.

I started by getting it running on my Pi and when it did not catch any data there I started with a clean Debian9 VM running on one of my servers. I changed the feed to come from the IP address of my Pi (it has 1090dump running). Still not getting any data even though I started from a clean slate. So I know I have screwed something up, I just don’t know what and I don’t know where to start troubleshooting.

I can netcat <ip_address_of_pi> 30003 and see tons of data streaming through from the VM and from the pi so I know the data is there, it is just that I missed something (probably in radar.php). I have defined my max & min lat/lon and altitude to cover the area that my sensor covers.

// set the rectangle and altitude to store aircraft-data in database
$user_set_array[‘max_lat’] = 41.748980; $user_set_array[‘min_lat’] = 39.637634; $user_set_array[‘max_alt’] = 14000;
$user_set_array[‘max_lon’] = -88.577271; $user_set_array[‘min_lon’] = -83.655396;

and I have defined the aicraft.json source as:

// set path to aircraft.json file
$user_set_array[‘url_json’] = ‘http://10.100.100.30/dump1090-fa/data/aircraft.json’;

php radar.php yields:

process-time(us): 0011491 - 1.000000000000 loops/s avg - since 0 days 00:00:02 - run(s) 2 → 000 dataset(s) =>
process-time(us): 0009928 - 1.000000000000 loops/s avg - since 0 days 00:00:03 - run(s) 3 → 000 dataset(s) =>

I do not have any of the limiting files in the directory of radar.php to attempt to capture everything as an initial test.

What should my next step troubleshooting be?

46
  • what do you get when you call http://10.100.100.30/dump1090-fa/data/aircraft.json from the machine where radar.php is running with wget, curl or browser
  • if you see there the aircraft.json data - are there aircrafts within the lat/lon/alt range you set up in the script?
  • then try $user_set_array[‘max_lon’] = -83.655396; $user_set_array[‘min_lon’] = -88.577271;

btw. i never tested this with the dump1090-fa as none of my feeders runs on the sd-card-image. but the json files hopefully should be the same …

47

The URL shows me real data such as:

{ “now” : 1506603164.6,
“messages” : 48932480,
“aircraft” : [
{“hex”:“a77d89”,“altitude”:20050,“mlat”:,“tisb”:,“messages”:7,“seen”:10.3,“rssi”:-11.2},
{“hex”:“ad63ec”,“squawk”:“2306”,“altitude”:16725,“mlat”:,“tisb”:,“messages”:47,“seen”:17.0,“rssi”:-10.2},
{“hex”:“a7b133”,“altitude”:30000,“vert_rate”:-64,“track”:334,“speed”:372,“mlat”:,“tisb”:,“messages”:8,“seen”:7.5,“rssi”:-9.8},
{“hex”:“c01aab”,“altitude”:30850,“mlat”:,“tisb”:,“messages”:17,“seen”:1.7,“rssi”:-10.3},
{“hex”:“a0a80e”,“flight”:"SKW5340

I looked for matches that fall within my Lat/Lon/Alt parameters in the stream and found this:

{“hex”:“a8df4b”,“lat”:41.600011,“lon”:-82.975201,“nucp”:0,“seen_pos”:0.4,“altitude”:13000,“vert_rate”:-1920,“track”:331,“speed”:355,“mlat”:[“lat”,“lon”,“track”,“speed”,“vert_rate”],“tisb”:,“messages”:813,“seen”:0.4,“rssi”:-9.3},

Switching out the order of the LON array parameters is yielding data, into the database that was the ticket.

For my next trick - I will work on getting FlightAirMap working on this server too.

Thank you for your help TomMuc!

2 Likes
48

@brainstomp

no big deal - happy that its working now for you. it’s a bit a sticking point for those with negative lon to be aware of that 0 is bigger than -1 aka -83.655396 is bigger than -88.577271. so i’ll add a hint in the above comment line :slight_smile:

2 Likes
49

this thread was about enhancement and debugging the script and got a little too long and confusing now. because of that change over to here: Dump1090 MySQL Database Script with Alert and Filter

1 Like
50

Get this error

SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn’t match value count at row 1
upt(us): 0006537 - 1.000000 loops/s avg - since 0 days 00:00:47 - run 47 @ 9,8 msg/s → 001 dataset(s) => db-error

51

it’s self-explaining your database-table does not fit …

52

OK? how to fix that?

53

Tom. Great stuff, thank you. I got it up and working with dump1090-mutability. It is pushing data to mysql database. I cannot get the email notification to work however, I am using gmail.

secret_email_key → Is this my gmail password? That is what I put however it is not sending anything. Are there any log functions? How can I troubleshoot?

54

Actually I figured it out. Looks like it was a dns issue. You need to use a real dns name when sending email, not localhost LoL

57

Nice program @TomMuc
I have gone up a very steep learning curve to get to the point where “SELECT * FROM aircrafts;” gives me a table with actual data. :slight_smile:
The next questions are based on moving from a small scale environment to collecting on going data in an ever expanding data-set

  1. how do I run the program from the command line on a Raspi 3b
  2. what modifications do I need to make to the script to divert the output to a hard disk attached to the USB port
    3 What software do you recommend to analyze the SQL data
    Sorry if these are noobie questions
58

There is a new thread on a more current version and installation on raspberry pi located here:

googling MySQL storage location debian brought this up: Where does MySQL store its databases on Debian? - Unix & Linux Stack Exchange

You might want to format the drive with ext4 and mount it via fstab
This seems like a good guide: https://www.theurbanpenguin.com/adding-external-storage-to-your-raspberry-pi/

Question number 3 i have no clue :slight_smile:

59

Thanks for you help!

60

I need help, I have zero programming knowledge. Just want a simple program for raspberry pi that will email me one specific plane if it’s detected n108rt hex A023F5. Running raspberry pi Debian GNU/Linux 12 (bookworm)" with 1090dump-fa. If you can please tell me in simplest terms how to set this up

61

@biloxicoast2010

This Python script was slapped together as a possible answer and is untested. Let me know if it works or not. If it does not work, it should at least get you started in the right direction. If you need some help getting it working let me know here and we will work on getting it going for you.

This Python script does not use MySQL but instead looks directly to dump1090’s JSON output. This should make it capable of running on any device with dump1090 installed on it and not just those storing data within MySQL. It should not require additional resources other than Python which is generally installed by default on Raspberry Pi OS as well so there is no need for additional PHP packages or PHP email scripts.

Email someone when an aircraft is seen on dump1090. (github.com)

Be sure to fill in the “SMTP Server Settings” as well as modify the “Aircraft Hexes” list. The list can contain one or more ICAOs.

All data made available by dump1090 is there. Use aircraft_data[…] with the proper data point NAME in aircraft.json to display the VALUE desired with the email body or subject. Refer to http://<YOUR_RECEIVER_IP>/dump1090/data/aircraft.json to see available data.

62

Thanks so much. I will try it later. Do what do I do with the script? Copy and paste in somewhere and I only need to fill out the hex and email part? SMTP s as if my email address password?