❄️

Snowflake の地理空間データを Jupyter Notebook + Folium で簡単にビジュアライズする

2020/12/11に公開

Jupyter Notebook

この記事は Snowflake Advent Calendar 2020 の 11 日目です。

こんにちは、Snowflake でサポートエンジニアをやっている @indigo13love です。

Snowflake では、まだプレビューではありますが、地理空間データ型 (Geospatial Data Types) のサポートが提供されています。

Snowflake の Geospatial は現状 GEOGRAPHY 型、つまり任意の座標系ではなく、地球を球体と仮定したモデルにおける球面座標系のみをサポートしています (WGS 84 / EPSG:4326)。GEOMETRY 型 (デカルト座標系を含む任意の座標系) は今後サポート予定です。

プレビューの現時点でも GeoJSON, (E)WKT, (E)WKB での入出力と、標準的な ST 関数を OpenGIS 規格に沿ってサポートしており、Geospatial なデータ処理は十分に行えるようになっています。しかし、現状 Snowflake ネイティブで地図上にビジュアライゼーションする方法が提供されていません。

そこで、この記事では、インタラクティブなデータ分析ツールである Jupyter Notebook と、マップを扱える JavaScript ライブラリである Leaflet.js を Python から扱えるようにした Folium を使って、Snowflake から読んだデータを直接簡単にビジュアライズしてみます。

(本記事は所属する組織の公式見解ではなく、個人的なプラクティスの共有になります。)

実行環境を準備する

今回は pipenv を使って、なるべく環境を汚さないようにやっていきます。別に気にしないという方は pip install で直接システムワイドに入れてしまって問題ないです。

準備が長くてめんどくさいなーと思ったかもしれませんが、Jupyter Notebook は今回のデモだけでなく Python でのテスト環境として便利ですし、GDAL は地理空間データを扱う上ではほぼ必須になってくるので、今後も使い回せると思ってやっていきましょう。

もちろん、Jupyter Notebook と Snowflake Connector for Python と　Folium をセットアップ済みの人は「データを準備する」まで移動してしまって問題ありません。

1. pipenv のインストール

下記のドキュメントの通り、Homebrew もしくは pip でインストールできます。

$ brew install pipenv

または、

$ pip install --user pipenv

2. ワーキングディレクトリの作成

pipenv を使うにせよ使わないにせよ、Jupyter Notebook が使うディレクトリが必要なので、適当にワーキングディレクトリを作って、その中に入ります。

$ mkdir jupyter-sf-map
$ cd jupyter-sf-map

3. 必要なパッケージのインストール

今回のデモで必要なのは Jupyter Notebook (jupyter), Snowflake Connector for Python (snowflake-connector-python), Folium (folium) の 3 つです。

まずは pipenv で仮想環境を作っていきます。このとき、必ず Python 3.8 以前を選択してください。 Snowflake Connector for Python は Python 3.9 をまだサポートしておらず、インストールに失敗します。

$ pipenv --python 3.8
Creating a virtualenv for this project...
Pipfile: /Users/ymatsuzaki/demo/jupyter-sf-map/Pipfile
Using /usr/local/opt/python@3.8/bin/python3.8 (3.8.6) to create virtualenv...
⠦ Creating virtual environment...created virtual environment CPython3.8.6.final.0-64 in 328ms
  creator CPython3Posix(dest=/Users/ymatsuzaki/.local/share/virtualenvs/jupyter-sf-map-a-oJXjDt, clear=False, no_vcs_ignore=False, global=False)
  seeder FromAppData(download=False, pip=bundle, setuptools=bundle, wheel=bundle, via=copy, app_data_dir=/Users/ymatsuzaki/Library/Application Support/virtualenv)
    added seed packages: pip==20.2.4, setuptools==50.3.2, wheel==0.35.1
  activators BashActivator,CShellActivator,FishActivator,PowerShellActivator,PythonActivator,XonshActivator

✔ Successfully created virtual environment!
Virtualenv location: /Users/ymatsuzaki/.local/share/virtualenvs/jupyter-sf-map-a-oJXjDt
Creating a Pipfile for this project...

その後、必要なパッケージをインストールしていきます(依存パッケージが多いのでちょっと時間がかかります)。

