Jeu de données Foursquare OS Places

Foursquare OS Places contient plus de 100 millions de points d’intérêt commerciaux (POI), notamment des magasins, des restaurants, des parcs, des aires de jeux et des monuments. Dans ce guide, vous connecterez ClickHouse au catalogue Iceberg de Foursquare, explorerez le jeu de données et le chargerez dans une table optimisée pour les requêtes géospatiales.

Le jeu de données est disponible via le Foursquare Places Portal et est gratuit sous licence Apache 2.0.

Avant de commencer

Avant d’exécuter les requêtes de ce guide, vous avez besoin des éléments suivants :

Un compte Foursquare Places Portal
Un jeton d’accès créé depuis l’onglet Access Data du jeu de données OS Places

Connectez-vous au catalogue Foursquare

Gardez votre jeton d’accès confidentiel. Démarrez votre client ClickHouse, puis remplacez <YOUR_ACCESS_TOKEN> par votre jeton dans la requête suivante :

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;

La base de données du catalogue est en lecture seule. La table places_os reflète la version actuellement publiée par Foursquare, plutôt qu’une version Parquet figée à une date donnée ; ses lignes et son schéma peuvent donc évoluer au fil du temps. Les requêtes sans clause ORDER BY peuvent par conséquent renvoyer des lignes d’échantillon différentes de celles présentées dans les réponses de ce guide.

Vérifier la connexion

Interrogez une ligne de la table Iceberg places_os :

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)

Explorer les données

La ligne d’exemple contient plusieurs champs NULL. Ajoutez des filtres pour obtenir une ligne plus complète :

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)

Utilisez DESCRIBE pour inspecter le schéma de la table :

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)) │
    └─────────────────────┴─────────────────────────────┘

Chargez les données dans ClickHouse

Pour stocker les données, créez une table sur clickhouse-server ou dans ClickHouse Cloud.

Créez une table MergeTree avec des colonnes encodées par dictionnaire et des coordonnées Web Mercator matérialisées :

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);

Plusieurs colonnes utilisent le type de données LowCardinality, qui stocke les valeurs répétées à l’aide d’un encodage par dictionnaire. Cette représentation peut considérablement améliorer les performances des requêtes SELECT.

Les deux colonnes UInt32 MATERIALIZED, mercator_x et mercator_y, convertissent la latitude et la longitude dans la projection Web Mercator, ce qui facilite le découpage de la carte en tuiles :

mercator_x UInt32 MATERIALIZED 0xFFFFFFFF * ((longitude + 180) / 360),
mercator_y UInt32 MATERIALIZED 0xFFFFFFFF * ((1 / 2) - ((log(tan(((latitude + 90) / 360) * pi())) / 2) / pi())),

Les expressions calculent les valeurs suivantes.

mercator_x

Cette colonne convertit une valeur de longitude en coordonnée X dans la projection de Mercator :

longitude + 180 décale la plage de longitude de [-180, 180] vers [0, 360].
La division par 360 normalise la valeur dans une plage comprise entre 0 et 1.
La multiplication par 0xFFFFFFFF, l'entier non signé maximal sur 32 bits, met à l'échelle la valeur normalisée sur toute la plage d'un entier sur 32 bits.

mercator_y

Cette colonne convertit une valeur de latitude en coordonnée Y dans la projection de Mercator :

latitude + 90 décale la plage de latitude de [-90, 90] vers [0, 180].
La division par 360 et la multiplication par pi convertissent la valeur en radians pour les fonctions trigonométriques.
log(tan(...)) applique la formule de base de la projection de Mercator.
La multiplication par 0xFFFFFFFF met le résultat à l'échelle sur toute la plage d'un entier sur 32 bits.

La spécification de MATERIALIZED permet à ClickHouse de calculer ces valeurs lors de l'insertion des données, sans que les données source aient besoin de contenir ces colonnes.

La table est ordonnée par mortonEncode(mercator_x, mercator_y), ce qui crée une courbe de remplissage de l'espace en ordre Z et organise les données selon leur proximité spatiale :

ORDER BY mortonEncode(mercator_x, mercator_y);

Deux index minmax accélèrent encore le filtrage spatial :

INDEX idx_x mercator_x TYPE minmax,
INDEX idx_y mercator_y TYPE minmax;

Chargez la version actuelle d’OS Places dans la table :

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;

Les listes explicites de colonnes source et de destination empêchent que les modifications de l’ordre des colonnes du catalogue ne désalignent les valeurs importées. La requête exclut unresolved_flags, car cette colonne n’est pas nécessaire dans la table locale, et filtre les lignes sans coordonnées, car elles ne peuvent pas être positionnées sur la carte. Les autres valeurs sources nullables restent nulles dans la table locale.

Visualiser les données

Lors d'un hackathon de l'entreprise, le cofondateur et CTO de ClickHouse, Alexey Milovidov, a utilisé ClickHouse pour créer les visualisations suivantes à partir du jeu de données Foursquare.

Carte de densité des points d'intérêt en Europe

Carte de l'Europe avec des points d'intérêt classés par pays