こちらのノートブックをウォークスルーします。

空間情報と航路をブレンドした分析をサポートするために、DBR 11.2とビルトインのH3 APIを活用します。
説明を容易にし、データ量を抑えるために単一のフライトにフォーカスしますが、データが多いほど良いことになります！

重要
Photon有効化クラスターを使ってください。

%pip install keplergl --quiet
dbutils.library.restartPython()

from keplergl import KeplerGl

# H3を取得するDatabricks関数のインポート
from pyspark.databricks.sql.functions import *

import pyspark.sql.functions as F
from pyspark.sql.functions import col

from pyspark.sql.types import *
from pyspark.sql import Window

import pandas as pd
import pprint

from typing import *

pp = pprint.PrettyPrinter(indent=4)

フライト追跡データ

このフライトでは、225イベントが利用でき、Atlanta (ATL)、Washington DC (IAD)間で15秒ごとにイベントが発生しています。

フライト追跡データ

flight_track = """time_edt,latitude,longitude,course,kts,mph,feet,rate,reporting_facility
Fri 05:41:28 PM,33.6350,-84.4410,272,169,194,1575,Level,FlightAware ADS-B (KATL)
Fri 05:41:44 PM,33.6356,-84.4564,273,173,199,2275,1734 Climbing,FlightAware ADS-B (KPDK)
Fri 05:42:00 PM,33.6348,-84.4726,256,188,216,2500,797 Climbing,FlightAware ADS-B (KPDK)
Fri 05:42:16 PM,33.6283,-84.4897,244,207,238,2700,844 Climbing,FlightAware ADS-B (KMGE)
Fri 05:42:32 PM,33.6213,-84.5060,243,222,255,2950,1292 Climbing,FlightAware ADS-B (KATL)
Fri 05:42:52 PM,33.6108,-84.5302,244,240,276,3475,1849 Climbing,FlightAware ADS-B (KATL)
Fri 05:43:15 PM,33.5994,-84.5589,244,248,285,4275,1923 Climbing,FlightAware ADS-B (KATL)
Fri 05:43:31 PM,33.5885,-84.5753,222,255,293,4725,2109 Climbing,FlightAware ADS-B (KPDK)
Fri 05:43:47 PM,33.5704,-84.5836,188,259,298,5400,2488 Climbing,FlightAware ADS-B (KATL)
Fri 05:44:12 PM,33.5399,-84.5826,179,266,306,6425,2394 Climbing,FlightAware ADS-B (KATL)
Fri 05:44:34 PM,33.5127,-84.5837,182,269,310,7275,2211 Climbing,FlightAware ADS-B (KFGU)
Fri 05:44:50 PM,33.4916,-84.5800,162,281,323,7825,2118 Climbing,FlightAware ADS-B (KATL)
Fri 05:45:08 PM,33.4722,-84.5640,134,295,339,8475,1875 Climbing,FlightAware ADS-B (KATL)
Fri 05:45:26 PM,33.4593,-84.5386,113,308,354,8950,1853 Climbing,FlightAware ADS-B (KPDK)
Fri 05:45:42 PM,33.4562,-84.5104,89,320,368,9525,1904 Climbing,FlightAware ADS-B (KATL)
Fri 05:46:18 PM,33.4561,-84.4448,91,334,384,10600,1477 Climbing,FlightAware ADS-B (KATL)
Fri 05:46:48 PM,33.4556,-84.3886,91,356,410,11150,1269 Climbing,FlightAware ADS-B (KATL)
Fri 05:47:10 PM,33.4586,-84.3436,78,368,423,11700,1819 Climbing,FlightAware ADS-B (KRYY)
Fri 05:47:35 PM,33.4734,-84.2964,63,372,428,12575,1943 Climbing,FlightAware ADS-B (KCCO)
Fri 05:48:11 PM,33.5031,-84.2307,62,379,436,13675,1500 Climbing,FlightAware ADS-B (KATL)
Fri 05:48:41 PM,33.5273,-84.1757,62,399,459,14225,1000 Climbing,FlightAware ADS-B (KLZU)
Fri 05:49:11 PM,33.5541,-84.1146,62,418,481,14675,925 Climbing,FlightAware ADS-B (KFGU)
Fri 05:49:41 PM,33.5830,-84.0503,62,434,499,15150,1100 Climbing,FlightAware ADS-B (KATL)
Fri 05:50:11 PM,33.6108,-83.9888,62,442,509,15775,1425 Climbing,FlightAware ADS-B (KLZU)
Fri 05:50:41 PM,33.6389,-83.9247,62,447,514,16575,1550 Climbing,FlightAware ADS-B (KCZL)
Fri 05:51:11 PM,33.6693,-83.8567,62,453,521,17325,1575 Climbing,FlightAware ADS-B (KPDK)
Fri 05:51:41 PM,33.6995,-83.7881,62,457,526,18150,1750 Climbing,FlightAware ADS-B (KATL)
Fri 05:52:11 PM,33.7294,-83.7201,62,453,521,19075,1650 Climbing,FlightAware ADS-B (KCNI)
Fri 05:52:41 PM,33.7582,-83.6547,62,455,524,19800,1297 Climbing,FlightAware ADS-B (KGVL)
Fri 05:53:25 PM,33.8066,-83.5637,57,460,529,20675,1196 Climbing,FlightAware ADS-B (KCNI)
Fri 05:53:55 PM,33.8433,-83.4954,57,466,536,21275,1100 Climbing,FlightAware ADS-B (KDNL)
Fri 05:54:25 PM,33.8780,-83.4300,58,471,542,21775,1100 Climbing,FlightAware ADS-B (KCNI)
Fri 05:54:55 PM,33.9126,-83.3654,57,473,544,22375,1325 Climbing,FlightAware ADS-B (KAHN)
Fri 05:55:25 PM,33.9479,-83.2992,58,472,543,23100,1050 Climbing,FlightAware ADS-B (KPDK)
Fri 05:55:55 PM,33.9832,-83.2324,58,479,551,23425,575 Climbing,FlightAware ADS-B (KLZU)
Fri 05:56:25 PM,34.0183,-83.1660,58,485,558,23675,700 Climbing,FlightAware ADS-B (KCHA)
Fri 05:56:55 PM,34.0562,-83.0946,58,487,560,24125,875 Climbing,FlightAware ADS-B (KLZU)
Fri 05:57:25 PM,34.0922,-83.0263,58,489,563,24550,850 Climbing,FlightAware ADS-B (KWDR)
Fri 05:57:55 PM,34.1284,-82.9577,57,490,564,24975,675 Climbing,FlightAware ADS-B (KBHM)
Fri 05:58:25 PM,34.1654,-82.8875,58,496,571,25225,475 Climbing,FlightAware ADS-B (KLZU)
Fri 05:58:55 PM,34.2036,-82.8143,58,502,578,25450,500 Climbing,FlightAware ADS-B (KCEU)
Fri 05:59:25 PM,34.2383,-82.7478,58,506,582,25725,750 Climbing,FlightAware ADS-B (KDZJ)
Fri 05:59:55 PM,34.2771,-82.6731,58,503,579,26200,925 Climbing,FlightAware ADS-B (KFGU)
Fri 06:00:25 PM,34.3133,-82.6025,58,501,577,26650,865 Climbing,FlightAware ADS-B (KGVL)
Fri 06:00:47 PM,34.3416,-82.5474,61,503,579,26950,868 Climbing,FlightAware ADS-B (KEBA)
Fri 06:01:03 PM,34.3551,-82.5073,71,504,580,27200,911 Climbing,FlightAware ADS-B (KHMP)
Fri 06:01:43 PM,34.3837,-82.4014,72,503,579,27800,814 Climbing,FlightAware ADS-B (KATL)
Fri 06:02:13 PM,34.4051,-82.3224,72,504,580,28150,750 Climbing,FlightAware ADS-B (KGYH)
Fri 06:02:43 PM,34.4274,-82.2424,72,505,581,28550,525 Climbing,FlightAware ADS-B (KLQK)
Fri 06:03:13 PM,34.4504,-82.1610,71,512,589,28675,650 Climbing,FlightAware ADS-B (KJAU)
Fri 06:03:43 PM,34.4735,-82.0805,70,508,585,29200,964 Climbing,FlightAware ADS-B (KFQD)
Fri 06:04:09 PM,34.5051,-82.0169,52,504,580,29575,1140 Climbing,FlightAware ADS-B (KJAU)
Fri 06:04:33 PM,34.5405,-81.9647,49,496,571,30150,1278 Climbing,FlightAware ADS-B (KEQY)
Fri 06:05:03 PM,34.5875,-81.9002,49,492,566,30725,1175 Climbing,FlightAware ADS-B (KTYS)
Fri 06:05:33 PM,34.6322,-81.8416,47,488,562,31325,1075 Climbing,FlightAware ADS-B (KDNL)
Fri 06:06:03 PM,34.6770,-81.7841,47,490,564,31800,1075 Climbing,FlightAware ADS-B (KUZA)
Fri 06:06:33 PM,34.7246,-81.7226,47,489,563,32400,1125 Climbing,FlightAware ADS-B (KEQY)
Fri 06:07:03 PM,34.7693,-81.6645,47,487,560,32925,590 Climbing,FlightAware ADS-B (KHKY)
Fri 06:07:34 PM,34.8204,-81.5986,47,488,562,33000,73 Climbing,FlightAware ADS-B (KAND)
Fri 06:08:05 PM,34.8662,-81.5386,48,487,560,33000,25 Climbing,FlightAware ADS-B (KEXX)
Fri 06:08:35 PM,34.9104,-81.4801,47,486,559,33025,Level,FlightAware ADS-B (KJAU)
Fri 06:09:05 PM,34.9572,-81.4188,47,486,559,33000,Level,FlightAware ADS-B (KINT)
Fri 06:09:37 PM,35.0066,-81.3540,47,487,560,33025,Level,FlightAware ADS-B (KSPA)
Fri 06:10:08 PM,35.0548,-81.2909,47,487,560,33000,-24 Descending,FlightAware ADS-B (KRUQ)
Fri 06:10:39 PM,35.1003,-81.2311,47,487,560,33000,Level,FlightAware ADS-B (KRUQ)
Fri 06:11:02 PM,35.1372,-81.1832,46,487,560,33000,Level,FlightAware ADS-B (KGEV)
Fri 06:11:20 PM,35.1710,-81.1527,31,483,556,33000,27 Climbing,FlightAware ADS-B (KJQF)
Fri 06:11:58 PM,35.2449,-81.1077,27,482,555,33025,22 Climbing,FlightAware ADS-B (KUKF)
Fri 06:12:28 PM,35.3061,-81.0755,22,479,551,33025,-25 Descending,FlightAware ADS-B (KHQU)
Fri 06:12:58 PM,35.3691,-81.0443,22,478,550,33000,-25 Descending,FlightAware ADS-B (KPSK)
Fri 06:13:29 PM,35.4303,-81.0152,21,480,552,33000,Level,FlightAware ADS-B (KPSK)
Fri 06:13:59 PM,35.4933,-80.9850,22,480,552,33000,Level,FlightAware ADS-B (KHQU)
Fri 06:14:29 PM,35.5562,-80.9549,21,480,552,33000,Level,FlightAware ADS-B (KSVH)
Fri 06:14:59 PM,35.6176,-80.9257,22,482,555,33000,Level,FlightAware ADS-B (KDKX)
Fri 06:15:29 PM,35.6802,-80.8949,22,482,555,33000,25 Climbing,FlightAware ADS-B (KDKX)
Fri 06:15:59 PM,35.7430,-80.8648,22,483,556,33025,Level,FlightAware ADS-B (KDKX)
Fri 06:16:29 PM,35.8056,-80.8341,22,483,556,33000,-25 Descending,FlightAware ADS-B (KDKX)
Fri 06:16:59 PM,35.8669,-80.8044,22,483,556,33000,Level,FlightAware ADS-B (KJQF)
Fri 06:17:29 PM,35.9293,-80.7739,22,484,557,33000,Level,FlightAware ADS-B (KUKF)
Fri 06:17:59 PM,35.9931,-80.7427,22,485,558,33000,Level,FlightAware ADS-B (KUKF)
Fri 06:18:29 PM,36.0546,-80.7128,22,486,559,33000,Level,FlightAware ADS-B (KGSP)
Fri 06:18:54 PM,36.1052,-80.6879,24,488,562,33000,Level,FlightAware ADS-B (KBUY)
Fri 06:19:41 PM,36.1948,-80.6053,40,495,570,33000,Level,FlightAware ADS-B (KCLT)
Fri 06:20:12 PM,36.2477,-80.5509,40,495,570,33000,Level,FlightAware ADS-B (KPSK)
Fri 06:20:42 PM,36.2986,-80.4982,40,496,571,33000,Level,FlightAware ADS-B (KEXX)
Fri 06:21:12 PM,36.3530,-80.4422,40,495,570,33000,Level,FlightAware ADS-B (KUKF)
Fri 06:21:43 PM,36.4071,-80.3865,40,495,570,33000,25 Climbing,FlightAware ADS-B (KEQY)
Fri 06:22:13 PM,36.4593,-80.3329,40,494,568,33025,Level,FlightAware ADS-B (KRDU)
Fri 06:22:43 PM,36.5132,-80.2785,39,493,567,33000,-25 Descending,FlightAware ADS-B (KROA)
Fri 06:23:13 PM,36.5662,-80.2253,39,492,566,33000,Level,FlightAware ADS-B (KBCB)
Fri 06:23:43 PM,36.6194,-80.1721,39,492,566,33000,Level,FlightAware ADS-B (KROA)
Fri 06:24:13 PM,36.6728,-80.1181,39,493,567,33000,25 Climbing,FlightAware ADS-B (KRDU)
Fri 06:24:43 PM,36.7256,-80.0648,39,493,567,33025,25 Climbing,FlightAware ADS-B (KHLX)
Fri 06:25:13 PM,36.7782,-80.0113,39,493,567,33025,-25 Descending,FlightAware ADS-B (KROA)
Fri 06:25:43 PM,36.8327,-79.9560,39,493,567,33000,Level,FlightAware ADS-B (KGSO)
Fri 06:26:13 PM,36.8856,-79.9027,39,494,568,33025,25 Climbing,FlightAware ADS-B (KGSO)
Fri 06:26:43 PM,36.9387,-79.8487,39,495,570,33025,-25 Descending,FlightAware ADS-B (KJPN)
Fri 06:27:14 PM,36.9935,-79.7926,40,495,570,33000,-25 Descending,FlightAware ADS-B (KLYH)
Fri 06:27:44 PM,37.0462,-79.7390,39,496,571,33000,25 Climbing,FlightAware ADS-B (KLYH)
Fri 06:28:14 PM,37.0998,-79.6843,39,497,572,33025,Level,FlightAware ADS-B (KLYH)
Fri 06:28:44 PM,37.1527,-79.6306,39,498,573,33000,-75 Descending,FlightAware ADS-B (KLYH)
Fri 06:29:14 PM,37.2072,-79.5755,39,499,574,32950,-412 Descending,FlightAware ADS-B (KMPG)
Fri 06:29:35 PM,37.2462,-79.5368,37,497,572,32650,-818 Descending,FlightAware ADS-B (KJPN)
Fri 06:29:58 PM,37.2911,-79.5064,23,490,564,32350,-750 Descending,FlightAware ADS-B (KLHZ)
Fri 06:30:15 PM,37.3276,-79.4939,11,483,556,32150,-750 Descending,FlightAware ADS-B (KOMH)
Fri 06:30:32 PM,37.3651,-79.4906,360,477,549,31925,-711 Descending,FlightAware ADS-B (KLYH)
Fri 06:30:53 PM,37.4120,-79.4912,0,481,554,31700,-679 Descending,FlightAware ADS-B (KIPJ)
Fri 06:31:14 PM,37.4568,-79.4907,1,483,556,31450,-731 Descending,FlightAware ADS-B (KOFP)
Fri 06:31:32 PM,37.4998,-79.4906,360,483,556,31225,-735 Descending,FlightAware ADS-B (KPSK)
Fri 06:32:05 PM,37.5740,-79.4909,360,485,558,30825,-682 Descending,FlightAware ADS-B (KGSO)
Fri 06:32:27 PM,37.6215,-79.4912,0,486,559,30600,-679 Descending,FlightAware ADS-B (KLYH)
Fri 06:32:47 PM,37.6662,-79.4917,359,487,560,30350,-750 Descending,FlightAware ADS-B (KEXX)
Fri 06:33:07 PM,37.7110,-79.4923,360,486,559,30100,-780 Descending,FlightAware ADS-B (KLYH)
Fri 06:33:37 PM,37.7776,-79.4927,0,483,556,29700,-781 Descending,FlightAware ADS-B (KLYH)
Fri 06:33:55 PM,37.8212,-79.4928,360,483,556,29475,-750 Descending,FlightAware ADS-B (KCHO)
Fri 06:34:11 PM,37.8558,-79.4927,0,483,556,29275,-721 Descending,FlightAware ADS-B (KDAA)
Fri 06:34:47 PM,37.9358,-79.4928,360,486,559,28850,-711 Descending,FlightAware ADS-B (KLYH)
Fri 06:35:08 PM,37.9829,-79.4930,360,488,562,28600,-825 Descending,FlightAware ADS-B (KLYH)
Fri 06:35:27 PM,38.0236,-79.4934,360,489,563,28300,-783 Descending,FlightAware ADS-B (KLYH)
Fri 06:35:54 PM,38.0826,-79.4937,360,490,564,28000,-690 Descending,FlightAware ADS-B (KFDK)
Fri 06:36:17 PM,38.1417,-79.4940,360,488,562,27725,-805 Descending,FlightAware ADS-B (KGSO)
Fri 06:36:35 PM,38.1817,-79.4938,0,487,560,27450,-829 Descending,FlightAware ADS-B (KCGS)
Fri 06:36:55 PM,38.2236,-79.4938,0,486,559,27200,-1013 Descending,FlightAware ADS-B (KAVL)
Fri 06:37:15 PM,38.2705,-79.4939,360,485,558,26775,-1826 Descending,FlightAware ADS-B (KLYH)
Fri 06:37:41 PM,38.3281,-79.4905,8,485,558,25800,-2198 Descending,FlightAware ADS-B (KIAD)
Fri 06:37:58 PM,38.3676,-79.4786,16,484,557,25200,-2230 Descending,FlightAware ADS-B (KZZV)
Fri 06:38:18 PM,38.4061,-79.4595,25,481,554,24425,-1600 Descending,FlightAware ADS-B (KFDK)
Fri 06:38:43 PM,38.4537,-79.4218,37,461,531,24000,-567 Descending,FlightAware ADS-B (KBWI)
Fri 06:39:03 PM,38.4844,-79.3860,46,458,527,24000,Level,FlightAware ADS-B (KLYH)
Fri 06:39:19 PM,38.5077,-79.3497,54,458,527,24000,-366 Descending,FlightAware ADS-B (KMRB)
Fri 06:39:44 PM,38.5328,-79.2966,64,460,529,23750,-1326 Descending,FlightAware ADS-B (KUSW)
Fri 06:40:02 PM,38.5477,-79.2489,72,461,531,23050,-2384 Descending,FlightAware ADS-B (KMRB)
Fri 06:40:40 PM,38.5677,-79.1492,76,450,518,21525,-2382 Descending,FlightAware ADS-B (KCHO)
Fri 06:41:10 PM,38.5821,-79.0766,76,433,498,20350,-2150 Descending,FlightAware ADS-B (KSHD)
Fri 06:41:40 PM,38.5963,-79.0021,77,421,484,19375,-1958 Descending,FlightAware ADS-B (KSHD)
Fri 06:42:22 PM,38.6123,-78.9011,78,413,475,18000,-1917 Descending,FlightAware ADS-B (KSHD)
Fri 06:42:52 PM,38.6243,-78.8281,78,414,476,17075,-1978 Descending,FlightAware ADS-B (KHGR)
Fri 06:43:31 PM,38.6457,-78.7375,73,404,465,15725,-2065 Descending,FlightAware ADS-B (KJYO)
Fri 06:44:01 PM,38.6619,-78.6680,73,392,451,14700,-1950 Descending,FlightAware ADS-B (KFDK)
Fri 06:44:31 PM,38.6769,-78.6045,73,380,437,13775,-1850 Descending,FlightAware ADS-B (KRMN)
Fri 06:45:01 PM,38.6923,-78.5408,73,369,425,12850,-1525 Descending,FlightAware ADS-B (KAGC)
Fri 06:45:31 PM,38.7071,-78.4774,74,348,400,12250,-975 Descending,FlightAware ADS-B (KIAD)
Fri 06:46:01 PM,38.7202,-78.4223,73,342,394,11875,-725 Descending,FlightAware ADS-B (KIAD)
Fri 06:46:31 PM,38.7343,-78.3623,74,342,394,11525,-765 Descending,FlightAware ADS-B (KDCA)
Fri 06:46:50 PM,38.7373,-78.3263,94,337,388,11250,-512 Descending,FlightAware ADS-B (KJYO)
Fri 06:47:12 PM,38.7253,-78.2887,122,323,372,11175,-115 Descending,FlightAware ADS-B (KJYO)
Fri 06:47:29 PM,38.7063,-78.2662,149,302,348,11175,Level,FlightAware ADS-B (KJYO)
Fri 06:47:47 PM,38.6825,-78.2593,178,284,327,11175,Level,FlightAware ADS-B (KDCA)
Fri 06:48:05 PM,38.6613,-78.2687,211,270,311,11175,Level,FlightAware ADS-B (KJYO)
Fri 06:48:22 PM,38.6486,-78.2883,241,266,306,11175,Level,FlightAware ADS-B (KBWI)
Fri 06:48:39 PM,38.6439,-78.3136,262,266,306,11175,-27 Descending,FlightAware ADS-B (KDCA)
Fri 06:49:18 PM,38.6411,-78.3772,268,262,302,11150,Level,FlightAware ADS-B (KIAD)
Fri 06:49:35 PM,38.6360,-78.4012,248,261,300,11175,27 Climbing,FlightAware ADS-B (KJPN)
Fri 06:50:14 PM,38.6142,-78.4551,243,262,302,11175,-26 Descending,FlightAware ADS-B (KSHD)
Fri 06:50:32 PM,38.6097,-78.4826,275,269,310,11150,Level,FlightAware ADS-B (KRMN)
Fri 06:50:48 PM,38.6165,-78.5060,302,279,321,11175,44 Climbing,FlightAware ADS-B (KCHO)
Fri 06:51:06 PM,38.6362,-78.5265,329,295,339,11175,Level,FlightAware ADS-B (KHGR)
Fri 06:51:22 PM,38.6563,-78.5316,355,312,359,11175,Level,FlightAware ADS-B (KSHD)
Fri 06:51:40 PM,38.6830,-78.5226,25,328,377,11175,Level,FlightAware ADS-B (KDCA)
Fri 06:51:56 PM,38.7016,-78.5026,49,338,389,11175,Level,FlightAware ADS-B (KFDK)
Fri 06:52:18 PM,38.7156,-78.4611,76,339,390,11175,Level,FlightAware ADS-B (KJYO)
Fri 06:52:49 PM,38.7268,-78.3995,76,339,390,11175,Level,FlightAware ADS-B (KJYO)
Fri 06:53:15 PM,38.7362,-78.3511,80,340,391,11175,-34 Descending,FlightAware ADS-B (KDCA)
Fri 06:53:33 PM,38.7338,-78.3134,104,334,384,11150,Level,FlightAware ADS-B (KDCA)
Fri 06:53:53 PM,38.7189,-78.2844,134,317,365,11175,41 Climbing,FlightAware ADS-B (KLUA)
Fri 06:54:10 PM,38.6982,-78.2698,162,297,342,11175,Level,FlightAware ADS-B (KOKV)
Fri 06:54:30 PM,38.6706,-78.2717,200,273,314,11175,Level,FlightAware ADS-B (KOFP)
Fri 06:54:47 PM,38.6556,-78.2859,227,264,304,11175,Level,FlightAware ADS-B (KCHO)
Fri 06:55:03 PM,38.6442,-78.3101,246,264,304,11175,Level,FlightAware ADS-B (KFDK)
Fri 06:55:19 PM,38.6389,-78.3323,256,264,304,11175,Level,FlightAware ADS-B (KFDK)
Fri 06:55:35 PM,38.6338,-78.3576,255,264,304,11175,Level,FlightAware ADS-B (KVKX)
Fri 06:56:05 PM,38.6236,-78.4004,253,264,304,11175,-29 Descending,FlightAware ADS-B (KJPN)
Fri 06:56:26 PM,38.6178,-78.4330,267,266,306,11150,-158 Descending,FlightAware ADS-B (KLYH)
Fri 06:56:43 PM,38.6227,-78.4612,290,272,313,11075,-42 Descending,FlightAware ADS-B (KMRB)
Fri 06:57:02 PM,38.6365,-78.4826,321,288,331,11125,129 Climbing,FlightAware ADS-B (KHWY)
Fri 06:57:18 PM,38.6580,-78.4937,351,309,356,11150,88 Climbing,FlightAware ADS-B (KCJR)
Fri 06:57:36 PM,38.6845,-78.4901,16,326,375,11175,42 Climbing,FlightAware ADS-B (KHWY)
Fri 06:57:54 PM,38.7066,-78.4718,43,338,389,11175,Level,FlightAware ADS-B (KIAD)
Fri 06:58:10 PM,38.7206,-78.4431,67,340,391,11175,-68 Descending,FlightAware ADS-B (KMRB)
Fri 06:58:38 PM,38.7338,-78.3882,72,340,391,11125,-389 Descending,FlightAware ADS-B (KIAD)
Fri 06:59:04 PM,38.7550,-78.3470,50,337,388,10825,-830 Descending,FlightAware ADS-B (KJPN)
Fri 06:59:34 PM,38.7859,-78.3016,49,332,382,10350,-895 Descending,FlightAware ADS-B (KVKX)
Fri 07:00:01 PM,38.8146,-78.2607,47,331,381,9975,-955 Descending, FlightAware ADS-B (KIAD)
Fri 07:00:29 PM,38.8411,-78.2236,49,326,375,9475,-1309 Descending,FlightAware ADS-B (KVKX)
Fri 07:00:48 PM,38.8579,-78.1889,65,326,375,8950,-1542 Descending,FlightAware ADS-B (KJYO)
Fri 07:01:05 PM,38.8651,-78.1615,74,327,376,8550,-1500 Descending,FlightAware ADS-B (KCJR)
Fri 07:01:35 PM,38.8791,-78.1052,71,320,368,7775,-1475 Descending,FlightAware ADS-B (KFDK)
Fri 07:02:05 PM,38.8929,-78.0518,72,318,366,7075,-1100 Descending,FlightAware ADS-B (KIAD)
Fri 07:02:35 PM,38.9062,-77.9996,72,314,361,6675,-825 Descending,FlightAware ADS-B (KIAD)
Fri 07:03:05 PM,38.9199,-77.9442,73,310,357,6250,-500 Descending,FlightAware ADS-B (KFDK)
Fri 07:03:35 PM,38.9322,-77.8938,72,310,357,6175,-75 Descending,FlightAware ADS-B (KOKV)
Fri 07:04:05 PM,38.9461,-77.8381,73,310,357,6175,Level,FlightAware ADS-B (KJYO)
Fri 07:04:35 PM,38.9585,-77.7881,72,311,358,6175,Level,FlightAware ADS-B (KHEF)
Fri 07:04:55 PM,38.9637,-77.7527,86,306,352,6175,Level,FlightAware ADS-B (KIAD)
Fri 07:05:14 PM,38.9609,-77.7173,100,300,345,6175,Level,FlightAware ADS-B (KHEF)
Fri 07:05:44 PM,38.9551,-77.6661,98,303,349,6175,-32 Descending,FlightAware ADS-B (KGAI)
Fri 07:06:01 PM,38.9477,-77.6361,116,295,339,6150,Level,FlightAware ADS-B (KIAD)
Fri 07:06:17 PM,38.9327,-77.6158,144,276,318,6175,Level,FlightAware ADS-B (KIAD)
Fri 07:06:44 PM,38.9065,-77.6013,158,231,266,6150,-26 Descending,FlightAware ADS-B (KIAD)
Fri 07:07:14 PM,38.8780,-77.5854,156,203,234,6150,29 Climbing,FlightAware ADS-B (KJYO)
Fri 07:07:36 PM,38.8595,-77.5757,160,205,236,6175,38 Climbing,FlightAware ADS-B (KDAA)
Fri 07:07:54 PM,38.8431,-77.5725,179,195,224,6175,-286 Descending,FlightAware ADS-B (KDCA)
Fri 07:08:18 PM,38.8216,-77.5746,184,192,221,5975,-1000 Descending,FlightAware ADS-B (KDCA)
Fri 07:08:48 PM,38.7960,-77.5760,182,188,216,5275,-906 Descending,FlightAware ADS-B (KIAD)
Fri 07:09:11 PM,38.7754,-77.5767,182,187,215,5175,-192 Descending,FlightAware ADS-B (KIAD)
Fri 07:09:27 PM,38.7614,-77.5768,173,188,216,5150,-516 Descending,FlightAware ADS-B (KIAD)
Fri 07:09:43 PM,38.7489,-77.5696,144,200,230,4900,-1219 Descending,FlightAware ADS-B (KHEF)
Fri 07:09:59 PM,38.7412,-77.5521,105,220,253,4500,-1453 Descending,FlightAware ADS-B (KHEF)
Fri 07:10:15 PM,38.7411,-77.5306,86,224,258,4125,-1500 Descending,FlightAware ADS-B (KIAD)
Fri 07:10:31 PM,38.7427,-77.5096,84,224,258,3700,-1547 Descending,FlightAware ADS-B (KIAD)
Fri 07:10:47 PM,38.7496,-77.4901,55,224,258,3300,-1313 Descending,FlightAware ADS-B (KDCA)
Fri 07:11:03 PM,38.7626,-77.4793,20,212,244,3000,-1078 Descending,FlightAware ADS-B (KJYO)
Fri 07:11:19 PM,38.7781,-77.4777,3,205,236,2725,-1029 Descending,FlightAware ADS-B (KJPN)
Fri 07:11:38 PM,38.7973,-77.4770,1,195,224,2400,-673 Descending,FlightAware ADS-B (KDCA)
Fri 07:12:08 PM,38.8238,-77.4766,1,192,221,2175,-225 Descending,FlightAware ADS-B (KIAD)
Fri 07:12:38 PM,38.8490,-77.4762,1,191,220,2175,-188 Descending,FlightAware ADS-B (KIAD)
Fri 07:12:56 PM,38.8652,-77.4760,1,189,217,2025,-750 Descending,FlightAware ADS-B (KIAD)
Fri 07:13:12 PM,38.8783,-77.4758,1,178,205,1750,-938 Descending,FlightAware ADS-B (KIAD)
Fri 07:13:28 PM,38.8906,-77.4757,1,159,183,1525,-844 Descending,FlightAware ADS-B (KIAD)
Fri 07:13:44 PM,38.9017,-77.4754,1,141,162,1300,-750 Descending,FlightAware ADS-B (KIAD)
Fri 07:14:00 PM,38.9119,-77.4753,0,141,162,1125,-656 Descending,FlightAware ADS-B (KDAA)
Fri 07:14:16 PM,38.9220,-77.4752,1,139,160,950,-703 Descending,FlightAware ADS-B (KIAD)
Fri 07:14:32 PM,38.9324,-77.4750,0,135,155,750,-750 Descending,FlightAware ADS-B (KIAD)
Fri 07:14:48 PM,38.9434,-77.4749,1,134,154,550,-563 Descending,FlightAware ADS-B (KIAD)
Fri 07:15:04 PM,38.9524,-77.4748,1,129,148,450,-375 Descending,FlightAware ADS-B (KHEF)"""

