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