Foursquare OS Places データセット

Foursquare OS Places には、店舗、レストラン、公園、遊び場、記念碑など、1 億件を超える商業施設・場所に関するポイントオブインタレスト (POI) が収録されています。このガイドでは、ClickHouse を Foursquare の Iceberg カタログに接続し、データセットを調査して、地理空間クエリ向けに最適化されたテーブルに読み込みます。このデータセットは Foursquare Places Portal で提供されており、Apache 2.0 ライセンスのもとで無料で利用できます。

Foursquare は OS Places へのアクセス方法を変更しました。このガイドの以前のバージョンでは、パブリック S3 バケット内の日付で固定されたファイルをクエリしていましたが、現在は Places Portal と認証済みの Iceberg カタログを使用してアクセスします。詳細については、Foursquare’s OS Places access documentation を参照してください。

始める前に

このガイドのクエリを実行する前に、次のものが必要です。

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

接続を確認する

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

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

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

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

データを可視化する

これらの可視化が作成されて以降、Foursquare のアクセスモデルは変更されています。元のインタラクティブな Places ビューは現在のアクセスモデルより前のものであり、歴史的な参照用としてリンクされていますが、Places データが表示されなくなっている可能性があります。以下の画像は歴史的な例として保持されています。

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

はじめに

セットアップ

サンプルデータセット

移行

始める前に

Foursquareカタログに接続する

接続を確認する

データを探索する

データをClickHouseに読み込む

データを可視化する

​始める前に

​Foursquareカタログに接続する

​接続を確認する

​データを探索する

​データをClickHouseに読み込む

​データを可視化する

始める前に

Foursquareカタログに接続する

接続を確認する

データを探索する

データをClickHouseに読み込む

データを可視化する