Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

Foursquare OS Places 数据集

View as MarkdownEdit this pageReport an issueSet up docs MCP

Foursquare OS Places 包含超过 1 亿个商业兴趣点 (POI) ， 包括商店、餐厅、公园、游乐场和纪念碑。本指南将介绍如何 将 ClickHouse 连接到 Foursquare 的 Iceberg 目录，浏览数据集，并将其加载 到针对地理空间查询优化的表中。

该数据集可通过 Foursquare Places Portal 获取，并可在 Apache 2.0 许可证下免费使用。

开始之前

在运行本指南中的查询之前，您需要：

连接到 Foursquare 目录

请妥善保管访问令牌。启动 ClickHouse 客户端，然后将以下查询中的 <YOUR_ACCESS_TOKEN> 替换为您的令牌：

Querysql
SET allow_database_iceberg = 1;

CREATE DATABASE places
ENGINE = DataLakeCatalog('https://catalog.h3-hub.foursquare.com/iceberg')
SETTINGS
    catalog_type = 'rest',
    warehouse = 'places',
    auth_header = 'Authorization: Bearer <YOUR_ACCESS_TOKEN>',
    vended_credentials = 1;

目录数据库为只读。places_os 表反映的是 Foursquare's 当前发布的发行版， 而非固定在某个日期的 Parquet 版本，因此其行和 schema 可能会随时间变化。 因此，不含 ORDER BY 子句的查询返回的样本行可能与本指南中显示的响应不同。

验证连接

从 places_os Iceberg 表中查询一行数据：

Querysql
SELECT *
FROM places.`datasets.places_os`
LIMIT 1;
Responseresponse
Row 1:
──────
fsq_place_id:        587711a138094df2b93ec3af
name:                Iyang Tadon
latitude:            ᴺᵁᴸᴸ
longitude:           ᴺᵁᴸᴸ
address:             2 38A Jalan Penrissen Batu 10 Pekan Batu 10 93250 Kuching Kuching Sarawak 93250 Malaysia Kuching Sarawak
locality:            Kuching
region:              Sarawak
postcode:            93250
admin_region:        ᴺᵁᴸᴸ
post_town:           ᴺᵁᴸᴸ
po_box:              ᴺᵁᴸᴸ
country:             MY
date_created:        2015-05-24
date_refreshed:      2015-05-24
date_closed:         ᴺᵁᴸᴸ
tel:                 082-617 033
website:             ᴺᵁᴸᴸ
email:               ᴺᵁᴸᴸ
facebook_id:         ᴺᵁᴸᴸ
instagram:           ᴺᵁᴸᴸ
twitter:             ᴺᵁᴸᴸ
fsq_category_ids:    []
fsq_category_labels: []
placemaker_url:      https://foursquare.com/placemakers/review-place/587711a138094df2b93ec3af
unresolved_flags:    []
geom:                ᴺᵁᴸᴸ
bbox:                (NULL,NULL,NULL,NULL)

探索数据

样本行中有多个 null 字段。添加过滤器以返回更完整的行：

Querysql
SELECT *
FROM places.`datasets.places_os`
WHERE address IS NOT NULL AND postcode IS NOT NULL AND instagram IS NOT NULL
LIMIT 1;
Responseresponse
Row 1:
──────
fsq_place_id:        4b9af2a9f964a52000e635e3
name:                KFC
latitude:            42.214429044404966
longitude:           -83.5428035767019
address:             2169 Rawsonville Rd
locality:            Van Buren Township
region:              MI
postcode:            48111
admin_region:        ᴺᵁᴸᴸ
post_town:           ᴺᵁᴸᴸ
po_box:              ᴺᵁᴸᴸ
country:             US
date_created:        2010-03-13
date_refreshed:      2026-07-08
date_closed:         ᴺᵁᴸᴸ
tel:                 (734) 482-7256
website:             https://locations.kfc.com/mi/belleville/2169-rawsonville-road
email:               kfccares@kfc.com
facebook_id:         159863790842385 -- 159.86 trillion
instagram:           kfc
twitter:             kfc
fsq_category_ids:    ['4d4ae6fc7a7b7dea34424761','4bf58dd8d48988d16e941735']
fsq_category_labels: ['Dining and Drinking > Restaurant > Fried Chicken Joint','Dining and Drinking > Restaurant > Fast Food Restaurant']
placemaker_url:      https://foursquare.com/placemakers/review-place/4b9af2a9f964a52000e635e3
unresolved_flags:    []
geom:                [binary data]
bbox:                (-83.5428035767019,42.214429044404966,-83.5428035767019,42.214429044404966)

使用 DESCRIBE 查看表 schema：

Querysql
DESCRIBE places.`datasets.places_os`;
Responseresponse
    ┌─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. │ unresolved_flags    │ Array(Nullable(String))     │
26. │ geom                │ Nullable(String)            │
27. │ bbox                │ Tuple(                     ↴│
    │                     │↳    xmin Nullable(Float64),↴│
    │                     │↳    ymin Nullable(Float64),↴│
    │                     │↳    xmax Nullable(Float64),↴│
    │                     │↳    ymax Nullable(Float64)) │
    └─────────────────────┴─────────────────────────────┘

