FlightAware Discussions

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

#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

#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!

#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:

#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

#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: https://unix.stackexchange.com/questions/113981/where-does-mysql-store-its-databases-on-debian

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!