Sky Dolly v0.10 has just been released, refer to the official announcement here:
While you can do all sorts of (visual) analysis with the exported files - CSV in flightradar24.com format, GeoJSON, GPX, … - sometimes you may want to go a bit more “after the actual numbers”. Perhaps.
Workshop
About
So here is a little “fun workshop” about “things you can do beyond the Sky Dolly user interface”. The idea is to dig in right into the logbook, respectively the actual database. Yes, that’s right: the logbook is an actual database - an SQLite file-based database, to be specific - from which you can extract and process the data from… with SQL!
“SQL? Me? But… I am not a programmer!” - okay, okay, let’s define our main goal first: the goal here is not to make you fluent in SQL. Which by the way stands for “structured query language”. You might not even understand the actual examples that I am going to show, how and why they work etc.
Goal
The goal here is to give you some ideas what is possible, and if you are interested in learning more there are excellent SQL tutorials out there! After all Sky Dolly is open source, and so is its database format (also known as “schema”) and you may have a brilliant idea what you could do with it.
That being said… let’s start!
Required Tools
The easiest way to “browse a database” is probably by using a (graphical) user interface which allows us to easily “discover” all “tables” (we’ll come to that in a minute) while also offering a way to “execute queries”. I am using the application DB Browser for SQLite, available here:
It is available for several platforms, among them Windows, macOS and Linux. Install the tool to be able to execute the below examples and continue with your own experiments. Otherwise just keep on reading…
The Logbook
So what is the “logbook”? In Sky Dolly it appears e.g. like this:
If your logbook is still empty then now would be a good time to record a few flights first, such that we have some actual data.
How do we find the location of the logbook? That’s easy:
- Open File | Logbook Settings…
- Click on the Show button (next to the logbook folder path)
The file explorer should open, showing the folder with a file named Sky Dolly.sdlog (by default). You may also have a Backup subfolder already.
Backup First…
Now is also a good time to ensure that we have an up-to-date backup of the logbook. In that same Logbook Settings dialog:
- Select Period: Next time, when exiting Sky Dolly
- Confirm with OK
- Quit Sky Dolly (CTRL + Q)
- A backup dialog should now open: execute the backup by clicking the Backup button
Open the Logbook with the DB Browser
In general it is a good idea to access the logbook (database) with only one application at the same time. While it is generally not a problem if we only browse (“read only”) the database with one application while the other one (Sky Dolly) is also writing data you may end up locking one or the other application when both applications try to modify the database at the same time.
So now that Sky Dolly is closed (and we have an up-to-date backup, just in case…) launch the DB Browser for SQLite. While the file extension of the Sky Dolly is *.sdlog (“Sky Dolly Logbook”) it is really an SQLite database file (typically having the extension *.sqlite). And we can open it as follows in the DB Browser:
- File | Open Database… and browser to the folder containing Sky Dolly.sdlog or
- Simply drag and drop Sky Dolly.sdlog onto the DB Browser
In either case we should now be seeing something like:
I have marked the areas of interest to us in this tutorial:
- The Database Structure shows us the elements of the database, most importantly the “tables”
- We can browse the data of any given table in the Browse Data tab
- Finally we can execute our own SQL statements in the Execute SQL tab
SQL - A Primer
The goal here is to get acquainted with the basics of databases and SQL, essentially in order to be able to execute and “follow the idea” of the upcoming examples.
- Table - a table is the most important object in a database: it allows us to organise our data. For instance you may have a table containing persons (name, familiy name, birthdate, …), and a table listing all possible hobbies in the world (hobby name, …), and yet another table linking the persons with one or several hobbies (person, hobby)
- Entries in those tables are also called records. Each record typically has one (or several) “identifer” - or ID in short - which uniquely identifies that record in the given table
- A record may also “point to” another record in another table. For such a “1 : n” relationship this is typically done by simply storing the “foreign ID” in the table as well (we will see this with a practical example below)
- SQL - the structured query language is a standardised language which allows us to select (and also update - but we deliberatley won’t cover this here) one or several records, either from one or possibly multiple tables (the data from the various tables is then joined).
- The most important concept in a SELECT statement is the WHERE-clause which allows us to define one or multiple selection criteria (“Get me all the data having the following attributes…”)
- The second most important concept is the FROM clause which specifies the involved tables from which we want to query the data
- In short, the SELECT … FROM … WHERE … is the “skeleton” of the majority of all SQL queries out there
Sky Dolly - Most Important Tables
Before we start with an actual SQL query let’s get a bit familiar with the Sky Dolly specific tables. Still on the Database Structure tab in the DB Browser we see all tables that Sky Dolly is using. The most important ones:
- flight - contains common data such as creation_data, title and description of each recorded flight, as well as initial ground altitude (of the airport), visibility and other weather related conditions
- aircraft - contains the flight number, airline name, tail number and a few other “static data”
- position (and similar tables) contain the actual recorded position data (latitude, longitude, altitude) and velocity
- A couple of “house keeping” related tables, but we won’t look into those in this tutorial
Browsing the Tables
We now switch to the Browse Data tab in the DB Browser. Note that I tend to CAPITALISE table and column names, for readability. There are different “SQL style guides”, but for all practical matters SQL is case-insensitive. So if you see me writing “id” instead of “ID” - that’s just me being inconsequential, but it means the same thing (column).
- In the Table dropdown list select the FLIGHT table
Does this look familiar? Yes, it resembles the Logbook in Sky Dolly itself. In fact, that is the table the Logbook in Sky Dolly is querying its exact data from. And we take note that the table ID is the exact same “Flight” number in Sky Dolly (the first column in the Sky Dolly logbook table).
We may repeat this browsing with the following tables:
- AIRCRAFT - contains “static aircraft data”
We note that besides the (unique) ID for each aircraft each record also contains a so-called “foreign key”, or ID, referring to another table. Here, the previous FLIGHT table to be specific. And the key (or identifier) is called FLIGHT_ID - do those numbers in that AIRCRAFT.FLIGHT_ID column look familiar? That is because those are the exact same IDs that we have seen in the previous FLIGHT table already (and in the Sky Dolly logbook).
If you have ever recorded a formation flight - a flight with multiple aircraft - then you may find multiple aircraft having the same FLIGHT_ID. Well, that is exactly because they “belong” to the same flight (as stored in table FLIGHT). In order to distinguish those aircraft for the given FLIGHT ID each aircraft additionally as a sequence number (column name: SEQ_NR).
All other AIRCRAFT columns should be self-describing.
Lastly, let us have a quick look at one of the tables which contains the actual sampled data:
- POSITION - contains the recorded positions and velocities
The columns mostly reflect the corresponding “simulation variables” as received via the SimConnect API from MSFS (including some data transformations, in order to reduce storage space where applicable).
All data tables have the TIMESTAMP in common, which stores the point in time in milliseconds the data has been recorded (“sampled”) since the beginning of the recording (of the given aircraft). Speaking of which: the position data always belongs to a given aircraft (as stored in table AIRCRAF), again identified by column AIRCRAFT_ID.
But wait - why isn’t there a unique identifer, a column named ID (in table POSITION, and also the other “sample data tables” like ENGINE etc.)? Well, there is a unique identifier already, and we just stumbled over it already: it is called TIMESTAMP! Yes, each position data (and each engine data…) has a unique timestamp (as a consequence, if MSFS sends us multiple values for the same timestamp then “the last one wins”: existing entries are then simply updated) - so the TIMESTAMP also acts as a unique key to identify each data record.
Okay, enought gray theory about tables, keys and foreign keys… let’s finally try this out!
First Example
Querying All Flights
We now switch to the Execute SQL tab in the DB Browser.
Type in the text editor the following SQL query:
select *
from flight;
Again, SQL is (mostly, depending on the underlying dabase) case-insensitive, so the following statement is the same (you will see this kind of CAPITALISATION in many SQL tutorials):
SELECT *
FROM FLIGHT;
Any combination (CAPITAL SQL keywords, lower-case table names and vice versa) will also work. I will stick to lower-case for now.
We could also write the whole query in one line:
select * from flight;
but especially with the addition of more complex WHERE-clauses distributing the SELECT, FROM, WHERE clauses onto multiple lines is common practice and makes the queries more human-readable.
Now execute this statement by either:
- Press the Execute all/selected SQL button in the toolbar above (the button has a “play” icon) or
- Select the entered statement and press CTRL + RETURN
In both cases you should see the same result set as we have already seen with the database browser on the previous Browse Data tab. So not very exciting, huh?
Before we continue, let’s have a quick look back at what we actually did here:
- SELECT - we selected all columns, as indicated by the placeholder symbol *
- FROM - we simply indicated the table name (in this example just one), the table from which we want to read the records
- Every SQL statement is terminated with a semicolon ; (especially important if we have multiple SQL statements that we want to execute “in one go”)
Select Some Flights Only
Now let’s say we only wanted the flights where the DESCRIPTION column contains the word “Zurich” (case-insensitive, so we convert all values to lower-case). And we are also only interested in the flight ID. CREATION_TIME and DESCRIPTION, so let’s try this:
select id, creation_time, description
from flight
where lower(description) like '%zurich%';
You can either enter this second SQL query below the first one and then select it before pressing the “play button” (or press CTRL + RETURN), or simply clear the previous statement (then you don’t have to worry about first selecting the SQL statement(s) to be executed).
In my case I get for instance:
| 1 | 2021-05-11 19:09:49 | A gorgeous city at the lake Zurich. Go and visit the Grossmünster church, the town’s landmark with its twin towers. |
|---|---|---|
| 67 | 2021-07-28 17:58:25 | Landing on the lake of Zurich |
| … |
If you are getting an empty result set back then this is because none of your flight descriptions contains the word “zurich” (or “Zurich”, “ZURICH”…). Try with another keyword then.
What did we do?
- the command lower() converts all letters to lower-case, so e.g. “ZURICH” becomes “zurich”
- The comparator like does a “fuzzy comparison”: with the help of the “placeholder” percentage symbol % it essentially checks whether the given sentence contains the given value (taking the placeholder values into account)
More Comparisons
LIkewise we can also select all flights were the initial visibility (at take-off) was very low, say less than 2000 meters, like so:
select *
from flight f
where f.visibility <= 2000;
Again, whether you receive any fllights at all depends on your actual data. You may adjust the 2000 meters, or do a “greater” test with > etc.
What did we do?
- There are many different ways to compare data with each other, the most common ones include <, <=, =, >, >= and LIKE
- I also introduced an alias for table flight, by simply calling it “f”; this becomes very useful once we start joining data from multiple tables (which may have columns with the same name, especially ID columns)
- But by using an alias (which can really be anything, but here is simply used the first letter of the table name) we also get “auto-completion” in the DB Browser SQL editor - nice!
Second Example
Now we’ll ramp up the complexity just a little bit, by querying data from multiple tables. I won’t go into the various inner, outer, left and right joins etc., that is something if you really want to dig deeper into SQL. But essentially this is all about set theory in the end.
In this example we want to list the (all) aircraft belonging to some given flight, identifed by its flight ID (in my example I am using ID 51 - choose any existing flight ID in your example). Let’s also assume we want to have the flight ID, TITLE and the aircraft TYPE (e.g. “Pitts Asobo”) and its sequence number SEQ_NR (only).
Let’s jump right into it:
select f.id, a.type, a.seq_nr
from flight f
join aircraft a
on a.flight_id = f.id
where f.id = 51
Each existing flight always has at least one aircraft, so if you choose an existing flight ID (here: 51) you should get at least one resulting row.
What did we do?
- We joined tables FLIGHT (with chosen alias f) and AIRCRAFT (with chosen alias a)…
- The default join method is an inner join (we could make this explicit by writing also INNER JOIN instead of just JOIN) - but again, the join-topic is beyond the scope here
- … given a join condition, defined in the ON clause, specifically: “Select all flights and aircraft where the aircraft foreign key - as stored in column A.FLIGHT_ID - matches the corresponding ID of the flight (as stored in its column F.ID)”
- This joined data alone would return way more rows than what we have asked for (essentially all flights, with all their aircraft)
- So now comes the WHERE clause with which we “filter out” the joined data set, and we essentially say: “From all this joined data only return those records where the FLIGHT.ID equals to 51”
In my example I get:
| 51 | King Kong | Pitts Asobo | 1 |
|---|---|---|---|
| 51 | King Kong | Pitts Asobo | 2 |
| 51 | King Kong | Pitts Asobo | 3 |
| 51 | King Kong | Pitts Asobo | 4 |
| 51 | King Kong | Pitts Asobo | 5 |
| 51 | King Kong | Pitts Asobo | 6 |
| 51 | King Kong | Pitts Asobo | 7 |
| … |
Conclusion
With SQL and the relatively simple Sky Dolly DB schema you can already extract a lot of data. Note that you can also extract the result set as CSV data, for further processing (visualisation) in e.g. a table editor like Excel or Libre Office Calc and the like.
If that has raised your interest in “datamining” then here are a few more things you could try:
- Try joining three tables, e.g. FLIGHT, AIRCRAFT AND POSITION (remember: POSITION.AIRCRAFT_ID associates the sampled position data with the given aircraft, identified by its ID)
- Try finding all flights which took off at night (take-off e.g. between 9 and 11 PM)
- Try finding all flights where the highest altitude is over 30’000 feet (hint: this goes beyond the toolset we have discussed above, but has a hint: you will probably need the MAX() function
Further Reading
Here are some resources that might help you going with SQL:
- SQLite Tutorial - The SQLite documentation provides a surprisingly good SQL tutorial (with some specifica to SQLite) as well
- SQL Tutorial - W3Schools provides many “user-friendly” tutorials, mostly for “web related technology” such as HTML and JavaScript, but also SQL