Kusto Detective Agency Season 2のメモ | Case 7

# Kusto Detective Agency Season 2 のメモ

https://qiita.com/georgeOsdDev@github/items/4bdf605aa6d222436597

の続き

# Case 7 Mission 'Connect'

> While we have gathered significant information about him, it is not enough to capture him. Our sources indicate that he was present at the Doha airport on August 11, 2023, between 03:30 AM and 05:30 AM (UTC). However, by the time our agents arrived, he had already made his escape, presumably utilizing a private jet. We have deployed dozens of officers to all potential landing destinations, but he has evaded us so far. We have a single lead that suggests Krypto may have attempted a plane-to-plane jump, given his skills as a wingsuit expert. Here is where we got stuck.

Krypto が Doha からどこに逃げたのかを探す。
与えられるテーブルは `Flights` と `Airports`。飛行機から飛行機に飛び移ったとかどうとか。

## テーブル情報

### `Flights`

便ごとの位置情報が時系列で保存されている。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/58b99f37-c0a4-a4f1-4dcc-fa7df1bffa53.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/3fbc1fd4-c94f-a7c8-e71b-81d0c3f79f1a.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/45166723-2382-0ee7-41df-9780a784f5b5.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/5b1d87e6-48ed-650e-46b9-900068d43c4b.png)


### `Airports`

空港の Static な情報

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/49e183f1-eec7-5dd4-dd04-191549ea4456.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/b6bf9168-5bd3-7e51-f52d-93c221c69477.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/83613c53-c74d-580f-5e0b-2092da4b0a0e.png)

### ポイント

Train me より、位置情報を扱うということで、geo 関連の機能を使えとのこと。

<地理空間グリッド システム | Microsoft Learn>
https://learn.microsoft.com/ja-jp/azure/data-explorer/kusto/query/geospatial-grid-systems

> Our sources indicate that he was present at the Doha airport on August 11, 2023, between 03:30 AM and 05:30 AM (UTC)

とのことなので、方針としては、

- 該当時間移行に Doha を発った便を探す
- 該当の便が目的地に到着するまでに近接した便Xを探す
  - 近接した便の高度(`geoaltitude`) は Doha 発の便より低い必要がある 
- 便 X の到着地を探す


#### 近接しているかを判定する

アルゴリズムは `Geo-Hash`、`H3`、 `S2`とあるが、まずは `S2` を使うのがベターとのこと

#### `geo_point_to_s2cell`

lat/long をもとにハッシュ値を得る関数
同じハッシュ値を得られれば、近接していると判断できる。

https://learn.microsoft.com/ja-jp/azure/data-explorer/kusto/query/geo-point-to-s2cell-function

```sql
let PointsNearBy = (Lon1:double, Lat1:double, Lon2:double, Lat2:double, s2_precision:int)
{
    geo_point_to_s2cell(Lon1, Lat1, s2_precision) ==
    geo_point_to_s2cell(Lon2, Lat2, s2_precision) 
};
Flights
| where PointsNearBy(lon, lat, -0.158474, 51.523769, s2_precision = 13) // Level: 13 is ~1km
```

#### `geo_distance_2points`

2つの位置の距離を測る

https://learn.microsoft.com/ja-jp/azure/data-explorer/kusto/query/geo-distance-2points-function

### Doha 発着リストを描画してみる

