Saturday, December 17, 2016

Hamburg Hacker Friday - MotionEyeOS

Many thanks to JörgC for taking the initiative to organize our Friday afternoon get-together.

Here my quick notes, tipps to setup a security camera with Raspberry:

MotionEyeOS - Hardware Recommendations

  • get the Raspi3
  • 8MP cam modules side by side
    (infrared pic on right - without the filter)
    • Wifi already onboard
      (no need to buy a Wifi dongle)
    • more CPU Performance (RPI1 was constantly at 90+% load, RPI3 only 25%)
  • get the new 8MP camera modules (I bought one for day and the InfraRed for night shots)
  • power cord, SD Card, and a suitable case

MotionEyeOS - Basic Installation

  • download image from
    (it makes a difference...e.g. the RPi 3 Image is configured to use the 4 CPU cores)
  • write the Image to SD Card
    On Win10 make sure to launch with admin rights - shift-ctrl-click)
  • connect Raspi with LAN cable first, check IP address on your WLAN router
  • browse to \\ip-address\ and login with user admin (no password)
  • Basic configuration
  • MotionEyeOS configuration
    • Hostname
    • secure user+password (admin + viewer)
    • Wifi credentials
    • Resolution/Framerate (on RPi you can safely go to max Settings)
    • Do not use "automatic brightness" (did not work at all for me...)
    • now properly shut down
  • Now power up without LAN cable (should connect to your wifi, check on your Wifi router)
    • set fixed IP address for your MotionEyeCam
      (caution: LAN and WLAN have different MAC addresses...)
  • Access the recorded pictures/vids via Web-GUI, per FTP or per fileshare (\\ipaddress\)
  • More configuration
    • Email notification
    • upload to Dropbox or Google Drive
    • carefully tweak the sensitivity of the Motion detection...
    • create mask for motion detection (e.g. for trees or other "false triggers")

MotionEyeOS - Advanced Config

MotionEyeOS - Next Steps

  • use GPIO pins to launch actions when motion is detected (siren, LEDs, 1000-W-Laser...)
  • create scripts to automatically download/process the Pictures
  • create script to provide daily digest (with timeline and thumbnails)
  • add InfraRed lighting for better night shots

Further Reference

Good luck and fun with your new toy!

Sunday, December 4, 2016

My first steps with AWS Lightsail

It is Sunday evening, it is quiet in the house.
Perfect time to play with my new toys.

I just spun up my own virtual server on Amazon AWS Lightsail.
Just amazing how fast and easy that is...literally takes just a few minutes:
  • go to AWS Lightsail (with a valid AWS account)
  • pick your instance Image (plain OS with/without stack like Wordpress, NGINX...)
  • choose the plan (I picked the 5US$ one)
  • select a Name for your instance
  • click on CREATE
  • around a minute later you can access your own VPS via SSH in the browser. Whoa!

As I wanted to setup a simple web server and reverse Proxy I chose to manually install NGINX (the pre-packaged Comes with PHP and many other goodies I don't need. yet.).
  • install NGINX (sudo apt-get install nginx)
  • update the HTML pages in the default Location 
  • install GOACCESS.IO to analyze the web logs
  • setup a cron-job to update the GOACCESS html report every 10 minutes
  • create a DNS-record in my own Domain (
  • boom - ready: my own static Website hosted on my own virtual private Server!
After a few hours I can see the activity in the Lightsail Dashboard. It is alive!

And what took the longest? Exactly, the Content:

Picking a new HTML5 template and adding some text/content.

Setting up a reverse proxy with NGINX and port forwarding to another NGINX instance...

Finetuning the subdomain/Proxy configuration...

Let's see how this works out...maybe time to retire my Homepage that is still hosted on Google App Engine.

But then...Hosting a few static pages (and reverse proxying)

What's next?

Now, what else can I do with this little Server? My own Proxy/VPN to circumvent geo-fencing?

Terminating my ADS-B experiments....for good reason

I've been running my own ADS-B logger for some months now and I learned many things. And while I am excited about the "endless" possibilities, I have started to reach some limits with my experiments:

The Limits of Radio Reception

Hamburg Low Altitude out of range...
Yes, I was surprised how much reception I did achieve with that tiny antenna on my USB dongle. But it became very clear that I missed most of the arriving/departing flights over Hamburg at low altitude. To improve coverage I would have had to install a bigger antenna or simply joined/used the data feed from or And yes, I was too cheap to go for a 1090 amplifier/filter or that famous FR24 dongle.

The Limits of the Free-Tier on AWS RDS

I did upload my aggregated ADS-B data to a free Amazon mySQL instance (free tier, free for one year). Great learning experience, but as the data accumulated the CPU utilization did go up. I would have had to clean up the data, or to switch to a bigger instance. Again, I was too cheap to go spend money on a bigger DB that would essentially host only my data.

The Limits of my own Attention Span

The other effect I experienced was the lack of new challenges.

Yes, the ADS-B logging worked fine, the mySQL upload as well, the analytics with is fun.  So where do I go from here?

At work, I have access to full 2 years of FR24 data and some powerful analytics tools. That allows me to tinker and explore with massive aircraft data - much much more than I could ever handle (the 2 week data set for testing alone has 200Mio entries).

My old Raspberry PI was re-purposed as a surveillance camera with the fabulous MotionEyeOS package. Highly recommended..sure beats all the commercially available products out there (especially when you use the new 8MP camera module on an RPi3).

And Amazon AWS? I deleted my RDS instance (before I forget and incur cost).  Now I'm experimenting with AWS Lightsail...articles to follow shortly.

Keep on learning...!

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...