Snowflakeでランダムな位置情報データを生成してみた

はじめに

Snowflakeでは、地理空間データを扱う機能がサポートされており、日々アップデートされています。今回、地理空間を扱う各機能の性能面についてざっと確認したかったので、ランダムな位置情報データをSnowflake内で生成してみました。

環境

Snowflake standard edition (November 11-14, 2023)
Snowsight + DBeaver（Geoデータ可視化に使用）

ランダムデータの作成方針

ランダムとはいえ、位置情報なので、落ちてほしい範囲を決めて作ることにしました。
東京（離島を除く）範囲で作りたい・・・ところですが、Snowflakeには、PostgreSQL + PostGISのST_GeneratePointsのような便利な関数はないので(マーケットプレイスで無料で使えるCarto Analytics ToolboxにてST_GeneratePoints()が用意されておりました！ありがたい・・・)、ひよって、東京（離島を除く）範囲を含む最小のバウンディングボックス内で、緯度経度をランダムに生成させることにします。

都道府県ポリゴン

今回はマーケットプレイスにて、Truestar社が無料公開してくださっているPODB都道府県データを使わせていただきました。
参考：マーケットプレイスリンク
都道府県データをサクッと使わせていただけるの大変ありがたいことです。
（国土数値情報>行政区域データからいい感じのデータを作ってSnowflakeに入れるのは結構大変です。。）

ランダム位置情報データを生成

sonwflakeでのデータ生成は、uniform関数で行えます。

Snowflake Doc uniform | https://docs.snowflake.com/ja/sql-reference/functions/unifDoc

ということで、東京都ポリゴンデータより矩形情報を取得しつつ、uniformでランダムに位置情報を作成してみます。

-- PODBより都道府県ポリゴン（離島無し）のデータから、東京都の矩形データを取得する
with t as (
select
    st_xmin(POLYGON_JP_PREF_2_LIGHT) as xmin,
    st_xmax(POLYGON_JP_PREF_2_LIGHT) as xmax,
    st_ymin(POLYGON_JP_PREF_2_LIGHT) as ymin,
    st_ymax(POLYGON_JP_PREF_2_LIGHT) as ymax
from
    -- 都道府県の基礎データ | 2020年 テーブル
    PREPPER_OPEN_DATA_BANK__JAPANESE_PREFECTURE_DATA.E_PODB.E_PR_FD20
where
    PREF_CODE= 13 -- 東京都
)
select 
  	st_point(
  		uniform(xmin, xmax, random()),
  		uniform(ymin, ymax, random())
	) AS geog
  FROM
    t,
 	table(generator(rowCount => 100000000))
;

…がエラーになってしまいました。

argument 1 to function UNIFORM needs to be constant, found 'SYS_VW.XMIN_1'

uniform関数に渡す引数は定数でなければならないとのこと。
CTEでの取得結果をつなぐのは無理そう。今回はsnowsightで作業していたため、SETで矩形情報を変数に結入れて、渡すことでエラー回避しました。

-- 一回矩形情報をsetで変数に入れておく。
set (xmin,xmax,ymin,ymax) = (
select
    st_xmin(POLYGON_JP_PREF_2_LIGHT) as xmin,
    st_xmax(POLYGON_JP_PREF_2_LIGHT) as xmax,
    st_ymin(POLYGON_JP_PREF_2_LIGHT) as ymin,
    st_ymax(POLYGON_JP_PREF_2_LIGHT) as ymax
from
    -- 都道府県の基礎データ | 2020年 テーブル
    PREPPER_OPEN_DATA_BANK__JAPANESE_PREFECTURE_DATA.E_PODB.E_PR_FD20
where
    PREF_CODE= 13 -- 東京都
);

-- １億レコード程、テーブルに挿入
create table dummy_tokyo_point as 
select 
  	st_point(
  		uniform($xmin, $xmax, random()),
  		uniform($ymin, $ymax, random())
	) AS geog
  FROM
 	table(generator(rowCount => 100000000))
;

1億レコードの挿入は、2分強で完了しました。
通常ウェアハウスはXSでしたので、10億レコード以上作る際は、もう少しサイズをあげてもよいかもしれません。

データもDBeaverのspatialデータモードで参照し、意図通り作成できていることが確認できました。
矩形単位で生成しているので、若干海に落ちてますね。

まとめ

Snowflakeで、位置情報のランダムポイントデータを生成してみました。
Snowflake内部のリソースを使って生成、インポートを行うことで、効率よくデータ生成ができたかと思います。
よりユースケースにマッチした複雑なランダムデータを作成する場合、Snowflakeは、Pythonも使えますので、そちらも検討したほうが良いと思います。
地理空間データを試す際、オープンデータやSnowflakeマーケットプレイスで無料で試すことができるものがあり、大変ありがたいと感じます。実際の検証ではそういったものを掛け合わせて進めていきたいと感じました！