Curled Cloudy Code

SkyHop end of 2018 development update

The end of a year is a moment for me to look back and reflect on the work done in the past year. I slowly became to realize the whole SkyHop project has been bigger than I could reasonably handle when I all started it back in November 2016. In these two years of on and off development the idea has evolved from a tool to compare flight performances between my brother and me to a state of the art flight analysis platform.

If you would like to participate in the public beta launching approximately Q2 2019, send me a message somewhere and I’ll add you to the list! Leave a comment on this post, send me a message on Twitter, Instagram, or even mail would do fine. I’d be happy to show you around!

Work done in the past year

The most important theme in Q1 2018 has been development of the FlightAnalysis library. This library, which allows automatic detection of flight metadata based on its flightpath, is as good as finished now with almost all bugs ironed out. There are still a few major features I’d wish to implement after other work has been done:

During Q2 the biggest focus has been on the improvement of stability. Several bugs throughout the stack have been fixed. This includes the TCP server, APRS server and the FlightAnalysis library. The data model has kind of matured for data storage and that’s about it. All in all not too much groundbreaking work happened before.

In Q3 we’ve been working pretty hard on the front-end interface in conjunction with the API. The API has been written in C#, but according to the GraphQL specification. It’s one of our goals to release the same API we’re building our UI upon to external developers for implementation in their solutions. This work reached well into Q4, and while the API will be completed in Q1 2019, the UI will be a work in progress for a while. The goal is to aim for a beta version in Q1 2019, but work on main features will extend well into Q2 2019. Of course this is all dependent on the amount of free time available to invest into the project as well.

The groundwork for a lot of the topics I’m going to describe next have been already done during the last year. Therefore reinforcing my expectation that a lot of features can be shipped throughout 2019!

Beta version

We aim for a public beta version at the end of Q1 2019. This version will contain the most important features:

After this first version we’ll release periodical updates which will contain new functionality:

Flight Registration

There are two stages in the development of automatic flight registration capabilities. The first one focuses on the registration of aircraft movements, and is dependent on Flarm or ADS-B. Focus in the second stage will be on detection of the pilots and will include fully automatic registration capabilities. A side effect of the technique we will be using is that the flight movement detection capabilities will be enhanced, resulting in a more clear view of your flight.

I think it’s worth mentioning that automatic pilot registration will work with any aircraft, as long as it can be tracked by Flarm, or ADS-B. More details are about to come later, after the first beta has been released.

Flight Insights

We will offer one of the best flight visualization tools available, right in your browser. As one of the first experiments I have plotted one of my flights through the Alps in Google Earth. Take this example, and imagine the final result to be the same as this, but with more information about your flight performance.

To be honest it’s also incredibly fun following the flight path, and reliving the flight, and more importantly, the decisions made during the flight. This will bring amazing opportunities educating people about safe practices with mountain soaring, and aviation in general. pic.twitter.com/WrnXtDSa7y — Corstian Boerman (@CorstianBoerman) November 19, 2018

After the first beta has been released I plan on implementing support for flight comparisons. Selecting two or more flights will show the same screen, but with information from multiple flights shown. Ultimately this boils down to where it all started, and would settle a lot of arguments between me and my brother.

Competition and Privacy

As important as all other items I want to take this change to talk a bit about privacy. My goal has been not to (semi-)publicly disclose detailed flight information. The primary reason is our club culture. While I am all in for an open competitive and safe flying environment I do not think publicly disclosing flight paths will help achieve this in a good way. For dealing with and prevention of airspace infringements are other options, which I’ll write about later on. This also is our biggest potential issue right now. So far my policy is that users can choose to share their flights if they want to, but this information is protected by default. If within an aeroclub, they might choose that only certain persons have access to this information like the safety commissioner.

When talking about competitions and flight performance a fellow pilot made the following comment:

“It would be amazing to see how other pilots do in a competition, and see the differences between our flying styles, and the impact on the result.”