# get columns
cols = [s for s in flight_track.split("\n")[0].split(",")]
print("columns -->",cols)
# get data
data = [[r for r in s.split(",")] for s in flight_track.split("\n")[1:]]
pp.pprint(data[:5])

# [f"{i}-{len(d)}" for i,d in enumerate(data)]

フライト追跡データフレーム

time_edtに基づき、イベントに対するランクを適用するために、Sparkのウィンドウ関数を使用します。

df = (
    spark.createDataFrame(data, cols)
        .withColumn("dt_aug", F.concat(F.lit("2021-05-28 "),F.substring("time_edt",5,8)))
        .withColumn("dt_edt", F.to_timestamp("dt_aug"))
        .withColumn("dt_rank", F.rank().over(Window.orderBy('dt_edt')))
    .selectExpr(
        "dt_rank", "time_edt", "cast(latitude as double)", "cast(longitude as double)", "cast(course as int)",
        "cast(kts as int)", "cast(mph as int)", "cast(feet as int)", "rate", "reporting_facility"
    )
    .orderBy("dt_rank")
)

display(df)

def display_kepler(kmap:KeplerGl, height=800, width=1200) -> None:
    """
    Convenience function to render map in kepler.gl
    - use this when cannot directly render or
      want to go beyond the %%mosaic_kepler magic.
    """
    displayHTML(
        kmap
            ._repr_html_()
            .decode("utf-8")
            .replace(".height||400", f".height||{height}")
            .replace(".width||400", f".width||{width}")
    )

