基于蜂窝基站数据集的地理空间分析

了解如何将 OpenCelliD 蜂窝基站数据集加载到 ClickHouse Cloud 中，借助字典丰富数据，使用地理函数进行查询，并通过 ClickHouse 智能体实现可视化

在本教程中，您将借助蜂窝基站数据集，探索如何使用 ClickHouse 处理地理数据。 您将把该数据集加载到 ClickHouse Cloud，查看其 schema，并对数千万行数据执行聚合查询。 随后，您将使用字典丰富数据，利用 pointInPolygon 函数统计德国境内的基站数量，并借助 ClickHouse 智能体将结果在地图上可视化。

​

前置条件

要完成本教程，您需要：

• 一个 ClickHouse Cloud 账户 (注册即可获得 300 美元免费额度)
• 一个 ClickHouse Cloud 服务

1. 加载示例数据

   本教程使用的数据集来自 OpenCelliD，这是全球最大的蜂窝基站开放数据库。 截至 2021 年，该数据集收录了全球数千万条蜂窝基站记录，包括基站的地理坐标，以及国家/地区代码、网络等元数据。

   1. 在 ClickHouse Cloud 左侧菜单的下拉列表中选择您的服务
   2. 选择 Data sources
   3. 点击 Add sample data 卡片
   4. 选择 Cell Towers (1.1 GB) 数据集
   5. 将目标数据库设为 default，然后点击 Import dataset

   随后，您应能在 Data upload history 下看到一条状态为 success 的记录

2. 查看 schema

   1. 从左侧菜单中选择 SQL console
   2. 点击主页图标旁的 + 选项卡，新建一个查询
   3. 在 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 表示。

3. 执行简单查询

   运行以下查询，按类型查看蜂窝基站数量：

   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。 你可以使用字典，将 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)
   

   创建字典后，即可使用它返回相同的列表，并在 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

   310	United States of America	5024650
   262	Germany	                    2622423
   250	Russia	                    1953176
   208	France	                    1891187
   724	Brazil	                    1836150
   
4. 接入地理空间数据

   您可能还想知道某个特定地理区域内有多少个蜂窝基站。 针对这一用例，ClickHouse 提供了许多实用的地理函数，例如 pointInPolygon 函数。

   假设您想了解德国境内共有多少个蜂窝基站。 创建一个名为 germany 的表，其中只包含一个 polygon 类型的列：

   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
   
5. 可选：使用 ClickHouse 智能体可视化数据

   接下来，我们希望将这些数据可视化。以下是一次可选的示例运行：ClickHouse 智能体是由模型驱动的 Beta 功能，因此每次运行时，其执行计划、工具调用 trace 和输出都可能有所不同。 ClickHouse 智能体让您可以通过对话轻松查询和探索 ClickHouse 数据，无需自行编写 SQL 或编排逻辑。 智能体会理解您的意图、规划步骤、调用您已配置的工具，并将结果返回给您。

   在左侧菜单底部，点击组织名称下方的 ClickHouse agents，即可打开 ClickHouse 智能体。 您可以通过聊天向 ClickHouse 智能体发送消息。 输入“Visualize cell towers within Germany on a map using the default.cell_towers and default.germany tables”，然后点击发送。 在某次运行中，智能体执行了以下步骤：

   • 在 mcc_country_dict 中查找德国的移动国家代码 (MCC) ，得到 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 中，查看了其 schema，并对数千万行数据运行了聚合查询。 您创建了一个字典，为数值型的 mcc 列补充国家名称；将德国的轮廓存储为多边形，并使用 pointInPolygon 统计了德国境内的基站数量。 最后，您借助 ClickHouse 智能体将这一问题直接转化为一张密度地图，无需亲自编写聚合或绘图代码。

接下来，您可以：

• 向智能体继续提问。 例如，询问 LTE 和 GSM 等不同制式的基站密度有何差异，或者哪些城市的 NR 基站最多。您可以将常用的提示词保存到提示词库中，或在智能体构建器中构建专用智能体。
• 比较不同国家。 插入其他国家的多边形，并对每个国家运行相同的 pointInPolygon 查询。借助您创建的字典，可以为结果标注国家名称。
• 使用 H3 或 geohash 对基站进行分桶。 智能体使用的是简单的舍入网格。ClickHouse 还提供 H3 和 geohash 函数，可生成层级化的单元，非常适合可缩放的地图。
• 探索其他数据集。 纽约出租车数据集同样包含上车和下车坐标，示例数据集索引页中还列出了更多数据集。