kbj110

0. 참고 공식 문서

https://cloud.google.com/bigquery/docs/reference/standard-sql/geography_functions

GoogleSQL for BigQuery supports geography functions. Geography functions operate on or generate GoogleSQL GEOGRAPHY values. The signature of most geography functions starts with ST_. GoogleSQL for BigQuery supports the following functions that can be used to analyze geographical data, determine spatial relationships between geographical features, and construct or manipulate GEOGRAPHYs.

1. ST_GEOPOINT 함수

ST_GEOPOINT 함수는 위도(latitude)와 경도(longitude) 값을 입력받아 지리적 좌표(Geography point)를 반환하는 함수.

이 함수는 위도와 경도를 사용하여 특정 지리적 지점을 정의할 때 유용합니다.

ST_GEOPOINT(longitude, latitude)

• longitude: 경도 값. (-180 ~ 180 사이의 값), latitude: 위도 값. (-90 ~ 90 사이의 값)

이 함수는 경도(longitude)가 첫 번째 인자로, 위도(latitude)가 두 번째 인자로 들어간다는 점에 유의해야 합니다. 반환값은 Geography 타입입니다.

예시

SELECT ST_GEOPOINT(-122.4194, 37.7749) AS geopoint;

결과

POINT(-122.4194 37.7749)

2. ST_DISTANCE 함수

ST_DISTANCE 함수는 두 지리적 좌표(Geography objects) 사이의 거리를 계산해 줌.

이 함수는 미터 단위로 두 점 사이의 최단 거리를 반환하며, 구면 지구 모델을 사용하여 거리를 계산합니다. 이는 지리적 분석에서 두 위치 간의 물리적 거리를 구할 때 유용합니다.

ST_DISTANCE(geography1, geography2)

• geography1: 첫 번째 지리적 좌표 (Geography 타입), geography2: 두 번째 지리적 좌표 (Geography 타입).

예시: 샌프란시스코와 뉴욕 사이의 거리를 계산

SELECT ST_DISTANCE(
  ST_GEOPOINT(-122.4194, 37.7749),  -- San Francisco
  ST_GEOPOINT(-74.0060, 40.7128)    -- New York
) AS distance;

결과: 

4130107.673 meters (약 4,130km)

3. ST_DISTANCE와 ST_GEOPOINT를 함께 사용하는 경우

WITH cities AS (
  SELECT 'San Francisco' AS city, ST_GEOPOINT(-122.4194, 37.7749) AS location UNION ALL
  SELECT 'New York' AS city, ST_GEOPOINT(-74.0060, 40.7128) AS location UNION ALL
  SELECT 'Los Angeles' AS city, ST_GEOPOINT(-118.2437, 34.0522) AS location
)
SELECT city, ST_DISTANCE(ST_GEOPOINT(-123.1207, 49.2827), location) AS distance
FROM cities
ORDER BY distance ASC
LIMIT 1;

위 쿼리는 캐나다 벤쿠버(경도 -123.1207, 위도 49.2827)와 가장 가까운 도시를 찾음.

결과:

Los Angeles (약 1734km)

4. 기타 같이 사용하면 좋을 것

1. Geolite mmdb

https://github.com/P3TERX/GeoLite.mmdb

이전에 python 라이브러리들을 통해서 몇가지 처리를 하려하였지만 open api들에 대해선 request limit이 걸려 다음과 같은 mmdb를 사용.

2. GeoPandas

import geopandas as gpd
from shapely.geometry import Point

data = {'City': ['San Francisco', 'New York'],
        'Latitude': [37.7749, 40.7128],
        'Longitude': [-122.4194, -74.0060]}
gdf = gpd.GeoDataFrame(data, geometry=gpd.points_from_xy(data['Longitude'], data['Latitude']))


print(gdf)

결과:

3. Geopy

from geopy.distance import geodesic

san_francisco = (37.7749, -122.4194)
new_york = (40.7128, -74.0060)

distance = geodesic(san_francisco, new_york).meters
print(f"Distance between San Francisco and New York: {distance} meters")

결과:

4. Folium (시각화)

import folium

m = folium.Map(location=[37.7749, -122.4194], zoom_start=12)

folium.Marker([37.7749, -122.4194], popup="San Francisco").add_to(m)
folium.Marker([40.7128, -74.0060], popup="New York").add_to(m)

m.save("map.html")  # 지도 저장

(로컬에 저장된 html 파일을 크롬으로 드래그 앤 드롭 하면 열린다.)

DECLARE variable_name[, ...] [variable_type] [DEFAULT expression];

DECLARE fromdate DATE DEFAULT '2014-01-01';  -- dates for after 2013
DECLARE todate DATE DEFAULT '2015-01-01';

SELECT FORMAT('From %t to %t', fromdate, todate);

Syntax

SET variable_name = expression;

SET (variable_name[, ...]) = (expression[, ...]);

Description

변수에 제공된 표현식의 값을 설정하거나 여러 변수를 여러 표현식의 결과를 기반으로 동시에 설정합니다.

SET 문은 다중 문장 쿼리 내에서 어디에서든 나타날 수 있습니다.

Examples

SET x = 5;

SET (a, b, c) = (1 + 3, 'foo', false);