Foursquare OS Places dataset

Foursquare OS Places contains over 100 million commercial points of interest (POIs), including shops, restaurants, parks, playgrounds, and monuments. In this guide, you connect ClickHouse to Foursquare’s Iceberg catalog, explore the dataset, and load it into a table optimized for geospatial queries. The dataset is available through the Foursquare Places Portal and is free to use under the Apache 2.0 license.

Foursquare has updated how OS Places is accessed. Older versions of this guide queried date-pinned files in a public S3 bucket; access now uses the Places Portal and an authenticated Iceberg catalog. See Foursquare’s OS Places access documentation for details.

Before you begin

Before running the queries in this guide, you need:

A Foursquare Places Portal account
An access token created from the Access Data tab of the OS Places dataset

Connect to the Foursquare catalog

Keep your access token private. Start your ClickHouse client, then replace <YOUR_ACCESS_TOKEN> in the following query with your token:

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;

The catalog database is read-only. The places_os table reflects Foursquare’s current published release rather than a date-pinned Parquet release, so its rows and schema can change over time. Queries without an ORDER BY clause may therefore return different sample rows than the responses shown in this guide.

Verify the connection

Query one row from the places_os Iceberg table:

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)

Explore the data

The sample row contains several null fields. Add filters to return a more complete row:

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:                 (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)

Use DESCRIBE to inspect the table schema:

Query

DESCRIBE places.`datasets.places_os`;

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

Load the data into ClickHouse

To persist the data, create a table on clickhouse-server or ClickHouse Cloud. Create a MergeTree table with dictionary-encoded columns and materialized Web Mercator coordinates:

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

Several columns use the LowCardinality data type, which stores repeated values with dictionary encoding. This representation can significantly improve SELECT query performance. The two UInt32 MATERIALIZED columns, mercator_x and mercator_y, map latitude and longitude to the Web Mercator projection, which makes it easier to segment the map into tiles:

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

The expressions calculate the following values. mercator_x This column converts a longitude value into an X coordinate in the Mercator projection:

longitude + 180 shifts the longitude range from [-180, 180] to [0, 360].
Dividing by 360 normalizes the value to a range between 0 and 1.
Multiplying by 0xFFFFFFFF, the maximum 32-bit unsigned integer, scales the normalized value to the full range of a 32-bit integer.

mercator_y This column converts a latitude value into a Y coordinate in the Mercator projection:

latitude + 90 shifts the latitude range from [-90, 90] to [0, 180].
Dividing by 360 and multiplying by pi converts the value to radians for the trigonometric functions.
log(tan(...)) applies the core Mercator projection formula.
Multiplying by 0xFFFFFFFF scales the result to the full 32-bit integer range.

Specifying MATERIALIZED makes ClickHouse calculate these values when data is inserted, without requiring the source data to contain the columns. The table is ordered by mortonEncode(mercator_x, mercator_y), which creates a Z-order space-filling curve and organizes data by spatial proximity:

ORDER BY mortonEncode(mercator_x, mercator_y);

Two minmax indices further accelerate spatial filtering:

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

Load the current OS Places release into the table:

This query reads and stores more than 100 million rows. It can take significant time, consume storage, and incur usage costs in ClickHouse Cloud. Running it again appends the same data, so ensure that foursquare_mercator is empty before retrying the import.

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;

The explicit source and destination column lists prevent changes to the catalog’s column order from misaligning imported values. The query excludes unresolved_flags because it is not needed by the local table and filters out rows without coordinates because they cannot be placed on the map. Other nullable source values remain null in the local table.

Visualize the data

Foursquare’s access model has changed since these visualizations were created. The original interactive Places view predates the current access model and is linked for historical reference, but it may no longer display Places data. The images below are retained as historical examples.

During a company hackathon, ClickHouse co-founder and CTO Alexey Milovidov used ClickHouse to create the following visualizations from the Foursquare dataset.

Get started

Setup

Sample datasets

Migrate

Before you begin

Connect to the Foursquare catalog

Verify the connection

Explore the data

Load the data into ClickHouse

Visualize the data

​Before you begin

​Connect to the Foursquare catalog

​Verify the connection

​Explore the data

​Load the data into ClickHouse

​Visualize the data

Before you begin

Connect to the Foursquare catalog

Verify the connection

Explore the data

Load the data into ClickHouse

Visualize the data