Sunday, March 20, 2016

Little Error in my script...remembers too much data.

Remember my approach to store aircraft data (flight number, altitude,...) in an array?
Works well to "remember" such data to complete ADS-B records.

Now I found out that it works a little too well - especially when it remembers the last flightnumber from hours ago. takes quite a few ADS-B blips to update the current flight number. 

What's the impact? You can see it below.... I record 2..3 blips with the old flight number EWG16U from 13:43 when the current flight at 17:29 is now EWG95U. 

I guess I have to improve my script to "forget" this data after a few minutes of having received no blips. Stay posted for new version of my scripts...

Carry-Over of Flight Number

Monday, March 14, 2016

Geographical Distance Calculation - Haversine

My first quick&dirty distance calculation was okay...but not good:

After a bit of research of converting Latitude/Longitude I finally found somesimple code for the Haversine function at

Pressed the code into a query like this and executed in mySQL workbench:

#Haversine SQL function pressed into a query
#code adapted from

SET @myLat := '53.465';
SET @myLon := '09.697';

select logdate, loghexid, logsign, altitude, latitude, longitude,

round(3959 * 2 * ATAN2( SQRT( 
pow(sin((RADIANS(latitude) - RADIANS(@myLat))/2),2) + 
pow(sin((RADIANS(longitude) - RADIANS(@myLon))/2),2) * COS(RADIANS(longitude)) * COS(RADIANS(@myLat))
), SQRT( 1 - 
pow(sin((RADIANS(latitude) - RADIANS(@myLat))/2),2) + 
pow(sin((RADIANS(longitude) - RADIANS(@myLon))/2),2) * COS(RADIANS(longitude)) * COS(RADIANS(@myLat))
))) as HaversineDistance

from flightlog
where logdate=curdate();

Exported the data to a CSV and mapped on - viola!

ADS-B blips color-coded by distance

Next step: create the mySQL function so the code in the query is a bit less comples...

Good night!

Sunday, March 6, 2016

ADS-B Filtering by Latitude/Longitude SQL query

Now that my scripts are collecting ADS-B data to mySQL it's time to learn more SQL queries.

How can I find the flights furthest away from me?
I simply let mySQL calculate the distance - with simplified Pythagoras (a² + b² = c²), assuming that Latitude and Longitude are somewhat similar...


select logsign, altitude, 
latitude, (latitude-53.46) as LatDiff, 
longitude, (longitude-9.69) as LonDiff,
sqrt((latitude-53.46)*(latitude-53.46) + (longitude-9.69)*(longitude-9.69)) as LatLonDistance
from flightlog
order by sqrt((latitude-53.46)*(latitude-53.46) + (longitude-9.69)*(longitude-9.69)) desc
limit 100;

Resulting Data  (using mySQL workbench)

Query Result

Resulting Map (using

Now mapping the resulting data on a map gives me this nice picture. As you can see, Latitude and Longitude are not perfectly square - resulting in a nice oval cut out in the middle....


New Aircraft Flight Tracks

Just trying the CSV files from my revised ADS-B data collection scripts.
Went to CARTODB.COM and had a quick map with yesterdays data...

Had to name the field columns, as my CSV doesn't have a nice header anymore...

Flights by Altitude...colors multiplied

NEW & IMPROVED: ADS-B Data Collection

My first scripts worked well - see post at

But...after installing Dump1090 Mutability the script often stopped working.
Also noticed some SQL errors. Time for some debugging and better error handling.

Here are my revised DUMP2SQL scripts

Topology - Data Flow

Main improvements:

  • Original script often stopped when Netcat did not receive data - fixed with another simple loop around. Added some logging to see how often this happens....
  • Now include data without a callsign - and immediately tracked a sail plane in the area...  :-)
  • Fixed SQL errors from empty fields - now passing "NULL" as value
  • Segregated the data collection from mySQL upload - to avoid missing ADS- messages when busy with. This way you can trigger the mySQL insert every few minutes by Crontab and not for every single message.
  • Added mySQL error handling and logging. SQL files are deleted when insert was successful (CSV data remains untouched).
  • Changed the SQL table layout (not a good idea to use reserved names for columns such as "index")

All the files-....

Results? Working absolutely fine!

No more missed ADS-B messages (averaging now 120.000 per day instead of 80.000).
No more SQL errors.
No more missed sail planes or helicopters (that fly without a callsign).

Collected ADS-B data in both CSV and SQL format  (main data aggregation script)

Main purpose of this script is to collect all ADS-B messages, and aggregate them to complete missing fields (e.g. to carry-over the call-sign) and to save data only when location is known.

Launch this script at startup with crontab (sudo crontab -e) and insert a line:
@reboot /home/pi03/ >/dev/null &

#Shell script to listen parse and aggregate ADSB data
#Run in foreground to see progress bar
#run  in background with: >/dev/null &

