For Hollywood Directors: Create Timelapse Videos - With SQL
I agree that this most likely falls under the more “esotheric” category of what you can do with MSFS and Sky Dolly. But it goes to show what you can achieve with a little bit of imagination and creativity.
Abstract
The goal of this little tutorial is to create a 2 minutes long timelapse video with a speedup factor of 16, without having to record 32 minutes of real-time MSFS video footage. After all, the recent Sky Dolly v0.14 now also adjusts the simulation rate upon replay speed changes, so let’s make use of that fact:
In a previous tutorial we have already seen how to open the Sky Dolly logbook with an SQLite database browser like SQLiteBrowser. and an introduction to the most important tables was given.
Preflight Checks
It is best to create a new logbook from within Sky Dolly (CTRL + N) and open this for your experiments. Like this you are not risking to loose any of your recorded flights. However don’t forget to close Sky Dolly before accessing the logbook (= SQLite database) witht the database browser: SQLite databases do not support concurrent write access from different applications.
Programmatically Create A Flight
So we are basically doing the following now:
- Create a flight
- Add an aircraft
- Add two positions with two distinct GPS coordinates and possibly camera movement (pan, rotate, tilt)
Now is the time to open the logbook with your database browser. Once you have done so, execute the following SQL statements.
Flight
Most flight data is optional. So the simplest way to create a flight is:
insert into flight (title, user_aircraft_seq_nr)
values ('LSZH camera pan', 1);
Note that even the title is optional, but it is good to have a descriptive title for later on in Sky Dolly.
The sequence number indicates the user aircraft in the flight (we are going to add the aircraft in a second). In most cases the sequence number equals to 1, but for formation flights (a flight having multiple aircraft this defines the user aircraft the camera is following during replay).
Note that internal IDs are automatically generated, typically by simply incrementing a number. We come to this point in a second.
Aircraft
Next we are going to add our aircraft. The aircraft table references the previous flight table with a so-called foreign key. Essentially this defines the association of the given aircraft with a flight.
So how do we evaluate the ID of the most recently inserted flight? Knowing that those IDs are automatically incremented (sic!) we can simply execute the following query:
select max(id) from flight;
This returns the automatically generated ID of the most recent flight. Good. We can nest this query into the following aircraft insert statement:
insert into aircraft (flight_id, seq_nr, type)
values ((select max(id) from flight), 1, 'Asobo XCub');
Now the type must exactly match with any existing aircraft_type. You can list all available aircraft types with the following query, in alphabetical order:
select * from aircraft_type
order by type;
Note that every Sky Dolly logbook comes with the default aircraft types that come “out of the box” with MSFS (including the premium aircraft). Otherwise this table is automatically populated with every new (3rd party) aircraft that is used for recording.
In fact, the aircraft type in this example - “Asobo XCub” - is not that important, because we can choose any user aircraft for replay in MSFS later on. But in any case we must satisfy the foreign key validation.
Camera Pan Positions
We want to create a simple camera pan from one to another position. Like any good filmmaker we need to create a room plan: we do we want to setup the camera? Where should it point to? Where is the action going to be?
Of course we think big and use any given world map or flight planning tool to determine our desired GPS coordinates and altitudes.
Once we have those we do:
-- Pan from 47.450681, 8.549628 at 1417.0 + 150 feet altitude (about 50 metres above ground)...
insert into position (aircraft_id, timestamp, latitude, longitude, altitude, pitch, bank, true_heading)
values ((select max(id) from aircraft), 0, 47.450681, 8.549628, 1417.0 + 150, 0, 0, 340);
-- ... to 47.454667, 8.571006, within 32 * 60 * 1000 [ms] = 32 minutes
insert into position (aircraft_id, timestamp, latitude, longitude, altitude, pitch, bank, true_heading)
values ((select max(id) from aircraft), 32 * 60 * 1000, 47.454667, 8.571006, 1417.0 + 150, 0, 0, 15);
The nested “select max(id)” does the same as we have already seen for the relation aircraft → flight: it associates each position data with the most recently inserted aircraft.
And that’s it: that is all there is to insert a minimal flight into a Sky Dolly logbook. Of course our camera dolly - Sky Dolly - is self-propelled and does not need any engine data… so we already have a flight from point A to B.
Don’t forget to commit your data (click the “Write Changes” button) before opening the logbook with Sky Dolly again. You can now load this flight, set the replay speed to 16 and enjoy your timelapse.
Pro tip: if you create a custom camera position, like in the front nose of the aircraft pointing forward, you can have your camera rotate with the aircraft. So the previous true heading changes that we inserted take effect. But this is left as an exercise for the dear reader
Go Crazy
Now it is up to you to play with your creativity, for instance:
- Create camera paths with multiple points (they will be smoothly interpolated with Hermite spline curves - as the segment length can be tricky to predict you may need to fiddle around a little bit with the timestamps of the in-between positions, in order to get a smooth velocity as well.
- Change also pitch, bank and altitude
- Think big: pan over the Himalya and increase the timelapse factor in your video editing software (= increase the duration of the panning) (*)
(*) Note: MSFS’ maximum simulation rate is 16. So if you want to have a factor of 32 you need to double the duration of the flight and then add a speedup factor of 2x in your video editing software to achieve a 32x timelapse
Happy filming
This is the complete SQL, ready to be copy & pasted into your database browser:
insert into flight (title, user_aircraft_seq_nr)
values ('LSZH camera pan', 1);
insert into aircraft (flight_id, seq_nr, type)
values ((select max(id) from flight), 1, 'Asobo XCub');
-- Pan from 47.450681, 8.549628 at 1417.0 + 150 feet altitude (about 50 metres above ground)...
insert into position (aircraft_id, timestamp, latitude, longitude, altitude, pitch, bank, true_heading)
values ((select max(id) from aircraft), 0, 47.450681, 8.549628, 1417.0 + 150, 0, 0, 340);
-- ... to 47.454667, 8.571006, within 32 * 60 * 1000 [ms] = 32 minutes
insert into position (aircraft_id, timestamp, latitude, longitude, altitude, pitch, bank, true_heading)
values ((select max(id) from aircraft), 32 * 60 * 1000, 47.454667, 8.571006, 1417.0 + 150, 0, 0, 15);
commit;