基地局データセットを使用した地理空間分析

このチュートリアルでは、基地局データセットを使用して、ClickHouse を Geo データの分析にどのように活用できるかを見ていきます。データセットを ClickHouse Cloud に読み込み、スキーマを確認したうえで、数千万行を対象に集計クエリを実行します。続いて、Dictionary を使ってデータを拡充し、pointInPolygon 関数でドイツ国内の基地局数を集計したうえで、ClickHouse Agents を使ってその結果を地図上に可視化します。

前提条件

このチュートリアルには、以下が必要です。

ClickHouse Cloud アカウント (サインアップ時に 300 ドル分の無料クレジットが付与されます)
ClickHouse Cloud サービス

サンプルデータを読み込む

このチュートリアルで使用するデータセットは、世界最大の基地局オープンデータベースであるから提供されています。 2021 年時点で、世界中の基地局に関する数千万件のレコードを収録しており、各レコードには地理座標のほか、国コードやネットワークなどのメタデータが含まれています。

ClickHouse Cloud の左側のメニューにあるドロップダウンから、対象のサービスを選択します
Data sources を選択します
Add sample data カードをクリックします
Cell Towers (1.1 GB) データセットを選択します
宛先データベースに default を指定し、Import dataset をクリックします

Data upload history に、ステータスが success のエントリが表示されます

スキーマを確認する

左側のメニューから SQL console を選択します
ホームアイコンの横にある + タブをクリックして、新しいクエリを作成します
SQL エディタに次のクエリを入力し、Run をクリックします。

DESCRIBE TABLE cell_towers

次のような結果テーブルが表示されます。

┌─name──────────┬─type──────────────────────────────────────────────────────────────────┬
│ radio         │ Enum8('' = 0, 'CDMA' = 1, 'GSM' = 2, 'LTE' = 3, 'NR' = 4, 'UMTS' = 5) │
│ mcc           │ UInt16                                                                │
│ net           │ UInt16                                                                │
│ area          │ UInt16                                                                │
│ cell          │ UInt64                                                                │
│ unit          │ Int16                                                                 │
│ lon           │ Float64                                                               │
│ lat           │ Float64                                                               │
│ range         │ UInt32                                                                │
│ samples       │ UInt32                                                                │
│ changeable    │ UInt8                                                                 │
│ created       │ DateTime                                                              │
│ updated       │ DateTime                                                              │
│ averageSignal │ UInt8                                                                 │
└───────────────┴───────────────────────────────────────────────────────────────────────┴

テーブルの各エントリは、地球上に実在する基地局に対応しており、その位置は座標 lon と lat で表されます。

基本的なクエリを実行する

次のクエリを実行して、種類別の基地局数を確認します。

SELECT radio, count() AS c FROM cell_towers GROUP BY radio ORDER BY c DESC

┌─radio─┬────────c─┐
│ UMTS  │ 20686487 │
│ LTE   │ 12101148 │
│ GSM   │  9931304 │
│ CDMA  │   556344 │
│ NR    │      867 │
└───────┴──────────┘

次に、モバイル国コード (MCC) ごとの基地局数を確認します。

SELECT mcc, count() FROM cell_towers GROUP BY mcc ORDER BY count() DESC LIMIT 10

┌─mcc─┬─count()─┐
│ 310 │ 5024650 │
│ 262 │ 2622423 │
│ 250 │ 1953176 │
│ 208 │ 1891187 │
│ 724 │ 1836150 │
│ 404 │ 1729151 │
│ 234 │ 1618924 │
│ 510 │ 1353998 │
│ 440 │ 1343355 │
│ 311 │ 1332798 │
└─────┴─────────┘

基地局の数が最も多い国の MCC は 310、262、250 であることがわかります。 Dictionary を使用すると、数値の mcc カラム値を国名に置き換えられます。

CREATE DICTIONARY mcc_country_dict
(
MCC UInt16,
Country String
)
PRIMARY KEY MCC
SOURCE(HTTP(
url 'https://raw.githubusercontent.com/ClickHouse/examples/refs/heads/main/datasets/mcc-countries.csv'
format 'CSVWithNames'
))
LAYOUT(HASHED())
LIFETIME(MIN 0 MAX 0)

