[공간자료] Foursquare 오픈소스 장소 데이터

https://docs.foursquare.com/data-products/docs/access-fsq-os-places

Access FSQ OS Places

 

 

Foursquare의 Open Source Places 데이터 접근

Foursquare의 Open Source Places를 통해 무료 데이터를 활용하여 지리 공간 혁신과 통찰을 가속화할 수 있습니다. 제공되는 속성 목록은 Places OS Data Schemas에서 확인하세요.

---

필수 조건

Foursquare의 Open Source Places 데이터에 접근하려면 다음이 필요합니다:

1. S3 경로:
   • 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
2. AWS CLI 또는 SDK:
   • Parquet 파일을 다운로드하여 로컬 환경에서 사용할 수 있어야 합니다.
3. DuckDB 사용 시:
   • 원격/로컬 테이블을 생성하는 방법은 "Using DuckDB" 섹션을 참조하세요.

---

추가 데이터 제공

OS Places 데이터는 다음 위치에서도 접근 가능합니다:

• HuggingFace
• Snowflake

---

쿼리 예제

다음은 Athena와 Spark를 사용해 FSQ Open Source Places 데이터를 쿼리하는 예제입니다.

---

1. 상위 레벨 카테고리 필터링

• Presto/Athena:
• SparkSQL:
• DuckDB:

WITH places_exploded_categories AS (
  -- 카테고리 배열을 펼침
  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) -- 중복 제거
  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);

---

2. 비상업 카테고리 제외

• Presto/Athena:
• SparkSQL:
• DuckDB:

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

---

3. 개방된 최근 활성 POI 필터링

• Presto/Athena:
• SparkSQL:
• DuckDB:

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

---

Appendix

DuckDB 사용

1. 원격 테이블 생성
   • Places Table:

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

2. 로컬 테이블 생성
   • 파일 다운로드:
   • $ aws s3 cp --no-sign s3://fsq-os-places-us-east-1/release/dt=2024-12-03/places/parquet . --recursive
   • 테이블 생성:
   • CREATE TABLE places AS SELECT * FROM read_parquet('/Users/{username}/fsq-os-places/places/places-_.zstd.parquet');

---

비상업 카테고리 테이블

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 라이선스에 따라 사용 가능합니다.
라이선스 조건 하에서만 사용 가능하며, 허가 없이 데이터 사용은 금지됩니다.
보다 자세한 정보는 라이선스를 참조하세요.