Big Query Geospatial Data

구글 빅쿼리에서 지리정보 데이터를 다루는 방법에 대해 알아본다. GEOGRAPHY라는 타입의 데이터를 사용해서 좌표계의 점, 선, 면등을 다룬다.

지구상의 한 점은 위도(latitude)와 경도(longtitude)로 표현할 수 있다. 위도는 북쪽으로부터의 각도이고, 경도는 동쪽으로부터의 각도이다. 위도와 경도는 각각 -90도에서 90도 사이의 값을 가진다. 경도는 -180도에서 180도 사이의 값을 가진다.

예를 들어 미국의 자유 여신상의 좌표는 (40.69046171976406, -74.04444975893499)으로 표현된다. ~~우리나라는 지도 데이터 반출 이슈 때문인지 좌표 조회가 안된다.~~

GeoJSON¹은 지리 정보데이터를 저장하는 방법 중에 하나며, JSON 형식으로 표현된다. GeoJSON은 지리 정보데이터를 표현하는데 필요한 기본적인 요소들을 정의하고 있다. 여기 뿐만 아니라 다른 곳에서도 많이 쓰이기 때문에 알아 두는 것이 좋다.

{ "type": "Point", "coordinates": [-121,41] }

예제1: 허리케인 경로 표시¶

Big Query에서 제공하는 공개 데이터세트 Global Hurricane Tracks (IBTrACS)로 허리케인의 경로를 표시해보자. 이 데이터세트는 1851년부터 2019년까지의 허리케인의 경로를 저장하고 있다. 튜토리얼은 여기를 참고했다.

Big Query 탐색기에서 +추가를 누르고 공개 데이터 세트에서 Global Hurricane Tracks를 검색하여 데이터를 추가한다.

공개 데이터 세트 noaa_hurricanes의 hurricanes 테이블을 선택하고 스키마를 눌러 데이터의 타입을 확인한다. 그리고 쿼리를 눌러서 아래의 쿼리를 실행한다.

-- # SELECT 절은 폭풍의 모든 날씨 데이터를 선택하고 ST_GeogPoint 함수를 사용하여 latitude 및 longitude 열의 값을 GEOGRAPHY 유형(점)으로 변환합니다.
SELECT
    ST_GeogPoint(longitude, latitude) AS point,  -- 위도, 경도
    name, -- 허리케인 이름
    iso_time,  -- 시간
    dist2land,  --육지와의 거리
    usa_wind,  -- 최대 지속 풍속(노트): 0 - 300 kts.
    usa_pressure,  -- 최소 중심 기압(밀리바)
    usa_sshs,  -- Saffir-Simpson Hurricane Wind Scale: -5 ~ 5
    (usa_r34_ne + usa_r34_nw + usa_r34_se + usa_r34_sw)/4 AS radius_34kt, -- 34kt 속도 평균 반경
    (usa_r50_ne + usa_r50_nw + usa_r50_se + usa_r50_sw)/4 AS radius_50kt -- 50kt 속도 평균 반경
FROM
    `bigquery-public-data.noaa_hurricanes.hurricanes`
-- WHERE 절은 2017년 허리케인 시즌의 허리케인 마리아를 따라 이 데이터를 대서양의 점으로 필터링합니다.
WHERE
    name LIKE '%MARIA%'  -- 마리아
    AND season = '2017'  -- 2017년
    AND ST_DWithin(
        -- 폴리곤 안의 모든 점들: https://wktmap.com/?484becc9
        ST_GeogFromText('POLYGON((-179 26, -179 48, -10 48, -10 26, -100 -10.1, -179 26))'), 
        ST_GeogPoint(longitude, latitude),  -- 좌표들2
        10  -- 거리
    )
-- ORDER BY 절은 점을 순서대로 나열하여 폭풍의 경로를 시간순으로 형성합니다.
ORDER BY
    iso_time ASC

ST_GeogPoint²: 좌표, (경도 longtitude, 위도 latitude)로 지정
ST_DWithin³: 두 Geometry가 지정된 거리 내에 있는지 판별, 거리 내에 있으면 TRUE, 아니면 FALSE 반환
ST_GeogFromText⁴: WKT(Well-Known Text)⁵ 형식의 문자열을 지정된 GEOGRAPHY 유형으로 변환

Quadraphonic Wind²

노트(kn 혹은 kt): 1시간에 1 해리(nm 혹은 nmile)를 진행하는 속도

파랑색 원: 태풍의 눈
초록색: 64노트(118km/h)의 방향별 풍속 최대 영향 반경
노랑색: 50노트(93km/h)의 방향별 풍속 최대 영향 반경
보라색: 34노트(63km/h)의 방향별 풍속 최대 영향 반경

실행 후 해당 데이터를 Geo Viz에서 쿼리 결과를 시각화한다. 쿼리를 실행하고 시각화 형식을 지정할 수 있다. 옵션은 다음과 같다.

Option	Description
fillColor	다각형 또는 점의 채우기 색상. 예를 들어 'linear' 또는 'interval' 함수는 숫자 값을 색상 그라디언트에 매핑하는 데 사용할 수 있음.
fillOpacity	다각형 또는 점의 채우기 불투명도. 값은 0에서 1 사이. 여기서 0은 투명이고, 1은 불투명.
strokeColor	다각형 또는 선의 획이나 윤곽선 색상.
strokeOpacity	다각형 또는 선의 획이나 윤곽선 불투명도. 값은 0에서 1 사이. 여기서 0은 투명이고, 1은 불투명.
strokeWeight	다각형 또는 선의 획 또는 윤곽선 너비(픽셀 단위).
circleRadius	점을 나타내는 원의 반지름(미터 단위). 예를 들어 산점도 스타일을 만들기 위해 'linear' 함수를 사용하여 숫자 값을 점 크기에 매핑할 수 있음.

설정

fillColorfillOpacitycircleRadius

Image title

예제2: 스타벅스와 생활인구 데이터 시각화¶

스타벅스 매장의 위치 데이터와 생활인구를 시각화해보자. 생활인구란 통신데이터로 특정 시점에 개인이 위치한 지역을 집계한 '현주인구'를 말한다. 시간대에 따라 변화하는 인구의 규모로 지역간 특성을 추측해 볼 수 있는 유용한 데이터다. 서울 열린데이터 광장²에서 데이터를 받을 수 있다.

2022년 데이터를 받아서 12월 25일 오후 3시대 각 20대 남녀 생활인구를 스타벅스 매장의 좌표 위치에 시각화해본다. 전처리 된 데이터는 여기에서 받을 수 있다.

starbucks라는 데이터 세트를 생성해주고 두 개의 데이터를 업로드한다. 각각 테이블의 이름은 starbucks_kr와 people_1225으로 한다. 그리고 시각화 데이터를 준비하기 위해 다음의 쿼리를 실행한다.

CREATE VIEW `[project_id]`.`starbucks`.`new_view` AS (
    SELECT 
    ST_GEOGPOINT(s.long, s.lat) AS point,  -- 좌표
    s.name,  -- 매장 이름
    p.time,  -- 시간
    s.region,  -- 시/군/구
    p.region_cat,  --  시/군/구 카테고리
    p.m2024 + p.m2529 + p.w2024 + p.w2529 AS p20,  -- 20대 남녀 인구
FROM `[project_id].starbucks.starbucks_kr` AS s
JOIN `[project_id].starbucks.people_1225` AS p
ON s.region = p.region
WHERE s.city = "서울특별시" AND p.time = "2022-12-25 15:00:00"
)