地図定義

map_1_config = {
  "version": "v1",
  "config": {
    "visState": {
      "filters": [],
      "layers": [
        {
          "id": "djdqoj4",
          "type": "point",
          "config": {
            "dataId": "flight_track",
            "label": "Point",
            "color": [
              18,
              147,
              154
            ],
            "columns": {
              "lat": "latitude",
              "lng": "longitude",
              "altitude": None
            },
            "isVisible": True,
            "visConfig": {
              "radius": 10,
              "fixedRadius": False,
              "opacity": 0.8,
              "outline": False,
              "thickness": 2,
              "strokeColor": None,
              "colorRange": {
                "name": "Global Warming",
                "type": "sequential",
                "category": "Uber",
                "colors": [
                  "#5A1846",
                  "#900C3F",
                  "#C70039",
                  "#E3611C",
                  "#F1920E",
                  "#FFC300"
                ]
              },
              "strokeColorRange": {
                "name": "Global Warming",
                "type": "sequential",
                "category": "Uber",
                "colors": [
                  "#5A1846",
                  "#900C3F",
                  "#C70039",
                  "#E3611C",
                  "#F1920E",
                  "#FFC300"
                ]
              },
              "radiusRange": [
                0,
                50
              ],
              "filled": True
            },
            "hidden": False,
            "textLabel": [
              {
                "field": None,
                "color": [
                  255,
                  255,
                  255
                ],
                "size": 18,
                "offset": [
                  0,
                  0
                ],
                "anchor": "start",
                "alignment": "center"
              }
            ]
          },
          "visualChannels": {
            "colorField": None,
            "colorScale": "quantile",
            "strokeColorField": None,
            "strokeColorScale": "quantile",
            "sizeField": None,
            "sizeScale": "linear"
          }
        }
      ],
      "interactionConfig": {
        "tooltip": {
          "fieldsToShow": {
            "flight_track": [
              {
                "name": "dt_rank",
                "format": None
              },
              {
                "name": "time_edt",
                "format": None
              },
              {
                "name": "course",
                "format": None
              },
              {
                "name": "kts",
                "format": None
              },
              {
                "name": "mph",
                "format": None
              }
            ]
          },
          "compareMode": False,
          "compareType": "absolute",
          "enabled": True
        },
        "brush": {
          "size": 0.3,
          "enabled": False
        },
        "geocoder": {
          "enabled": False
        },
        "coordinate": {
          "enabled": False
        }
      },
      "layerBlending": "normal",
      "splitMaps": [],
      "animationConfig": {
        "currentTime": None,
        "speed": 1
      }
    },
    "mapState": {
      "bearing": 0,
      "dragRotate": False,
      "latitude": 38.67071059328123,
      "longitude": -78.19625488193067,
      "pitch": 0,
      "zoom": 8.03406766396259,
      "isSplit": False
    },
    "mapStyle": {
      "styleType": "dark",
      "topLayerGroups": {},
      "visibleLayerGroups": {
        "label": True,
        "road": True,
        "border": False,
        "building": True,
        "water": True,
        "land": True,
        "3d building": False
      },
      "threeDBuildingColor": [
        9.665468314072013,
        17.18305478057247,
        31.1442867897876
      ],
      "mapStyles": {}
    }
  }
}

