Snowflake의 지리 공간 쿼리 기능은 지리 공간 오브젝트를 대상으로 계산을 분석, 구성 및 실행하는 데 사용할 수 있는 데이터 타입과 특화된 쿼리 기능의 조합을 기반으로 구축되었습니다. 이 가이드에서는 GEOGRAPHY 데이터 타입을 소개하며 여러분이 Snowflake에서 지원하는 지리 공간 형식을 이해할 수 있도록 돕습니다. 또한 Snowflake Marketplace에서 가져온 샘플 지리 공간 데이터 세트에 다양한 함수를 사용하는 방법을 안내합니다.

사전 필요 조건 및 지식

학습할 내용

필요한 것

구축할 것

이 가이드의 첫 단계는 Snowflake 지리 공간 기능의 기본 사항을 탐색하기 위해 자유롭게 사용할 수 있는 지리 공간 데이터 세트를 습득하는 것입니다. 이 데이터를 습득할 최적의 장소는 Snowflake Marketplace입니다!

Snowflake의 Preview 웹 UI에 액세스

app.snowflake.com

처음으로 새로운 Preview Snowflake UI에 로그인하는 것이라면 평가판을 습득할 때 받았던 여러분의 계정 이름 또는 계정 URL 입력을 요청하는 프롬프트가 나타납니다. 계정 URL에는 여러분의 계정 이름과 잠재적으로 지역이 포함되어 있습니다.

Sign-in을 클릭하면 여러분의 사용자 이름과 암호를 요청하는 프롬프트가 나타납니다.

계정 권한 확장

새로운 Preview Snowflake 웹 인터페이스는 다양한 기능을 제공하지만 지금은 여러분의 현재 역할을 기본값인 SYSADMIN에서 ACCOUNTADMIN으로 전환합니다. 이 권한 확장을 통해 Snowflake Marketplace 목록에서 공유 데이터베이스를 생성할 수 있습니다.

Positive : ACCOUNTADMIN 역할이 없다면 대신 IMPORT SHARE 권한을 가진 역할로 전환합니다.

account-role-change-image

가상 웨어하우스 생성(필요할 경우)

쿼리를 실행할 가상 웨어하우스에 대한 액세스가 아직 없다면 가상 웨어하우스를 하나 생성해야 합니다.

create-warehouse-image

Suspend After (min) 필드를 1분으로 변경하여 컴퓨팅 크레딧이 낭비되지 않도록 합니다.

Snowflake Marketplace에서 데이터 습득

이제 Snowflake Marketplace에서 샘플 지리 공간 데이터를 습득할 수 있습니다.

marketplace-listing-image

set-profile-image

get-data-image

축하드립니다! 방금 Snowflake Marketplace의 목록에서 공유 데이터베이스를 생성했습니다. 큰 파란색 Query Data 버튼을 클릭하고 이 가이드의 다음 단계로 이동합니다.

이전 섹션의 마지막 단계에서 워크시트 편집기를 새로운 Snowflake UI에서 열었습니다. Marketplace 목록에서 정의된 샘플 쿼리에서 가져온 몇몇 사전 정의된 쿼리가 포함되어 있었습니다. 이 가이드에서는 어떠한 쿼리도 실행하지만 추후에 실행해 보십시오. 대신 새로운 워크시트 편집기를 열고 다양한 쿼리를 실행하여 GEOGRAPHY 데이터 타입이 Snowflake에서 어떻게 작동하는지 이해해 보겠습니다.

새로운 워크시트 열기 및 웨어하우스 선택

이제 일부 쿼리를 실행할 준비가 되었습니다.

GEOGRAPHY 데이터 타입

Snowflake의 GEOGRAPHY 데이터 타입은 모든 포인트를 평면이 아닌 구형 지구의 경도와 위도로 취급한다는 점에서 기타 지리 공간 데이터베이스의 GEOGRAPHY 데이터 타입과 비슷합니다. 이는 기타 지리 공간 타입(예: GEOMETRY)과의 중요한 차이점입니다. 그러나 이 가이드에서는 이러한 차이점을 다루지 않습니다. Snowflake의 명세에 대한 자세한 정보는 여기에서 찾을 수 있습니다.

다음 쿼리를 실행하여 GEOGRAPHY 열을 보유하고 있는 공유 데이터베이스의 뷰 중 하나를 살펴보겠습니다. 아래 SQL을 여러분의 워크시트 편집기에 복사하여 붙여넣습니다. 커서를 실행하고자 하는 쿼리 텍스트 부근(주로 시작 또는 끝)에 올려놓고 브라우저 창 오른쪽 상단에 있는 파란색 ‘Play' 버튼을 클릭하거나 CTRL+Enter 또는 CMD+Enter(Windows 또는 Mac)를 눌러 쿼리를 실행합니다.

// Set the working database schema
use schema osm_newyork.new_york;

USE SCHEMA 명령은 여러분의 추후 쿼리를 위해 활성 database.schema를 설정합니다. 따라서 여러분의 오브젝트는 자격을 완벽하게 갖출 필요가 없습니다.

// Describe the v_osm_ny_shop_electronics view 
desc view v_osm_ny_shop_electronics;

