Documentation
SupportLog In

Access FSQ OS Places

Suggest Edits

With Foursquare’s Open Source Places, you can access free data to accelerate geospatial innovation and insights. View the Places OS Data Schemas for a full list of available attributes.

Prerequisites

In order to access Foursquare's Open Source Places data, you will need the following:

  • S3 paths:
    • Places - s3://fsq-os-places-us-east-1/release/dt=2024-12-03/places/parquet
    • Categories - s3://fsq-os-places-us-east-1/release/dt=2024-12-03/categories/parquet
  • AWS CLI or SDK’s to download the parquet files and use in your local environment
  • If using DuckDb, please refer to the Using DuckDb section for instructions on how to create remote/local tables for use.

ℹ️

Additional Data Offerings

You can also access our OS Places data in the following locations:

Example Queries

The following are examples on how to query FSQ Open Source Places using Athena and Spark:

Filter by Parent Level Category

WITH places_exploded_categories AS (
  -- Unnest categories array
  SELECT fsq_place_id,
         cid as fsq_category_id
  FROM places p
  CROSS JOIN UNNEST(p.fsq_category_ids) AS t(cid) 
),
 distinct_places AS (
  SELECT 
       DISTINCT(fsq_place_id) -- Get distinct ids to reduce duplicates from cross join
  FROM places_exploded_categories p
  JOIN categories c -- Join to categories to filter on Level 2 Category
  ON p.fsq_category_id = c.category_id 
  WHERE c.level2_category_id = '4d4b7105d754a06374d81259' -- Restaurants
)
SELECT * FROM places
WHERE fsq_place_id IN (SELECT fsq_place_id FROM distinct_places)


WITH places_exploded_categories AS (
  -- Unnest categories array
  SELECT fsq_place_id, 
         name, 
         explode(fsq_category_ids) as fsq_category_id 
  FROM places
),
 distinct_places AS (
  SELECT 
	DISTINCT(fsq_place_id) -- Get distinct ids to reduce duplicates from explode function
  FROM places_exploded_categories p 
  JOIN categories c -- Join to categories to filter on Level 2 Category
  ON p.fsq_category_id = c.category_id 
  WHERE c.level2_category_id = '4d4b7105d754a06374d81259' -- Restaurants
)
SELECT * FROM places
WHERE fsq_place_id IN (SELECT fsq_place_id FROM distinct_places)

WITH places_exploded_categories AS (
    -- Unnest categories array using UNNEST with ordinality
    SELECT fsq_place_id,
           UNNEST(fsq_category_ids) as fsq_category_id
    FROM places
  ),
  distinct_places AS (
    SELECT 
      DISTINCT(fsq_place_id)
    FROM places_exploded_categories p
    JOIN categories c
    ON p.fsq_category_id = c.category_id 
    WHERE c.level2_category_id = '4d4b7105d754a06374d81259'
  )
  SELECT * FROM places
  WHERE fsq_place_id IN (SELECT fsq_place_id FROM distinct_places)

Filter out Non-Commercial Categories

SELECT * FROM places 
WHERE arrays_overlap(fsq_category_ids, ARRAY['4bf58dd8d48988d1f0931735',	-- Airport Gate 
'62d587aeda6648532de2b88c',	-- Beer Festival 
'4bf58dd8d48988d12b951735',	-- Bus Line 
'52f2ab2ebcbc57f1066b8b3b',	-- Christmas Market 
'50aa9e094b90af0d42d5de0d',	-- City 
'5267e4d9e4b0ec79466e48c6',	-- Conference 
'5267e4d9e4b0ec79466e48c9',	-- Convention 
'530e33ccbcbc57f1066bbff7',	-- Country 
'5345731ebcbc57f1066c39b2',	-- County 
'63be6904847c3692a84b9bb7',	-- Entertainment Event 
'4d4b7105d754a06373d81259',	-- Event 
'5267e4d9e4b0ec79466e48c7',	-- Festival 
'4bf58dd8d48988d132951735',	-- Hotel Pool 
'52f2ab2ebcbc57f1066b8b4c',	-- Intersection 
'50aaa4314b90af0d42d5de10',	-- Island 
'58daa1558bbb0b01f18ec1fa',	-- Line 
'63be6904847c3692a84b9bb8',	-- Marketplace 
'4f2a23984b9023bd5841ed2c',	-- Moving Target 
'5267e4d9e4b0ec79466e48d1',	-- Music Festival 
'4f2a25ac4b909258e854f55f',	-- Neighborhood 
'5267e4d9e4b0ec79466e48c8',	-- Other Event 
'52741d85e4b0d5d1e3c6a6d9',	-- Parade 
'4bf58dd8d48988d1f7931735',	-- Plane 
'4f4531504b9074f6e4fb0102',	-- Platform 
'4cae28ecbf23941eb1190695',	-- Polling Place 
'4bf58dd8d48988d1f9931735',	-- Road 
'5bae9231bedf3950379f89c5',	-- Sporting Event 
'530e33ccbcbc57f1066bbff8',	-- State 
'530e33ccbcbc57f1066bbfe4',	-- States and Municipalities 
'52f2ab2ebcbc57f1066b8b54',	-- Stoop Sale 
'5267e4d8e4b0ec79466e48c5',	-- Street Fair 
'53e0feef498e5aac066fd8a9',	-- Street Food Gathering 
'4bf58dd8d48988d130951735',	-- Taxi 
'530e33ccbcbc57f1066bbff3',	-- Town 
'5bae9231bedf3950379f89c3',	-- Trade Fair 
'4bf58dd8d48988d12a951735',	-- Train 
'52e81612bcbc57f1066b7a24',	-- Tree 
'530e33ccbcbc57f1066bbff9',	-- Village
]) = false