```sql
let s2_precision = 13; 
let flightList =
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
    | where Timestamp between (datetime(2023-08-11 03:30:00) .. datetime(2023-08-11 05:30:00))
) on key
| where geo_distance_2points(lon, lat, lon1, lat1) < 5000
| distinct callsign;
Flights
// | where callsign == "OJIT393"
| where callsign in (flightList)
| where Timestamp > datetime(2023-08-11 05:30:00)
| where onground != true
| project lon, lat, callsign
| render scatterchart with (kind = map)
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/8919b223-dd2d-ad48-cdee-50dc6587e00a.png)


Dohaに関連する便があるけど、関係なさそうなデータもある。。。

### 


<details><summary>セットアップ</summary>

```sql
.execute database script <|
.create-merge table Flights(Timestamp:datetime, callsign:string, lat:real, lon:real, velocity:real, heading:real, vertrate:real, onground:bool, geoaltitude:real) 
.create-merge table Airports (Id:string, Ident:string, Type:string, Name:string, lat:real, lon:real, elevation_ft:long, iso_country:string, iso_region:string, municipality:string, gps_code:string, local_code:string)  
.ingest into table Airports (@'https://kustodetectiveagency.blob.core.windows.net/kda2c7flights/airports.csv.gz')
.ingest async into table Flights (@'https://kustodetectiveagency.blob.core.windows.net/kda2c7flights/flights_1.csv.gz')
.ingest async into table Flights (@'https://kustodetectiveagency.blob.core.windows.net/kda2c7flights/flights_2.csv.gz')
.ingest into table Flights (@'https://kustodetectiveagency.blob.core.windows.net/kda2c7flights/flights_3.csv.gz')
```
</details>

<details><summary>回答</summary>

> In which city did the suspect land?

ググった。

https://www.bing.com/videos/search?q=kusto+detective+Agency+case+7&view=detail&mid=207DE0F528270AD5D51E207DE0F528270AD5D51E&FORM=VIRE

https://zedsec.gitbook.io/writeups/write-ups/kusto-detective-agency/season-2/case-7-mission-connect

ここで飛び移るらしい。。。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/519ad086-f582-6c88-9bfd-cd218f531b3c.png)

近接した便を探す
```sql
let s2_precision = 13; 
// Doha 発の便のリスト
let flightList =
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
    | where Timestamp between (datetime(2023-08-11 03:30:00) .. datetime(2023-08-11 05:30:00))
) on key
| where geo_distance_2points(lon, lat, lon1, lat1) < 5000
| distinct callsign;
// Doha発の便のリストのpointデータ
Flights
| where callsign in (flightList)
| where Timestamp > datetime(2023-08-11 05:30:00)
| where onground != true
| extend point=geo_point_to_s2cell(lon, lat, s2_precision)
| project Timestamp,lon, lat, callsign, geoaltitude, point, ts=bin(Timestamp, 1m)
| join kind = inner (
    // 該当のポイントを通った便をjoin
    Flights
    | where Timestamp > datetime(2023-08-11 05:30:00)
    | where onground != true
    | extend point=geo_point_to_s2cell(lon, lat, s2_precision)
    | project Timestamp,lon, lat, callsign, point, geoaltitude, ts=bin(Timestamp, 1m)
) on point, ts
| project ts,callsign,callsign1,point,lon,lat,geoaltitude,geoaltitude1
// 自分自身を除外
| where callsign != callsign1
// Doha発の便の高度が高いと飛び移れない
| where geoaltitude > geoaltitude1
| extend pair=strcat(callsign, '_', callsign1)
| summarize count() by pair
| order by count_ desc
```

`OJIT393_HFID97` のペアがほかに比べて件数が多い

2機の高度の時系列表示
```sql
Flights
| where callsign == "OJIT393" or callsign == "HFID97"
| order by vertrate  asc 
| project Timestamp,geoaltitude,callsign
| render timechart 
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/7fa0d76d-9f07-a0b8-aed1-9562cee4b3f7.png)


HFID97のあて先を探す

```sql
let s2_precision = 13; 
// HFID97の最終レコードをもとにポイントを算出してAirportからルックアップ
Flights
| where callsign == "HFID97"
| where onground == true
| summarize (maxTimestamp,maxLon,maxLat) = arg_max(Timestamp,lon,lat) by callsign
| extend key=geo_point_to_s2cell(maxLon, maxLat, s2_precision)
| lookup kind=inner(
Airports
| extend key=geo_point_to_s2cell(lon, lat, s2_precision)
) on key
| project Id,Name,municipality,gps_code
```



</details>