DESC 또는 DESCRIBE 명령은 여러분에게 뷰 정의를 보여줍니다. 여기에는 열, 데이터 타입 및 기타 관련 세부 정보가 포함되어 있습니다. coordinates 열은 GEOGRAPHY 타입으로 정의됩니다. 이는 다음 단계에서 집중적으로 다룰 열입니다.

GEOGRAPHY 출력 형식 보기

Snowflake는 3가지 기본 지리 공간 형식과 이러한 형식의 2가지 추가 변형을 지원합니다. 기본 지리 공간 형식:

이러한 형식은 수집(이러한 형식을 포함하는 파일은 GEOGRAPHY 형식의 열로 로드될 수 있음), 쿼리 결과 디스플레이 및 새로운 파일로의 데이터 언로딩을 위해 지원됩니다. Snowflake가 어떻게 숨겨진 데이터를 저장하는지 걱정할 필요는 없습니다. 대신 데이터가 여러분에게 표시되거나 GEOGRAPHY_OUTPUT_FORMAT이라는 세션 변수의 값을 통해 파일로 언로드되는 방법을 고려해야 합니다.

아래 쿼리를 실행하여 현재 형식이 GeoJSON인지 확인합니다.

// Set the output format to GeoJSON
alter session set geography_output_format = 'GEOJSON';

ALTER SESSION 명령을 통해 현재 사용자 세션(이 경우 GEOGRAPHY_OUTPUT_FORMAT)을 위한 매개 변수를 설정할 수 있습니다. 이 매개 변수의 기본값은 'GEOJSON'이기에 일반적으로는 해당 형식을 원한다면 이 명령을 실행하지 않아도 됩니다. 그러나 이 가이드에서는 다음 쿼리가 'GEOJSON' 출력으로 실행됨을 확실시 하고자 합니다.

이제 다음 쿼리를 V_OSM_NY_SHOP_ELECTRONICS 뷰와 비교하여 실행합니다.

// Query the v_osm_ny_shop_electronics view for rows of type 'node' (long/lat points)
select coordinates, name from v_osm_ny_shop_electronics where type = 'node' limit 25;

결과 세트에서 coordinates 열을 확인하고 어떻게 포인트의 JSON 표현을 표시하는지 확인합니다. 다음과 비슷하게 나타나야 합니다.

{   "coordinates": [     -7.390351649999999e+01,     4.074499730000000e+01   ],   "type": "Point" }

쿼리 결과의 coordinates 열에 있는 셀을 클릭하면 JSON 표현은 쿼리 창 오른쪽에 있는 셀 패널에 표시됩니다. 또한 해당 JSON 텍스트(아래 스크린샷 참고)를 복사할 수 있는 버튼이 포함되어 있습니다. 이 기능은 추후 연습에서 사용하겠습니다.

cell-panel-image

이제 다음 쿼리를 실행합니다.

// Query the v_osm_ny_shop_electronics view for rows of type 'way' (a collection of many points)
select coordinates, name from v_osm_ny_shop_electronics where type = 'way' limit 25;

쿼리 결과의 coordinates 열에 있는 셀을 클릭합니다. 셀 패널에서 JSON이 어떻게 JSON 배열에 있는 더 많은 포인트를 통해 확장되는지를 확인하십시오. 이는 단일 포인트의 지리 공간 표현과 여러 포인트 표현 간의 차이점을 보여줍니다.

이제 동일하지만 다른 형식을 가진 쿼리를 확인하겠습니다. 다음 쿼리를 실행합니다.

// Set the output format to WKT
alter session set geography_output_format = 'WKT';

이전 쿼리 2개를 다시 실행합니다. 각 실행 시 coordinates 열에 있는 셀을 클릭하고 출력을 살펴봅니다.

select coordinates, name from v_osm_ny_shop_electronics where type = 'node' limit 25;
select coordinates, name from v_osm_ny_shop_electronics where type = 'way' limit 25;

WKT는 GeoJSON과 다르게 보이며 훨씬 더 읽기 좋습니다. 여기에서 아래 예시 출력에서 표시된 지리 공간 오브젝트 유형을 훨씬 더 명확하게 볼 수 있습니다.

// An example of a POINT
POINT(-74.0266511 40.6346599)
// An example of a POLYGON
POLYGON((-74.339971 43.0631175,-74.3397734 43.0631363,-74.3397902 43.0632306,-74.3399878 43.0632117,-74.339971 43.0631175))

마지막으로 WKB 출력을 확인하겠습니다. 다음 쿼리를 실행합니다.

// Set the output format to WKB
alter session set geography_output_format = 'WKB';

2개의 쿼리를 다시 실행하고 실행할 때마다 coordinates 열에 있는 셀을 클릭합니다.

select coordinates, name from v_osm_ny_shop_electronics where type = 'node' limit 25;
select coordinates, name from v_osm_ny_shop_electronics where type = 'way' limit 25;

인간 독자는 WKB를 이해할 수 없습니다. 바이너리 값의 길이 외에는 POINT와 POLYGON의 차이를 알기란 어렵습니다. 그러나 이 형식은 다음 섹션에서 확인할 수 있듯이 데이터 로드/언로드에 유용합니다.

