Season 2 Case 7: Mission 'Connect'

Highway To The Dangerzone

Once again, the administration of Digitown has come up less than stellar. Seems like cracking open kuanda.org wasn’t enough, El Puente ran out of donuts or something at let Krypto slip through their fingers. Or maybe El P is #TeamProfSmoke after all?

Anyway, Agent Stas Fistuko (anagram anyone?) from the National Security Office has informed us that Krypto has been spotted at Doha airport (so I guess they’re not queer then, ooh political Liesel…) jumping on a plane, with a special connection to catch. Literally.

The Data

Agent Stas has ‘acquired’ flight logs for the day and a tip off that Krypto is a Tom Petty fan - they like a bit of free-fallin’ apparently. Our job is to find out where they jumped and where they landed.

Let’s have a look at the Airports first.

Airports
| take 100

Airports

Ok nothing special. And the flights?

Flights
| take 100

Flights

Again, nothing out of the ordinary.

The Mac Dad will make you

The clues:

last seen at Doha airport on August 11 2023, between 03:30 AM and 05:30 AM
at some point, put some Van Halen on his Walkman and jumped between planes

So, much like the car switching case from last year, we need to find all the flights that left after 5:30 and had a mid air rendezvous.

Let’s “dive” in.

Geoguesser has entered the chat

First, Wikipedia. “Doha International” airport is the OG, the current airport is Hamad International ICAO code OTHH Let’s find it

Airports 
| where Ident == "OTHH"

Now, well need to use one of the many geospatial grid features of Kusto to match the planes to location. Since we’ll be using these hashes a lot, let’s cache them in new tables:

.set-or-replace FlightsEx <|
let s2_precision = 11; 
Flights
| extend key=geo_point_to_s2cell(lon, lat, s2_precision)

.set-or-replace  AirportsEx <|
let s2_precision = 11; 
Airports 
| extend key=geo_point_to_s2cell(lon, lat, s2_precision);

Now we can join on hash key and since we’re interested in planes that were on the ground between 3:30 and 5:30, we can add that filter into our suspects list.

let SuspectFlights =
    FlightsEx
    | where 
        key in ( (AirportsEx | where Ident == "OTHH" | project key)) 
        and Timestamp  between (datetime("2023-08-11 03:30:00") .. datetime("2023-08-11 05:30:00")) 
        and onground == true
    | distinct callsign;
SuspectFlights

19 Records. Cool, now let’s see what other flight paths they crossed. We’ll join our FlightsEx with our SuspectFlights and remove any self intersections

let SuspectFlights =
    FlightsEx
    | where 
        key in ( (AirportsEx | where Ident == "OTHH" | project key)) 
        and Timestamp  between (datetime("2023-08-11 03:30:00") .. datetime("2023-08-11 05:30:00")) 
        and onground == true
    | distinct callsign;
FlightsEx
| where callsign in ( SuspectFlights ) and onground == false and Timestamp > datetime("2023-08-11 05:30:00")
| join kind=inner (FlightsEx | where Timestamp > datetime("2023-08-11 05:30:00") and onground == false) on key , Timestamp
| where callsign <> callsign1

Hmm 919 records…

If Krypto did make like a sugar glider, we can make some good assumptions

The planes will probably track closely (although really, they wouldn’t need to once he’s bailed)
Speed is going to be similar
Our first plane will be higher (but not by a huge amount) than the second.

So let’s find all the flight paths that meet our criteria, then find the destination airport of the second plane.

let SuspectFlights =
    FlightsEx
    | where 
        key in ( (AirportsEx | where Ident == "OTHH" | project key)) 
        and Timestamp  between (datetime("2023-08-11 03:30:00") .. datetime("2023-08-11 05:30:00")) 
        and onground == true
    | distinct callsign;