Dictionary を作成したので、これを使用して、同じリストに MCC と並べて国名も返せるようになりました。

Query

SELECT
mcc,
dictGet('mcc_country_dict', 'Country', mcc) AS country,
count() AS cnt
FROM cell_towers
GROUP BY mcc
ORDER BY cnt DESC
LIMIT 5

Response

United States of America	5024650
Germany	                    2622423
Russia	                    1953176
France	                    1891187
Brazil	                    1836150

地理空間データを取り込む

特定の地理的範囲内に基地局がいくつあるかを知りたい場合もあるでしょう。 ClickHouse には、pointInPolygon 関数をはじめ、このようなユースケースに役立つ Geo 関数が数多く用意されています。例として、ドイツ国内にある基地局の数を調べたい場合を考えてみましょう。 polygon 型のカラムを 1 つだけ持つ germany という名前のテーブルを作成します。

CREATE TABLE germany (polygon Array(Tuple(Float64, Float64)))
ORDER BY polygon;

次に、ドイツ (本土) の輪郭を表す座標をテーブルに挿入します。

INSERT INTO germany VALUES ([(10.454459535579133, 47.55573834324997), (10.890209019630618, 47.537202200721026), (10.970328786144592, 47.40002058348318), (11.272891854275713, 47.39785257163891), (11.637139099589831, 47.59425903862376),
(12.20388194699433, 47.60680973608049), (12.162598450658038, 47.70112977791695), (12.256972280946059, 47.7430071703447), (12.255189497297522, 47.679294992270115), (12.440126465064338, 47.69517254727526),
(12.499216429994306, 47.625017004631104), (12.781115045223203, 47.674141358936595), (12.80384823830559, 47.54988690165925), (13.04750486076074, 47.49208302661043), (13.080793954277624, 47.68702414364935),
(12.905046866219948, 47.72364341860896), (13.003294582878766, 47.85031169133157), (12.758258591061178, 48.126325188057024), (13.329748843534333, 48.32352787700995), (13.508992162958009, 48.59060155142049),
(13.727359451977179, 48.51300820806239), (13.839506769122124, 48.771604902544254), (13.628668330878838, 48.949206192112115), (13.402895552887514, 48.98734273487935), (13.029109282267598, 49.304348045426025),
(12.655893345501568, 49.43454683436897), (12.521638213932476, 49.68676182809594), (12.40071737081007, 49.75384886863043), (12.547730388346793, 49.92034323954891), (12.201422342567128, 50.10850072273024),
(12.10090036995615, 50.31802824350871), (12.184453107724778, 50.32232471771158), (12.289803626736045, 50.176930077860845), (12.334553826997592, 50.17174892257475), (12.512047935363967, 50.39725816952898),
(12.819380927587815, 50.45979531680047), (12.912391784952945, 50.42372909162435), (12.937308320622549, 50.406328326990774), (12.947972824086605, 50.40427966057791), (13.370982951801807, 50.65054589707552),
(13.464767584956121, 50.6018925959666), (13.526820353776372, 50.70501677580364), (13.855192645324394, 50.727109704649195), (14.387635007249855, 50.899087975929376), (14.258647344299561, 50.98753751504432),
(14.301858340490071, 51.05504617034455), (14.508211954118451, 51.043154596807256), (14.59917754360879, 50.98711100167145), (14.564810750215997, 50.91835296052079), (14.650215415109358, 50.93153456746637),
(14.618868455215079, 50.85771029809183), (14.793911832764934, 50.82014753807846), (15.041789401659628, 51.274035224546026), (14.949088456067443, 51.47126488866371), (14.729118627322862, 51.53143962304023),
(14.7575478735356, 51.66150403168575), (14.590144372564794, 51.82100998951944), (14.759042582749032, 52.06476202734575), (14.681595447571908, 52.11665177535917), (14.715746889307525, 52.23588355048554),
(14.576039609740235, 52.288366710755156), (14.534357221808364, 52.39500777668758), (14.63911057749118, 52.57299886300717), (14.12292694822014, 52.83765710679012), (14.143657774686574, 52.96136543636584),
(14.348570677994417, 53.05471830950245), (14.450570288887775, 53.26224993248843), (14.30255731786599, 53.55340466159589), (14.283595832881417, 53.772302946323975), (13.822463699939874, 53.847704554621544),
(13.914106243308538, 53.921857458754744), (13.743604331474785, 54.02871742866648), (13.808750796911795, 54.10045250547881), (13.7062583845169, 54.17118193160093), (13.489404453952432, 54.083845783755976),
(13.142765860556096, 54.2536547023027), (13.178427336852167, 54.26927398011014), (13.132873048252634, 54.27941493335254), (13.138935061603831, 54.319290755963664), (13.393587180304166, 54.220971308479136),
(13.350821423170203, 54.269835443950626), (13.57531796425269, 54.352998349893994), (13.723904396071077, 54.273407259737326), (13.766909407942876, 54.34183448887063), (13.57052592673017, 54.45765298249921),
(13.661158812029612, 54.57578947060762), (13.425207789507908, 54.57776585760831), (13.377190829792994, 54.63485405846808), (13.429096601926688, 54.68488300844416), (13.250612882278745, 54.660459153766965),
(13.160936899918056, 54.559083360308875), (13.28297271262062, 54.64558892578833), (13.245451284549176, 54.5578326488577), (13.368071411966127, 54.61469746081923), (13.446130971966852, 54.55160125899096),
(13.511133452346996, 54.566114026241394), (13.557162122533953, 54.438539184735646), (13.345708024605699, 54.52048608046829), (13.368973991356654, 54.57891966081377), (13.304904763544073, 54.51411689467898),
(13.144180806199302, 54.546965665732955), (13.270112698742082, 54.47988865533722), (13.149423426549959, 54.42813433982798), (13.260337438621946, 54.38045384347015), (13.129103578567367, 54.32114076573379),
(13.015778021838287, 54.43939322591672), (12.809663608988387, 54.34360109191692), (12.668525237349854, 54.408133446301576), (12.42937122989838, 54.29356120888275), (12.436138349505256, 54.37908880570387),
(12.693160014523073, 54.432254058577996), (12.926951675779264, 54.42796397405823), (12.514359690347476, 54.484348643375256), (12.100993421009605, 54.16928820758477), (11.682936883230639, 54.1532711821194),
(11.552519675043015, 54.09268650258008), (11.626244382272489, 54.09015710806415), (11.452965579797706, 53.894822219270054), (11.179984660863454, 54.01516631277519), (10.898693904600066, 53.95665538983445),
(10.760186530184285, 54.02460718607284), (11.09341311572274, 54.19866713068359), (11.07191809484425, 54.34224630552262), (11.125609497880077, 54.373613504659545), (11.118558230278154, 54.39442093987452),
(10.706974464899702, 54.30503645926524), (10.321948337845583, 54.43574765223241), (10.174162085012256, 54.34574538594086), (10.199382745317507, 54.45556154343052), (10.134027446509492, 54.48433790277204),
(9.85537006887364, 54.456283168433515), (10.027329336579669, 54.55311722278168), (10.036023455118425, 54.68841701763131), (9.909510390991784, 54.79991988959131), (9.845578708190885, 54.75603640555573),
(9.591910925348998, 54.886970216702196), (9.343610061754987, 54.80024799285064), (8.94793864795031, 54.90256336560691), (8.638003412527496, 54.911251855288924), (8.42740949814987, 54.877399149451264),
(8.352821841055174, 54.96785703207644), (8.46419569987171, 55.04571479500186), (8.415419699759411, 55.05866217082257), (8.297902985734254, 54.91065659132869), (8.278555583673324, 54.752208180463185),
(8.340561373708454, 54.88057457195896), (8.414381016970424, 54.84724070929133), (8.61261435866578, 54.87846776077862), (8.686209753277524, 54.73034082227116), (8.892967769546999, 54.59677687545252),
(8.807015453500412, 54.47015896026784), (8.983598917356176, 54.52404630129581), (9.018963414130326, 54.4747456609835), (8.660693777196116, 54.39516479791712), (8.6025729405888, 54.31421651837769),
(8.841787401367526, 54.278455117509736), (8.80886763603263, 54.17103733680369), (8.985125080358216, 54.060986664003394), (8.804773173842932, 54.023438800360395), (8.914904897143344, 53.92489909541695),
(9.275123379340528, 53.857269302336476), (8.61491387027894, 53.88211822337735), (8.483191313228588, 53.694068058094444), (8.556142860704483, 53.526062883695204), (8.28300703264847, 53.61001675866652),
(8.226915998274762, 53.52008371229357), (8.315820116805014, 53.461673297584696), (8.249573034967625, 53.398362336752484), (8.071310815641652, 53.46743230775485), (8.170974825128098, 53.54047017575806),
(8.00872870406647, 53.71213492358788), (7.296326719002934, 53.680098653130415), (7.090335704828988, 53.579652246054366), (7.129998987962949, 53.532359981627906), (7.034145450951826, 53.545670230296935),
(7.012918474807805, 53.34466909190513), (7.265760915633791, 53.32719714862958), (7.191115728008469, 53.31760156675898), (7.217281326671923, 53.00698383493608), (7.087323855511556, 52.84992858723456),
(7.055577137075261, 52.64336989040817), (6.75264448085818, 52.64813255966993), (6.766621548046032, 52.561512582439775), (6.680868309524897, 52.553336398755846), (6.753103465152833, 52.46379697395122),
(6.991802701633446, 52.467452567881764), (7.065868436279345, 52.24125898091728), (6.694652945177324, 52.06980283263465), (6.830419114950246, 51.98620651731699), (6.732607205258375, 51.898707142520664),
(6.401752481791675, 51.82729228551062), (6.11804667081725, 51.901669637044165), (6.166407642798504, 51.840834718447525), (5.945029570158567, 51.82365634356546), (6.212084663676308, 51.513378407087885),
(6.226129918361096, 51.36051764687198), (6.072656924877208, 51.24258730406086), (6.175415266801167, 51.15847977362273), (5.867096631104687, 51.04667907857669), (6.093924751825909, 50.921325464498636),
(5.974861423316099, 50.79797295258976), (6.266895404299532, 50.64152787241346), (6.196995433692337, 50.531031950664044), (6.351259021186934, 50.48826779162533), (6.407870066950068, 50.33513396126335),
(6.175687402551659, 50.235415978964284), (6.112562492565019, 50.05922877348917), (6.322636569122437, 49.838032009569645), (6.529314320899232, 49.8093868550194), (6.357852033844324, 49.573820737836854),
(6.367083526083889, 49.46951204834244), (6.550815842976817, 49.425379524945185), (6.738600535586272, 49.16367513140227), (6.938108243600595, 49.222433998330416), (7.052445915346766, 49.11275784459889),
(7.293176904690426, 49.11490732950972), (7.445646679250899, 49.18415102974626), (7.631201346365117, 49.05488570651022), (8.232632820348442, 48.9665714470147), (7.839740454658283, 48.64127586299071),
(7.744638258948555, 48.32781093512659), (7.577585310114046, 48.12065583849619), (7.621952269681572, 47.97280642807425), (7.511871830220855, 47.696037521514484), (7.604484107155884, 47.57779110477571),
(7.693596586013655, 47.60053085751309), (7.667618493298903, 47.536221133898266), (8.582542232737921, 47.59610411291453), (8.60662329822668, 47.67212011105431), (8.405559327800802, 47.67451554525627),
(8.56785207691189, 47.80848124891054), (8.893659067611907, 47.6521787220704), (8.989864116335923, 47.743465936104144), (9.21772790891589, 47.66612201881617), (9.03953653098614, 47.81748197511314),
(9.680775632889208, 47.542264915293686), (9.80050512701007, 47.59598448329734), (9.97089855812203, 47.54568043790192), (10.091470374358323, 47.45907615923744), (10.09978048058474, 47.35473600336394),
(10.23619637995904, 47.381860769459024), (10.172036399760088, 47.27904122237459), (10.232299537493702, 47.27047759546474), (10.436472139577631, 47.38064780435275), (10.454459535579133, 47.55573834324997)]);