It set me to think a bit. How can we achieve this while ensuring privacy? Sure, all pilots can share their flights, but it would defy the possibilities available with real-time tracking, also for the pick-up team. After the first beta has been released we will invest some time in developing functionality specifically aimed at competition organizers. This will enable organizers to register aircraft flying in the competition with us to enable competition mode, and give organizers the power to disable features like real-time tracking, but also to enable safety features, and flight comparisons between all participants.

Safety Features

The safety features we’ve been talking about before will be primarily aimed at preventing and educating about airspace infringements.

There are multiple known occurrences of pilots making (accidental) airspace infringements going unnoticed by themselves until someone else discovered them.

The goal is to provide tooling for flight planning to show a clear overview of the airspace structure. While usage of mobile devices in the cockpit is frowned upon, I think they can be a valuable source of information, when used responsibly. With this mindset we’re going to offer functionality to notify pilots when they are about to enter controlled or restricted airspace (ofcourse we cannot know whether they have clearance), in order to improve spatial awareness.

External Integration

In order to be able to provide information to our users in near-real-time we are planning on using Telegram’s bot platform. Telegram has built an incredibly powerful platform which can be used by external developers (like me) to have interactions with users.

Telegram settings configuration screen in the SkyHop web app

Right now there are a few different messages we want to send via Telegram:

Telegram is one of the ways we can make interacting with SkyHop as easy as possible. More is yet to come!

Participation

My goal for the beta period of SkyHop is to gain feedback from all users involved. In exchange for feedback I’ll give you free access to all of the platforms features while the beta period lasts.

Given the amount of time and other resources I have personally invested in this project I can impossibly keep offering this service for free, and therefore will be offering a subscription based service after the beta ends. I am not yet sure how long the platform will be in beta, but it can be anywhere from three months to a year. I will keep you updated on that, though.

In exchange for participating in the beta I will also offer a discounted subscription for when the beta period ends.

General Aviation

While SkyHop was originally intended to be a software suite aimed at the soaring community, there is a broad range of features that would suit the whole general aviation community. There is nothing which prevents you from doing so at all! Please let me know if you want to participate as a GA pilot in the beta, and do not have Flarm equipped. I am happy to give priority to implementing an ADS-B feed so your flights can be processed!

Feedback

Last but not least; feedback. As a pilot myself I know from experience what I features I want to have, and I do have the required skills to build those tools. But other people tend to have better ideas than me. If you want to see specific features implemented, or you are a developer at your aviation club or you do anything related to aviation and you think we could work together on something, let me know! I am really happy to listen to and discuss your feedback!

Cursor based pagination with C# and SQL Server

For some C# code, skip to the last paragraph!

Introduction

Out of pagination techniques, cursor based pagination is the one that allows for the most flexible and easy implementation, in my humble opinion. See this post for an explanation how Slack evolved to cursor based pagination.

The beautiful thing about cursor based pagination is that it gets you a whole lot of benefits:

  1. You do not have to retrieve the total number of items (which is quite costly)
  2. Your pagination method plays well with live data
  3. It forces you to think about a more user friendly way to implement pagination. (Mainly thinking about search and filter options).
  4. This way of implementing pagination allows for a flexible implementation at the client side, based on UX related requirements.

If you read between the code, this post does a pretty great job explaining why offset based pagination is bad, both from a user experience based perspective, as well as from a performance based point of view.

However, developer in the .NET space are not yet quite used to cursor based pagination. Why would they? .Skip(page*10).Take(10) works quite well for most use cases, after all.

Goals

First of all we would need a way to prove we can efficiently implement this pagination method at all. But before we dive into the details we’re going to define a set of rules to which this pagination method must adhere:

  1. A cursor can be anything, from an identifier to (an in my eyes cleaner method) a base64 encoded value to identify unique items.
  2. Order should be maintained for int, string and datetime properties
  3. Live data shall not impact the resulting data (in most cases)

Essentially we should be able to order the result set on a specific field, and then retrieve a certain amount of data, relative to the specified cursor.

The cursor in question however, might be the item from the data source relative to which data after this one is being retrieved, or from which data before this point is being retrieved. Usually provided with the ‘after’ or ‘before’ parameters.

