Access FSQ OS Places
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-11-19/places/parquet/
- Categories -
s3://fsq-os-places-us-east-1/release/dt=2024-11-19/categories/parquet/
- Places -
- AWS CLI or SDK’s to download the parquet files and use in your local environment
Example Queries
The following are examples on how to query FSQ Open Source Places using Athena and Spark:
- Filter Categories by the parent level
- Filter out non-commercial venues
- Find open and recently active POI
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)
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
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);
Appendix
Non-Commercial Categories Table
Category Name | Category ID |
---|---|
Airport Gate | 4bf58dd8d48988d1f0931735 |
Beer Festival | 62d587aeda6648532de2b88c |
Bus Line | 4bf58dd8d48988d12b951735 |
Christmas Market | 52f2ab2ebcbc57f1066b8b3b |
City | 50aa9e094b90af0d42d5de0d |
Conference | 5267e4d9e4b0ec79466e48c6 |
Convention | 5267e4d9e4b0ec79466e48c9 |
Country | 530e33ccbcbc57f1066bbff7 |
County | 5345731ebcbc57f1066c39b2 |
Entertainment Event | 63be6904847c3692a84b9bb7 |
Event | 4d4b7105d754a06373d81259 |
Festival | 5267e4d9e4b0ec79466e48c7 |
Hotel Pool | 4bf58dd8d48988d132951735 |
Intersection | 52f2ab2ebcbc57f1066b8b4c |
Island | 50aaa4314b90af0d42d5de10 |
Line | 58daa1558bbb0b01f18ec1fa |
Marketplace | 63be6904847c3692a84b9bb8 |
Moving Target | 4f2a23984b9023bd5841ed2c |
Music Festival | 5267e4d9e4b0ec79466e48d1 |
Neighborhood | 4f2a25ac4b909258e854f55f |
Other Event | 5267e4d9e4b0ec79466e48c8 |
Parade | 52741d85e4b0d5d1e3c6a6d9 |
Plane | 4bf58dd8d48988d1f7931735 |
Platform | 4f4531504b9074f6e4fb0102 |
Polling Place | 4cae28ecbf23941eb1190695 |
Road | 4bf58dd8d48988d1f9931735 |
State | 530e33ccbcbc57f1066bbff8 |
States and Municipalities | 530e33ccbcbc57f1066bbfe4 |
Stopp Sale | 52f2ab2ebcbc57f1066b8b54 |
Street Fair | 5267e4d8e4b0ec79466e48c5 |
Street Food Gathering | 53e0feef498e5aac066fd8a9 |
Taxi | 4bf58dd8d48988d130951735 |
Town | 530e33ccbcbc57f1066bbff3 |
Trade Fair | 5bae9231bedf3950379f89c3 |
Train | 4bf58dd8d48988d12a951735 |
Tree | 52e81612bcbc57f1066b7a24 |
Village | 530e33ccbcbc57f1066bbff9 |
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 about 2 hours ago