다양한 출력 형식을 이해했으니 electronics 뷰에서 새로운 파일을 생성한 다음 이러한 파일을 GEOGRAPHY 데이터 타입을 가진 새로운 테이블로 로드할 수 있습니다. 또한 지리 공간 파서 및 _생성자_의 첫 예를 마주하게 됩니다.

쿼리에서 새로운 WKB 파일 생성

이 단계에서는 Snowflake의 COPY INTO 위치를 사용하여 쿼리의 출력을 가져오고 파일을 로컬 사용자 스테이지에서 생성하겠습니다. 출력 형식이 WKB로 설정되어 있기에 해당 테이블의 지리 공간 열은 새로운 파일에서 WKB 형식으로 나타납니다.

다음 쿼리를 다시 실행하여 WKB 출력 형식을 사용하고 있는지 확인합니다.

alter session set geography_output_format = 'WKB';

COPY 명령의 구조에 익숙하지 않다면 아래 코드 메모에서 electronics 뷰의 일부 열과 모든 행을 복사하는 첫 쿼리의 코드를 분석합니다.

// Define the write location (@~/ = my user stage) and file name for the file 
copy into @~/osm_ny_shop_electronics_all.csv 
// Define the query that represents the data output
from (select id,coordinates,name,type from v_osm_ny_shop_electronics) 
// Indicate the comma-delimited file format and tell it to double-quote strings
file_format=(type=csv field_optionally_enclosed_by='"') 
// Tell Snowflake to write one file and overwrite it if it already exists
single=true overwrite=true;

위 쿼리를 실행하면 848개의 행이 언로드되었음을 나타내는 출력이 나타나야 합니다.

출력 쿼리와 파서에 일부 필터를 추가하는 아래에 있는 두 번째 언로드 쿼리를 실행합니다.

copy into @~/osm_ny_shop_electronics_points.csv 
from (
  select id,coordinates,name,type,st_x(coordinates),st_y(coordinates) 
  from v_osm_ny_shop_electronics where type='node'
) file_format=(type=csv field_optionally_enclosed_by='"') 
single=true overwrite=true;

이 쿼리에서 ST_X 및 ST_Y 파서는 GEOGRAPHY POINT 오브젝트에서 경도와 위도를 추출합니다. 이러한 파서는 단일 포인트만 입력으로 허용하기에 여러분은 쿼리를 type = 'node'로 필터링해야 했습니다. Snowflake에서 ‘x' 좌표는 언제나 경도이며 ‘y'좌표는 언제나 위도입니다. 또한 추후 생성자에서 확인할 수 있듯이 경도가 항상 먼저 나열됩니다.

사용자 스테이징된 파일 LIST 및 쿼리

이제 여러분의 사용자 스테이지에 2개의 파일이 있어야 합니다. LIST 명령을 실행하여 파일이 해당 위치에 존재하는지 확인합니다. ‘osm' 문자열은 ‘osm'으로 시작하는 파일만 표시되도록 명령에 요청하는 필터의 역할을 수행합니다.

list @~/osm;

아래 ‘$' 표기법을 사용해 단순한 파일을 스테이지에서 바로 쿼리하여 파일에 있는 구분된 각각의 열을 나타낼 수 있습니다. 이 경우 Snowflake는 이를 쉼표로 구분된 CSV라고 가정합니다. 다음 쿼리를 실행합니다.

select $1,$2,$3,$4 from @~/osm_ny_shop_electronics_all.csv;

파일을 생성한 방식으로 인해 두 번째 열에서는 WKB 지리 공간 데이터를 큰따옴표로 표시합니다. 이는 GEOGRAPHY 데이터 타입으로 바로 로드되지 않기에 여러분이 추가적으로 파일 형식을 정의해야 합니다. 아래에 있는 각각의 쿼리를 실행하여 해당 데이터베이스에 로컬 데이터베이스와 새로운 파일 형식을 생성합니다. 또한 여러분의 GEOGRAPHY 출력 형식을 WKT로 다시 전환하여 추후 쿼리 가독성을 개선하겠습니다.

// Create a new local database
create or replace database geocodelab;
// Change your working schema to the public schema in that database
use schema geocodelab.public;
// Create a new file format in that schema
create or replace file format geocsv type = 'csv' field_optionally_enclosed_by='"';
// Set the output format back to WKT
alter session set geography_output_format = 'WKT';

이제 파일 형식을 사용하여 스테이지의 ‘all' 파일을 쿼리합니다.

select $1,TO_GEOGRAPHY($2),$3,$4 
from @~/osm_ny_shop_electronics_all.csv 
(file_format => 'geocsv');

Snowflake에 WKB 바이너리 문자열을 지리 공간 데이터로 해석하고 GEOGRAPHY 타입을 구성하도록 지시하는 TO_GEOGRAPHY 생성자의 사용을 확인하십시오. WKT 출력 형식을 통해 이 표현을 보다 읽기 좋은 형태로 확인할 수 있습니다. 다음과 같은 2개의 쿼리를 실행하여 이 파일을 이제 GEOGRAPHY 형식의 열을 포함하는 테이블로 로드할 수 있습니다.

