Места Foursquare - ClickHouse Documentation

Набор данных

Этот набор данных от Foursquare доступен для загрузки и может бесплатно использоваться по лицензии Apache 2.0. Он содержит более 100 миллионов записей о коммерческих точках интереса (POI), таких как магазины, рестораны, парки, детские площадки и памятники. Также в него входят дополнительные метаданные об этих местах, например категории и информация из социальных сетей.

Исследование данных

Для исследования данных мы будем использовать clickhouse-local — небольшую утилиту командной строки, которая предоставляет полноценный движок ClickHouse, хотя вы также можете использовать ClickHouse Cloud, clickhouse-client или даже chDB. Выполните следующий запрос, чтобы выбрать данные из S3 бакета, где они хранятся:

Query

SELECT * FROM s3('s3://fsq-os-places-us-east-1/release/dt=2025-04-08/places/parquet/*') LIMIT 1

Response

Row 1:
──────
fsq_place_id:        4e1ef76cae60cd553dec233f
name:                @VirginAmerica In-flight Via @Gogo
latitude:            37.62120111687914
longitude:           -122.39003793803701
address:             ᴺᵁᴸᴸ
locality:            ᴺᵁᴸᴸ
region:              ᴺᵁᴸᴸ
postcode:            ᴺᵁᴸᴸ
admin_region:        ᴺᵁᴸᴸ
post_town:           ᴺᵁᴸᴸ
po_box:              ᴺᵁᴸᴸ
country:             US
date_created:        2011-07-14
date_refreshed:      2018-07-05
date_closed:         2018-07-05
tel:                 ᴺᵁᴸᴸ
website:             ᴺᵁᴸᴸ
email:               ᴺᵁᴸᴸ
facebook_id:         ᴺᵁᴸᴸ
instagram:           ᴺᵁᴸᴸ
twitter:             ᴺᵁᴸᴸ
fsq_category_ids:    ['4bf58dd8d48988d1f7931735']
fsq_category_labels: ['Travel and Transportation > Transport Hub > Airport > Plane']
placemaker_url:      https://foursquare.com/placemakers/review-place/4e1ef76cae60cd553dec233f
geom:                �^��a�^@Bσ���
bbox:                (-122.39003793803701,37.62120111687914,-122.39003793803701,37.62120111687914)

Мы видим, что во многих полях значение ᴺᵁᴸᴸ, поэтому можем добавить в запрос несколько дополнительных условий, чтобы получить более пригодные для анализа данные:

Query

SELECT * FROM s3('s3://fsq-os-places-us-east-1/release/dt=2025-04-08/places/parquet/*')
   WHERE address IS NOT NULL AND postcode IS NOT NULL AND instagram IS NOT NULL LIMIT 1

Row 1:
──────
fsq_place_id:        59b2c754b54618784f259654
name:                Villa 722
latitude:            ᴺᵁᴸᴸ
longitude:           ᴺᵁᴸᴸ
address:             Gijzenveldstraat 75
locality:            Zutendaal
region:              Limburg
postcode:            3690
admin_region:        ᴺᵁᴸᴸ
post_town:           ᴺᵁᴸᴸ
po_box:              ᴺᵁᴸᴸ
country:             ᴺᵁᴸᴸ
date_created:        2017-09-08
date_refreshed:      2020-01-25
date_closed:         ᴺᵁᴸᴸ
tel:                 ᴺᵁᴸᴸ
website:             https://www.landal.be
email:               ᴺᵁᴸᴸ
facebook_id:         522698844570949 -- 522.70 trillion
instagram:           landalmooizutendaal
twitter:             landalzdl
fsq_category_ids:    ['56aa371be4b08b9a8d5734e1']
fsq_category_labels: ['Travel and Transportation > Lodging > Vacation Rental']
placemaker_url:      https://foursquare.com/placemakers/review-place/59b2c754b54618784f259654
geom:                ᴺᵁᴸᴸ
bbox:                (NULL,NULL,NULL,NULL)

Выполните следующий запрос, чтобы посмотреть автоматически определённую схему данных с помощью DESCRIBE:

