> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-revert-104359-revert-104251-parquet-single.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
> Learn how to explore and load the Foursquare OS Places geospatial dataset with ClickHouse.
# Foursquare OS Places dataset
export const Image = ({img, alt, size = "lg"}) => {
const normalizedSize = ["sm", "md", "lg"].includes(size) ? size : "lg";
return ;
};
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](https://places.foursquare.com/)
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](https://docs.foursquare.com/data-products/docs/access-fsq-os-places)
for details.
Before you begin
Before running the queries in this guide, you need:
* A [Foursquare Places Portal](https://places.foursquare.com/) 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
`` in the following query with your token:
```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;
```
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:
```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)
```
Explore the data
The sample row contains several null fields. Add filters to return a more complete row:
```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)
```
Use `DESCRIBE` to inspect the table schema:
```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)) │
└─────────────────────┴─────────────────────────────┘
```
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:
```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);
```
Several columns use the [`LowCardinality`](/reference/data-types/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](https://en.wikipedia.org/wiki/Web_Mercator_projection),
which makes it easier to segment the map into tiles:
```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())),
```
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:
```sql theme={null}
ORDER BY mortonEncode(mercator_x, mercator_y);
```
Two `minmax` indices further accelerate spatial filtering:
```sql theme={null}
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.
```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;
```
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](https://adsb.exposed/?dataset=Places\&zoom=5\&lat=52.3488\&lng=4.9219)
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.