// Create a new 'all' table in the current schema
create or replace table electronics_all 
(id number, coordinates geography, name string, type string);
// Load the 'all' file into the table
copy into electronics_all from @~/osm_ny_shop_electronics_all.csv 
file_format=(format_name='geocsv');

848개의 행이 성공적으로 테이블에 로드되었으며 0개의 오류가 나타나야 합니다.

이제 기타 ‘points' 파일을 다루겠습니다. 앞서 ST_X 및 ST_Y를 사용하여 이 파일에 이산 경도와 위도를 생성했습니다. 다양한 열에 있는 이러한 값을 포함한 데이터를 수신하는 일이 종종 발생합니다. 또한 ST_MAKEPOINT 생성자를 사용하여 2개의 이산 위도와 경도 열을 하나의 GEOGRAPHY 형식의 열로 결합할 수 있습니다. 다음 쿼리를 실행합니다.

select $1,ST_MAKEPOINT($5,$6),$3,$4,$5,$6 
from @~/osm_ny_shop_electronics_points.csv 
(file_format => 'geocsv');

이제 테이블을 생성하고 ‘points' 파일을 해당 테이블로 로드합니다. 이러한 2개의 쿼리를 실행합니다.

// Create a new 'points' table in the current schema
create or replace table electronics_points 
(id number, coordinates geography, name string, type string, 
long number(38,7), lat number(38,7));
// Load the 'points' file into the table
copy into electronics_points from (
  select $1,ST_MAKEPOINT($5,$6),$3,$4,$5,$6 
  from @~/osm_ny_shop_electronics_points.csv
) file_format=(format_name='geocsv');

728개의 행이 성공적으로 테이블에 로드되었으며 0개의 오류가 나타나야 합니다.

이 섹션을 마무리하기 위해 다음과 같은 2개의 쿼리를 사용하여 최근 로드된 테이블을 쿼리할 수 있습니다.

select * from electronics_all;
select * from electronics_points;

GEOGRAPHY 데이터 타입의 작동 방법과 다양한 출력 형식을 가진 데이터 지리 공간 표현의 모습에 대한 기본 사항을 이해했습니다. 이제 여러분이 일부 질문에 답하기 위해 일부 지리 공간 쿼리를 실행해야 하는 시나리오를 진행할 시간입니다.

시나리오를 시작하기 전에 활성 스키마를 공유 데이터베이스로 다시 전환하고 다른 웹 사이트를 사용하여 쿼리 결과를 시각화할 것이기에 출력 형식이 GeoJSON 또는 WKT인지 확인합니다. 어떤 출력을 선택할지는 여러분의 개인 선호도에 달려 있습니다. WKT는 일반인이 읽기에 더 쉽지만 GeoJSON이 확실히 더 많이 사용됩니다. GeoJSON 시각화 도구로 포인트, 라인 및 도형을 더 쉽게 확인할 수 있기에 이 가이드에서는 GeoJSON을 위한 출력을 선보입니다.

또한 지금부터 이전에 다뤘던 SQL 문과 함수에는 해당 가이드의 코드 메모 또는 텍스트에서 설명했던 사용법이 더 이상 적용되지 않습니다. 다음 쿼리 2개를 실행합니다.

use schema osm_newyork.new_york;
// Run just one of the below queries based on your preference
alter session set geography_output_format = 'GEOJSON';
alter session set geography_output_format = 'WKT';

시나리오

현재 여러분이 뉴욕 타임스퀘어 주변에 있는 아파트에 거주하고 있다고 가정하겠습니다. Best Buy와 주류 판매점을 방문하여 쇼핑하고 커피숍에서 커피를 구매해야 합니다. 여러분의 현재 위치를 기준으로 이러한 심부름을 수행하기 위해 가장 가까운 매장이나 가게가 어디인가요? 또한 이러한 매장이나 가게가 전체적으로 방문하기에 가장 이상적인가요? 이동하는 동안 방문할 수 있는 다른 가게가 있나요?

여러분의 현재 위치를 나타내는 쿼리를 실행하며 시작합니다. 이 위치는 해당 가이드를 위해 지도에서 위치를 클릭하면 위도와 경도를 반환하는 웹 사이트를 사용하여 사전에 선택되었습니다. 다음 쿼리를 실행합니다.

select to_geography('POINT(-73.986226 40.755702)');

이 쿼리에는 from 절이 없기에 단순한 select 문에 GEOGRAPHY 오브젝트를 구성할 수 있습니다.

current-location-image

위 이미지에서 어두운 회색 지도 위치 아이콘은 POINT 오브젝트 위치를 나타냅니다. 이제 여러분이 어디에 있는지 알 수 있습니다!

가장 가까운 위치 찾기

다음 단계에서는 쿼리를 실행하여 위에서 보이는 여러분의 현재 위치에서 가장 가까운 Best Buy, 주류 판매점 및 커피숍을 찾아보겠습니다. 이러한 쿼리는 매우 비슷하며 다음과 같이 여러 작업을 수행합니다.

다음 쿼리(첫 번째 쿼리에 위와 비슷한 메모가 있음)를 실행합니다.