Query

DESCRIBE s3('s3://fsq-os-places-us-east-1/release/dt=2025-04-08/places/parquet/*')

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)            │
│ geom                │ Nullable(String)            │
│ bbox                │ Tuple(                     ↴│
    │                     │↳    xmin Nullable(Float64),↴│
    │                     │↳    ymin Nullable(Float64),↴│
    │                     │↳    xmax Nullable(Float64),↴│
    │                     │↳    ymax Nullable(Float64)) │
    └─────────────────────┴─────────────────────────────┘

Загрузка данных в ClickHouse

Если вы хотите хранить данные на диске, можно использовать clickhouse-server или ClickHouse Cloud. Чтобы создать таблицу, выполните следующую команду:

Query

CREATE TABLE foursquare_mercator
(
    fsq_place_id String,
    name String,
    latitude Float64,
    longitude Float64,
    address String,
    locality String,
    region LowCardinality(String),
    postcode LowCardinality(String),
    admin_region LowCardinality(String),
    post_town LowCardinality(String),
    po_box LowCardinality(String),
    country LowCardinality(String),
    date_created Nullable(Date),
    date_refreshed Nullable(Date),
    date_closed Nullable(Date),
    tel String,
    website String,
    email String,
    facebook_id String,
    instagram String,
    twitter String,
    fsq_category_ids Array(String),
    fsq_category_labels Array(String),
    placemaker_url String,
    geom String,
    bbox Tuple(
        xmin Nullable(Float64),
        ymin Nullable(Float64),
        xmax Nullable(Float64),
        ymax Nullable(Float64)
    ),
    category LowCardinality(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
)
ORDER BY mortonEncode(mercator_x, mercator_y)

Обратите внимание на использование типа данных LowCardinality для нескольких столбцов: он изменяет внутреннее представление данных этих типов на словарное кодирование. Работа с данными, закодированными с помощью словаря, значительно повышает производительность запросов SELECT во многих приложениях. Кроме того, создаются два MATERIALIZED столбца типа UInt32 — mercator_x и mercator_y, которые сопоставляют координаты lat/lon с проекцией 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 (hex-представление максимального 32-битного беззнакового целого) масштабирует это нормализованное значение до полного диапазона 32-битного целого

mercator_y Этот столбец преобразует значение широты в координату Y в проекции Меркатора:

latitude + 90 сдвигает диапазон широты из [-90, 90] в [0, 180]
Деление на 360 и умножение на pi() переводит значение в радианы для тригонометрических функций
Часть log(tan(...)) — это основа формулы проекции Меркатора
умножение на 0xFFFFFFFF масштабирует значение до полного диапазона 32-битного целого

Указание MATERIALIZED гарантирует, что ClickHouse вычислит значения этих столбцов при INSERT данных, без необходимости явно указывать эти столбцы (которые не входят в исходную схему данных) в операторе `INSERT. Таблица упорядочена по mortonEncode(mercator_x, mercator_y), что создает пространственно-заполняющую Z-кривую для 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

Как видите, в ClickHouse есть абсолютно всё, что нужно для картографических приложений, работающих в реальном времени! Выполните следующий запрос, чтобы загрузить данные:

INSERT INTO foursquare_mercator 
SELECT * FROM s3('s3://fsq-os-places-us-east-1/release/dt=2025-04-08/places/parquet/*')

Визуализация данных

Чтобы увидеть, что можно сделать с этими данными, загляните на adsb.exposed. Изначально adsb.exposed был создан сооснователем и CTO Алексеем Миловидовым для визуализации полётных данных ADS-B (Automatic Dependent Surveillance-Broadcast), объём которых в 1000 раз больше. Во время одного из хакатонов компании Алексей добавил в этот инструмент данные Foursquare. Ниже — некоторые из наших любимых визуализаций.

​Набор данных

​Исследование данных

​Загрузка данных в ClickHouse

​Визуализация данных

Набор данных

Исследование данных

Загрузка данных в ClickHouse

Визуализация данных