print("'map_1_config' ran...")

Kepler.glにフライトイベントをプロット

dt_rank が141 から 181であるイベントで待機パターンが生じています(ツールチップを見るにはポイント上にホバーします)。

map_1 = KeplerGl(height=600, config=map_1_config)
map_1.add_data(data=df.toPandas(), name="flight_track")
display_kepler(map_1)

display(df.filter("dt_rank >= 141").filter("dt_rank <= 181"))

H3グリッドの解像度

飛行機が ~483 kph (300 mph)で飛行しているならば、フライトデータレポートの間隔と近い、分当たり ~8 km (5 mi) や 30秒当たり ~4 km (2.5 mi) や 15秒あたり 2 km (1.25 mi) をカバーしていることになります。 .737 km^2 のH3解像度の8が直接的な航路距離では有用となります。しかし、 我々は待機パターンに着目しているので、初期分析では解像度6を用います。

H3 Resolution	Average Hexagon Area (km2)	Average Hexagon Edge Length (km)	Number of unique indexes
0	4,250,546.8477000	1,107.712591000	122
1	607,220.9782429	418.676005500	842
2	86,745.8540347	158.244655800	5,882
3	12,392.2648621	59.810857940	41,162
4	1,770.3235517	22.606379400	288,122
5	252.9033645	8.544408276	2,016,842
6	36.1290521	3.229482772	14,117,882
7	5.1612932	1.220629759	98,825,162
8	0.7373276	0.461354684	691,776,122
9	0.1053325	0.174375668	4,842,432,842
10	0.0150475	0.065907807	33,897,029,882
11	0.0021496	0.024910561	237,279,209,162
12	0.0003071	0.009415526	1,660,954,464,122
13	0.0000439	0.003559893	11,626,681,248,842
14	0.0000063	0.001348575	81,386,768,741,882
15	0.0000009	0.000509713	569,707,381,193,162

