Lugares de Foursquare

Conjunto de datos con más de 100 millones de registros que contiene información sobre lugares del mapa, como tiendas, restaurantes, parques, zonas de juegos infantiles y monumentos.

​

Conjunto de datos

Este conjunto de datos de Foursquare está disponible para descargar y puede usarse gratuitamente bajo la licencia Apache 2.0.

Contiene más de 100 millones de registros de puntos de interés (POI) comerciales, como tiendas, restaurantes, parques, zonas de juegos y monumentos. También incluye metadatos adicionales sobre estos lugares, como categorías e información de redes sociales.

​

Exploración de datos

Para explorar los datos usaremos clickhouse-local, una pequeña herramienta de línea de comandos que ofrece todo el motor de ClickHouse, aunque también puede usar ClickHouse Cloud, clickhouse-client o incluso chDB.

Ejecute la siguiente consulta para seleccionar los datos del bucket de S3 donde están almacenados:

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)

Vemos que bastantes campos tienen ᴺᵁᴸᴸ, así que podemos añadir algunas condiciones más a nuestra consulta para obtener datos más útiles:

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)

Ejecute la siguiente consulta para ver el esquema inferido automáticamente de los datos con DESCRIBE:

Query

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

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

​

Carga de los datos en ClickHouse

Si quieres almacenar los datos de forma persistente en disco, puedes usar clickhouse-server o ClickHouse Cloud.

Para crear la tabla, ejecuta el siguiente comando:

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)

Tenga en cuenta el uso del tipo de datos LowCardinality para varias columnas, lo que cambia la representación interna de los datos para codificarlos mediante diccionario. Trabajar con datos codificados mediante diccionario aumenta significativamente el rendimiento de las consultas SELECT en muchas aplicaciones.

Además, se crean dos columnas UInt32 MATERIALIZED, mercator_x y mercator_y, que convierten las coordenadas de latitud/longitud a la proyección Web Mercator para segmentar más fácilmente el mapa en teselas:

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

Vamos a desglosar lo que ocurre arriba en cada columna.

mercator_x

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

• longitude + 180 desplaza el rango de longitudes de [-180, 180] a [0, 360]
• Dividir entre 360 lo normaliza a un valor entre 0 y 1
• Multiplicar por 0xFFFFFFFF (hexadecimal del valor máximo de un entero sin signo de 32 bits) escala este valor normalizado al rango completo de un entero de 32 bits

mercator_y

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

• latitude + 90 desplaza la latitud de [-90, 90] a [0, 180]
• Dividir entre 360 y multiplicar por pi() lo convierte a radianes para las funciones trigonométricas
• La parte log(tan(...)) es el núcleo de la fórmula de proyección de Mercator
• Multiplicar por 0xFFFFFFFF lo escala al rango completo de enteros de 32 bits

Especificar MATERIALIZED garantiza que ClickHouse calcule los valores de estas columnas cuando hacemos INSERT de los datos, sin tener que especificar estas columnas (que no forman parte del esquema de datos original) en la sentencia `INSERT.

La tabla se ordena por mortonEncode(mercator_x, mercator_y), lo que genera una curva de llenado espacial en orden Z de mercator_x, mercator_y para mejorar significativamente el rendimiento de las consultas geoespaciales. Esta ordenación por curva en Z garantiza que los datos queden organizados físicamente según su proximidad espacial:

ORDER BY mortonEncode(mercator_x, mercator_y)

También se crean dos índices minmax para acelerar la búsqueda:

INDEX idx_x mercator_x TYPE minmax,
INDEX idx_y mercator_y TYPE minmax

Como puede ver, ClickHouse tiene absolutamente todo lo que necesita para aplicaciones de mapas en tiempo real.

Ejecute la siguiente consulta para cargar los datos:

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

​

Visualización de los datos

Para ver lo que se puede hacer con este conjunto de datos, eche un vistazo a adsb.exposed. adsb.exposed fue creado originalmente por el cofundador y CTO Alexey Milovidov para visualizar datos de vuelos ADS-B (Automatic Dependent Surveillance-Broadcast), que son 1000 veces mayores. Durante un hackatón de la empresa, Alexey añadió los datos de Foursquare a la herramienta.

A continuación, presentamos algunas de nuestras visualizaciones favoritas.