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?

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

Result:
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?

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

Who is running these busy planes? 

Apparently it's German Wings (GWI):

Query:
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
Query:
Select date, count(*), count(distinct hexident), count(distinct sign),
max(alti), min(alti), max(speed), min(speed) from flights group by date;
Result:
Highest/Lowest/Fastest/Slowest

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 know...you can also have access to the mySQL data and experiment yourself...






















No comments:

Post a Comment