こんにちは、ナイトレイインターン生の鈴木です。
Wantedlyをご覧の方に、ナイトレイのエンジニアがどのようなことをしているか知っていただきたく、Qiitaに公開している記事をストーリーに載せています。

今回はGISエンジニアメンバー徳竹さんの記事です。
少しでも私たちに興味を持ってくれた方は下に表示される募集記事もご覧ください↓↓

はじめに

Snowflakeでは地理空間関数を利用して位置情報データの分析ができます。
普段よく利用している地理空間関数を紹介したいと思います！

緯度経度から地理空間データ型を生成

緯度経度（テキスト）から地理空間データを作成します。地理空間データ型を作成しておくと、後々の処理が楽です。

user_id, log_time, longitude, latitudeの4つのカラムを持っているテーブルを例に、Geometry型のカラムを作ります。

はじめに、geomという名称のGeometry型のカラムを追加します。

ALTER TABLE sample ADD COLUMN geom geometry;;

続いて、緯度経度（longitude, latitude）を用いてGeometryを作ります。
以下のクエリでは以下の4ステップを行っています。

1. st_makepoint()で緯度経度からGeograpyオブジェクトを構築する。
2. st_asgeojson()でGeograpyオブジェクトをGeoJSON化する。
3. st_geometry()でGeoJSONをGeometry化する。
4. 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メッシュを利用した解析は以前からチャレンジしたいと思っているもののあまりトライできていないので、これを期に触ってみたいと思います！

採用から

私たちの会社、ナイトレイでは一緒に事業を盛り上げてくれるエンジニアを募集しています！
Web開発メンバー、GISエンジニア、サーバーサイドエンジニアなど複数ポジションで募集しているため、
「専攻分野を活かしたい」「横断的に様々な業務にチャレンジしてみたい」と言ったご要望も相談可能です！

✔︎ GISの使用経験があり、観光・まちづくり・交通・防災系などの分野でスキルを活かしてみたい
✔︎ ビッグデータの処理が好き！（達成感を感じられる）
✔︎ データベース構築、サーバー周りを触るのが好き
✔︎ 社内メンバーだけではなく顧客とのやり取りも実はけっこう好き
✔︎ 自社Webサービスの開発で事業の発展に携わってみたい
✔︎ 地理や地図が好きで、位置情報データにも興味を持っている

一つでも当てはまる方は是非お気軽にエントリーください！