// Find the closest Best Buy
select id, coordinates, name, addr_housenumber, addr_street, 
// Use st_distance to calculate the distance between your location and Best Buy
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters 
from v_osm_ny_shop_electronics 
// Filter just for Best Buys
where name = 'Best Buy' and 
// Filter for Best Buys that are within about a US mile (1600 meters)
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
// Order the results by the calculated distance and only return the lowest
order by 6 limit 1;

// Find the closest liquor store
select id, coordinates, name, addr_housenumber, addr_street, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters 
from v_osm_ny_shop_food_beverages 
where shop = 'alcohol' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 6 limit 1;

// Find the closest coffee shop
select id, coordinates, name, addr_housenumber, addr_street, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters 
from v_osm_ny_shop_food_beverages 
where shop = 'coffee' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 6 limit 1;

각각의 경우 쿼리는 POINT 오브젝트를 반환합니다. 아직 이를 사용하지는 않겠지만 이제 여러분은 원하는 결과를 반환하는 쿼리를 보유하고 있습니다. 그러나 이러한 포인트가 서로 어떻게 관련되어 있는지 쉽게 시각화할 수 있다면 정말 좋겠습니다.

포인트를 라인으로 수집

이 섹션의 다음 단계에서는 ST_COLLECT를 사용하여 포인트를 ‘수집'하고 ST_MAKELINE 생성자로 LINESTRING 오브젝트를 만들어보겠습니다. 그런 다음 geojson.io에서 이 라인을 시각화할 수 있습니다.

다음 쿼리를 실행하고 출력을 살펴봅니다.

// Create the CTE 'locations'
with locations as (
(select to_geography('POINT(-73.986226 40.755702)') as coordinates, 
0 as distance_meters)
union all
(select coordinates, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters from v_osm_ny_shop_electronics 
where name = 'Best Buy' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1)
union all
(select coordinates, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters from v_osm_ny_shop_food_beverages 
where shop = 'alcohol' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1)
union all
(select coordinates, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters from v_osm_ny_shop_food_beverages 
where shop = 'coffee' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1))
// Query the CTE result set, aggregating the coordinates into one object
select st_collect(coordinates) as multipoint from locations;

다음으로 수행해야 하는 작업은 포인트 세트를 입력으로 사용하고 이를 LINESTRING 오브젝트로 변환하는 ST_MAKELINE을 사용하여 해당 MULTIPOINT 오브젝트를 LINESTRING 오브젝트로 변환하는 것입니다. MULTIPOINT는 가정된 연결이 없는 포인트를 보유한 반면 LINESTRING에 있는 포인트는 나타나는 순서대로 연결되었다고 해석됩니다. ST_MAKELINE으로 피드하기 위해 포인트 컬렉션이 필요하기에 위 ST_COLLECT 단계를 수행했습니다. 또한 위 쿼리를 대상으로 수행해야 하는 유일한 작업은 ST_LINESTRING의 ST_COLLECT를 다음과 같이 래핑하는 것입니다.

select st_makeline(st_collect(coordinates),to_geography('POINT(-73.986226 40.755702)'))

실행할 전체 쿼리(메모 제외)는 다음과 같습니다.

with locations as (
(select to_geography('POINT(-73.986226 40.755702)') as coordinates, 
0 as distance_meters)
union all
(select coordinates, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters from v_osm_ny_shop_electronics 
where name = 'Best Buy' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1)
union all
(select coordinates, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters from v_osm_ny_shop_food_beverages 
where shop = 'alcohol' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1)
union all
(select coordinates, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters from v_osm_ny_shop_food_beverages 
where shop = 'coffee' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1))
select st_makeline(st_collect(coordinates),to_geography('POINT(-73.986226 40.755702)'))
as linestring from locations;

위 쿼리에서 결과 셀을 복사하여 geojson.io에 붙여넣습니다. 다음과 같이 나타나야 합니다.

first-linestring-image

이런! 위 이미지에서는 다양한 가게가 여러분의 기존 위치를 기준으로 서로 다른 세 가지 방향에 있습니다. 많이 걸어야겠습니다. 다행히도 미터로 라인의 길이를 계산하는 LINESTRING 오브젝트를 대상으로 ST_DISTANCE 함수를 래핑하여 얼마나 많이 걸어야 하는지 확인할 수 있습니다. 다음 쿼리를 실행합니다.

with locations as (
(select to_geography('POINT(-73.986226 40.755702)') as coordinates, 
0 as distance_meters)
union all
(select coordinates, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters from v_osm_ny_shop_electronics 
where name = 'Best Buy' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1)
union all
(select coordinates, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters from v_osm_ny_shop_food_beverages 
where shop = 'alcohol' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1)
union all
(select coordinates, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters from v_osm_ny_shop_food_beverages 
where shop = 'coffee' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1))
// Feed the linestring into an st_length calculation
select st_length(st_makeline(st_collect(coordinates),
to_geography('POINT(-73.986226 40.755702)')))
as length_meters from locations;

우와! 2,120미터에 달합니다!

