使用蜂窝基站数据集的地理数据

目标

在本指南中，您将学习如何：

• 将 OpenCelliD 数据导入 ClickHouse
• 将 Apache Superset 连接到 ClickHouse
• 基于数据集中可用的数据构建一个仪表盘

下面是本指南中创建的仪表盘预览：

获取数据集

该数据集来自 OpenCelliD——全球最大的蜂窝基站开放数据库。

截至 2021 年，它包含了全球蜂窝基站（GSM、LTE、UMTS 等）的 4,000 多万条记录，包括其地理坐标以及元数据（国家代码、网络等）。

OpenCelliD 项目采用 Creative Commons Attribution-ShareAlike 4.0 International License 授权，我们在相同许可条款下重新分发该数据集的一个快照。最新版本的数据集可在登录后下载。

ClickHouse Cloud

加载示例数据

ClickHouse Cloud 提供了从 S3 上传该数据集的一键式操作。登录到你的 ClickHouse Cloud 组织，或在 ClickHouse.cloud 创建一个免费试用。

选择你的服务，然后依次选择 Data sources -> Predefined sample data。

在 Sample data 选项卡中选择 Cell Towers 数据集，然后点击 Load data：

查看 cell_towers 表的结构

DESCRIBE TABLE cell_towers

SQL console

如果你需要通过 SQL 客户端进行连接，你的 ClickHouse Cloud 服务提供了一个基于 Web 的 SQL 控制台；展开下方的 Connect to SQL console 查看详细信息。

Connect to SQL console

在 ClickHouse Cloud 的服务列表中，点击一个服务。

系统会将你重定向到 SQL 控制台。

下面是 DESCRIBE 的输出。本指南后文将介绍各字段类型选择的理由。

┌─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                                                                 │
└───────────────┴───────────────────────────────────────────────────────────────────────┴

自托管

1. 创建表：

CREATE TABLE cell_towers
(
    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
)
ENGINE = MergeTree ORDER BY (radio, mcc, net, created);

2. 从公共 S3 存储桶导入数据集（686 MB）：

INSERT INTO cell_towers SELECT * FROM s3('https://datasets-documentation.s3.amazonaws.com/cell_towers/cell_towers.csv.xz', 'CSVWithNames')

运行一些示例查询

1. 各类型蜂窝基站的数量：

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 │
└───────┴──────────┘

5 rows in set. Elapsed: 0.011 sec. Processed 43.28 million rows, 43.28 MB (3.83 billion rows/s., 3.83 GB/s.)