From https://www.programmersought.com/article/30995709682/

H3 解像度 6 のデータフレーム `df_hex6`

H3セルの緯度経度を取得するための h3_longlatash3
H3セルのセントロイドをWKT(Well Known Text)として取得するための h3_centeraswkt

df_hex6 =  (
    df
        .withColumn("h3_resolution", F.lit(6))
        .withColumn("hex_id", h3_longlatash3("longitude", "latitude", 6))
        .withColumn("hex_centroid", h3_centeraswkt("hex_id"))
    .select(*df.columns,"hex_id","hex_centroid","h3_resolution")
)

display(df_hex6)

地図の定義

map_4_config = {
  "version": "v1",
  "config": {
    "visState": {
      "filters": [],
      "layers": [
        {
          "id": "ypdgbzv",
          "type": "point",
          "config": {
            "dataId": "hex6_flight",
            "label": "Point",
            "color": [
              18,
              147,
              154
            ],
            "columns": {
              "lat": "latitude",
              "lng": "longitude",
              "altitude": None
            },
            "isVisible": True,
            "visConfig": {
              "radius": 10,
              "fixedRadius": False,
              "opacity": 0.8,
              "outline": False,
              "thickness": 2,
              "strokeColor": None,
              "colorRange": {
                "name": "Global Warming",
                "type": "sequential",
                "category": "Uber",
                "colors": [
                  "#5A1846",
                  "#900C3F",
                  "#C70039",
                  "#E3611C",
                  "#F1920E",
                  "#FFC300"
                ]
              },
              "strokeColorRange": {
                "name": "Global Warming",
                "type": "sequential",
                "category": "Uber",
                "colors": [
                  "#5A1846",
                  "#900C3F",
                  "#C70039",
                  "#E3611C",
                  "#F1920E",
                  "#FFC300"
                ]
              },
              "radiusRange": [
                0,
                50
              ],
              "filled": True
            },
            "hidden": False,
            "textLabel": [
              {
                "field": None,
                "color": [
                  255,
                  255,
                  255
                ],
                "size": 18,
                "offset": [
                  0,
                  0
                ],
                "anchor": "start",
                "alignment": "center"
              }
            ]
          },
          "visualChannels": {
            "colorField": None,
            "colorScale": "quantile",
            "strokeColorField": None,
            "strokeColorScale": "quantile",
            "sizeField": None,
            "sizeScale": "linear"
          }
        },
        {
          "id": "wf51rvc",
          "type": "hexagonId",
          "config": {
            "dataId": "hex6_dense_flight",
            "label": "hex_id",
            "color": [
              210,
              0,
              0
            ],
            "columns": {
              "hex_id": "hex_id"
            },
            "isVisible": True,
            "visConfig": {
              "opacity": 0.8,
              "colorRange": {
                "name": "Global Warming",
                "type": "sequential",
                "category": "Uber",
                "colors": [
                  "#5A1846",
                  "#900C3F",
                  "#C70039",
                  "#E3611C",
                  "#F1920E",
                  "#FFC300"
                ]
              },
              "coverage": 1,
              "enable3d": False,
              "sizeRange": [
                0,
                500
              ],
              "coverageRange": [
                0,
                1
              ],
              "elevationScale": 5
            },
            "hidden": False,
            "textLabel": [
              {
                "field": None,
                "color": [
                  255,
                  255,
                  255
                ],
                "size": 18,
                "offset": [
                  0,
                  0
                ],
                "anchor": "start",
                "alignment": "center"
              }
            ]
          },
          "visualChannels": {
            "colorField": None,
            "colorScale": "quantile",
            "sizeField": None,
            "sizeScale": "linear",
            "coverageField": None,
            "coverageScale": "linear"
          }
        },
        {
          "id": "meq5i7",
          "type": "hexagonId",
          "config": {
            "dataId": "hex6_flight",
            "label": "hex_id",
            "color": [
              255,
              248,
              51
            ],
            "columns": {
              "hex_id": "hex_id"
            },
            "isVisible": True,
            "visConfig": {
              "opacity": 0.8,
              "colorRange": {
                "name": "Global Warming",
                "type": "sequential",
                "category": "Uber",
                "colors": [
                  "#5A1846",
                  "#900C3F",
                  "#C70039",
                  "#E3611C",
                  "#F1920E",
                  "#FFC300"
                ]
              },
              "coverage": 1,
              "enable3d": False,
              "sizeRange": [
                0,
                500
              ],
              "coverageRange": [
                0,
                1
              ],
              "elevationScale": 5
            },
            "hidden": False,
            "textLabel": [
              {
                "field": None,
                "color": [
                  255,
                  255,
                  255
                ],
                "size": 18,
                "offset": [
                  0,
                  0
                ],
                "anchor": "start",
                "alignment": "center"
              }
            ]
          },
          "visualChannels": {
            "colorField": None,
            "colorScale": "quantile",
            "sizeField": None,
            "sizeScale": "linear",
            "coverageField": None,
            "coverageScale": "linear"
          }
        }
      ],
      "interactionConfig": {
        "tooltip": {
          "fieldsToShow": {
            "hex6_flight": [
              {
                "name": "dt_rank",
                "format": None
              },
              {
                "name": "time_edt",
                "format": None
              },
              {
                "name": "course",
                "format": None
              },
              {
                "name": "kts",
                "format": None
              },
              {
                "name": "mph",
                "format": None
              }
            ],
            "hex6_dense_flight": [
              {
                "name": "hex_id",
                "format": None
              },
              {
                "name": "count",
                "format": None
              }
            ]
          },
          "compareMode": False,
          "compareType": "absolute",
          "enabled": True
        },
        "brush": {
          "size": 0.5,
          "enabled": False
        },
        "geocoder": {
          "enabled": False
        },
        "coordinate": {
          "enabled": False
        }
      },
      "layerBlending": "normal",
      "splitMaps": [],
      "animationConfig": {
        "currentTime": None,
        "speed": 1
      }
    },
    "mapState": {
      "bearing": 0,
      "dragRotate": False,
      "latitude": 38.71144595005603,
      "longitude": -78.36777841190144,
      "pitch": 0,
      "zoom": 8.356475191956187,
      "isSplit": False
    },
    "mapStyle": {
      "styleType": "dark",
      "topLayerGroups": {},
      "visibleLayerGroups": {
        "label": True,
        "road": True,
        "border": False,
        "building": True,
        "water": True,
        "land": True,
        "3d building": False
      },
      "threeDBuildingColor": [
        9.665468314072013,
        17.18305478057247,
        31.1442867897876
      ],
      "mapStyles": {}
    }
  }
}