이제 다음 섹션으로 이동하여 여러분의 쇼핑을 최적화할 방법을 알아보겠습니다.

이전 섹션에서는 가장 가까운 Best Buy, 주류 판매점 및 커피숍을 찾기 위한 모든 쿼리가 여러분의 타임스퀘어 아파트와 얼마나 가까운지를 기반으로 했습니다. 그러나 예를 들어 Best Buy와 더 가까운 주류 판매점 및 또는 커피숍이 있는지 확인하는 것이 더 효율적이지 않을까요? 테이블 조인의 지리 공간 함수를 사용하여 이를 확인할 수 있습니다.

Best Buy와 더 가까운 가게가 있나요?

여러분의 쿼리에서 지금까지 다음과 같은 2개의 뷰를 사용했습니다. v_osm_ny_shop_electronics 뷰는 Best Buy와 같은 매장을 목록으로 나열하며 v_osm_ny_shop_food_beverage 뷰는 주류 판매점과 커피숍을 목록으로 나열합니다. 전자와 가까운 곳에 있는 후자를 찾기 위해 이러한 2개의 테이블을 조인하겠습니다. 새로운 쿼리에는 몇몇 변경 사항이 적용됩니다.

다음 쿼리 2개(첫 쿼리에만 메모 포함)를 실행합니다.

// Join to electronics to find a liquor store closer to Best Buy
select fb.id,fb.coordinates,fb.name,fb.addr_housenumber,fb.addr_street,
// The st_distance calculation uses coordinates from both views
st_distance(e.coordinates,fb.coordinates) as distance_meters 
from v_osm_ny_shop_electronics e 
// The join is based on being within a certain distance
join v_osm_ny_shop_food_beverages fb on st_dwithin(e.coordinates,fb.coordinates,1600) 
// Hard-coding the known Best Buy id below
where e.id = 1428036403 and fb.shop = 'alcohol' 
// Ordering by distance and only showing the lowest
order by 6 limit 1;

// Join to electronics to find a coffee shop closer to Best Buy
select fb.id,fb.coordinates,fb.name,fb.addr_housenumber,fb.addr_street,
st_distance(e.coordinates,fb.coordinates) as distance_meters 
from v_osm_ny_shop_electronics e 
join v_osm_ny_shop_food_beverages fb on st_dwithin(e.coordinates,fb.coordinates,1600) 
where e.id = 1428036403 and fb.shop = 'coffee' 
order by 6 limit 1;

각 쿼리의 결과를 보았을 때 첫 쿼리는 Best Buy와 더 가까운 다른 주류 판매점을 찾았지만, 두 번째 쿼리는 여러분의 기존 검색과 동일한 커피숍을 반환했습니다. 따라서 여러분은 가능한 만큼 최적화했습니다.

새로운 Linestring 계산

이제 주류 판매점에 더 나은 옵션이 있다는 것을 알게 되었으니 위 주류 판매점 쿼리로 기존 linestring 쿼리를 대체하여 다른 오브젝트를 생성합니다. 시각화를 위해 공용 구조체의 문 순서가 변경되었으며 이는 오브젝트의 포인트 순서에 영향을 줍니다.

with locations as (
(select to_geography('POINT(-73.986226 40.755702)') as coordinates, 
0 as distance_meters)
union all
(select coordinates, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters 
from v_osm_ny_shop_food_beverages 
where shop = 'coffee' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1)
union all
(select fb.coordinates, st_distance(e.coordinates,fb.coordinates) as distance_meters 
from v_osm_ny_shop_electronics e 
join v_osm_ny_shop_food_beverages fb on st_dwithin(e.coordinates,fb.coordinates,1600) 
where e.id = 1428036403 and fb.shop = 'alcohol' 
order by 2 limit 1)
union all
(select coordinates, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters 
from v_osm_ny_shop_electronics 
where name = 'Best Buy' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1))
select st_makeline(st_collect(coordinates),
to_geography('POINT(-73.986226 40.755702)')) as linestring from locations;

위 쿼리에서 결과 셀을 복사하여 geojson.io에 붙여넣습니다. 다음과 같이 나타나야 합니다.

second-linestring-image

훨씬 더 낫습니다! 더 효율적인 쇼핑 경로처럼 보입니다. 다음 쿼리를 실행하여 새로운 거리를 확인합니다.

with locations as (
(select to_geography('POINT(-73.986226 40.755702)') as coordinates, 
0 as distance_meters)
union all
(select coordinates, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters 
from v_osm_ny_shop_food_beverages 
where shop = 'coffee' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1)
union all
(select fb.coordinates, st_distance(e.coordinates,fb.coordinates) as distance_meters 
from v_osm_ny_shop_electronics e 
join v_osm_ny_shop_food_beverages fb on st_dwithin(e.coordinates,fb.coordinates,1600) 
where e.id = 1428036403 and fb.shop = 'alcohol' 
order by 2 limit 1)
union all
(select coordinates, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters 
from v_osm_ny_shop_electronics 
where name = 'Best Buy' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1))
select st_length(st_makeline(st_collect(coordinates),
to_geography('POINT(-73.986226 40.755702)'))) 
as length_meters from locations;