SELECT * FROM places
WHERE arrays_overlap(fsq_category_ids, array('4bf58dd8d48988d1f0931735',	-- Airport Gate 
'62d587aeda6648532de2b88c',	-- Beer Festival 
'4bf58dd8d48988d12b951735',	-- Bus Line 
'52f2ab2ebcbc57f1066b8b3b',	-- Christmas Market 
'50aa9e094b90af0d42d5de0d',	-- City 
'5267e4d9e4b0ec79466e48c6',	-- Conference 
'5267e4d9e4b0ec79466e48c9',	-- Convention 
'530e33ccbcbc57f1066bbff7',	-- Country 
'5345731ebcbc57f1066c39b2',	-- County 
'63be6904847c3692a84b9bb7',	-- Entertainment Event 
'4d4b7105d754a06373d81259',	-- Event 
'5267e4d9e4b0ec79466e48c7',	-- Festival 
'4bf58dd8d48988d132951735',	-- Hotel Pool 
'52f2ab2ebcbc57f1066b8b4c',	-- Intersection 
'50aaa4314b90af0d42d5de10',	-- Island 
'58daa1558bbb0b01f18ec1fa',	-- Line 
'63be6904847c3692a84b9bb8',	-- Marketplace 
'4f2a23984b9023bd5841ed2c',	-- Moving Target 
'5267e4d9e4b0ec79466e48d1',	-- Music Festival 
'4f2a25ac4b909258e854f55f',	-- Neighborhood 
'5267e4d9e4b0ec79466e48c8',	-- Other Event 
'52741d85e4b0d5d1e3c6a6d9',	-- Parade 
'4bf58dd8d48988d1f7931735',	-- Plane 
'4f4531504b9074f6e4fb0102',	-- Platform 
'4cae28ecbf23941eb1190695',	-- Polling Place 
'4bf58dd8d48988d1f9931735',	-- Road 
'5bae9231bedf3950379f89c5',	-- Sporting Event 
'530e33ccbcbc57f1066bbff8',	-- State 
'530e33ccbcbc57f1066bbfe4',	-- States and Municipalities 
'52f2ab2ebcbc57f1066b8b54',	-- Stoop Sale 
'5267e4d8e4b0ec79466e48c5',	-- Street Fair 
'53e0feef498e5aac066fd8a9',	-- Street Food Gathering 
'4bf58dd8d48988d130951735',	-- Taxi 
'530e33ccbcbc57f1066bbff3',	-- Town 
'5bae9231bedf3950379f89c3',	-- Trade Fair 
'4bf58dd8d48988d12a951735',	-- Train 
'52e81612bcbc57f1066b7a24',	-- Tree 
'530e33ccbcbc57f1066bbff9',	-- Village
)) = false

SELECT * 
FROM places
WHERE array_length(array_intersect(fsq_category_ids, array_value(
'4bf58dd8d48988d1f0931735',    -- Airport Gate 
'62d587aeda6648532de2b88c',   -- Beer Festival 
'4bf58dd8d48988d12b951735',    -- Bus Line 
'52f2ab2ebcbc57f1066b8b3b',    -- Christmas Market 
'50aa9e094b90af0d42d5de0d',    -- City 
'5267e4d9e4b0ec79466e48c6',    -- Conference 
'5267e4d9e4b0ec79466e48c9',    -- Convention 
'530e33ccbcbc57f1066bbff7',    -- Country 
'5345731ebcbc57f1066c39b2',    -- County 
'63be6904847c3692a84b9bb7',    -- Entertainment Event 
'4d4b7105d754a06373d81259',    -- Event 
'5267e4d9e4b0ec79466e48c7',    -- Festival 
'4bf58dd8d48988d132951735',    -- Hotel Pool 
'52f2ab2ebcbc57f1066b8b4c',    -- Intersection 
'50aaa4314b90af0d42d5de10',    -- Island 
'58daa1558bbb0b01f18ec1fa',    -- Line 
'63be6904847c3692a84b9bb8',    -- Marketplace 
'4f2a23984b9023bd5841ed2c',    -- Moving Target 
'5267e4d9e4b0ec79466e48d1',    -- Music Festival 
'4f2a25ac4b909258e854f55f',    -- Neighborhood 
'5267e4d9e4b0ec79466e48c8',    -- Other Event 
'52741d85e4b0d5d1e3c6a6d9',    -- Parade 
'4bf58dd8d48988d1f7931735',    -- Plane 
'4f4531504b9074f6e4fb0102',    -- Platform 
'4cae28ecbf23941eb1190695',    -- Polling Place 
'4bf58dd8d48988d1f9931735',    -- Road 
'5bae9231bedf3950379f89c5',    -- Sporting Event 
'530e33ccbcbc57f1066bbff8',    -- State 
'530e33ccbcbc57f1066bbfe4',    -- States and Municipalities 
'52f2ab2ebcbc57f1066b8b54',    -- Stoop Sale 
'5267e4d8e4b0ec79466e48c5',    -- Street Fair 
'53e0feef498e5aac066fd8a9',    -- Street Food Gathering 
'4bf58dd8d48988d130951735',    -- Taxi 
'530e33ccbcbc57f1066bbff3',    -- Town 
'5bae9231bedf3950379f89c3',    -- Trade Fair 
'4bf58dd8d48988d12a951735',    -- Train 
'52e81612bcbc57f1066b7a24',    -- Tree 
'530e33ccbcbc57f1066bbff9'    -- Village
))) = 0