print("'map_4_config' ran...")

MPH > 275 + Count > 3 をH3解像度6でプロット (赤色)

df_hex6_dense = (
    df_hex6
        .filter("mph > 275")
        .groupBy("hex_id")
        .count()
        .filter("count > 3")
        .orderBy(F.desc("count"))
)

map_4 = KeplerGl(height=600, config=map_4_config)
map_4.add_data(data = df_hex6.select(*df.columns, h3_h3tostring("hex_id").alias("hex_id"), "hex_centroid", "h3_resolution").toPandas(), name = "hex6_flight")
map_4.add_data(data = df_hex6_dense.select(h3_h3tostring("hex_id").alias("hex_id"),"count").toPandas(), name = "hex6_dense_flight")
display_kepler(map_4)

評価: H3解像度6では、通常ポリゴンあたり1-3ポイントがあります。しかし、MPH > 275 で > 3 のポイントを持つ7つのエントリーはすべて待機パターンレンジ(141 - 181)にあります。これのメリットは同じポリゴンを経由する任意の繰り返し経路を直接検知できるさらなるポテンシャルがあることです。

K-Ringバリエーションの例

h3.hexring(<h3_id>,1)を使う場合にはセルの境界でデータが失われないので、上のアプローチではより多くのデータをキャプチャすることができますので、以下でそれを実施しています。

