Набор данных Foursquare OS Places

Foursquare OS Places содержит более 100 миллионов коммерческих точек интереса (POI), включая магазины, рестораны, парки, игровые площадки и памятники. В этом руководстве вы подключите ClickHouse к каталогу Iceberg Foursquare, изучите набор данных и загрузите его в таблицу, оптимизированную для геопространственных запросов. Набор данных доступен на портале Foursquare Places и бесплатен для использования по лицензии Apache 2.0.

Foursquare изменил способ доступа к OS Places. В предыдущих версиях этого руководства выполнялись запросы к файлам с фиксированной датой в публичном S3 бакете; теперь доступ предоставляется через портал Foursquare Places и аутентифицированный каталог Iceberg. Подробнее см. документацию Foursquare по доступу к OS Places.

Перед началом работы

Перед выполнением запросов из этого руководства вам потребуются:

аккаунт на портале Foursquare Places
токен доступа, созданный на вкладке Access Data набора данных OS Places

Подключение к каталогу 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, а не версию Parquet, привязанную к определённой дате, поэтому её строки и схема могут меняться со временем. Поэтому запросы без предложения ORDER BY могут возвращать другие строки выборки, чем показанные в этом руководстве результаты.

Проверьте подключение

Выполните запрос, возвращающий одну строку из Iceberg-таблицы places_os:

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. Создайте таблицу MergeTree со столбцами, закодированными по словарю, и материализованными координатами в проекции Web Mercator:

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 — mercator_x и mercator_y — преобразуют широту и долготу в проекцию Web Mercator, что упрощает разбиение карты на тайлы:

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 в таблицу:

Этот запрос считывает и сохраняет более 100 миллионов строк. Его выполнение может занять значительное время, потребовать места в хранилище и повлечь расходы в 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 значения источника остаются NULL в локальной таблице.

Визуализируйте данные

Модель доступа Foursquare изменилась с момента создания этих визуализаций. исходное интерактивное представление Places создано до введения текущей модели доступа и приведено для исторической справки, однако оно может больше не отображать данные Places. Изображения ниже сохранены в качестве исторических примеров.

Во время корпоративного хакатона сооснователь и CTO ClickHouse Алексей Миловидов использовал ClickHouse, чтобы создать следующие визуализации на основе набора данных Foursquare.

Get started

Setup

Sample datasets

Migrate

Набор данных Foursquare OS Places

Перед началом работы

Подключение к каталогу Foursquare

Проверьте подключение

Изучение данных

Загрузите данные в ClickHouse

Визуализируйте данные

​Перед началом работы

​Подключение к каталогу Foursquare

​Проверьте подключение

​Изучение данных

​Загрузите данные в ClickHouse

​Визуализируйте данные

Перед началом работы

Подключение к каталогу Foursquare

Проверьте подключение

Изучение данных

Загрузите данные в ClickHouse

Визуализируйте данные