BigQuery GIS(以下、GIS)がbetaになっていたので触ってみるついでに、ファンであるアーティストのライブツアーマップを作ったので、その過程を残しておきます。
betaになってたBIgQuery GISを使ってポルノグラフィティ16thサーキットのマップを作ってみた。Geo vizは地図専用BIだな。 #bigquery pic.twitter.com/CG1H3MP2ml
— タツヤ (@stand_arrow) 2018年10月6日
昨日のBigQuery GISによるポルノ16thツアーマップを少しアップデート。参戦フラグを追加して、参戦有無で色分けできるようにした。#bigquery pic.twitter.com/Meu4sz7OXu
— タツヤ (@stand_arrow) 2018年10月7日
作成後のツイート
目次
BigQuery GISとは
BigQueryで地理情報を扱うための機能で、以下(おそらく)2機能が提供されています。どちらも現状(2018/10/07)はbetaになります。
- Geography Functions(地理関数)
- Geo Viz
Geography Functions(以下、地理関数)は標準SQLに追加されたSQL関数です。GIS追加に伴い、地理情報のための新データ型としてGEOGRAPHY型が追加されているのですが、BigQueryに既にある測地系情報(経緯度等)を地理関数を使ってGEOGRAPHY型に変換したりできます。
Geo VizはGIS用の地図BIツールのようです。GEOGRAPHY型の座標データを含むSQLクエリの結果を使って地図にポイントをプロットできたりします。
マップのアイデア
今回、GISを試してみるにあたり、チュートリアルをやるだけではつまらないなと思い、何かマップ化したいと考えました。何か考えていた当日(2018/10/6)に、ファンであるポルノグラフィティの16thライブサーキット(ライブツアーのこと)のファンクラブ先行当選メールが届いて、この機会にツアー会場をマップ化してみようと思ったわけです。
データ作成
というわけで、ポルノグラフィティの16thライブサーキットのツアー会場マップ化のためにまずデータ作成です。今回のデータ作成には、Googleスプレッドシートを使っています。16thライブサーキットは全部で15公演と手動で作成できる量であり、BigQueryでインポート可能なCSV形式をエクスポートできるためです。BigQueryのスキーマを意識して以下のカラムを用意してデータを準備します。
- Date:公演日
- place:会場名
- latitude:緯度
- longitude:経度
- state:県
- city:市
- address:住所(区町村番地)
- artist:アーティスト名(ポルノグラフィティで固定)
- title:ツアー名(16thライヴサーキットで固定)
- entry_flg:参戦フラグ(1=参戦、0=不参戦)
- other:備考欄
ここで注意するのは緯度と経度です。GISで対応している測地系は世界測地系(WGS84)です。国内だと、世界測地系と日本測地系の2種類が使われているので誤って後者を使わないようにしましょう。マップ化するときに誤差が出てくると思います。
ツアーの日程と会場情報は公式サイト、経緯度情報はMapFanから取ってきました。
データ入力できたら、CSVでエクスポートです。
テーブル・クエリ作成
BigQueryでのテーブル作成ですが、データはCSVファイルのアップロード、スキーマはCSVファイルの自動検出で作成しています。自動検知が不安でしたが、以下画像の様に経緯度もFLOAT型になっており、問題なさそうです。
クエリはGeo Vizで使うために作成します。今回はプロット時に表示したい情報の抽出と経緯度をGEOGRAPHY型に変換するために地理関数からST_GeogPoint()を使ったクエリになります。entory_flgをORDERBYしているのはGeo Vizで色分けをするための苦肉の策です(詳細は省略)。
クエリ:
SELECT
date,
place,
entry_flg,
ST_GeogPoint(longitude, latitude) AS WKT
FROM
`pornograffitti_live_tour.16thlivecircuit`
ORDER BY
entry_flg
クエリはBigQueryのQueryEditorで問題なく実行・抽出できることを確認しておきます。
BigQuery Geo Vizでマップ化
データとクエリの準備ができたら、Geo VizのWEB UIを開きましょう。BigQuery Web UIにはリンクがないようなので、こちらに貼っておきます。最初のアクセスではアカウント認証が求められます。
Geo Vizで行う作業は以下3つです。Web UIの左メニューにある項目と一致させています。
- Select Data
- Define columns
- Style
Select DataではGeo Vizで扱う情報をBigQueryから抽出するためのクエリを書いて実行します。先ほど作成したクエリと抽出元になるGCPのプロジェクトIDを選択してクエリを実行します。
GCPのプロジェクトID部分は塗りつぶしてます。
Define columnsはクエリの実行結果の確認と、マップにプロットするためのカラム選択をします。今回はWKTカラムがそれに該当するので、WKTカラムを選択。正しければ、マップのプロットが表示されます。
赤枠がカラム選択をする場所。
Styleではクエリで抽出した情報を使って色の勾配、ポイントのサイズ可変などビジュアライズの調整ができます。今回はentory_flg(参戦フラグ)を使って、参戦する会場は赤色、参戦しない会場は青色にしてみました。
完成!
所感
BigQuery GISを初めて使ってみましたが、BigQueryでできることがまた一つ増えたなという感じです。(よく知らないけど)GeoJSONを扱えるので地理情報の蓄積や集計処理に使ったりもできそうです。あとは、この地理情報データを使って視覚化できるツールが増えてくれると良いなと思いますね。