좋습니다! 약 583미터 또는 1/3마일이 줄어든 1,537미터입니다. 2개의 shop 뷰를 합쳐 여러분의 경로를 최적화하기 위해 또 다른 테이블의 오브젝트와 가장 가까운 하나의 테이블에서 오브젝트를 찾을 수 있었습니다. 이제 보다 최적화된 경로를 보유하고 있으니 이동하는 동안 다른 가게에 들를 수 있을까요? 다음 섹션으로 이동하여 확인해 보겠습니다.

이전 섹션에서 생성했던 LINESTRING 오브젝트는 깔끔하고 멋진 4면을 가진 폴리곤처럼 보입니다. 곧 알게 되겠지만 POLYGON은 여러분이 구성하고 작업할 수 있는 또 다른 지리 공간 오브젝트 유형입니다. LINESTRING을 도형의 테두리로 생각했을 때 POLYGON은 도형 자체가 채워진 버전입니다. POLYGON에 대한 중요한 부분은 시작점에서 끝나야 한다는 것입니다. 반면에 LINESTRING은 시작점으로 돌아올 필요가 없습니다.

폴리곤 구성

POLYGON 구성에는 ST_MAKELINE과 같이 ST_MAKEPOLYGON 함수를 사용합니다. 유일한 차이점은 ST_MAKELINE은 포인트로부터 라인을 만들지만 ST_MAKEPOLYGON은 라인으로부터 폴리곤을 만든다는 것입니다. 따라서 라인을 구성했던 이전 쿼리를 대상으로 수행해야 할 유일한 작업은 다음과 같이 ST_MAKEPOLYGON으로 해당 구성을 래핑하는 것입니다.

select st_makepolygon(st_makeline(st_collect(coordinates),
to_geography('POINT(-73.986226 40.755702)')))

이는 개별 포인트에서 포인트 컬렉션, 라인, 폴리곤까지 구성 과정을 묘사하는 데 큰 도움이 됩니다. 다음 쿼리를 실행하여 폴리곤을 생성합니다.

with locations as (
(select to_geography('POINT(-73.986226 40.755702)') as coordinates, 
0 as distance_meters)
union all
(select coordinates, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters 
from v_osm_ny_shop_food_beverages 
where shop = 'coffee' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1)
union all
(select fb.coordinates, st_distance(e.coordinates,fb.coordinates) as distance_meters 
from v_osm_ny_shop_electronics e 
join v_osm_ny_shop_food_beverages fb on st_dwithin(e.coordinates,fb.coordinates,1600) 
where e.id = 1428036403 and fb.shop = 'alcohol' 
order by 2 limit 1)
union all
(select coordinates, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters 
from v_osm_ny_shop_electronics 
where name = 'Best Buy' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1))
select st_makepolygon(st_makeline(st_collect(coordinates),
to_geography('POINT(-73.986226 40.755702)'))) as polygon from locations;

위 쿼리에서 결과 셀을 복사하여 geojson.io에 붙여넣습니다. 다음과 같이 나타나야 합니다.

polygon-empty-image

또한 앞서 ST_DISTANCE를 사용하여 LINESTRING의 거리를 계산했던 것과 같이 라인 구성 주위를 래핑했던 것과 동일한 방식으로 폴리곤 구성 주위를 래핑하는 ST_PERIMETER를 사용하여 POLYGON의 둘레를 계산할 수 있습니다. 다음 쿼리를 실행하여 둘레를 계산합니다.

with locations as (
(select to_geography('POINT(-73.986226 40.755702)') as coordinates, 
0 as distance_meters)
union all
(select coordinates, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters 
from v_osm_ny_shop_food_beverages 
where shop = 'coffee' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1)
union all
(select fb.coordinates, st_distance(e.coordinates,fb.coordinates) as distance_meters 
from v_osm_ny_shop_electronics e 
join v_osm_ny_shop_food_beverages fb on st_dwithin(e.coordinates,fb.coordinates,1600) 
where e.id = 1428036403 and fb.shop = 'alcohol' 
order by 2 limit 1)
union all
(select coordinates, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters 
from v_osm_ny_shop_electronics 
where name = 'Best Buy' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1))
select st_perimeter(st_makepolygon(st_makeline(st_collect(coordinates),
to_geography('POINT(-73.986226 40.755702)')))) as perimeter_meters from locations;

좋습니다! 해당 쿼리는 앞서 반환되었던 LINESTRING의 거리와 같이 1,537미터를 반환했습니다. 이는 POLYGON의 둘레가 POLYGON을 구성하는 LINESTRING과 동일한 거리이기 때문입니다.

폴리곤 내에서 가게 찾기

이 가이드에서의 마지막 활동은 이전 단계에서 생성했던 POLYGON 내에 존재하는 v_osm_ny_shop 뷰 내에서 모든 유형의 가게를 찾는 것입니다. 이는 주요 목적지로 가는 경로 도중에 방문할 수 있는 모든 매장을 여러분에게 제공합니다. 이를 달성하기 위해 POLYGON을 구축하는 쿼리를 대상으로 수행할 작업은 다음과 같습니다.