これで、pointInPolygon 関数を使って、ドイツ本土にある基地局の数を確認できます。

Query

SELECT formatReadableQuantity(count()) FROM cell_towers
WHERE pointInPolygon((lon, lat), (SELECT * FROM germany))

Response

2.62 million

任意：ClickHouse Agents を使ってデータを可視化する

次に、このデータを可視化してみましょう。以下は任意の実行例です。ClickHouse Agents はモデル駆動型のベータ機能であるため、計画、ツールのトレース、出力は実行ごとに異なる場合があります。 ClickHouse Agents を使用すると、SQL やオーケストレーションロジックを自分で記述しなくても、会話を通じて ClickHouse のデータを手軽にクエリ・探索できます。エージェントはユーザーの意図を解釈して手順を計画し、設定済みのツールを呼び出して結果を返します。左側メニューの下部にある組織名の下の ClickHouse agents をクリックして、ClickHouse Agents を開きます。チャットから ClickHouse agent にメッセージを送信できます。「Visualize cell towers within Germany on a map using the default.cell_towers and default.germany tables」と入力して送信します。ある実行例では、エージェントは次の手順を実行しました。

mcc_country_dict でドイツの Mobile Country Code を検索し、MCC が 262 であることを特定しました。
pointInPolygon を使用して、germany のポリゴン内にある mcc = 262 の基地局を数え、2,588,635 基という結果を得ました。追加の mcc フィルタによって、ポリゴン内にあっても他国に登録されている基地局が除外されるため、前述の件数よりわずかに少なくなっています。
260 万個の点を個別にプロットするのではなく、lon と lat を丸めて基地局を 0.1° × 0.1° のグリッドに集約し、4,821 個のセルを生成しました。さらに、セルごとの件数の合計がちょうど 2,588,635 になることを確認し、ビニングの過程で欠落や二重カウントが発生していないことを検証しました。
germany からポリゴンを取得してグリッド上に輪郭として描画し、対数カラースケールを用いることで、密度の高い都市部のセルと疎らな農村部のセルの両方が見えるようにしました。