$ pipenv install jupyter snowflake-connector-python folium
Installing jupyter...
Adding jupyter to Pipfile's [packages]...
✔ Installation Succeeded
Installing snowflake-connector-python...
Adding snowflake-connector-python to Pipfile's [packages]...
✔ Installation Succeeded
Installing folium...
Adding folium to Pipfile's [packages]...
✔ Installation Succeeded
Pipfile.lock not found, creating...
Locking [dev-packages] dependencies...
Locking [packages] dependencies...
Building requirements...
Resolving dependencies...
✔ Success!
Updated Pipfile.lock (de4312)!
Installing dependencies from Pipfile.lock (de4312)...
  🐍   ▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉ 0/0 — 00:00:00
To activate this project's virtualenv, run pipenv shell.
Alternatively, run a command inside the virtualenv with pipenv run.

これでパッケージの実行環境の準備は完了です。

4. ogr2ogr のインストール

Snowflake に Geospatial データを投入する際には、大抵の場合でデータ形式を GeoJSON に変換するために ogr2ogr が必要になります。 ogr2ogr は GDAL (Geospatial Data Abstraction Library) の中に入っているので、GDAL のインストールをしましょう。

$ brew install gdal
...
==> Installing gdal
==> Pouring gdal-3.2.0_1.catalina.bottle.tar.gz
Warning: gdal dependency gcc was built with a different C++ standard
library (libstdc++ from clang). This may cause problems at runtime.
🍺  /usr/local/Cellar/gdal/3.2.0_1: 296 files, 57.6MB

これで ogr2ogr が使えるようになります。

$ ogr2ogr --version
GDAL 3.2.0, released 2020/10/26

5. Jupyter Notebook を起動する

jupyter notebook コマンドを pipenv run 経由で実行するだけで Jupyter Notebook が起動し、ブラウザにノートブックが開かれます。

$ pipenv run jupyter notebook
[I 11:11:32.633 NotebookApp] ローカルディレクトリからノートブックをサーブ: /Users/ymatsuzaki/demo/jupyter-sf-map
[I 11:11:32.633 NotebookApp] Jupyter Notebook 6.1.5 is running at:
[I 11:11:32.633 NotebookApp] http://localhost:8888/?token=c375160381cb0f5f8ec2d4dccfcb18884c1899d4c458f678
[I 11:11:32.633 NotebookApp]  or http://127.0.0.1:8888/?token=c375160381cb0f5f8ec2d4dccfcb18884c1899d4c458f678
[I 11:11:32.633 NotebookApp] サーバを停止し全てのカーネルをシャットダウンするには Control-C を使って下さい(確認をスキップするには2回)。
[C 11:11:32.638 NotebookApp]

    To access the notebook, open this file in a browser:
        file:///Users/ymatsuzaki/Library/Jupyter/runtime/nbserver-31143-open.html
    Or copy and paste one of these URLs:
        http://localhost:8888/?token=c375160381cb0f5f8ec2d4dccfcb18884c1899d4c458f678
     or http://127.0.0.1:8888/?token=c375160381cb0f5f8ec2d4dccfcb18884c1899d4c458f678

データを準備する

1. データの取得と前処理

今回はサンプルデータとして新宿区オープンデータカタログサイトで公開されている新宿区道路線網を使用します。

まず、上記ページの「新宿区道路線網」から Zip ファイルをダウンロード、解凍し、そのディレクトリに移動します。

$ cd cd Downloads/000259916
$ ll
total 8072
-rw-rw-r--@ 1 ymatsuzaki  staff  2363650  3 27  2019 認定.dbf
-rw-rw-r--@ 1 ymatsuzaki  staff      410  3 27  2019 認定.prj
-rw-rw-r--@ 1 ymatsuzaki  staff   277356  3 27  2019 認定.shp
-rw-rw-r--@ 1 ymatsuzaki  staff    14508  3 27  2019 認定.shx
-rw-rw-r--@ 1 ymatsuzaki  staff     4833  3 27  2019 認定.xml

認定なんちゃらという謎のファイル名のファイルが並んでいますが、「認定.shp」が Shapefile、つまり GIS 情報が格納されているファイルです。

まずは、この Shapefile を GeoJSON に変換しましょう。

