Conjunto de datos de Foursquare OS Places

Aprenda a explorar y cargar el conjunto de datos geoespaciales Foursquare OS Places con ClickHouse.

Foursquare OS Places contiene más de 100 millones de puntos de interés (POI) comerciales, incluidos comercios, restaurantes, parques, zonas de juegos y monumentos. En esta guía, conectarás ClickHouse al catálogo Iceberg de Foursquare, explorarás el conjunto de datos y lo cargarás en una tabla optimizada para consultas geoespaciales.

El conjunto de datos está disponible a través del portal de Foursquare Places y se puede utilizar gratuitamente conforme a la licencia Apache 2.0.

Foursquare ha actualizado la forma de acceder a OS Places. Las versiones anteriores de esta guía consultaban archivos con una fecha específica en un bucket S3 público; ahora se accede mediante Places Portal y un catálogo Iceberg autenticado. Consulta la documentación de Foursquare sobre el acceso a OS Places para obtener más información.

​

Antes de empezar

Antes de ejecutar las consultas de esta guía, necesita lo siguiente:

• Una cuenta en el portal Foursquare Places
• Un token de acceso creado desde la pestaña Access Data del conjunto de datos OS Places

​

Conéctate al catálogo de Foursquare

Mantén privado tu token de acceso. Inicia el client de ClickHouse y sustituye <YOUR_ACCESS_TOKEN> por tu token en la siguiente consulta:

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;

La base de datos del catálogo es de solo lectura. La tabla places_os refleja la versión actualmente publicada por Foursquare’s, en lugar de una versión de Parquet fijada a una fecha, por lo que sus filas y su esquema pueden cambiar con el tiempo. Por tanto, las consultas sin una cláusula ORDER BY pueden devolver filas de muestra distintas de las respuestas mostradas en esta guía.

​

Verifique la conexión

Consulte una fila de la tabla 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)

​

Explorar los datos

La fila de muestra contiene varios campos nulos. Añada filtros para obtener una fila más completa:

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:                 [phone]
website:             https://locations.kfc.com/mi/belleville/2169-rawsonville-road
email:               [e-mail]
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)

Use DESCRIBE para inspeccionar el esquema de la tabla:

Query

DESCRIBE places.`datasets.places_os`;

Response

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

​

Cargue los datos en ClickHouse

Para almacenar los datos de forma persistente, cree una tabla en clickhouse-server o en ClickHouse Cloud.

Cree una tabla MergeTree con columnas codificadas mediante diccionario y coordenadas de Web Mercator materializadas:

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

Varias columnas utilizan el tipo de datos LowCardinality, que almacena valores repetidos mediante codificación de diccionario. Esta representación puede mejorar significativamente el rendimiento de las consultas SELECT.

Las dos columnas UInt32 MATERIALIZED, mercator_x y mercator_y, asignan la latitud y la longitud a la proyección Web Mercator, lo que facilita dividir el mapa en mosaicos:

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

Las expresiones calculan los siguientes valores.

mercator_x

Esta columna convierte un valor de longitud en una coordenada X de la proyección de Mercator:

• longitude + 180 desplaza el intervalo de longitud de [-180, 180] a [0, 360].
• Dividir entre 360 normaliza el valor a un intervalo entre 0 y 1.
• Multiplicar por 0xFFFFFFFF, el entero sin signo máximo de 32 bits, escala el valor normalizado a todo el intervalo de un entero de 32 bits.

mercator_y

Esta columna convierte un valor de latitud en una coordenada Y de la proyección de Mercator:

• latitude + 90 desplaza el intervalo de latitud de [-90, 90] a [0, 180].
• Dividir entre 360 y multiplicar por pi convierte el valor a radianes para las funciones trigonométricas.
• log(tan(...)) aplica la fórmula básica de la proyección de Mercator.
• Multiplicar por 0xFFFFFFFF escala el resultado a todo el intervalo de enteros de 32 bits.

Al especificar MATERIALIZED, ClickHouse calcula estos valores al insertar los datos, sin que los datos de origen tengan que contener las columnas.

La tabla se ordena mediante mortonEncode(mercator_x, mercator_y), que crea una curva de relleno espacial de orden Z y organiza los datos según su proximidad espacial:

ORDER BY mortonEncode(mercator_x, mercator_y);

Otros dos índices minmax aceleran el filtrado espacial:

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

Cargue la versión actual de OS Places en la tabla:

Esta consulta lee y almacena más de 100 millones de filas. Puede tardar bastante tiempo, consumir almacenamiento y generar costes de uso en ClickHouse Cloud. Si la ejecuta de nuevo, se añadirán los mismos datos, así que asegúrese de que foursquare_mercator esté vacía antes de reintentar la importación.

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;

Las listas explícitas de columnas de origen y destino evitan que los cambios en el orden de las columnas del catálogo desalineen los valores importados. La consulta excluye unresolved_flags porque no se necesita en la tabla local y filtra las filas sin coordenadas porque no pueden situarse en el mapa. Los demás valores anulables del origen permanecen como nulos en la tabla local.

​

Visualización de los datos

El modelo de acceso de Foursquare ha cambiado desde que se crearon estas visualizaciones. La vista interactiva original de Places es anterior al modelo de acceso actual y se enlaza como referencia histórica, pero es posible que ya no muestre datos de Places. Las imágenes siguientes se conservan como ejemplos históricos.

Durante un hackatón de la empresa, el cofundador y CTO de ClickHouse, Alexey Milovidov, utilizó ClickHouse para crear las siguientes visualizaciones a partir del conjunto de datos de Foursquare.