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:

  • Detection of launch methods. See whether an aircraft has been winched, towed or started by itself.
  • Detection of tow duration. Keep track of tow starts to see when the aircraft unhooks.

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:

  • Automatic flight registration stage 1
  • Detailed flight insights
  • Logbook functionality

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

  • Fully automatic flight registration (stage 2)
  • Flight comparison
  • Group management functionality
  • Telegram integration

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.

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:

  • METAR updates tailored to your position
  • NOTAMS around you
  • Logbook entries after each flight
  • Detailed flight information (including a map and charts of your flight)
  • Daily, weekly or montly summaries of your flights

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!



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:

  • How to dynamically order the results based on a provided field?
  • How to implement the before/after cursor behaviour?
  • How to pour this into a reusable component?

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!



Recently I had a brainfart that told me to retroactively process about 2 billion data points. The kind of processing that had to be done was to run all these data points (describing flights) through an algorithm that extracted some metadata.

First and foremost my problem is this; the data is stored in a MSSQL instance, whereas this processing algorithm is written with C#. Now these two generally play fairly nice with each other, but in this case, with these amounts of data (+- 1TB) you just can’t load it all into memory, and process it all at once. Another option would be to use batching, but I’d be too lazy (and it’d be too slow) to use.

Why not use Entity Framework?

Entity Framework is a great ORM! I absolutely love it for my day to day work and I wouldn’t want to work without it. However, Entity Framework has it’s limitations, too. One of these is that Entity Framework retrieves the complete dataset, before it returns. What we want to achieve is that we receive part of the data, already before the SQL query has finished executing.

Let’s get coding

The gist of it is easy. Open a connection to the server, execute a command, and retrieve the results.

There’s really not too much to this code. This is more of a reference for I know future me will hate previous me if I do not make this note.

using (var connection = new SqlConnection("[connectionstring]"))
using (var command = new SqlCommand("[query]", connection))
{
    await connection.OpenAsync();

    using (var reader = await command.ExecuteReaderAsync()) {
        if (reader.HasRows) {
            while (await reader.ReadAsync()) {

                reader.GetInt32(0);
                // Or use any applicable cast for the data type you're trying to retrieve
            }
        }
    }

    connection.Close();
}

The aftermath

In the end this code ran for about 26 hours to crunch ‘n munch through all this data. If you care about performance, take this from me, and do not run this on your local machine. I have ran the .NET code directly on the database machine for network bandwidth and dev machine uptime not to be a limiting factor.



It happens more often than not when working with spatial data I have at least a reference to NetTopologySuite (NTS). NTS has a very useful implementation of an r-tree which is the STRtree. This object allows insertion of 2D spatial data (shapes and points), and allows querying those. My use-case is to store all kinds of polygons in the STRtree object and to retrieve some of them based on a single point. I want to retrieve polygons that overlap a single point.

After googling for a while I think I’m looking for something like a Bounding Volume Hierarchy (BVH). I do think the STRtree is a type of a BVH. I did not dig any deeper into it, especially since I’m too lazy to write a decent data type myself, so we’re stuck with an STRtree for now.

Why I don’t think it works as intended

The STRtree object has a Query method which accepts an Envelope object. This Envelope object also accepts a Coordinate object in it’s constructor, and therefore only representing a single point.

When using this method I expect to be returned with all polygons that overlap the specified point. While it does not do this, it’ll probably return points, if, provided at that position.

What I can do to make it work ‘as intended’

As the STRtree seems to do intersections, we’re going to deal with this. The idea is that we draw a line from the edge of the bounding box to the point where we’re at in order to retrieve all polygons that could potentially be related. I’m executing the following steps:

  1. Determine what side of the bounding box is closest to the point you want to find.
  2. Draw a line from the side of the box to the single point.
  3. Add an additional filter on the retrieved objects to determine whether the point is actually inside any of the polygons.

For the circumstances I do think this is a fairly elegant way of retrieving this data. It’s not too performant though. I only manage to do 20,000 lookups a second this way. I want it to be faster, and I do think it can be a lot faster. Turned out I am right, but more on that later.

Let’s get some code going. First we need to populate the STRtree object. Just the same as usual.

STRtree<object> _tree = new STRtree<object>();

// Do something like this a few times, just hook your own data.
var polygon = new Polygon();
_tree.Insert(polygon.EnvelopeInternal, polygon);

// And build the tree to start using it.
// Note that you cannot modify the contents of the tree after this.
_tree.Build();

Now in order to retrieve some useful data from the tree I’m using the following helper method. I highly recommend you to modify this code to your specific needs!