df_hex6_kring = (
    df_hex6
        .filter("mph > 275")
        .withColumn("k_rings_1", h3_hexring("hex_id", 1)) # h3_kring_udf("hex_id",F.lit(1)))
        .select("*", F.explode("k_rings_1").alias("k_ring_1"))
        .drop("k_rings_1", "hex_centroid")
        .orderBy("dt_rank")
)
print(f"count? {df_hex6_kring.count():,}")
display(df_hex6_kring.limit(100))

k_ring_1がhex_idとしてデータにも存在している行のみを維持しています。


df_hex6_join = (
    df_hex6
        .join(
            df_hex6_kring.selectExpr("dt_rank as dt_rank_k_ring", "k_ring_1"),
            col("k_ring_1") == col("hex_id")) # inner join
         .filter("mph > 275") # mphで事前フィルタ
         .select("dt_rank", "dt_rank_k_ring", "hex_id")
            .distinct()
        .orderBy("dt_rank","dt_rank_k_ring")
)

print(f"count? {df_hex6_join.count():,}")
display(df_hex6_join)

地図の定義

map_4a_config = {
  "version": "v1",
  "config": {
    "visState": {
      "filters": [],
      "layers": [
        {
          "id": "9njw9hq",
          "type": "hexagonId",
          "config": {
            "dataId": "hex6_kring_flight",
            "label": "hex_id",
            "color": [
              227,
              26,
              26
            ],
            "columns": {
              "hex_id": "hex_id"
            },
            "isVisible": True,
            "visConfig": {
              "opacity": 0.64,
              "colorRange": {
                "name": "Global Warming",
                "type": "sequential",
                "category": "Uber",
                "colors": [
                  "#5A1846",
                  "#900C3F",
                  "#C70039",
                  "#E3611C",
                  "#F1920E",
                  "#FFC300"
                ]
              },
              "coverage": 1,
              "enable3d": False,
              "sizeRange": [
                0,
                500
              ],
              "coverageRange": [
                0,
                1
              ],
              "elevationScale": 5
            },
            "hidden": False,
            "textLabel": [
              {
                "field": None,
                "color": [
                  255,
                  255,
                  255
                ],
                "size": 18,
                "offset": [
                  0,
                  0
                ],
                "anchor": "start",
                "alignment": "center"
              }
            ]
          },
          "visualChannels": {
            "colorField": None,
            "colorScale": "quantile",
            "sizeField": None,
            "sizeScale": "linear",
            "coverageField": None,
            "coverageScale": "linear"
          }
        },
        {
          "id": "r4cuopf",
          "type": "point",
          "config": {
            "dataId": "hex6_flight",
            "label": "Point",
            "color": [
              18,
              147,
              154
            ],
            "columns": {
              "lat": "latitude",
              "lng": "longitude",
              "altitude": None
            },
            "isVisible": True,
            "visConfig": {
              "radius": 10,
              "fixedRadius": False,
              "opacity": 0.8,
              "outline": False,
              "thickness": 2,
              "strokeColor": None,
              "colorRange": {
                "name": "Global Warming",
                "type": "sequential",
                "category": "Uber",
                "colors": [
                  "#5A1846",
                  "#900C3F",
                  "#C70039",
                  "#E3611C",
                  "#F1920E",
                  "#FFC300"
                ]
              },
              "strokeColorRange": {
                "name": "Global Warming",
                "type": "sequential",
                "category": "Uber",
                "colors": [
                  "#5A1846",
                  "#900C3F",
                  "#C70039",
                  "#E3611C",
                  "#F1920E",
                  "#FFC300"
                ]
              },
              "radiusRange": [
                0,
                50
              ],
              "filled": True
            },
            "hidden": False,
            "textLabel": [
              {
                "field": None,
                "color": [
                  255,
                  255,
                  255
                ],
                "size": 18,
                "offset": [
                  0,
                  0
                ],
                "anchor": "start",
                "alignment": "center"
              }
            ]
          },
          "visualChannels": {
            "colorField": None,
            "colorScale": "quantile",
            "strokeColorField": None,
            "strokeColorScale": "quantile",
            "sizeField": None,
            "sizeScale": "linear"
          }
        },
        {
          "id": "3bz7bh",
          "type": "hexagonId",
          "config": {
            "dataId": "hex6_flight",
            "label": "hex_id",
            "color": [
              255,
              251,
              152
            ],
            "columns": {
              "hex_id": "hex_id"
            },
            "isVisible": True,
            "visConfig": {
              "opacity": 0.8,
              "colorRange": {
                "name": "Global Warming",
                "type": "sequential",
                "category": "Uber",
                "colors": [
                  "#5A1846",
                  "#900C3F",
                  "#C70039",
                  "#E3611C",
                  "#F1920E",
                  "#FFC300"
                ]
              },
              "coverage": 1,
              "enable3d": False,
              "sizeRange": [
                0,
                500
              ],
              "coverageRange": [
                0,
                1
              ],
              "elevationScale": 5
            },
            "hidden": False,
            "textLabel": [
              {
                "field": None,
                "color": [
                  255,
                  255,
                  255
                ],
                "size": 18,
                "offset": [
                  0,
                  0
                ],
                "anchor": "start",
                "alignment": "center"
              }
            ]
          },
          "visualChannels": {
            "colorField": None,
            "colorScale": "quantile",
            "sizeField": None,
            "sizeScale": "linear",
            "coverageField": None,
            "coverageScale": "linear"
          }
        }
      ],
      "interactionConfig": {
        "tooltip": {
          "fieldsToShow": {
            "hex6_flight": [
              {
                "name": "dt_rank",
                "format": None
              },
              {
                "name": "time_edt",
                "format": None
              },
              {
                "name": "course",
                "format": None
              },
              {
                "name": "kts",
                "format": None
              },
              {
                "name": "mph",
                "format": None
              }
            ],
            "hex6_kring_flight": [
              {
                "name": "hex_id",
                "format": None
              },
              {
                "name": "count",
                "format": None
              }
            ]
          },
          "compareMode": False,
          "compareType": "absolute",
          "enabled": True
        },
        "brush": {
          "size": 0.5,
          "enabled": False
        },
        "geocoder": {
          "enabled": False
        },
        "coordinate": {
          "enabled": False
        }
      },
      "layerBlending": "normal",
      "splitMaps": [],
      "animationConfig": {
        "currentTime": None,
        "speed": 1
      }
    },
    "mapState": {
      "bearing": 0,
      "dragRotate": False,
      "latitude": 38.77023102722031,
      "longitude": -78.53921745522278,
      "pitch": 0,
      "zoom": 8.339597955479144,
      "isSplit": False
    },
    "mapStyle": {
      "styleType": "dark",
      "topLayerGroups": {},
      "visibleLayerGroups": {
        "label": True,
        "road": True,
        "border": False,
        "building": True,
        "water": True,
        "land": True,
        "3d building": False
      },
      "threeDBuildingColor": [
        9.665468314072013,
        17.18305478057247,
        31.1442867897876
      ],
      "mapStyles": {}
    }
  }
}