$ ogr2ogr -f geojsonseq shinjuku-roads.geojson 認定.shp

-f は出力フォーマットを指定するオプションですが、ここでは GeoJSONSeq を指定します。これは GeoJSON に出力する際に NDJSON (New-line Delimited JSON / 改行区切りの JSON) で出力してくれます。Snowflake は NDJSON をネイティブにサポートしているので、ロード時に扱いやすくておすすめです。

それでは生成した shinjuku-roads.geojson の中身を覗いてみましょう。

$ head -n1 shinjuku-roads.geojson|jq .
{
  "type": "Feature",
  "properties": {
    "SAUID": 686,
    "SAUPDATE": "2019-03-27T09:37:04",
    "SAFIELD000": 686,
    "SAFIELD001": "12-200",
    "SAFIELD002": 0,
    "SAFIELD003": "1963年02月20日",
    "SAFIELD004": "2014年01月28日",
    "SAFIELD005": "新宿区西新宿四丁目272番25、272番26、272番36地内",
    "SAFIELD006": null,
    "SAFIELD007": "無",
    "SAFIELD008": 12,
    "SAFIELD009": 200,
    "SAFIELD010": null,
    "SAFIELD011": null,
    "SAFIELD012": 12,
    "SAFIELD013": 2.93,
    "SAFIELD014": 6.49,
    "SAFIELD015": "12-200",
    "SAFIELD016": "3",
    "SAFIELD017": "3",
    "SAFIELD018": null,
    "SAFIELD019": "12-200"
  },
  "geometry": {
    "type": "LineString",
    "coordinates": [
      [
        139.6869705,
        35.6866801
      ],
...
      [
        139.6858085,
        35.6897216
      ]
    ]
  }
}

geometry の下に LineString として複数の座標が格納されていることがわかります。それ以外にも、SAFIELD000, SAFIELD001 ... といったプロパティが並んでいるのがわかります。

これらのプロパティもデータとして使えそうですが、このままだと意味がわからないので「認定.xml」を確認して、各プロパティの定義を確認します。このファイルは Shift_JIS なので iconv で UTF-8 に変換して確認しましょう。

$ cat 認定.xml|iconv -f shift_jis -t utf-8
<?xml version="1.0" encoding="shift_jis"?>
<S2AConfig>
  <LayerConfig>
    <LayerNo>5001</LayerNo>
    <Primitive>20</Primitive>
    <AttributeTable>Atr05001A</AttributeTable>
    <CoordinateSystemNo>209</CoordinateSystemNo>
    <CoordinateSystemType>2000</CoordinateSystemType>
  </LayerConfig>
  <ShapeAttributeConfig>
    <Fields>
      <Field id="SAUID">
        <ColID>UserId</ColID>
      </Field>
      <Field id="SAUPDATE">
        <ColID />
      </Field>
      <Field id="SAFIELD000">
        <ColID>UserID</ColID>
        <DataType>L</DataType>
        <Title>リンクID</Title>
        <FieldSize>10</FieldSize>
        <FieldScale>0</FieldScale>
      </Field>
      <Field id="SAFIELD001">
        <ColID>ATTR001</ColID>
        <DataType>S</DataType>
        <Title>路線名</Title>
        <FieldSize>20</FieldSize>
        <FieldScale>0</FieldScale>
      </Field>
      <Field id="SAFIELD002">
        <ColID>ATTR002</ColID>
        <DataType>L</DataType>
        <Title>認定原議番号</Title>
        <FieldSize>10</FieldSize>
        <FieldScale>0</FieldScale>
      </Field>
...

いくつかのプロパティは役に立ちそうです。今回は下記のプロパティを使うことにします。

SAFIELD000: リンクID
SAFIELD001: 路線名
SAFIELD004: 供用開始年月日
SAFIELD005: 始点
SAFIELD006: 終点
SAFIELD012: 延長
SAFIELD013: 平均幅員
SAFIELD014: 面積

2. データのロード

まず、先ほどのプロパティから下記のようにテーブルを定義します。

create or replace table shinjuku_roads (
  id int,
  name varchar,
  start_date date,
  start_address varchar,
  end_address varchar,
  length double,
  average_width double,
  area double,
  geo geography
);

