> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-trino-dialect.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> 了解如何使用 ClickHouse 浏览和加载 Foursquare OS Places 地理空间数据集。

# Foursquare OS Places 数据集

export const Image = ({img, alt, size = "lg"}) => {
  const normalizedSize = ["sm", "md", "lg"].includes(size) ? size : "lg";
  return <div className={`ch-image-${normalizedSize}`}>
      <Frame>
        <img src={img} alt={alt} />
      </Frame>
    </div>;
};

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

该数据集可通过 [Foursquare Places Portal](https://places.foursquare.com/)
获取，并可在 Apache 2.0 许可证下免费使用。

<Note>
  Foursquare 已更新 OS Places 的访问方式。本指南的旧版本查询
  公网 S3 bucket 中按日期固定的文件；现在则通过 Places Portal 和经过
  身份验证的 Iceberg 目录访问。详情请参阅 [Foursquare 的 OS Places 访问文档](https://docs.foursquare.com/data-products/docs/access-fsq-os-places)。
</Note>

<div id="before-you-begin">
  ## 开始之前
</div>

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

* 一个 [Foursquare Places Portal](https://places.foursquare.com/) 账户
* 通过 **OS Places** 数据集的 **Access Data** 选项卡创建的访问令牌

<div id="connect-to-the-foursquare-catalog">
  ## 连接到 Foursquare 目录
</div>

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

```sql title="Query" theme={null}
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` 子句的查询返回的样本行可能与本指南中显示的响应不同。

<div id="verify-the-connection">
  ## 验证连接
</div>

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

```sql title="Query" theme={null}
SELECT *
FROM places.`datasets.places_os`
LIMIT 1;
```

```response title="Response" theme={null}
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)
```

<div id="explore-the-data">
  ## 探索数据
</div>

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

```sql title="Query" theme={null}
SELECT *
FROM places.`datasets.places_os`
WHERE address IS NOT NULL AND postcode IS NOT NULL AND instagram IS NOT NULL
LIMIT 1;
```

```response title="Response" theme={null}
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：

```sql title="Query" theme={null}
DESCRIBE places.`datasets.places_os`;
```

```response title="Response" theme={null}
    ┌─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)) │
    └─────────────────────┴─────────────────────────────┘
```

<div id="loading-the-data">
  ## 将数据加载到 ClickHouse
</div>

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

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

```sql title="Query" theme={null}
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`](/zh/reference/data-types/lowcardinality) 数据类型，
该类型通过字典编码存储重复值。这种表示方式可以显著
提升 `SELECT` 查询性能。

两个 `UInt32` `MATERIALIZED` 列 `mercator_x` 和 `mercator_y` 将纬度和
经度映射到 [Web 墨卡托投影](https://en.wikipedia.org/wiki/Web_Mercator_projection)，
从而更方便地将地图划分为图块：

```sql theme={null}
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 阶
空间填充曲线，并按空间邻近性组织数据：

```sql theme={null}
ORDER BY mortonEncode(mercator_x, mercator_y);
```

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

```sql theme={null}
INDEX idx_x mercator_x TYPE minmax,
INDEX idx_y mercator_y TYPE minmax;
```

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

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

```sql title="Query" theme={null}
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。

<div id="data-visualization">
  ## 数据可视化
</div>

<Note>
  自创建这些可视化效果以来，Foursquare 的访问模型已发生变化。
  [原始交互式 Places 视图](https://adsb.exposed/?dataset=Places\&zoom=5\&lat=52.3488\&lng=4.9219)
  早于当前的访问模型，提供该链接仅供历史参考，但它可能已不再
  显示 Places 数据。下方图片保留为历史示例。
</Note>

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

<Image img="https://mintcdn.com/private-7c7dfe99-trino-dialect/pOHtM6L6ptAMkHyp/images/getting-started/example-datasets/visualization_1.webp?fit=max&auto=format&n=pOHtM6L6ptAMkHyp&q=85&s=04dc03a464d2bca403fe377ca20d8541" size="md" alt="欧洲兴趣点密度图" width="2251" height="1509" data-path="images/getting-started/example-datasets/visualization_1.webp" />

<Image img="https://mintcdn.com/private-7c7dfe99-trino-dialect/pOHtM6L6ptAMkHyp/images/getting-started/example-datasets/visualization_2.webp?fit=max&auto=format&n=pOHtM6L6ptAMkHyp&q=85&s=3a0337e5b68cf668c06e5ad30b3f8543" size="md" alt="日本的清酒吧" width="2381" height="1585" data-path="images/getting-started/example-datasets/visualization_2.webp" />

<Image img="https://mintcdn.com/private-7c7dfe99-trino-dialect/pOHtM6L6ptAMkHyp/images/getting-started/example-datasets/visualization_3.webp?fit=max&auto=format&n=pOHtM6L6ptAMkHyp&q=85&s=b540ff5b870c25c7d10a9187625d16dc" size="md" alt="ATM" width="2130" height="1565" data-path="images/getting-started/example-datasets/visualization_3.webp" />

<Image img="https://mintcdn.com/private-7c7dfe99-trino-dialect/pOHtM6L6ptAMkHyp/images/getting-started/example-datasets/visualization_4.webp?fit=max&auto=format&n=pOHtM6L6ptAMkHyp&q=85&s=614d52849db7a08a1a037ee2ca9f365d" size="md" alt="按国家分类的欧洲兴趣点地图" width="633" height="583" data-path="images/getting-started/example-datasets/visualization_4.webp" />