public IEnumerable<object> GetPolygons(Coordinate point)
{
    // Figure out which boundary is closest.
    var xBoundary = point.X.Closest(
        _tree.Root.Bounds.MinX,
        _tree.Root.Bounds.MaxX);

    var yBoundary = point.Y.Closest(
        _tree.Root.Bounds.MinY,
        _tree.Root.Bounds.MaxY);

    var dX = point.X.Difference(xBoundary);
    var dY = point.Y.Difference(yBoundary);

    Envelope envelope;

    if (dX < dY)
        envelope = new Envelope(xBoundary, point.X, point.Y, point.Y);
    else
        envelope = new Envelope(point.X, point.X, yBoundary, point.Y);

    // Note that my object has a Polygon property which contains the polygon itself.
    return _tree.Query(envelope).Where(q => q.Polygon.Contains(point));
}

Note that this code only manages to execute 20,000 lookups each second. If that’s good enough for you, well, go use it! If not, read on.

Bonus: More bang for your bucks 🎉

Now if you need to squeeze a little more performance out of it I have good news for you: that’s totally possible. By the usual wizardy with the Visual Studio profiler you can see the Contains() method takes the biggest chunk of CPU time. I’m not sure at all what takes so much processing time, but I have a gut feeling it can be a lot faster.

As I went googling a bit I stumbled upon this answer on StackOverflow which suggested checking whether a point is on a single side of a polygon. I have slightly adapted the code to my use-case:

public static bool IsPointInPolygon(Coordinate[] polygon, Coordinate testPoint)
{
    bool result = false;
    int j = polygon.Count() - 1;
    for (int i = 0; i < polygon.Count(); i++)
    {
        if (polygon[i].Y < testPoint.Y && polygon[j].Y >= testPoint.Y || polygon[j].Y < testPoint.Y && polygon[i].Y >= testPoint.Y)
        {
            if (polygon[i].X + (testPoint.Y - polygon[i].Y) / (polygon[j].Y - polygon[i].Y) * (polygon[j].X - polygon[i].X) < testPoint.X)
            {
                result = !result;
            }
        }
        j = i;
    }
    return result;
}

Which you can use as follows:

_tree.Query(envelope).Where(q => Extensions.IsPointInPolygon(q.Polygon.Coordinates, point));

Using the code above I can manage to do about 260,000 lookups each second. While these measurements are far but scientific it shows roughly a 13x increase in performance, which is great!



Given my background with .NET technology, and me being stupid enough to store the DateTime struct’s Ticks value in a SQL database I found the need to convert these Ticks to a DateTime again, in order to visualize the stuff going on in the database.

Let me just drop the code here. It’s not mine, but I’m having trouble finding it on the internet every time I need it.

The script to convert ticks (long value) to datetime2:

SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.ToDateTime2 ( @Ticks bigint )
    RETURNS datetime2
AS
BEGIN
    DECLARE @DateTime datetime2 = '00010101';
    SET @DateTime = DATEADD( DAY, @Ticks / 864000000000, @DateTime );
    SET @DateTime = DATEADD( SECOND, ( @Ticks % 864000000000) / 10000000, @DateTime );
    RETURN DATEADD( NANOSECOND, ( @Ticks % 10000000 ) * 100, @DateTime );
END
GO

After you created the function you can use it as:

SELECT dbo.ToDateTime2(`Table`.`Ticks`) AS `TimeStamp` FROM `Table`

The script to convert a datetime2 value to ticks again:

SET ANSI_NULLS ON
GO

CREATE FUNCTION [dbo].[Ticks] (@dt DATETIME)
    RETURNS BIGINT
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @year INT = DATEPART(yyyy, @dt)
    DECLARE @month INT = DATEPART(mm, @dt)
    DECLARE @day INT = DATEPART(dd, @dt)
    DECLARE @hour INT = DATEPART(hh, @dt)
    DECLARE @min INT = DATEPART(mi, @dt)
    DECLARE @sec INT = DATEPART(ss, @dt)

    DECLARE @days INT =
        CASE @month - 1
            WHEN 0 THEN 0
            WHEN 1 THEN 31
            WHEN 2 THEN 59
            WHEN 3 THEN 90
            WHEN 4 THEN 120
            WHEN 5 THEN 151
            WHEN 6 THEN 181
            WHEN 7 THEN 212
            WHEN 8 THEN 243
            WHEN 9 THEN 273
            WHEN 10 THEN 304
            WHEN 11 THEN 334
            WHEN 12 THEN 365
        END

    IF  @year % 4 = 0 AND (@year % 100  != 0 OR (@year % 100 = 0 AND @year % 400 = 0)) AND @month > 2 BEGIN
        SET @days = @days + 1
    END
    RETURN CONVERT(bigint,
        ((((((((@year - 1) * 365) + ((@year - 1) / 4)) - ((@year - 1) / 100)) + ((@year - 1) / 400)) + @days) + @day) - 1) * 864000000000) +
        ((((@hour * 3600) + CONVERT(bigint, @min) * 60) + CONVERT(bigint, @sec)) * 10000000) + (CONVERT(bigint, DATEPART(ms, @dt)) * CONVERT(bigint,10000));

END
GO

Which can be used in the following way again:

SELECT dbo.Ticks(`Table`.`TimeStamp`) AS `Ticks` FROM `Table`

 

In case you figure out the source of these scripts, please pass me a message and I’ll give proper credit