> ## 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 バケット内の日付で固定されたファイルをクエリしていましたが、現在は Places Portal と認証済みの Iceberg カタログを使用してアクセスします。詳細については、[Foursquare's OS Places access documentation](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` テーブルは日付固定の Parquet リリースではなく、Foursquare's の最新公開リリースを反映するため、行とスキーマは時間の経過とともに変更される可能性があります。したがって、`ORDER BY` 句のないクエリでは、このガイドに示した応答とは異なるサンプル行が返される場合があります。

<div id="verify-the-connection">
  ## 接続を確認する
</div>

`places_os` Icebergテーブルから1行をクエリします。

```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` を使用してテーブルスキーマを確認します：

```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 にテーブルを作成します。

辞書エンコードされたカラムとマテリアライズドされた Web Mercator
座標を含む `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`](/ja/reference/data-types/lowcardinality) データ型を使用しています。
この表現により、`SELECT` クエリのパフォーマンスを大幅に向上させることができます。

2 つの `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 の範囲に正規化されます。
* 32 ビット符号なし整数の最大値である `0xFFFFFFFF` を乗算すると、正規化された値が 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);
```

2 つの `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` を
除外し、座標のない行はマップ上に配置できないため除外します。その他の Nullable なログソース値は、ローカルテーブルでも 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" />