Zenn
DATUM STUDIOPublicationへの投稿
❄️

【Snowflake】地理データの結合を列で実現したい

hama
2023/12/08に公開
Python
GIS
Snowflake
wkt
tech

概要(背景・目的)

地理データで同じようなポリゴン達の列を結合したいがST_COLLECT関数だとただ並べるだけなので重複を排除できない。その結果、レコードに乗らないデータが錬成されてしまう。ST_UNIONという関数もあるがこちらは引数が2列必要であり列の処理に向いていない。(入力までの準備が難しい)
なので、重複を削除しつつレコードで表示できるような処理をUDFsで解決していく。

関連資料

作業概要

  1. ダミーデータづくり
  2. 変換するための関数説明
  3. 重複削除の結果確認

作業詳細

1. ダミーデータ作り

SnowflakeのST_UNIONにあるサンプルコードを参考に作成

CREATE OR REPLACE TABLE GEO3 (ID NUMBER, G1 GEOGRAPHY);
INSERT INTO GEO3 (ID, G1) VALUES
    (1, 'POLYGON((0 0,1 0,2 1,1 2,2 2.999999999999986,1 4,0 4,0 0))')
    ,(2, 'POLYGON((3 0,3 4,2 4,1 2.999999999999986,2 2,1 1,2 0,3 0))')
    ,(2, 'POLYGON((3 0,3 4,2 4,1 2.999999999999986,2 2,1 1,2 0,3 0))')
    ,(3, 'POLYGON((5 0,3 4,2 4,1 2.999999999999986,2 2,1 1,2 0,5 0))')
    ,(3, 'POLYGON((5 0,3 4,2 4,1 2.999999999999986,2 2,1 1,2 0,5 0))')
    ,(3, 'POLYGON((5 0,3 4,2 4,1 2.999999999999986,2 2,1 1,2 0,5 0))')
;

今回はid=3に着目してこちらでプロットしてみる。

2. 変換するための関数説明

How to use Python UDFs for Geospatial use cases in Snowflakeにある関数をそのまま用いる。引数は配列を想定しているのでGeoJSONの配列で入力できるよう調整が必要。(元はPython 3.8だが3.9でも問題なく動作する)

CREATE OR REPLACE FUNCTION PY_UNION_AGG(G1 ARRAY)
RETURNS GEOGRAPHY
LANGUAGE PYTHON
RUNTIME_VERSION = 3.9
PACKAGES = ('shapely')
HANDLER = 'udf'
AS $$
from shapely.ops import unary_union
from shapely.geometry import shape, mapping
def udf(g1):
    shape_union = unary_union([shape(i) for i in g1])
    return mapping(shape_union)
$$;

3. 重複削除の結果確認

テーブルを新しく作り確認をする。念の為ST_COLLECTとの差分も見ておく。

ST_COLLECTの場合

id=3のポリゴンは3重になり、データとしても重く表示も重なっているので濃くなりますね。

CREATE OR REPLACE TABLE GEO4 AS 
SELECT ID, ST_COLLECT(G1) AS ST_COL_G1
FROM GEO3
GROUP BY ID
;

SELECT ID, ST_ASWKT(ST_COL_G1) FROM GEO4
;

ID	ST_ASWKT(ST_COL_G1)
3	MULTIPOLYGON(((5 0,3 4,2 4,1 3,2 2,1 1,2 0,5 0)),((5 0,3 4,2 4,1 3,2 2,1 1,2 0,5 0)),((5 0,3 4,2 4,1 3,2 2,1 1,2 0,5 0)))
2	MULTIPOLYGON(((3 0,3 4,2 4,1 3,2 2,1 1,2 0,3 0)),((3 0,3 4,2 4,1 3,2 2,1 1,2 0,3 0)))
1	MULTIPOLYGON(((0 0,1 0,2 1,1 2,2 3,1 4,0 4,0 0)))

Python UDFsで作成した関数の場合

id=3のポリゴンは1つとなり順序は異なりますが、重複を削除した同じ図形であることがわかります。

CREATE OR REPLACE TABLE GEO5 AS 
SELECT ID, PY_UNION_AGG(ARRAY_AGG(ST_ASGEOJSON(G1))) AS PY_UNION_G1
FROM GEO3
GROUP BY ID
;

SELECT ID, ST_ASWKT(PY_UNION_G1) FROM GEO5
;

ID	ST_ASWKT(PY_UNION_G1)
3	POLYGON((3 4,5 0,2 0,1 1,2 2,1 3,2 4,3 4))
2	POLYGON((3 4,3 0,2 0,1 1,2 2,1 3,2 4,3 4))
1	POLYGON((0 0,1 0,2 1,1 2,2 3,1 4,0 4,0 0))

おまけ (3種類のポリゴンを重ね合わせてみた)

もちろん一致していることを確認

さいごに

色々悩みながらごにょごにょやってたら身近に答えが落ちていたパターン。地理データはデカい・チェックがムズい・規格が多いなので困惑しやすいのですがSnowflakeで適切に処理していきたいと思います。
ST_UNIONで列の和集合取らせて・・・

DATUM STUDIO
DATUM STUDIOPublication

Discussion