Background: Krypto slipped through the fingers of Digitown’s law enforcement.
Mission: We need your expertise and experience to help us find the final destination of Krypto.
For all the marbles: In which city did the suspect land?
What we know:
- Krypto was spotted in Doha airport on August 11, 2023, between 03:30 AM and 05:30 AM (UTC).
- Krypto is up in the skies doing plane hopping in his wingsuit.
We need to find which flights were at the Doha Airport at during time.
let s2_precision = 13; // ~1 km
Airports
| where municipality == 'Doha'
| extend key=geo_point_to_s2cell(lon, lat, s2_precision)
| join kind=inner (
Flights
| extend key=geo_point_to_s2cell(lon, lat, s2_precision)
| where onground // Only consider planes that were on the ground
) on key
| where Timestamp between(datetime(2023-08-11 03:30:00.0000000)..datetime(2023-08-11 05:30:00.000))
| distinct callsign
Which of these flights were on air after 5:30 am.
let s2_precision = 13;
Flights
| where callsign in ((
Airports
| where municipality == 'Doha'
| extend key=geo_point_to_s2cell(lon, lat, s2_precision)
| join kind=inner (
Flights
| extend key=geo_point_to_s2cell(lon, lat, s2_precision)
| where onground // Only consider planes that were on the ground
) on key
| where Timestamp between(datetime(2023-08-11 03:30:00.0000000)..datetime(2023-08-11 05:30:00.000))
| distinct callsign))
| where Timestamp > datetime(2023-08-11 05:30:00.000) and onground == false
| summarize dcount(callsign)
He hoped off of these 5 planes to another at some point. We need to find planes that at the same place at the same time.
He has to jump from a high to a low point at a distance that makes sense for life. So, we filter by altitude difference.
Airports
| where municipality == "Doha"
let s2_precision = 13;
Flights
| where callsign in ((
Airports
| where municipality == 'Doha'
| extend key=geo_point_to_s2cell(lon, lat, s2_precision)
| join kind=inner (
Flights
| extend key=geo_point_to_s2cell(lon, lat, s2_precision)
| where onground // Only consider planes that were on the ground
) on key
| where Timestamp between(datetime(2023-08-11 03:30:00.0000000)..datetime(2023-08-11 05:30:00.000))
| distinct callsign))
| where Timestamp > datetime(2023-08-11 05:30:00.000) and onground == false
| extend key = geo_point_to_s2cell(lon, lat, s2_precision)
| join kind=inner (Flights
| extend key=geo_point_to_s2cell(lon, lat, s2_precision)
| where Timestamp > datetime(2023-08-11 05:30:00.000))
on key, Timestamp
| where callsign != callsign1
| extend altitudeDiff = geoaltitude - geoaltitude1
| where altitudeDiff < 1000 and altitudeDiff > 0
We are down to one plane. Let us go get Krypto! We need to find out where the plane he jumps to will land.
Airports
| where municipality == "Doha"
let s2_precision = 13;
Flights
| where callsign in ((
Airports
| where municipality == 'Doha'
| extend key=geo_point_to_s2cell(lon, lat, s2_precision)
| join kind=inner (
Flights
| extend key=geo_point_to_s2cell(lon, lat, s2_precision)
| where onground // Only consider planes that were on the ground
) on key
| where Timestamp between(datetime(2023-08-11 03:30:00.0000000)..datetime(2023-08-11 05:30:00.000))
| distinct callsign))
| where Timestamp > datetime(2023-08-11 05:30:00.000) and onground == false
| extend key = geo_point_to_s2cell(lon, lat, s2_precision)
| join kind=inner (Flights
| extend key=geo_point_to_s2cell(lon, lat, s2_precision)
| where Timestamp > datetime(2023-08-11 05:30:00.000))
on key, Timestamp
| where callsign != callsign1
| extend altitudeDiff = geoaltitude - geoaltitude1
| where altitudeDiff < 1000 and altitudeDiff > 0
| project Timestamp1, callsign=callsign1, lat1, lon1, key1
| join kind=inner (
Flights
| where onground // Check planes that are only on the ground
| summarize (LandingTime, LandingLon, LandingLat)=arg_max(Timestamp, lon, lat) by callsign
) on callsign
| where LandingTime > Timestamp1 // Make sure we count only flights that fly from London
| extend key=geo_point_to_s2cell(LandingLon, LandingLat, s2_precision)
| lookup (Airports | extend key=geo_point_to_s2cell(lon, lat, s2_precision)) on key
| where geo_distance_2points(lon, lat, LandingLon, LandingLat) < 5000