One of the primary difficulties is, that we should not order based on the identifier. Personally I prefer to use GUID’s as cursor, as I can translate this directly to the primary keys as used in my data source. Besides this users cannot be expected to guess random entries, which is an additional barrier to take in case actual security measures lack, or are poorly implemented. No order can be inferred from GUID’s, and possibly other opaque cursors, so we should not assume there is one.

A proof-of-concept

This first example has been created to figure out whether it’s possible, at all, to implement cursors, using Entity Framework 6 (Because LinqPad). In this example we treat the ID (78) as the cursor.

var serialNumber = Devices.Where(q => q.Id == 78).Select(q => q.SerialNumber).FirstOrDefault();

Devices
	.OrderBy(q => q.SerialNumber)
	.Where(q => String.Compare(q.SerialNumber, serialNumber) >= 0)
	.Take(50)
	.Dump();

Surprisingly this works pretty well. Entity Framework perfectly translates the String.Compare method to SQL code. The generated SQL code is surprisingly clean.

SELECT TOP (1)
    [Extent1].[SerialNumber] AS [SerialNumber]
    FROM [dbo].[Devices] AS [Extent1]
    WHERE 78 = [Extent1].[Id]
GO

-- Region Parameters
DECLARE @p__linq__0 NVarChar(1000) = ''
-- EndRegion
SELECT TOP (50)
    [Project1].[Id] AS [Id],
    [Project1].[SerialNumber] AS [SerialNumber]
    -- And some more fields
    FROM ( SELECT
        [Extent1].[Id] AS [Id],
        [Extent1].[SerialNumber] AS [SerialNumber],
        -- Again, some more fields here
        FROM [dbo].[Devices] AS [Extent1]
        WHERE [Extent1].[SerialNumber] >= @p__linq__0
    )  AS [Project1]
    ORDER BY [Project1].[SerialNumber] ASC

This query would be composed with a cursor defined as after: 78. Implementing a before: 78 operation would require us to use an inverted sorting method. No big deal.

Properly handling queries

This is something, when done right, that would be repeated many, many times over and over inside your application. The cost of implementing pagination should be neglectable in order to promote an uniform API design. Given we have proven the effectiveness of cursor based pagination, we now have to tackle the following questions:

There’s not much to help us with this in Entity Framework (Core). I would have imagined that we could use the `SkipWhile()` method for that. We’d use it like `Table.SkipWhile(q => q.Id < 1684).Take(10);`. However, `SkipWhile` is not implemented (yet). I set on to look for other possibilities. While considering to use Dapper for this functionality in the time being I wasn’t really keen on writing and validating my own SQL query builders so I went to look for sql builders. I ended up with SqlKata. This library seems to be a nice middle way between using Entity Framework and manually writing your queries. After all, code that has not been written is code that doesn’t need to be tested.

Implementation

After all it only took about 15 lines of code to achieve the wanted behaviour with SqlKata in an extension method. When used at the end of your query (so the Query object contains all your other clauses), this extension methods compiles the from and orderby clauses in order to be consistent with the order of the result set to the application.

/// <summary>
/// A generic method for applying cursor based pagination.
/// </summary>
/// <param name="query">The query to apply pagination to</param>
/// <param name="column">The column to use for cursor based pagination</param>
/// <param name="cursor">The cursor itself</param>
/// <param name="count">The number of items to retrieve after the cursor</param>
/// <returns></returns>
public static Query CursoredOffset(this Query query, string column, string cursor, int count)
{
    var compiler = new SqlServerCompiler();

    var ctx = new SqlResult
    {
        Query = query
    };

    var from = compiler.CompileFrom(ctx);
    var order = compiler.CompileOrders(ctx);

    query = query.CombineRaw($@"OFFSET (
        SELECT TOP 1 rn
            FROM(
                SELECT {column}, ROW_NUMBER() OVER({ order }) AS rn { from }
            ) T
            WHERE T.{column} = '{cursor}'
        ) rows
        FETCH NEXT {count} ROWS ONLY");

    return query;
}

What’s next? I’m going to publish a library containing some extension methods for use with SqlKata which enables you to easily use cursors in your queries.

Happy coding!