> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-trino-dialect.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
> Découvrez comment explorer et charger le jeu de données géospatiales Foursquare OS Places avec ClickHouse.
# Jeu de données Foursquare OS Places
export const Image = ({img, alt, size = "lg"}) => {
const normalizedSize = ["sm", "md", "lg"].includes(size) ? size : "lg";
return ;
};
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](https://places.foursquare.com/)
et est gratuit sous licence Apache 2.0.
Foursquare a modifié le mode d’accès à OS Places. Les anciennes versions de ce guide interrogeaient
des fichiers datés dans un bucket S3 public ; l’accès s’effectue désormais via le Places Portal et un
catalogue Iceberg authentifié. Consultez la [documentation de Foursquare sur l’accès à OS Places](https://docs.foursquare.com/data-products/docs/access-fsq-os-places)
pour plus de détails.
## 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](https://places.foursquare.com/)
* 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
`` par votre jeton dans la requête suivante :
```sql title="Query" theme={null}
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 ',
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` :
```sql title="Query" theme={null}
SELECT *
FROM places.`datasets.places_os`
LIMIT 1;
```
```response title="Response" theme={null}
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 :
```sql title="Query" theme={null}
SELECT *
FROM places.`datasets.places_os`
WHERE address IS NOT NULL AND postcode IS NOT NULL AND instagram IS NOT NULL
LIMIT 1;
```
```response title="Response" theme={null}
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 :
```sql title="Query" theme={null}
DESCRIBE places.`datasets.places_os`;
```
```response title="Response" theme={null}
┌─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 :
```sql title="Query" theme={null}
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`](/fr/reference/data-types/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](https://en.wikipedia.org/wiki/Web_Mercator_projection),
ce qui facilite le découpage de la carte en tuiles :
```sql theme={null}
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 :
```sql theme={null}
ORDER BY mortonEncode(mercator_x, mercator_y);
```
Deux index `minmax` accélèrent encore le filtrage spatial :
```sql theme={null}
INDEX idx_x mercator_x TYPE minmax,
INDEX idx_y mercator_y TYPE minmax;
```
Chargez la version actuelle d’OS Places dans la table :
Cette requête lit et stocke plus de 100 millions de lignes. Son exécution peut prendre beaucoup de temps,
consommer de l’espace de stockage et entraîner des coûts d’utilisation dans ClickHouse Cloud. La relancer ajoute les
mêmes données ; assurez-vous donc que `foursquare_mercator` est vide avant de réessayer l’importation.
```sql title="Query" theme={null}
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
Le modèle d'accès de Foursquare a changé depuis la création de ces visualisations. La
[vue interactive originale de Places](https://adsb.exposed/?dataset=Places\&zoom=5\&lat=52.3488\&lng=4.9219)
date d'avant le modèle d'accès actuel et est fournie à titre de référence historique, mais elle peut ne plus
afficher les données Places. Les images ci-dessous sont conservées à titre d'exemples historiques.
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.