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 Places Portal のアカウント
  • OS Places データセットの Access Data タブで作成したアクセストークン

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 テーブルは日付固定の Parquet リリースではなく、Foursquare's の最新公開リリースを反映するため、行とスキーマは時間の経過とともに変更される可能性があります。したがって、ORDER BY 句のないクエリでは、このガイドに示した応答とは異なるサンプル行が返される場合があります。

接続を確認する

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

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

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

辞書エンコードされたカラムとマテリアライズドされた Web Mercator 座標を含む 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 クエリのパフォーマンスを大幅に向上させることができます。

2 つの 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 の範囲に正規化されます。
  • 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 オーダーの 空間充填曲線が作成され、空間的な近接性に基づいてデータが整理されます。

ORDER BY mortonEncode(mercator_x, mercator_y);

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

データを可視化する

社内ハッカソンで、ClickHouse の共同創業者兼 CTO である Alexey Milovidov は ClickHouse を使用して、 Foursquare データセットから以下の可視化を作成しました。

ヨーロッパのポイントオブインタレストの密度マップ
日本の日本酒バー
ATM
ポイントオブインタレストを国別に分類したヨーロッパ地図
Navigation