다음 쿼리를 실행하여 폴리곤 내부에 어떤 가게가 있는지 확인하십시오.

// Define the outer CTE 'search_area'
with search_area as (
with locations as (
(select to_geography('POINT(-73.986226 40.755702)') as coordinates, 
0 as distance_meters)
union all
(select coordinates, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters 
from v_osm_ny_shop_food_beverages 
where shop = 'coffee' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1)
union all
(select fb.coordinates, 
st_distance(e.coordinates,fb.coordinates) as distance_meters 
from v_osm_ny_shop_electronics e 
join v_osm_ny_shop_food_beverages fb on st_dwithin(e.coordinates,fb.coordinates,1600) 
where e.id = 1428036403 and fb.shop = 'alcohol' 
order by 2 limit 1)
union all
(select coordinates, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters 
from v_osm_ny_shop_electronics 
where name = 'Best Buy' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1))
select st_makepolygon(st_makeline(st_collect(coordinates),
to_geography('POINT(-73.986226 40.755702)'))) as polygon from locations)
select sh.id,sh.coordinates,sh.name,sh.shop,sh.addr_housenumber,sh.addr_street 
from v_osm_ny_shop sh 
// Join v_osm_ny_shop to the 'search_area' CTE using st_within
join search_area sa on st_within(sh.coordinates,sa.polygon);

아래와 같이 13개의 결과(가독성을 위해 아래에서는 WKT 출력으로 표시됨)가 나타나야 합니다.

polygon-results-image

또한 마지막 단계에서 생성했던 POLYGON과 더불어 POLYGON 내부에 있는 모든 가게를 위한 POINT를 포함한 단일 지리 공간 오브젝트를 구성하겠습니다. 하나의 그룹화로 모든 지리 공간 오브젝트의 조합을 보유할 수 있는 지리 공간 타입인 이 단일 오브젝트는 GEOMETRYCOLLECTION으로 알려져 있습니다. 이 오브젝트를 생성하려면 다음을 수행해야 합니다.

다음 쿼리를 실행합니다.

// Define the outer CTE 'final_plot'
with final_plot as (
// Get the original polygon
(with locations as (
(select to_geography('POINT(-73.986226 40.755702)') as coordinates, 
0 as distance_meters)
union all
(select coordinates, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters 
from v_osm_ny_shop_food_beverages 
where shop = 'coffee' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1)
union all
(select fb.coordinates, 
st_distance(e.coordinates,fb.coordinates) as distance_meters 
from v_osm_ny_shop_electronics e 
join v_osm_ny_shop_food_beverages fb on st_dwithin(e.coordinates,fb.coordinates,1600) 
where e.id = 1428036403 and fb.shop = 'alcohol' 
order by 2 limit 1)
union all
(select coordinates, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters 
from v_osm_ny_shop_electronics 
where name = 'Best Buy' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1))
select st_makepolygon(st_makeline(st_collect(coordinates),
to_geography('POINT(-73.986226 40.755702)'))) as polygon from locations)
union all
// Find the shops inside the polygon
(with search_area as (
with locations as (
(select to_geography('POINT(-73.986226 40.755702)') as coordinates, 
0 as distance_meters)
union all
(select coordinates, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters 
from v_osm_ny_shop_food_beverages 
where shop = 'coffee' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1)
union all
(select fb.coordinates, 
st_distance(e.coordinates,fb.coordinates) as distance_meters 
from v_osm_ny_shop_electronics e 
join v_osm_ny_shop_food_beverages fb on st_dwithin(e.coordinates,fb.coordinates,1600) 
where e.id = 1428036403 and fb.shop = 'alcohol' 
order by 2 limit 1)
union all
(select coordinates, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters 
from v_osm_ny_shop_electronics 
where name = 'Best Buy' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1))
select st_makepolygon(st_makeline(st_collect(coordinates),
to_geography('POINT(-73.986226 40.755702)'))) as polygon from locations)
select sh.coordinates 
from v_osm_ny_shop sh 
join search_area sa on st_within(sh.coordinates,sa.polygon)))
// Collect the polygon and shop points into a geometrycollection
select st_collect(polygon) from final_plot;

위 쿼리에서 결과 셀을 복사하여 geojson.io에 붙여넣습니다. 다음과 같이 나타나야 합니다.

polygon-full-image

이 가이드에서는 Snowflake Marketplace에서 지리 공간 데이터를 습득하고, GEOGRAPHY 데이터 타입과 관련 형식의 작동 방법을 알아보고, 지리 공간 데이터가 포함된 데이터 파일을 생성하고, GEOGRAPHY 형식의 열을 가진 새로운 테이블에 이러한 파일을 로드하고, 단일 테이블과 조인을 포함한 여러 테이블에서 파서, 생성자, 변환 및 계산 함수를 사용하여 지리 공간 데이터를 쿼리했습니다. 그런 다음 새롭게 구성된 지리 공간 오브젝트를 geojson.io 또는 WKT Playground와 같은 도구로 시각화할 수 있는 방법을 확인했습니다.

이제 더 방대한 Snowflake 지리 공간 지원과 지리 공간 함수를 탐색할 준비가 되었습니다.

다룬 내용