先ほどのプロパティに加えて、GEOGRAPHY 型のデータを格納するための geo カラムを追加しておきます。

次にデータをロードしていきます。まずは、shinjuku-roads.geojson を Snowflake にアップロードするための内部ステージを作成します。

create or replace stage stage_shinjuku_roads
file_format = (type = json);

そこに SnowSQL などのクライアントを使用してアップロードしていきます。

put file:///Users/ymatsuzaki/Downloads/000259916/shinjuku-roads.geojson @stage_shinjuku_roads

アップロードした GeoJSON ファイルは、SELECT で加工しつつ COPY コマンドでテーブルにロードすることができます。

今回のケースでは、ほぼ加工なしでいけますが yyyy年mm月dd日 を認識するために TO_DATE 関数をフォーマット引数付きで明示的に呼ぶ必要があるのと、GeoJSON を GEOGRAPHY 型としてロードするために TO_GEOGRAPHY 関数を呼ぶ必要があります。

copy into shinjuku_roads
from (select
  $1:properties:SAFIELD000 id,
  $1:properties:SAFIELD001 name,
  to_date($1:properties:SAFIELD004::varchar, 'yyyy年mm月dd日') start_date,
  $1:properties:SAFIELD005 start_address,
  $1:properties:SAFIELD006 end_address,
  $1:properties:SAFIELD012 length,
  $1:properties:SAFIELD013 average_width,
  $1:properties:SAFIELD014 area,
  to_geography($1)
from @shinjuku_roads);

これで、データの準備は完了です。いよいよビジュアライズしてみましょう。

準備したデータをビジュアライズする

先ほど開いた Jupyter Notebook のウィンドウで、右上の「新規」から「Python 3」を選んで、新しい Python 3 ノートブックを作成しましょう。

一応 GitHub にサンプルノートブックを用意してありますが、最初は 1 つずつ追いながらやってみてもいいかもしれません。

開いたらまずは Folium と Snowflake Connector for Python をインポートします。

import folium
import snowflake.connector

次に Snowflake に接続します。

conn = snowflake.connector.connect(
    user = '<user>',
    password = '<password>',
    account = '<account>',
    database = '<database>',
    schema = '<schema>',
    role = '<role>',
    warehouse = '<warehouse>'
)

接続したら、shinjuku_roads テーブルをクエリしましょう。

Snowflake Connector for Python では、接続オブジェクトから cursor() メソッドでカーソルを取得し、カーソルオブジェクトの execute() メソッドでクエリを実行します。このとき、cursor() に snowflake.connector.DictCursor を渡すと、結果に辞書としてアクセスできるようになるので便利です。

execute() はクエリ実行後のカーソルオブジェクトを返すので、fetchone(), fetchmany(), fetchall() などのメソッドを使って結果を取得します。今回は行数がそんなにないことがわかっているので fetchall() を使います。

rs = conn.cursor(snowflake.connector.DictCursor).execute('select * from shinjuku_roads').fetchall()

次に Folium の Map オブジェクトを初期化します。いい感じに新宿区が入るようなズームレベルに設定し、マップの初期位置(中心)をだいたい新宿区の真ん中ぐらいにある東新宿駅の座標に指定します。

map = folium.Map(
    location = [35.697917, 139.7075],
    zoom_start=14
)

初期化した Map オブジェクトの map に、GeoJSON として返された geo カラムを取り込んでいきます。

Folium の GeoJSON() メソッドで GeoJSON 文字列を Folium のジオメトリオブジェクトに変換し、add_to() メソッドで map に追加していきます。

for row in rs:
    folium.GeoJson(row['GEO']).add_to(map)

あとは map を参照するだけで、Folium がいい感じにインタラクティブな地図でビジュアライズしてくれます。

map

まとめ

Jupyter Notebook と Folium を使うことで、最小限のコードだけで Snowflake の地理空間データを地図上にビジュアライズできました。Jupyter Notebook のおかげでインタラクティブにいろいろ試せるので、いろいろなクエリを投げつつ、ぱぱっと絵で確認していくことができます。

Snowflake にロードした地理空間データのちょっとしたデバッグやクエリ作成に便利だと思うので、ぜひ活用してみてください。