Foursquare OS Places 数据集

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

Foursquare 已更新 OS Places 的访问方式。本指南的旧版本查询公网 S3 bucket 中按日期固定的文件；现在则通过 Places Portal 和经过身份验证的 Iceberg 目录访问。详情请参阅 Foursquare 的 OS Places 访问文档。

开始之前

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

一个 Foursquare Places Portal 账户
通过 OS Places 数据集的 Access Data 选项卡创建的访问令牌

连接到 Foursquare 目录

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

Query

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 表中查询一行数据：

Query

SELECT *
FROM places.`datasets.places_os`
LIMIT 1;

Response

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 字段。添加过滤器以返回更完整的行：

Query

SELECT *
FROM places.`datasets.places_os`
WHERE address IS NOT NULL AND postcode IS NOT NULL AND instagram IS NOT NULL
LIMIT 1;

Response

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：

Query

DESCRIBE places.`datasets.places_os`;

Response

    ┌─name────────────────┬─type────────────────────────┬
│ fsq_place_id        │ Nullable(String)            │
│ name                │ Nullable(String)            │
│ latitude            │ Nullable(Float64)           │
│ longitude           │ Nullable(Float64)           │
│ address             │ Nullable(String)            │
│ locality            │ Nullable(String)            │
│ region              │ Nullable(String)            │
│ postcode            │ Nullable(String)            │
│ admin_region        │ Nullable(String)            │
│ post_town           │ Nullable(String)            │
│ po_box              │ Nullable(String)            │
│ country             │ Nullable(String)            │
│ date_created        │ Nullable(String)            │
│ date_refreshed      │ Nullable(String)            │
│ date_closed         │ Nullable(String)            │
│ 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)            │
│ unresolved_flags    │ Array(Nullable(String))     │
│ geom                │ Nullable(String)            │
│ bbox                │ Tuple(                     ↴│
    │                     │↳    xmin Nullable(Float64),↴│
    │                     │↳    ymin Nullable(Float64),↴│
    │                     │↳    xmax Nullable(Float64),↴│
    │                     │↳    ymax Nullable(Float64)) │
    └─────────────────────┴─────────────────────────────┘

将数据加载到 ClickHouse

要持久存储数据，请在 clickhouse-server 或 ClickHouse Cloud 上创建表。创建一个包含字典编码列和 materialized Web 墨卡托投影坐标的 MergeTree 表：

Query

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 发行版加载到表中：

此查询会读取并存储超过 1 亿行数据，可能需要较长时间、占用存储空间，并在 ClickHouse Cloud 中产生使用费用。再次运行会追加相同的数据，因此重试导入前请确保 foursquare_mercator 为空。

Query

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。

数据可视化

自创建这些可视化效果以来，Foursquare 的访问模型已发生变化。原始交互式 Places 视图早于当前的访问模型，提供该链接仅供历史参考，但它可能已不再显示 Places 数据。下方图片保留为历史示例。

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

开始使用

设置

示例数据集

迁移

开始之前

连接到 Foursquare 目录

验证连接

探索数据

将数据加载到 ClickHouse

数据可视化

​开始之前

​连接到 Foursquare 目录

​验证连接

​探索数据

​将数据加载到 ClickHouse

​数据可视化

开始之前

连接到 Foursquare 目录

验证连接

探索数据

将数据加载到 ClickHouse

数据可视化