#variables DUMP1090 server
#variables  mySQL credentials...use certificates in next version
#now declare the arrays
counter=0  #for loop control
declare -a arr_call    #collect call_signs per aircraft
declare -a arr_alti    #collect altitude per aircraft
declare -a arr_sped    #collect groundspeed per aircraft
declare -a arr_trck    #collect track per aircraft
declare -a arr_vert    #collect vertical speed per aircraft
TimestampDaily=$(date +"%Y-%m-%d")        #separate logfile every day
TimestampMinut=$(date +"%Y-%m-%d-%H-%M")   #separate SQL file every minute
ADSB_log="/home/pi03/adsb-log.txt"   #status log -
ADSB_csv="/home/pi03/adsb-csv-"   #data capture - collected per day - append TimestampDaily
ADSB_sql="/home/pi03/adsb-sql-"   #SQL statements - append TimestampMinut

#Startup Messages
echo "DUMP1090 Aggregator2mySQL by Matthias Gemelli 2016"
echo "Listening to DUMP1090 on $ADSBhost port $ADSBport"
echo "Writing Logs to $ADSB_log"
echo "Writing Data to $ADSB_dat"
echo "Writing SQL  to $ADSB_sql"
echo "SQL insert to $SQLsrv"
echo "--------------------------------"

#Startup log
Timestamp=$(date +"%Y-%m-%d %H:%M:%S")
echo "Dump2SQL launched with PID $$ at $Timestamp" >> $ADSB_log
echo "MsgCount,HexIdent,Date,Time,Lat,Long,Callsign,Altitude,Speed,Track,Vertical" >> "$ADSB_csv                                                                                                 $TimestampDaily.csv"
echo "select count(*) from flights;" >> "$ADSB_sql$TimestampMinut.sql"

#----------------------LOOP starts------------------------
while true; do #outer loop - because netcat stops too often with Dump1090 mutability
#inner loop - netcat listener
nc -d $ADSBhost $ADSBport | while IFS="," read -r f1 f2 f3 f4 f5 f6 f7 f8 f9 f10 f11 f12 f13 f14                                                                                                  f15 f16 f17 f18 f19 f20 f21 f22
do     #loop until a break is thrown or netcat stops

#first update the timestamp for log files
TimestampDaily=$(date +"%Y-%m-%d")        #separate logfile every day
TimestampMinut=$(date +"%Y-%m-%d-%H-%M")  #separate SQL file every minute

#relevant data fields in every ADSB record
#echo "Field 05 HexIdent         :$f5"
#echo "Field 07 Date message gen :$f7"
#echo "Field 08 Time message gen :$f8"
#echo "Field 11 Callsign         :$f11"
#echo "Field 12 Altitude         :$f12"
#echo "Field 13 GroundSpeed      :$f13"
#echo "Field 14 Track            :$f14"
#echo "Field 15 Latitude         :$f15"
#echo "Field 16 Longitude        :$f16"
#echo "Field 17 Vertical Rate    :$f17"

#now save the data into array, using HexIdent as index
#overwrite only if field is not empty
ident=$((0x${f5}))   #convert hex to decimal
if [ "$f11" != "" ];  then arr_call[ident]="$f11"; fi
if [ "$f12" != "" ];  then arr_alti[ident]="$f12"; fi
if [ "$f13" != "" ];  then arr_velo[ident]="$f13"; fi
if [ "$f14" != "" ];  then arr_trck[ident]="$f14"; fi
if [ "$f17" != "" ];  then arr_vert[ident]="$f17"; fi
#write default values - important for SQL insert
if [ "${arr_call[ident]}" = "" ]; then arr_call[ident]="unknown"; fi
if [ "${arr_alti[ident]}" = "" ]; then arr_alti[ident]="NULL"; fi
if [ "${arr_velo[ident]}" = "" ]; then arr_velo[ident]="NULL"; fi
if [ "${arr_trck[ident]}" = "" ]; then arr_trck[ident]="NULL"; fi
if [ "${arr_vert[ident]}" = "" ]; then arr_vert[ident]="NULL"; fi

#debugging and progress bar - display RAW-before - AGG-after aggregation data
#echo "Debug for $f5 Altitude $f12-${arr_alti[ident]} Flight $f11-${arr_call[ident]}"
#progress bar  X for every location, DOT for every ADSB-message
#if [ "$f15" != "" ]; then  printf "x"; else printf "."; fi

#----------save ADSB data only if location is known--------
#----------pointless to write speed...altitude without location
if [ "$f15" != "" ]; then  #if Latitude is not empty
echo "Position Point received for $f5 ${arr_call[ident]} at alt ${arr_alti[ident]}"

#---------write CSV data and SQL commands to file-------
#echo "MsgCount,HexIdent,Date,Time,Lat,Long,Callsign,Altitude,Speed,Track,Vertical" >> "$ADSB_cs                                                                                                 v$TimestampDaily.csv"
echo "$CSV" >> "$ADSB_csv$TimestampDaily.csv"

QUERY="INSERT INTO esp8266data.flightlog "
QUERY="$QUERY (logmsg, loghexid, logdate, logtime, latitude, longitude, "
QUERY="$QUERY logsign, altitude, speed, track, vertical) VALUES "
QUERY="$QUERY ($counter,\"$f5\",\"$f7\",\"$f8\",\"$f15\",\"$f16\","
QUERY="$QUERY \"${arr_call[ident]}\",${arr_alti[ident]},${arr_velo[ident]},"
QUERY="$QUERY ${arr_trck[ident]},${arr_vert[ident]});"
echo "$QUERY" >> "$ADSB_sql$TimestampMinut.sql"

