Lieux Foursquare - ClickHouse Documentation

Jeu de données

Ce jeu de données de Foursquare peut être téléchargé et utilisé gratuitement sous licence Apache 2.0. Il contient plus de 100 millions d’enregistrements de points d’intérêt (POI) commerciaux, comme des magasins, des restaurants, des parcs, des aires de jeux et des monuments. Il comprend également des métadonnées supplémentaires sur ces lieux, comme des catégories et des informations sur les réseaux sociaux.

Exploration des données

Pour explorer les données, nous utiliserons clickhouse-local, un petit outil en ligne de commande qui fournit l’intégralité du moteur ClickHouse, mais vous pouvez aussi utiliser ClickHouse Cloud, clickhouse-client ou même chDB. Exécutez la requête suivante pour sélectionner les données du bucket S3 où elles sont stockées :

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)

Nous constatons qu’un certain nombre de champs ont la valeur ᴺᵁᴸᴸ. Nous pouvons donc ajouter quelques conditions supplémentaires à notre requête afin d’obtenir des données plus exploitables :

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)

Exécutez la requête suivante pour afficher le schéma des données inféré automatiquement à l’aide de 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)) │
    └─────────────────────┴─────────────────────────────┘

Chargement des données dans ClickHouse

Si vous souhaitez stocker les données sur disque de manière persistante, vous pouvez utiliser clickhouse-server ou ClickHouse Cloud. Pour créer la table, exécutez la commande suivante :

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)

Notez l’utilisation du type de données LowCardinality pour plusieurs colonnes, ce qui modifie la représentation interne de ces types de données pour utiliser un encodage par dictionnaire. L’exploitation de données encodées par dictionnaire améliore considérablement les performances des requêtes SELECT dans de nombreuses applications. De plus, deux colonnes UInt32 MATERIALIZED, mercator_x et mercator_y, sont créées pour projeter les coordonnées lat/lon dans la projection Web Mercator afin de faciliter 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())),

Décomposons ce qui se passe ci-dessus pour chaque colonne. 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 cette valeur pour obtenir un nombre compris entre 0 et 1
La multiplication par 0xFFFFFFFF (hexadécimal correspondant à la valeur maximale d’un entier non signé sur 32 bits) met cette valeur normalisée à l’échelle sur toute la plage d’un entier 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 latitude de [-90, 90] vers [0, 180]
La division par 360 puis la multiplication par pi() convertissent la valeur en radians pour les fonctions trigonométriques
La partie log(tan(...)) constitue le cœur de la formule de projection de Mercator
La multiplication par 0xFFFFFFFF met la valeur à l’échelle sur toute la plage d’entiers 32 bits

Le fait de spécifier MATERIALIZED garantit que ClickHouse calcule les valeurs de ces colonnes lorsque nous INSERT les données, sans avoir à spécifier ces colonnes (qui ne font pas partie du schéma de données d’origine) dans l’instruction `INSERT. La table est ordonnée par mortonEncode(mercator_x, mercator_y), ce qui produit une courbe de remplissage de l’espace en ordre Z pour mercator_x, mercator_y, afin d’améliorer considérablement les performances des requêtes géospatiales. Cet ordonnancement selon une courbe en Z garantit que les données sont physiquement organisées en fonction de leur proximité spatiale :

ORDER BY mortonEncode(mercator_x, mercator_y)

Deux index minmax sont également créés pour accélérer les recherches :

INDEX idx_x mercator_x TYPE minmax,
INDEX idx_y mercator_y TYPE minmax

Comme vous pouvez le voir, ClickHouse dispose de tout ce qu’il faut pour les applications de cartographie en temps réel ! Exécutez la requête suivante pour charger les données :

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

Visualisation des données

Pour voir ce qu’il est possible de faire avec ce jeu de données, consultez adsb.exposed. adsb.exposed a été conçu à l’origine par le cofondateur et CTO Alexey Milovidov pour visualiser des données de vol ADS-B (Automatic Dependent Surveillance-Broadcast), mille fois plus volumineuses. Lors d’un hackathon de l’entreprise, Alexey a ajouté les données Foursquare à l’outil. Voici quelques-unes de nos visualisations préférées, à découvrir ci-dessous.

​Jeu de données

​Exploration des données

​Chargement des données dans ClickHouse

​Visualisation des données

Jeu de données

Exploration des données

Chargement des données dans ClickHouse

Visualisation des données