Foursquare OS Places 데이터셋
Foursquare OS Places 데이터셋
상점, 식당, 공원, 놀이터, 기념물 등 1억 개가 넘는 상업적 관심 지점(POI)을 담은 데이터셋입니다. ClickHouse를 Foursquare의 Iceberg 카탈로그에 연결해 데이터를 탐색하고, 지리공간(geospatial) 쿼리에 최적화된 테이블로 로드하는 과정을 보여줍니다.
출처: 문서
본문
Foursquare OS Places에는 상점, 식당, 공원, 놀이터, 기념물 등 1억 개가 넘는 상업적 관심 지점(POI)이 포함됩니다. 이 가이드에서는 ClickHouse를 Foursquare의 Iceberg 카탈로그에 연결하고, 데이터셋을 탐색한 뒤, 지리공간 쿼리에 최적화된 테이블로 로드합니다.
이 데이터셋은 Foursquare Places Portal을 통해 제공되며 Apache 2.0 라이선스 하에 무료로 사용할 수 있습니다.
Foursquare는 OS Places 접근 방식을 업데이트했습니다. 이 가이드의 구버전은 공용 S3 버킷의 날짜 고정(date-pinned) 파일을 조회했지만, 이제 Places Portal과 인증된 Iceberg 카탈로그를 통해 접근합니다. 자세한 내용은 Foursquare의 OS Places 접근 문서를 참고하세요.
시작하기 전에 (Before you begin)
이 가이드의 쿼리를 실행하기 전에 다음이 필요합니다:
- Foursquare Places Portal 계정
- OS Places 데이터셋의 Access Data 탭에서 생성한 액세스 토큰
Foursquare 카탈로그에 연결 (Connect to the Foursquare catalog)
액세스 토큰은 비밀로 유지하세요. ClickHouse 클라이언트를 시작한 뒤, 다음 쿼리의 <YOUR_ACCESS_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_...KEN>',
vended_credentials = 1;
카탈로그 데이터베이스는 읽기 전용입니다. places_os 테이블은 날짜 고정 Parquet 릴리스가 아니라 Foursquare의 현재 공개 릴리스를 반영하므로, 행과 스키마가 시간에 따라 변할 수 있습니다. 따라서 ORDER BY 절이 없는 쿼리는 이 가이드에 표시된 응답과 다른 샘플 행을 반환할 수 있습니다.
연결 확인 (Verify the connection)
places_os Iceberg 테이블에서 한 행을 조회합니다:
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)
샘플 행에는 여러 null 필드가 있습니다. 필터를 추가해 더 완전한 행을 반환해 보세요:
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: [email protected]
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)
DESCRIBE로 테이블 스키마를 확인합니다:
Query
DESCRIBE places.`datasets.places_os`;
Response
┌─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)) │
└─────────────────────┴─────────────────────────────┘
데이터를 ClickHouse로 로드 (Load the data into ClickHouse)
데이터를 영속화하려면 clickhouse-server 또는 ClickHouse Cloud에 테이블을 생성하세요.
딕셔너리 인코딩 컬럼과 실체화된(MATERIALIZED) Web Mercator 좌표를 가진 MergeTree 테이블을 만듭니다:
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);
여러 컬럼이 LowCardinality 데이터 타입을 사용하는데, 이는 반복되는 값을 딕셔너리 인코딩으로 저장합니다. 이 표현은 SELECT 쿼리 성능을 크게 향상시킬 수 있습니다.
두 개의 UInt32 MATERIALIZED 컬럼인 mercator_x와 mercator_y는 위도·경도를 Web Mercator 투영으로 매핑해 지도를 타일로 분할하기 쉽게 합니다:
mercator_x UInt32 MATERIALIZED 0xFFFFFFFF * ((longitude + 180) / 360),
mercator_y UInt32 MATERIALIZED 0xFFFFFFFF * ((1 / 2) - ((log(tan(((latitude + 90) / 360) * pi())) / 2) / pi())),
이 표현식들은 다음 값을 계산합니다.
mercator_x
이 컬럼은 경도를 Mercator 투영의 X 좌표로 변환합니다:
longitude + 180은 경도 범위를 [-180, 180]에서 [0, 360]으로 이동합니다.- 360으로 나누면 값을 0과 1 사이의 범위로 정규화합니다.
- 32비트 부호 없는 정수의 최댓값인
0xFFFFFFFF를 곱하면 정규화된 값을 32비트 정수의 전체 범위로 확장합니다.
mercator_y
이 컬럼은 위도를 Mercator 투영의 Y 좌표로 변환합니다:
latitude + 90은 위도 범위를 [-90, 90]에서 [0, 180]으로 이동합니다.- 360으로 나누고
pi를 곱하면 삼각 함수를 위해 값을 라디안으로 변환합니다. log(tan(...))는 핵심 Mercator 투영 공식을 적용합니다.0xFFFFFFFF를 곱하면 결과를 전체 32비트 정수 범위로 확장합니다.
MATERIALIZED를 지정하면 원본 데이터에 이 컬럼들이 없어도 ClickHouse가 데이터 삽입 시 이 값을 계산합니다.
테이블은 mortonEncode(mercator_x, mercator_y)로 정렬되는데, 이는 Z-order 공간 채움 곡선을 만들어 공간적 근접성에 따라 데이터를 구성합니다:
ORDER BY mortonEncode(mercator_x, mercator_y);
두 개의 minmax 인덱스가 공간 필터링을 더욱 가속화합니다:
INDEX idx_x mercator_x TYPE minmax,
INDEX idx_y mercator_y TYPE minmax;
현재 OS Places 릴리스를 테이블에 로드합니다:
이 쿼리는 1억 개가 넘는 행을 읽고 저장합니다. 상당한 시간이 걸리고 스토리지를 소비하며 ClickHouse Cloud에서 사용 비용이 발생할 수 있습니다. 다시 실행하면 같은 데이터가 추가되므로, 재시도 전에 foursquare_mercator가 비어 있는지 확인하세요.
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;
명시적인 원본·대상 컬럼 목록은 카탈로그의 컬럼 순서가 바뀌어도 임포트되는 값이 어긋나지 않게 합니다. 쿼리는 로컬 테이블에 필요하지 않은 unresolved_flags를 제외하고, 지도에 배치할 수 없는 좌표 없는 행도 걸러냅니다. 다른 nullable 원본 값들은 로컬 테이블에서 null로 유지됩니다.
데이터 시각화 (Visualize the data)
Foursquare의 접근 모델은 이 시각화들이 만들어진 이후 변경되었습니다. 원본 인터랙티브 Places 뷰는 현재 접근 모델 이전의 것이며 역사적 참고용으로 연결되지만, Places 데이터를 더 이상 표시하지 못할 수 있습니다. 아래 이미지들은 역사적 예시로 보존됩니다.
회사 해커톤에서 ClickHouse 공동 창업자이자 CTO인 Alexey Milovidov가 ClickHouse로 Foursquare 데이터셋에서 다음 시각화들을 만들었습니다.