#instead of inserting every separate blip I collect into a SQL file and run in batches
#mysql -h $SQLsrv -u $SQLusr -p$SQLpwd $SQLdbs <$SQLqueries
#if [ $? != 0 ]; then echo "error: $QUERY" >> $ADSB_log ; fi

fi #if position is not empty

#reset the array if it is midnight (fewer planes)
((counter++))   #increase the loop counter
done            #netcat listener loop
Timestamp=$(date +"%Y-%m-%d %H:%M:%S")
echo "Dump2SQL Netcat stopped...Re-Launch with PID $$ at $Timestamp" >> $ADSB_log

done            #outer loop
#-----------------end of the loops----------------
#attention: variables set within the loop stay in the loop

echo "Done for the day..."
echo "Dump2sql done" >> $ADSB_log
echo $(date +"%Y-%m-%d %H:%M%S") >> $ADSB_log  (insert the data to mySQL)

Main purpose of this script is to insert the flight data into a mySQL database using the mysql-client software package. It automatically looks for the SQL files generated with the dump2sql script and only processes the ones older than one minute (to prevent processing files that are still being written to).
Launch this script every 5 minutes startup with crontab (sudo crontab -e) and insert a line:
*/5 * * * * /home/pi03/ >/dev/null

#variables  mySQL credentials...use certificates in next version
SQL_log="/home/pi03/mysql-log.txt"   #status log -
Timestamp=$(date +"%Y-%m-%d-%H-%M")   #separate logfile every day

#Now cycle through all SQL files and process them with mySQL
for f in $FILES
  #echo "Processing $f file..."
  #ls -ls $f
  Timestamp=$(date +"%Y-%m-%d-%H-%M")
  #now check if file is old enough to touch
  if [[ $(find $f -mmin +1) != "" ]]
        then #file found that is older as one minute
        #echo "old $f"
        #now launch SQL command
        SQLout=$(mysql -h $SQLsrv -u $SQLusr -p$SQLpwd $SQLdbs <$f 2>&1)
        if [ $SQLerr != 0 ];
                then  #SQL error
                echo "Error in $f with $SQLout" >>$SQL_log
                cp $f /home/pi03/err$f     #copy the error SQL file
                rm $f  #delete the original file
                echo "Successfully Processed $f" >>$SQL_log
                rm $f   #delete SQL file
                fi  #SQL error
        else #file is fresh - dont touch it
        echo "Ignored fresh file $f" >>$SQL_log
        fi #find mmin1 - check for file age


Good Luck! Feedback and improvement ideas very welcome!

Sunday, February 21, 2016

The magic of SQL queries on my flight data collection....

If you have read my other post you know that I am logging all received ADS-B "squirts" into a mySQL database. On average I collect around 50.000 complete entries per day (with callsign, altitude, speed, position, track, date/time).

I can retrieve all data with a simple "select * from flights;"

Table Structure & Data
After one week of data collection I have collected 400.000 entries in my database.
Now let's explore this data and learn some SQL query language at the same time.

How many Aircraft and Flights each day?

SELECT date, count(distinct hexident), count(distinct sign) FROM flights GROUP BY date;

Unique/Distinct Aircraft and Flights
You can clearly see that Aircraft are serving multiple flights each day - e.g. arriving with Lufthansa 001 and departing with Lufthansa 002.

Which Aircraft are racking up most flights?

Select distinct hexident,count(distinct sign) from flights
group by hexident order by count(distinct sign);
Number of flights per Aircraft HexID

Who is running these busy planes? 

Apparently it's German Wings (GWI):

select distinct sign, hexident from flights
where hexident like '3C5EF%' order by hexident;

German Wings keeps its planes busy...

The fastest/slowest/highest/lowest each day?

Let's focus on the the highes/lowest/fastest/slowest flights with
Select date, count(*), count(distinct hexident), count(distinct sign),
max(alti), min(alti), max(speed), min(speed) from flights group by date;

And I can look for the specifics:

  • Flights below 0 feet altitude? Just a few blips from nearby Airbus plant Finkenwerder...pilots are either diving in the river or have not set proper altitude...
  • Flights above 44.000 feet altitude? AOJ87D a Swiss Jet? , FPG111 - a Dassault Falcon? , MMD6424 - another Dassault Falcon? It must be nice to look down from your own private jet.
  • Flights slower than 100 (knots?) - Who is CHX26? Looks like I tracked a helicopter?
  • Flights faster than 600 (knots?) - Looks like aircraft passing by (Air India, Air Canada...)

The furthest away?

Let's look for the flights that are furthest away... with this query:
select date, count(*), min(lat), max(lat), min(lon), max(lon) from flights group by date;

Furthest Flights
I could do some math (difference in Lat/Long to my position), but I am trying to keep it simple.

What interesting SQL queries can you think of?

If you have some ideas let me can also have access to the mySQL data and experiment yourself...