生成されたファイルを開いて結果を確認してください。明るく表示されている集中箇所は、ベルリン、ハンブルク、ミュンヘン、フランクフルトといった主要な大都市圏です。

次のステップ

このチュートリアルでは、OpenCelliD の基地局データセットを ClickHouse Cloud に読み込み、そのスキーマを確認したうえで、数千万行を対象に集計クエリを実行しました。さらに、Dictionary を作成して数値の mcc カラムに国名を付与し、ドイツの輪郭をポリゴンとして保存して、pointInPolygon でその内部にある基地局の数をカウントしました。最後に、ClickHouse Agents を使用し、集計コードや描画コードを自分で書くことなく、その問いを密度マップとして可視化しました。次のステップとして、以下のようなことを試してみてください。

エージェントに追加で質問する。 LTE と GSM などの無線方式によって密度がどう異なるか、あるいは NR の基地局が最も多い都市はどこかを尋ねてみてください。繰り返し使うプロンプトはプロンプトライブラリに保存できます。また、エージェントビルダーで特定用途向けのエージェントを構築することもできます。
国ごとに比較する。 他の国のポリゴンを挿入し、それぞれに対して同じ pointInPolygon クエリを実行します。作成した Dictionary を使えば、結果に国名のラベルを付けられます。
H3 や geohash で基地局を集計する。 エージェントは単純な丸め処理によるグリッドを使用しました。ClickHouse には H3 関数や geohash 関数も用意されています。これらは階層的なセルを生成するため、ズーム可能なマップに適しています。
他のデータセットを探索する。 NYC タクシーデータセットにも乗車地点と降車地点の座標が含まれています。そのほかにも、サンプルデータセットの一覧に多数のデータセットが掲載されています。

はじめる

セットアップ

サンプルデータセット

移行

基地局データセットを使用した地理空間分析

前提条件

サンプルデータを読み込む

スキーマを確認する

基本的なクエリを実行する

地理空間データを取り込む

任意：ClickHouse Agents を使ってデータを可視化する

次のステップ

​前提条件

サンプルデータを読み込む

スキーマを確認する

基本的なクエリを実行する

地理空間データを取り込む

任意：ClickHouse Agents を使ってデータを可視化する

​次のステップ

前提条件

次のステップ