本文字数：5939；估计阅读时间：15 分钟
作者：Alexey Milovidov
Meetup活动
ClickHouse Shenzhen User Group第2届 Meetup 火热报名中，详见文末海报！
最近我们公司举行了一次线下全员活动，来自各地的同事齐聚一堂。每逢这样的聚会，我都会组织一次 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 10Row 1:──────fsq_place_id: 4ed7a0b89adf06cbf6d71fecname: Частная Бильярднаяlatitude: 55.82704778252206longitude: 37.44663365528853address: ᴺᵁᴸᴸlocality: ᴺᵁᴸᴸregion: ᴺᵁᴸᴸpostcode: ᴺᵁᴸᴸadmin_region: ᴺᵁᴸᴸpost_town: ᴺᵁᴸᴸpo_box: ᴺᵁᴸᴸcountry: RUdate_created: 2011-12-01date_refreshed: 2013-01-13date_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/4ed7a0b89adf06cbf6d71fecgeom: @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 10Row 1:──────fsq_place_id: 643c2e2fc5a3b53de7ddfea7name: VIBE Nagymaroslatitude: 47.781879longitude: 18.946042address: Szamaras Utcalocality: Nagymarosregion: PEpostcode: 2626admin_region: ᴺᵁᴸᴸpost_town: ᴺᵁᴸᴸpo_box: ᴺᵁᴸᴸcountry: HUdate_created: 2023-04-16date_refreshed: 2024-10-18date_closed: ᴺᵁᴸᴸtel: ᴺᵁᴸᴸwebsite: http://www.vibenagymaros.huemail: ᴺᵁᴸᴸfacebook_id: ᴺᵁᴸᴸinstagram: vibenagymarostwitter: ᴺᵁᴸᴸfsq_category_ids: ['56aa371be4b08b9a8d5734e1']fsq_category_labels: ['Travel and Transportation > Lodging > Vacation Rental']placemaker_url: https://foursquare.com/placemakers/review-place/643c2e2fc5a3b53de7ddfea7geom: @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)) │└─────────────────────┴─────────────────────────────┘
我创建了如下表结构：
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 Shenzhen User Group第2届 Meetup 火热报名中，将于2025年05月17日在深圳BIO ONE创新中心会议室（深圳市南山区海天一路17-6软件产业基地4栋B座1楼大堂）举行，扫码免费报名
/END/
注册ClickHouse中国社区大使，领取认证考试券
试用阿里云 ClickHouse企业版
轻松节省30%云资源成本？阿里云数据库ClickHouse 云原生架构全新升级，首次购买ClickHouse企业版计算和存储资源组合，首月消费不超过99.58元（包含最大16CCU+450G OSS用量）了解详情：https://t.aliyun.com/Kz5Z0q9G
征稿启示
面向社区长期正文，文章内容包括但不限于关于 ClickHouse 的技术研究、项目实践和创新做法等。建议行文风格干货输出&图文并茂。质量合格的文章将会发布在本公众号，优秀者也有机会推荐到 ClickHouse 官网。请将文章稿件的 WORD 版本发邮件至：Tracy.Wang@clickhouse.com