FlightsEx
| where callsign in ( SuspectFlights ) and onground == false and Timestamp > datetime("2023-08-11 05:30:00")
| join kind=inner (FlightsEx | where Timestamp > datetime("2023-08-11 05:30:00") and onground == false) on key , Timestamp
| where callsign <> callsign1 
| extend HeightDiff = bin(geoaltitude - geoaltitude1, 10)
| extend HeadingDiff = round(heading - heading1)
| extend VelocityDiff = round(velocity - velocity1)
| where HeightDiff > 0 and VelocityDiff < 20 and HeadingDiff == 0
| project Timestamp, callsign, callsign1, HeightDiff, VelocityDiff, HeadingDiff

And where did that plane go?

let SuspectFlights =
FlightsEx
    | where 
        key in ( (AirportsEx | where Ident == "OTHH" | project key)) 
        and Timestamp  between (datetime("2023-08-11 03:30:00") .. datetime("2023-08-11 05:30:00")) 
        and onground == true
    | distinct callsign;
FlightsEx
| where callsign in ( SuspectFlights ) and onground == false and Timestamp > datetime("2023-08-11 05:30:00")
| join kind=inner (FlightsEx | where Timestamp > datetime("2023-08-11 05:30:00") and onground == false) on key , Timestamp
| where callsign <> callsign1 
| extend HeightDiff = bin(geoaltitude - geoaltitude1, 10)
| extend HeadingDiff = round(heading - heading1)
| extend VelocityDiff = round(velocity - velocity1)
| where HeightDiff > 0 and VelocityDiff < 20 and HeadingDiff == 0
| distinct callsign1
| project callsign = callsign1
| join kind=inner FlightsEx on callsign
| top 1 by Timestamp desc
| join kind=inner AirportsEx on key
| project municipality

Boom. Barcelona

Let’s see what this looks like graphically. Kusto can’t plot path (I think) so let’s jump into a Fabric Notebook.

Snakes On Planes

Yeah, for real, I’m only doing this to be able to make that joke.

Let’s make a simple query to turn our two flights into paths of Well Known Text LINESTRINGs

Flights
| where callsign in ("OJIT393", "HFID97")
| sort by callsign, Timestamp asc
| summarize  wkt=strcat("LINESTRING(",strcat_array(make_list(strcat(round(lon,5)," ", round(lat,5))), ", "), ")") by callsign

Now we can head over to a notebook, use KQLMagic to run that query, and use a little bit of Python to see exactly where Krypto jumped. We’ll use geopandas to plot the paths.

I’m using Fabric notebook here, but Azure Data Studio, VS Code or similar will work just as well.

First, we’ll need a few cells for housekeeping. Install deps:

!pip install Kqlmagic geopandas folium mapclassify --no-cache-dir --upgrade

Add a new cell and reload the kqlmagic extension

reload_ext Kqlmagic

Add a third cell and authenticate to Kusto

%kql kusto://code;cluster='https://youclustergoeshere';database='yourdatabasegoeshere'

Now we’re ready to find Krypto’s jump point. In a final cell run the following code:

import numpy as np
import pandas as pd
import geopandas as gpd
from shapely import wkt

q = '''
Flights
| where callsign in ("OJIT393", "HFID97")
| order by Timestamp asc
| summarize  wkt=strcat("LINESTRING(",strcat_array(make_list(strcat(round(lon,5)," ", round(lat,5))), ", "), ")") by callsign
'''
%kql res << -query q
df = res.to_dataframe()

df['geometry'] = gpd.GeoSeries.from_wkt(df['wkt'])
gdf = gpd.GeoDataFrame(df, geometry='geometry', crs="EPSG:4326")

gdf['color'] = ['#FF00D0',  '#543AB7']
gdf.explore(color=gdf['color'])

Zooming in..

So there you go, Krypto made the jump just outside Manchester. Exotic.

Highway To The Dangerzone#

The Data#

The Mac Dad will make you#

Geoguesser has entered the chat#

Snakes On Planes#

Highway To The Dangerzone

The Data

The Mac Dad will make you

Geoguesser has entered the chat

Snakes On Planes