Find Open and Recently Active POI

SELECT * FROM places p 
WHERE p.date_closed IS NULL
    AND DATE(p.date_refreshed) >= DATE_ADD('day', -365, current_date);

SELECT * FROM places p
WHERE p.date_closed IS NULL
    AND p.date_refreshed >= DATE_SUB(current_date(), 365);

SELECT * 
FROM places p 
WHERE p.date_closed IS NULL 
  AND CAST(p.date_refreshed AS DATE) >= CURRENT_DATE - INTERVAL '365 days';

Appendix

Using DuckDB

Create Remote Tables

CREATE TABLE places AS  
  SELECT _ FROM read_parquet( 's3://fsq-os-places-us-east-1/release/dt=2024-12-03/places/parquet-_.zstd.parquet');

create TABLE categories as  
  SELECT _ FROM read_parquet( 's3://fsq-os-places-us-east-1/release/dt=2024-12-03/categories/parquet/_.zstd.parquet');

Create Local Tables

NOTE: Downloading the data makes it much faster than doing it from S3.

Download files to your laptop
$ aws s3 cp --no-sign s3://fsq-os-places-us-east-1/release/dt=2024-12-03/places/parquet . --recursive

Create tables

CREATE TABLE places AS  
‣   SELECT _ FROM read_parquet( '/Users/{username}/fsq-os-places/places/places-_.zstd.parquet');

create TABLE categories as  
·   SELECT _ FROM read_parquet( '/Users/{username}/fsq-os-places/categories/_.zstd.parquet');

Non-Commercial Categories Table

Category NameCategory ID
Airport Gate4bf58dd8d48988d1f0931735
Beer Festival62d587aeda6648532de2b88c
Bus Line4bf58dd8d48988d12b951735
Christmas Market52f2ab2ebcbc57f1066b8b3b
City50aa9e094b90af0d42d5de0d
Conference5267e4d9e4b0ec79466e48c6
Convention5267e4d9e4b0ec79466e48c9
Country530e33ccbcbc57f1066bbff7
County5345731ebcbc57f1066c39b2
Entertainment Event63be6904847c3692a84b9bb7
Event4d4b7105d754a06373d81259
Festival5267e4d9e4b0ec79466e48c7
Hotel Pool4bf58dd8d48988d132951735
Intersection52f2ab2ebcbc57f1066b8b4c
Island50aaa4314b90af0d42d5de10
Line58daa1558bbb0b01f18ec1fa
Marketplace63be6904847c3692a84b9bb8
Moving Target4f2a23984b9023bd5841ed2c
Music Festival5267e4d9e4b0ec79466e48d1
Neighborhood4f2a25ac4b909258e854f55f
Other Event5267e4d9e4b0ec79466e48c8
Parade52741d85e4b0d5d1e3c6a6d9
Plane4bf58dd8d48988d1f7931735
Platform4f4531504b9074f6e4fb0102
Polling Place4cae28ecbf23941eb1190695
Road4bf58dd8d48988d1f9931735
State530e33ccbcbc57f1066bbff8
States and Municipalities530e33ccbcbc57f1066bbfe4
Stopp Sale52f2ab2ebcbc57f1066b8b54
Street Fair5267e4d8e4b0ec79466e48c5
Street Food Gathering53e0feef498e5aac066fd8a9
Taxi4bf58dd8d48988d130951735
Town530e33ccbcbc57f1066bbff3
Trade Fair5bae9231bedf3950379f89c3
Train4bf58dd8d48988d12a951735
Tree52e81612bcbc57f1066b7a24
Village530e33ccbcbc57f1066bbff9

ℹ️

Apache 2.0

Copyright 2024 Foursquare Labs, Inc. All rights reserved.
Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License.
You may obtain a copy of the License at:http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and limitations under the License.

Updated 14 days ago