Snowflakeでは地理空間関数を利用して位置情報データの分析ができます。
普段よく利用している地理空間関数を紹介したいと思います！
緯度経度から地理空間データ型を生成
緯度経度（テキスト）から地理空間データを作成します。地理空間データ型を作成しておくと、後々の処理が楽です。
user_id, log_time, longitude, latitudeの4つのカラムを持っているテーブルを例に、Geometry型のカラムを作ります。
はじめに、geomという名称のGeometry型のカラムを追加します。
ALTER TABLE sample ADD COLUMN geom geometry;;
続いて、緯度経度（longitude, latitude）を用いてGeometryを作ります。
以下のクエリでは以下の4ステップを行っています。
-
st_makepoint()で緯度経度からGeograpyオブジェクトを構築する。 -
st_asgeojson()でGeograpyオブジェクトをGeoJSON化する。 -
st_geometry()でGeoJSONをGeometry化する。 -
st_setsrid()でGeometryにSRIDをセットする。
UPDATE sample
SET geom = ST_SetSRID(TO_GEOMETRY(ST_ASGEOJSON(ST_MakePoint(longitude, latitude))), 4326);
緯度経度情報から直接Geometryを作成する関数はないため、一度Geograpyオブジェクトを作った後にそれをGeometryに変換しています。
GeograpyとGeometryを変換する関数はないため、公式ドキュメントにあるように一度GeoJSONを挟む形で変換します。
地理空間データ型をWKTで出力する
地理空間データ型でデータベースに登録されているメッシュやポリゴンデータをQGISで表示したい場合にはWKTに変換するのがおすすめです。
CSVで出力すれば、QGISはWKTカラムを参照してポリゴンなどを描画してくれます。
変換はST_ASTEXT()関数でサクッとできます。
例えば、1kmメッシュごとの集計結果をQGISで描画したいというときには、以下のような感じで集計結果をCSVで出力しています。
with temp as (
-- メッシュごとに何かしらの集計
)
select
mesh_1km, -- meshID等
count, -- 何かしらの集計結果
st_aswkt(m.geom) as wkt -- geometryをwktに変換
from
temp t
join mesh.mesh_1km m -- メッシュテーブルを別途用意してある想定
on t.mesh_1km = m.mesh_id
;
範囲内の地物を抽出する
特定のエリア内にあるログを抽出するときにはST_Within()をよく利用します。
例えば、areaというポリゴンテーブルの中にあるログをsampleテーブルから抽出したいときには、以下のようにします。
select
s.*
from
sample s
join area a
on st_within(s.geom, a.geom)
;
また、ポイントやラインから一定のエリア内にあるものを抽出するときにはST_DWithin()を利用します。
例えば、複数の道路ラインから25m以内にあるログをsampleテーブル抽出して、道路ごとのログ数をカウントするみたいなときには、以下のようにします。（ついでにWKTを付与して、QGISで可視化もできるようにしています。）
select
road_name,
count(*) as log_count,
st_aswkt(r.geom) as wkt -- QGIS可視化用に道路ラインのwktを付与
from
sample s
join road_line r
on st_dwithin( -- st_dwithinはgeography型のみ可
to_geography(ST_ASGEOJSON(s.geom)),
to_geography(ST_ASGEOJSON(r.geom)),
25) -- 第3引数はメートルで指定
group by
road_name,
wkt
;
ポイント間の距離を計算する
ポイント間の距離を計算するにはST_Distance()を利用します。
例えば、時系列になっているGPSログデータに対して、ログ間の距離（移動距離）を計算するには以下のようにします。
select
id,
log_time,
ST_Distance(
to_geography(ST_ASGEOJSON(geom)),
to_geography(lag(ST_ASGEOJSON(geom)) OVER (PARTITION BY id ORDER BY log_time))
) as distance
from sample
;
ポイント間のラインを作成する
ポイント間のラインを作成するにはST_MakeLine()を利用します。
例えば、From-To間のラインを作るには以下のようにします。（fromとtoのポイントのgeometryが付与されている想定）
select
from_area,
to_area,
ST_MakeLine(from_geom, to_geom)
from
sample
;
おわりに
Snowflakeの地理空間データへの対応はどんどんバージョンアップしているようです。
最近ではH3メッシュに関連する関数も利用できるようになっています。
H3メッシュを利用した解析は以前からチャレンジしたいと思っているもののあまりトライできていないので、これを期に触ってみたいと思います！