print("map_4a_config ran...")

MPH > 275 + K-Ring Count > 20 を H3解像度6でプロット (赤色)

df_hex6_dense_kring = (
    df_hex6_join
        .groupBy("hex_id")
        .count()
        .filter("count > 20")
        .orderBy(F.desc("count"))
)

map_4a = KeplerGl(height=600, config=map_4a_config)
map_4a.add_data(data = df_hex6.select(*df.columns, h3_h3tostring("hex_id").alias("hex_id"), "hex_centroid", "h3_resolution").toPandas(), name = "hex6_flight")
map_4a.add_data(data = df_hex6_dense_kring.select(h3_h3tostring("hex_id").alias("hex_id"), "count").toPandas(), name = "hex6_kring_flight")
display_kepler(map_4a)

評価: k-ringアプローチでは解像度6で同じhex_idに数多くのエントリーが含まれているので、待機パターンを検知したい場合にはカウントがより多くなります。count > 20で待機パターンのみを検知します。

メッセージ間の距離に対するウィンドウ関数

H3には距離を計算するAPIがあります。最初にH3の解像度8をベースとした距離を用いて、Sparkのウィンドウ関数でどのような結果を得られるのかを見ていきましょう。

h3_distance: 2つのインデックスの間のグリッドセルにおける距離を返却します。解像度をベースにしたkm (こちら)

display(
    df_hex6
        .withColumn("hex_lag", F.lag("hex_id",5).over(Window.orderBy("dt_rank")))
        .withColumn("hex_lag_dist", h3_distance("hex_id", "hex_lag"))
            .filter("mph > 275")
        .orderBy("dt_rank")
)

評価: スピード (すなわち距離) は飛行機が一般的に待機パターンに入った際には遅くなることが予測できるので、特に~145 から ~180では非常に減少しています。また、待機パターンの中央でのこぶも以下のフライト追跡概要のいずれかを反映しています。

はじめてのDatabricks

はじめてのDatabricks

Databricks無料トライアル

Databricks無料トライアル