本文字数：5939；估计阅读时间：15 分钟
作者：Alexey Milovidov
本文在公众号【ClickHouselnc】首发
最近我们公司举行了一次线下全员活动，来自各地的同事齐聚一堂。每逢这样的聚会，我都会组织一次 hackathon。本次的主题是数据可视化，规则非常简单：每组有两个小时，从一个数据集中出发，基于 ClickHouse 打造一个有趣的可视化演示。我也参与了这次活动，下面是我完成的项目。
数据集
本次使用的数据集是公开发布的，遵循 Apache 2.0 协议，支持免费下载和自由使用。数据集中包含各种地图上的地点信息，如商店、餐厅、公园、游乐场、纪念碑等，并附带分类、邮箱等元数据。以 ClickHouse 的处理能力来看，这个数据集规模并不大，记录数略高于 1 亿。但从类别来看，它可能是目前最大规模的此类开源数据集。
此前我曾为全球航班流量制作过一个可视化工具，实时处理超过 1300 亿条记录，因此用这个工具来处理 Foursquare 的这个“小数据集”几乎毫无压力。我决定直接复用它来进行可视化。
数据预览
想要快速预览并查询该数据集，可以直接使用 s3 表函数：
:) SELECT * FROM s3('s3://fsq-os-places-us-east-1/release/dt=2025-04-08/places/parquet/*') LIMIT 10
Row 1:
──────
fsq_place_id: 4ed7a0b89adf06cbf6d71fec
name: Частная Бильярдная
latitude: 55.82704778252206
longitude: 37.44663365528853
address: ᴺᵁᴸᴸ
locality: ᴺᵁᴸᴸ
region: ᴺᵁᴸᴸ
postcode: ᴺᵁᴸᴸ
admin_region: ᴺᵁᴸᴸ
post_town: ᴺᵁᴸᴸ
po_box: ᴺᵁᴸᴸ
country: RU
date_created: 2011-12-01
date_refreshed: 2013-01-13
date_closed: ᴺᵁᴸᴸ
tel: ᴺᵁᴸᴸ
website: ᴺᵁᴸᴸ
email: ᴺᵁᴸᴸ
facebook_id: ᴺᵁᴸᴸ
instagram: ᴺᵁᴸᴸ
twitter: ᴺᵁᴸᴸ
fsq_category_ids: ['4bf58dd8d48988d1e3931735']
fsq_category_labels: ['Arts and Entertainment > Pool Hall']
placemaker_url: https://foursquare.com/placemakers/review-place/4ed7a0b89adf06cbf6d71fec
geom: @B�+J�`�@K�ܳ��
bbox: (37.44663365528853,55.82704778252206,37.44663365528853,55.82704778252206)
:) SELECT * FROM s3('s3://fsq-os-places-us-east-1/release/dt=2025-04-08/places/parquet/*')
WHERE address IS NOT NULL AND postcode IS NOT NULL AND instagram IS NOT NULL LIMIT 10
Row 1:
──────
fsq_place_id: 643c2e2fc5a3b53de7ddfea7
name: VIBE Nagymaros
latitude: 47.781879
longitude: 18.946042
address: Szamaras Utca
locality: Nagymaros
region: PE
postcode: 2626
admin_region: ᴺᵁᴸᴸ
post_town: ᴺᵁᴸᴸ
po_box: ᴺᵁᴸᴸ
country: HU
date_created: 2023-04-16
date_refreshed: 2024-10-18
date_closed: ᴺᵁᴸᴸ
tel: ᴺᵁᴸᴸ
website: http://www.vibenagymaros.hu
email: ᴺᵁᴸᴸ
facebook_id: ᴺᵁᴸᴸ
instagram: vibenagymaros
twitter: ᴺᵁᴸᴸ
fsq_category_ids: ['56aa371be4b08b9a8d5734e1']
fsq_category_labels: ['Travel and Transportation > Lodging > Vacation Rental']
placemaker_url: https://foursquare.com/placemakers/review-place/643c2e2fc5a3b53de7ddfea7
geom: @2�/���v@G��o6�
bbox: (18.946042,47.781879,18.946042,47.781879)
通过 clickhouse-local 命令行工具，可以非常方便地分析本地或外部数据。这个小工具具备完整的 ClickHouse 引擎能力。
使用 DESCRIBE 语句可以自动推断数据的表结构（schema）：
:) DESCRIBE s3('s3://fsq-os-places-us-east-1/release/dt=2025-04-08/places/parquet/*')
┌─name────────────────┬─type────────────────────────┐
1. │ fsq_place_id │ Nullable(String) │
2. │ name │ Nullable(String) │
3. │ latitude │ Nullable(Float64) │
4. │ longitude │ Nullable(Float64) │
5. │ address │ Nullable(String) │
6. │ locality │ Nullable(String) │
7. │ region │ Nullable(String) │
8. │ postcode │ Nullable(String) │
9. │ admin_region │ Nullable(String) │
10. │ post_town │ Nullable(String) │
11. │ po_box │ Nullable(String) │
12. │ country │ Nullable(String) │
13. │ date_created │ Nullable(String) │
14. │ date_refreshed │ Nullable(String) │
15. │ date_closed │ Nullable(String) │
16. │ tel │ Nullable(String) │
17. │ website │ Nullable(String) │
18. │ email │ Nullable(String) │
19. │ facebook_id │ Nullable(Int64) │
20. │ instagram │ Nullable(String) │
21. │ twitter │ Nullable(String) │
22. │ fsq_category_ids │ Array(Nullable(String)) │
23. │ fsq_category_labels │ Array(Nullable(String)) │
24. │ placemaker_url │ Nullable(String) │
25. │ geom │ Nullable(String) │
26. │ bbox │ Tuple( ↴│
│ │↳ xmin Nullable(Float64),↴│
│ │↳ ymin Nullable(Float64),↴│
│ │↳ xmax Nullable(Float64),↴│
│ │↳ ymax Nullable(Float64)) │
└─────────────────────┴─────────────────────────────┘
导入 ClickHouse
我创建了如下表结构：
CREATE TABLE foursquare_mercator
(
fsq_place_id String,
name String,
latitude Float64,
longitude Float64,
address String,
locality String,
region LowCardinality(String),
postcode LowCardinality(String),
admin_region LowCardinality(String),
post_town LowCardinality(String),
po_box LowCardinality(String),
country LowCardinality(String),
date_created Nullable(Date),
date_refreshed Nullable(Date),
date_closed Nullable(Date),
tel String,
website String,
email String,
facebook_id String,
instagram String,
twitter String,
fsq_category_ids Array(String),
fsq_category_labels Array(String),
placemaker_url String,
geom String,
bbox Tuple(
xmin Nullable(Float64),
ymin Nullable(Float64),
xmax Nullable(Float64),
ymax Nullable(Float64)),
category LowCardinality(String) ALIAS fsq_category_labels[1],
mercator_x UInt32 MATERIALIZED 0xFFFFFFFF * ((longitude + 180) / 360),
mercator_y UInt32 MATERIALIZED 0xFFFFFFFF * ((1 / 2) - ((log(tan(((latitude + 90) / 360) * pi())) / 2) / pi())),
INDEX idx_x mercator_x TYPE minmax,
INDEX idx_y mercator_y TYPE minmax
)
ORDER BY mortonEncode(mercator_x, mercator_y)
该表大部分字段与原始数据一致。同时，我新增了两个物化列 mercator_x 和 mercator_y，用于将地理坐标（经纬度）映射为 Web Mercator 投影坐标。在该投影方式下，坐标可用两个 UInt32 整数表示，从而便于将地图划分为图块（tile）。此外，我还按照空间填充曲线（space-filling curve）对表进行排序，并添加了两个 minmax 索引以加快检索速度。ClickHouse 本身就具备构建实时地图应用所需的全部能力。
随后，我通过以下查询将数据导入到了表中：
INSERT INTO foursquare_mercator SELECT * FROM s3('s3://fsq-os-places-us-east-1/release/dt=2025-04-08/places/parquet/*')
最后，我通过如下语句将数据导入，整个加载过程仅耗时 42 秒，最终占用存储约 11 GB。
可视化展示
我对 adsb.exposed 工具仅做了少量修改，整体只更改了 48 行代码，主要是替换了表名，并添加了一些新的查询示例。
打开页面的那一刻，我被最终呈现的效果惊艳到了——清晰、美观，令人印象深刻！
此外，图表还能展现许多有趣的数据洞察。例如，你可以框选东京地区，然后在全球范围内筛选清酒（Sake）酒吧；或者分析哪些国家的 ATM 机数量最多，哪些最少。
对比其他可视化工具
当然，这种类型的可视化并非首次出现，之前已有一些类似项目。
Foursquare Studio 的可视化在风格上与本项目接近，但加载速度较慢，图像分辨率也较低。其采用 H3 六边形网格进行聚合，而本项目直接按单像素维度进行可视化。在如阿尔卑斯山这样的复杂地形区域，细节差异尤其明显。
需要指出的是，本工具未对不同纬度区域的点密度进行标准化处理，而原始版本对此进行了补偿处理。此外，我的工具通过加载栅格化图块，在浏览器中几乎不产生卡顿；而 Foursquare Studio 的可视化会显著拖慢浏览器性能。
Simon Wilson 的项目使用 DuckDB 实现了一个基础版本的可视化，功能较为简单。
Mark 在其博客中展示的可视化结果也非常精美，与本项目风格相近，但其展示内容为静态图像，缺乏交互功能。
Kepler.gl 是一个支持 GPU 加速的浏览器端本地可视化工具。在我使用 MacBook Pro M3 进行测试时，它只能处理约一百万条记录，并且渲染过程中浏览器表现出明显的性能瓶颈。虽然 Kepler.gl 在视觉呈现方面极具特色，但在数据处理能力上存在限制。
结语
ClickHouse 非常适合用于大规模地理空间数据的分析。Foursquare 数据集规模约为 1 亿条记录，而 ADS-B 数据集已超过 1300 亿条，并持续增长中。ClickHouse 甚至支持客户处理规模达数十万亿记录的数据集。在应对此类高负载场景时，ClickHouse 不仅响应迅速，性能也极为稳定可靠。
征稿启示
面向社区长期正文，文章内容包括但不限于关于 ClickHouse 的技术研究、项目实践和创新做法等。建议行文风格干货输出&图文并茂。质量合格的文章将会发布在本公众号，优秀者也有机会推荐到 ClickHouse 官网。请将文章稿件的 WORD 版本发邮件至：Tracy.Wang@clickhouse.com
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