2. 按移动国家码 (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 │
└─────┴─────────┘

10 rows in set. Elapsed: 0.019 sec. Processed 43.28 million rows, 86.55 MB (2.33 billion rows/s., 4.65 GB/s.)

根据上述查询以及 MCC 列表，基站数量最多的国家包括：美国、德国和俄罗斯。

您可以考虑在 ClickHouse 中创建一个 Dictionary 来对这些值进行解码。

使用场景：集成地理数据

使用 pointInPolygon 函数。

1. 创建一个用于存储多边形的表：

ClickHouse Cloud

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

Self-managed

CREATE TEMPORARY TABLE
moscow (polygon Array(Tuple(Float64, Float64)));

2. 下面是莫斯科的大致轮廓（不包含“新莫斯科”）：

INSERT INTO moscow VALUES ([(37.84172564285271, 55.78000432402266),
(37.8381207618713, 55.775874525970494), (37.83979446823122, 55.775626746008065), (37.84243326983639, 55.77446586811748), (37.84262672750849, 55.771974101091104), (37.84153238623039, 55.77114545193181), (37.841124690460184, 55.76722010265554),
(37.84239076983644, 55.76654891107098), (37.842283558197025, 55.76258709833121), (37.8421759312134, 55.758073999993734), (37.84198330422974, 55.75381499999371), (37.8416827275085, 55.749277102484484), (37.84157576190186, 55.74794544108413),
(37.83897929098507, 55.74525257875241), (37.83739676451868, 55.74404373042019), (37.838732481460525, 55.74298009816793), (37.841183997352545, 55.743060321833575), (37.84097476190185, 55.73938799999373), (37.84048155819702, 55.73570799999372),
(37.840095812164286, 55.73228210777237), (37.83983814285274, 55.73080491981639), (37.83846476321406, 55.729799917464675), (37.83835745269769, 55.72919751082619), (37.838636380279524, 55.72859509486539), (37.8395161005249, 55.727705075632784),
(37.83897964285276, 55.722727886185154), (37.83862557539366, 55.72034817326636), (37.83559735744853, 55.71944437307499), (37.835370708803126, 55.71831419154461), (37.83738169402022, 55.71765218986692), (37.83823396494291, 55.71691750159089),
(37.838056931213345, 55.71547311301385), (37.836812846557606, 55.71221445615604), (37.83522525396725, 55.709331054395555), (37.83269301586908, 55.70953687463627), (37.829667367706236, 55.70903403789297), (37.83311126588435, 55.70552351822608),
(37.83058993121339, 55.70041317726053), (37.82983872750851, 55.69883771404813), (37.82934501586913, 55.69718947487017), (37.828926414016685, 55.69504441658371), (37.82876530422971, 55.69287499999378), (37.82894754100031, 55.690759754047335),
(37.827697554878185, 55.68951421135665), (37.82447346292115, 55.68965045405069), (37.83136543914793, 55.68322046195302), (37.833554015869154, 55.67814012759211), (37.83544184655761, 55.67295011628339), (37.837480388885474, 55.6672498719639),
(37.838960677246064, 55.66316274139358), (37.83926093121332, 55.66046999999383), (37.839025050262435, 55.65869897264431), (37.83670784390257, 55.65794084879904), (37.835656529083245, 55.65694309303843), (37.83704060449217, 55.65689306460552),
(37.83696819873806, 55.65550363526252), (37.83760389616388, 55.65487847246661), (37.83687972750851, 55.65356745541324), (37.83515216004943, 55.65155951234079), (37.83312418518067, 55.64979413590619), (37.82801726983639, 55.64640836412121),
(37.820614174591, 55.64164525405531), (37.818908190475426, 55.6421883258084), (37.81717543386075, 55.64112490388471), (37.81690987037274, 55.63916106913107), (37.815099354492155, 55.637925371757085), (37.808769150787356, 55.633798276884455),
(37.80100123544311, 55.62873670012244), (37.79598013491824, 55.62554336109055), (37.78634567724606, 55.62033499605651), (37.78334147619623, 55.618768681480326), (37.77746201055901, 55.619855533402706), (37.77527329626457, 55.61909966711279),
(37.77801986242668, 55.618770300976294), (37.778212973541216, 55.617257701952106), (37.77784818518065, 55.61574504433011), (37.77016867724609, 55.61148576294007), (37.760191219573976, 55.60599579539028), (37.75338926983641, 55.60227892751446),
(37.746329965606634, 55.59920577639331), (37.73939925396728, 55.59631430313617), (37.73273665739439, 55.5935318803559), (37.7299954450912, 55.59350760316188), (37.7268679946899, 55.59469840523759), (37.72626726983634, 55.59229549697373),
(37.7262673598022, 55.59081598950582), (37.71897193121335, 55.5877595845419), (37.70871550793456, 55.58393177431724), (37.700497489410374, 55.580917323756644), (37.69204305026244, 55.57778089778455), (37.68544477378839, 55.57815154690915),
(37.68391050793454, 55.57472945079756), (37.678803592590306, 55.57328235936491), (37.6743402539673, 55.57255251445782), (37.66813862698363, 55.57216388774464), (37.617927457672096, 55.57505691895805), (37.60443099999999, 55.5757737568051),
(37.599683515869145, 55.57749105910326), (37.59754177842709, 55.57796291823627), (37.59625834786988, 55.57906686095235), (37.59501783265684, 55.57746616444403), (37.593090671936025, 55.57671634534502), (37.587018007904, 55.577944600233785),
(37.578692203704804, 55.57982895000019), (37.57327546607398, 55.58116294118248), (37.57385012109279, 55.581550362779), (37.57399562266922, 55.5820107079112), (37.5735356072979, 55.58226289171689), (37.57290393054962, 55.582393529795155),
(37.57037722355653, 55.581919415056234), (37.5592298306885, 55.584471614867844), (37.54189249206543, 55.58867650795186), (37.5297256269836, 55.59158133551745), (37.517837865081766, 55.59443656218868), (37.51200186508174, 55.59635625174229),
(37.506808949737554, 55.59907823904434), (37.49820432275389, 55.6062944994944), (37.494406071441674, 55.60967103463367), (37.494760001358024, 55.61066689753365), (37.49397137107085, 55.61220931698269), (37.49016528606031, 55.613417718449064),
(37.48773249206542, 55.61530616333343), (37.47921386508177, 55.622640129112334), (37.470652153442394, 55.62993723476164), (37.46273446298218, 55.6368075123157), (37.46350692265317, 55.64068225239439), (37.46050283203121, 55.640794546982576),
(37.457627470916734, 55.64118904154646), (37.450718034393326, 55.64690488145138), (37.44239252645875, 55.65397824729769), (37.434587576721185, 55.66053543155961), (37.43582144975277, 55.661693766520735), (37.43576786245721, 55.662755031737014),
(37.430982915344174, 55.664610641628116), (37.428547447097685, 55.66778515273695), (37.42945134592044, 55.668633314343566), (37.42859571562949, 55.66948145750025), (37.4262836402282, 55.670813882451405), (37.418709037048295, 55.6811141674414),
(37.41922139651101, 55.68235377885389), (37.419218771842885, 55.68359335082235), (37.417196501327446, 55.684375235224735), (37.41607020370478, 55.68540557585352), (37.415640857147146, 55.68686637150793), (37.414632153442334, 55.68903015131686),
(37.413344899475064, 55.690896881757396), (37.41171432275391, 55.69264232162232), (37.40948282275393, 55.69455101638112), (37.40703674603271, 55.69638690385348), (37.39607169577025, 55.70451821283731), (37.38952706878662, 55.70942491932811),
(37.387778313491815, 55.71149057784176), (37.39049275399779, 55.71419814298992), (37.385557272491454, 55.7155489617061), (37.38388335714726, 55.71849856042102), (37.378368238098155, 55.7292763261685), (37.37763597123337, 55.730845879211614),
(37.37890062088197, 55.73167906388319), (37.37750451918789, 55.734703664681774), (37.375610832015965, 55.734851959522246), (37.3723813571472, 55.74105626086403), (37.37014935714723, 55.746115620904355), (37.36944173016362, 55.750883999993725),
(37.36975304365541, 55.76335905525834), (37.37244070571134, 55.76432079697595), (37.3724259757175, 55.76636979670426), (37.369922155757884, 55.76735417953104), (37.369892695770275, 55.76823419316575), (37.370214730163575, 55.782312184391266),
(37.370493611114505, 55.78436801120489), (37.37120164550783, 55.78596427165359), (37.37284851456452, 55.7874378183096), (37.37608325135799, 55.7886695054807), (37.3764587460632, 55.78947647305964), (37.37530000265506, 55.79146512926804),
(37.38235915344241, 55.79899647809345), (37.384344043655396, 55.80113596939471), (37.38594269577028, 55.80322699999366), (37.38711208598329, 55.804919036911976), (37.3880239841309, 55.806610999993666), (37.38928977249147, 55.81001864976979),
(37.39038389947512, 55.81348641242801), (37.39235781481933, 55.81983538336746), (37.393709457672124, 55.82417822811877), (37.394685720901464, 55.82792275755836), (37.39557615344238, 55.830447148154136), (37.39844478226658, 55.83167107969975),
(37.40019761214057, 55.83151823557964), (37.400398790382326, 55.83264967594742), (37.39659544313046, 55.83322180909622), (37.39667059524539, 55.83402792148566), (37.39682089947515, 55.83638877400216), (37.39643489154053, 55.83861656112751),
(37.3955338994751, 55.84072348043264), (37.392680272491454, 55.84502158126453), (37.39241188227847, 55.84659117913199), (37.392529730163616, 55.84816071336481), (37.39486835714723, 55.85288092980303), (37.39873052645878, 55.859893456073635),
(37.40272161111449, 55.86441833633205), (37.40697072750854, 55.867579567544375), (37.410007082016016, 55.868369880337), (37.4120992989502, 55.86920843741314), (37.412668021163924, 55.87055369615854), (37.41482461111453, 55.87170587948249),
(37.41862266137694, 55.873183961039565), (37.42413732540892, 55.874879126654704), (37.4312182698669, 55.875614937236705), (37.43111093783558, 55.8762723478417), (37.43332105622856, 55.87706546369396), (37.43385747619623, 55.87790681284802),
(37.441303050262405, 55.88027084462084), (37.44747234260555, 55.87942070143253), (37.44716141796871, 55.88072960917233), (37.44769797085568, 55.88121221323979), (37.45204320500181, 55.882080694420715), (37.45673176190186, 55.882346110794586),
(37.463383999999984, 55.88252729504517), (37.46682797486874, 55.88294937719063), (37.470014457672086, 55.88361266759345), (37.47751410450743, 55.88546991372396), (37.47860317658232, 55.88534929207307), (37.48165826025772, 55.882563306475106),
(37.48316434442331, 55.8815803226785), (37.483831555817645, 55.882427612793315), (37.483182967125686, 55.88372791409729), (37.483092277908824, 55.88495581062434), (37.4855716508179, 55.8875561994203), (37.486440636245746, 55.887827444039566),
(37.49014203439328, 55.88897899871799), (37.493210285705544, 55.890208937135604), (37.497512451065035, 55.891342397444696), (37.49780744510645, 55.89174030252967), (37.49940333499519, 55.89239745507079), (37.50018383334346, 55.89339220941865),
(37.52421672750851, 55.903869074155224), (37.52977457672118, 55.90564076517974), (37.53503220370484, 55.90661661218259), (37.54042858064267, 55.90714113744566), (37.54320461007303, 55.905645048442985), (37.545686966066306, 55.906608607018505),
(37.54743976120755, 55.90788552162358), (37.55796999999999, 55.90901557907218), (37.572711542327866, 55.91059395704873), (37.57942799999998, 55.91073854155573), (37.58502865872187, 55.91009969268444), (37.58739968913264, 55.90794809960554),
(37.59131567193598, 55.908713267595054), (37.612687423278814, 55.902866854295375), (37.62348079629517, 55.90041967242986), (37.635797880950896, 55.898141151686396), (37.649487626983664, 55.89639275532968), (37.65619302513125, 55.89572360207488),
(37.66294133862307, 55.895295577183965), (37.66874564418033, 55.89505457604897), (37.67375601586915, 55.89254677027454), (37.67744661901856, 55.8947775867987), (37.688347, 55.89450045676125), (37.69480554232789, 55.89422926332761),
(37.70107096560668, 55.89322256101114), (37.705962965606716, 55.891763491662616), (37.711885134918205, 55.889110234998974), (37.71682005026245, 55.886577568759876), (37.7199315476074, 55.88458159806678), (37.72234560316464, 55.882281005794134),
(37.72364385977171, 55.8809452036196), (37.725371142837474, 55.8809722706006), (37.727870902099546, 55.88037213862385), (37.73394330422971, 55.877941504088696), (37.745339592590376, 55.87208120378722), (37.75525267724611, 55.86703807949492),
(37.76919976190188, 55.859821640197474), (37.827835219574, 55.82962968399116), (37.83341438888553, 55.82575289922351), (37.83652584655761, 55.82188784027888), (37.83809213491821, 55.81612575504693), (37.83605359521481, 55.81460347077685),
(37.83632178569025, 55.81276696067908), (37.838623105812026, 55.811486181656385), (37.83912198147584, 55.807329380532785), (37.839079078033414, 55.80510270463816), (37.83965844708251, 55.79940712529036), (37.840581150787344, 55.79131399999368),
(37.84172564285271, 55.78000432402266)]);

3. 查看莫斯科有多少个基站：

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

┌─count()─┐
│  310463 │
└─────────┘

返回 1 行。耗时:0.067 秒。处理了 4328 万行,692.42 MB(每秒 6.4583 亿行,10.33 GB/秒)。

查看模式

在 Superset 中构建可视化之前，请先查看你将要使用的列。此数据集主要提供全球移动蜂窝基站的位置（经度和纬度）以及无线接入技术类型。各列的说明可以在社区论坛中找到。下面描述将用于构建可视化的列。

以下是从 OpenCelliD 论坛中摘取的列说明：

Column	Description
radio	技术代际：CDMA、GSM、UMTS、5G NR
mcc	移动国家码（Mobile Country Code）：204 代表荷兰
lon	经度：与纬度一起，表示基站的大致位置
lat	纬度：与经度一起，表示基站的大致位置

mcc

要查找你的 MCC，请查看 Mobile network codes，并使用 Mobile country code 列中的三位数字。

此表的模式专为在磁盘上实现紧凑存储和提升查询速度而设计。

• radio 数据以 Enum8（UInt8）而不是字符串形式存储。
• mcc（移动国家码）以 UInt16 存储，因为我们知道其取值范围为 1 - 999。
• lon 和 lat 为 Float64。

本指南中的查询或可视化不会用到其他字段，但如果你感兴趣，可以在上面链接的论坛中查看它们的说明。

使用 Apache Superset 构建可视化

Superset 可以很方便地通过 Docker 运行。 如果已经在运行 Superset，只需要执行 pip install clickhouse-connect 来添加 ClickHouse Connect 即可。 如果需要安装 Superset，请打开下方的 Launch Apache Superset in Docker。

在 Docker 中启动 Apache Superset

Superset 提供了使用 Docker Compose 在本地安装 Superset 的说明文档。从 GitHub 克隆 Apache Superset 代码仓库后,您可以运行最新的开发代码或指定的标签版本。我们推荐使用 2.0.0 版本,因为它是最新的正式发布版本(未标记为 pre-release)。

运行 docker compose 之前需要完成以下几项任务:

1. 添加官方 ClickHouse Connect 驱动
2. 获取 Mapbox API 密钥并将其添加为环境变量(可选)
3. 指定要运行的 Superset 版本

提示

以下命令需要在 GitHub 代码仓库 superset 的根目录下运行。

官方 ClickHouse Connect 驱动程序

要在 Superset 部署中启用 ClickHouse Connect 驱动程序，请将其添加到本地 requirements 文件中：

echo "clickhouse-connect" >> ./docker/requirements-local.txt

Mapbox

此步骤是可选的。你可以在不提供 Mapbox API 密钥的情况下在 Superset 中绘制地理位置信息，但你会看到一条提示信息，建议你添加密钥，而且地图的背景图像会缺失（你只能看到数据点，而看不到地图底图）。如果你想使用，Mapbox 提供了免费套餐。

部分示例可视化（指南中引导你创建的那些）会使用位置信息，例如经纬度数据。Superset 内置对 Mapbox 地图的支持。要使用 Mapbox 可视化，你需要一个 Mapbox API 密钥。注册 Mapbox 免费套餐，并生成一个 API 密钥。

将该 API 密钥提供给 Superset 使用：

echo "MAPBOX_API_KEY=pk.SAMPLE-Use-your-key-instead" >> docker/.env-non-dev

部署 Superset 2.0.0 版本

要部署 2.0.0 版本,请运行:

git checkout 2.0.0
TAG=2.0.0 docker-compose -f docker-compose-non-dev.yml pull
TAG=2.0.0 docker-compose -f docker-compose-non-dev.yml up

要使用 OpenCelliD 数据集构建 Superset dashboard，你需要：

• 将 ClickHouse 服务添加为 Superset 的 database
• 将表 cell_towers 添加为 Superset 的 dataset
• 创建一些 charts
• 将这些图表添加到一个 dashboard

将你的 ClickHouse 服务添加为 Superset 数据库

要通过 HTTP(S) 连接到 ClickHouse，您需要以下信息：

参数	说明
HOST 和 PORT	通常，在使用 TLS 时端口为 8443，不使用 TLS 时端口为 8123。
DATABASE NAME	默认提供一个名为 default 的数据库，请填写您要连接的目标数据库名称。
USERNAME 和 PASSWORD	默认用户名为 default。请使用适合您使用场景的用户名。

您的 ClickHouse Cloud 服务的详细信息可以在 ClickHouse Cloud 控制台中查看。 选择某个服务并点击 Connect：

选择 HTTPS。连接信息会显示在示例 curl 命令中。

如果您使用的是自托管 ClickHouse，则连接信息由您的 ClickHouse 管理员进行设置。

在 Superset 中，可以通过选择数据库类型并提供连接信息来添加数据库。打开 Superset，找到 + 按钮，在其菜单中选择 Data，然后选择 Connect database 选项。

从列表中选择 ClickHouse Connect：

注意

如果在可选项中没有 ClickHouse Connect，则需要先安装它。命令为 pip install clickhouse-connect，更多信息请参见此处。

添加连接详细信息

提示

在连接到 ClickHouse Cloud 或其他强制使用 SSL 的 ClickHouse 系统时，请确保已启用 SSL。

将表 cell_towers 添加为 Superset 数据集（dataset）

在 Superset 中，一个 dataset 对应数据库中的一张表。点击 “Add dataset”，选择你的 ClickHouse 服务、包含该表的数据库（default），然后选择 cell_towers 表：

创建一些图表

在 Superset 中选择添加图表时，您需要指定数据集（cell_towers）和图表类型。由于 OpenCelliD 数据集为基站提供了经度和纬度坐标，我们将创建一个地图图表（Map）。deck.gl Scatterplot 类型非常适合该数据集，因为它在地图上展示高密度数据点时表现出色。

为地图指定所使用的查询

deck.gl 的散点图（Scatterplot）需要经度和纬度字段，并且可以在查询中应用一个或多个过滤条件。本示例中应用了两个过滤条件：一个用于带有 UMTS 无线电的基站，一个用于分配给荷兰的移动国家码（Mobile Country Code, MCC）。

字段 lon 和 lat 包含经度和纬度：

添加一个过滤条件，使 mcc = 204（或替换为任意其他 mcc 值）：

添加一个过滤条件，使 radio = 'UMTS'（或替换为任意其他 radio 值，你可以在 DESCRIBE TABLE cell_towers 的输出中查看可选值）：

以下是图表的完整配置，用于过滤出 radio = 'UMTS' 且 mcc = 204 的数据：

点击 UPDATE CHART 来渲染该可视化图表。

将图表添加到仪表板

此截图展示了包含 LTE、UMTS 和 GSM 无线电的基站位置。所有图表均以相同方式创建，然后添加到同一个仪表板中。

提示

这些数据也可以在 Playground 中通过交互式查询进行访问。

此示例会为你预填用户名，甚至查询语句。

尽管你不能在 Playground 中创建表，但你可以运行所有查询，甚至可以使用 Superset（只需调整主机名和端口号）。