将数据加载到 ClickHouse

要持久存储数据，请在 clickhouse-server 或 ClickHouse Cloud 上创建表。

创建一个包含字典编码列和 materialized Web 墨卡托投影 坐标的 MergeTree 表：

Querysql
CREATE TABLE foursquare_mercator
(
    fsq_place_id Nullable(String),
    name Nullable(String),
    latitude Float64,
    longitude Float64,
    address Nullable(String),
    locality Nullable(String),
    region LowCardinality(Nullable(String)),
    postcode LowCardinality(Nullable(String)),
    admin_region LowCardinality(Nullable(String)),
    post_town LowCardinality(Nullable(String)),
    po_box LowCardinality(Nullable(String)),
    country LowCardinality(Nullable(String)),
    date_created Nullable(Date),
    date_refreshed Nullable(Date),
    date_closed Nullable(Date),
    tel Nullable(String),
    website Nullable(String),
    email Nullable(String),
    facebook_id Nullable(Int64),
    instagram Nullable(String),
    twitter Nullable(String),
    fsq_category_ids Array(Nullable(String)),
    fsq_category_labels Array(Nullable(String)),
    placemaker_url Nullable(String),
    geom Nullable(String),
    bbox Tuple(
        xmin Nullable(Float64),
        ymin Nullable(Float64),
        xmax Nullable(Float64),
        ymax Nullable(Float64)
    ),
    category LowCardinality(Nullable(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
)
ENGINE = MergeTree
ORDER BY mortonEncode(mercator_x, mercator_y);

多个列使用 LowCardinality 数据类型， 该类型通过字典编码存储重复值。这种表示方式可以显著 提升 SELECT 查询性能。

两个 UInt32 MATERIALIZED 列 mercator_x 和 mercator_y 将纬度和 经度映射到 Web 墨卡托投影， 从而更方便地将地图划分为图块：

mercator_x UInt32 MATERIALIZED 0xFFFFFFFF * ((longitude + 180) / 360),
mercator_y UInt32 MATERIALIZED 0xFFFFFFFF * ((1 / 2) - ((log(tan(((latitude + 90) / 360) * pi())) / 2) / pi())),

这些表达式计算以下值。

mercator_x

此列将经度值转换为墨卡托投影中的 X 坐标：

  • longitude + 180 将经度范围从 [-180, 180] 平移到 [0, 360]。
  • 除以 360 可将该值归一化到 0 到 1 的范围。
  • 乘以 0xFFFFFFFF (最大的 32 位无符号整数) 可将归一化后的值缩放到完整的 32 位整数范围。

mercator_y

此列将纬度值转换为墨卡托投影中的 Y 坐标：

  • latitude + 90 将纬度范围从 [-90, 90] 平移到 [0, 180]。
  • 除以 360 并乘以 pi，可将该值转换为供三角函数使用的弧度。
  • log(tan(...)) 应用墨卡托投影的核心公式。
  • 乘以 0xFFFFFFFF 可将结果缩放到完整的 32 位整数范围。

指定 MATERIALIZED 后，ClickHouse 会在插入数据时计算这些值， 无需源数据包含这些列。

该表按 mortonEncode(mercator_x, mercator_y) 排序，这会创建一条 Z 阶 空间填充曲线，并按空间邻近性组织数据：

ORDER BY mortonEncode(mercator_x, mercator_y);

两个 minmax 索引可进一步加快空间筛选：

INDEX idx_x mercator_x TYPE minmax,
INDEX idx_y mercator_y TYPE minmax;

将当前 OS Places 发行版加载到表中：

Querysql
INSERT INTO foursquare_mercator
(
    fsq_place_id,
    name,
    latitude,
    longitude,
    address,
    locality,
    region,
    postcode,
    admin_region,
    post_town,
    po_box,
    country,
    date_created,
    date_refreshed,
    date_closed,
    tel,
    website,
    email,
    facebook_id,
    instagram,
    twitter,
    fsq_category_ids,
    fsq_category_labels,
    placemaker_url,
    geom,
    bbox
)
SELECT
    fsq_place_id,
    name,
    assumeNotNull(latitude),
    assumeNotNull(longitude),
    address,
    locality,
    region,
    postcode,
    admin_region,
    post_town,
    po_box,
    country,
    date_created,
    date_refreshed,
    date_closed,
    tel,
    website,
    email,
    facebook_id,
    instagram,
    twitter,
    fsq_category_ids,
    fsq_category_labels,
    placemaker_url,
    geom,
    bbox
FROM places.`datasets.places_os`
WHERE latitude IS NOT NULL AND longitude IS NOT NULL;

显式指定源列和目标端列列表，可防止目录的列 顺序变更导致导入值错位。该查询排除了 unresolved_flags，因为本地表不需要该列， 并过滤掉没有坐标的行，因为这些行无法显示在地图上。其他可为空的源值在本地表中仍为 null。

数据可视化

在公司的一次黑客松活动中，ClickHouse 联合创始人兼 CTO Alexey Milovidov 使用 ClickHouse 基于 Foursquare 数据集创建了以下可视化效果。

欧洲兴趣点密度图
日本的清酒吧
ATM
按国家分类的欧洲兴趣点地图
Navigation