TatsuyaのテクTechブログ

IT技術を中心に勉強したことを書くブログ

BigQuery GISでライブのツアーマップを作ってみた

BigQuery GIS(以下、GIS)がbetaになっていたので触ってみるついでに、ファンであるアーティストのライブツアーマップを作ったので、その過程を残しておきます。

 作成後のツイート

 

目次

  • BigQuery GISとは
  • マップのアイデア
  • データ作成
  • テーブル・クエリ作成
  • BigQuery Geo Vizでマップ化
  • 所感

BigQuery GISとは

 BigQueryで地理情報を扱うための機能で、以下(おそらく)2機能が提供されています。どちらも現状(2018/10/07)はbetaになります。

  1. Geography Functions(地理関数)
  2. 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から取ってきました。

sp.pornograffitti.jp

mapfan.com

 

 データ入力できたら、CSVでエクスポートです。

f:id:dolphin-jump1011:20181007192446p:plain

テーブル・クエリ作成

 BigQueryでのテーブル作成ですが、データはCSVファイルのアップロード、スキーマCSVファイルの自動検出で作成しています。自動検知が不安でしたが、以下画像の様に経緯度もFLOAT型になっており、問題なさそうです。

f:id:dolphin-jump1011:20181007193154p:plain

 

 クエリは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で問題なく実行・抽出できることを確認しておきます。

f:id:dolphin-jump1011:20181007195449p:plain


BigQuery Geo Vizでマップ化

 データとクエリの準備ができたら、Geo VizのWEB UIを開きましょう。BigQuery Web UIにはリンクがないようなので、こちらに貼っておきます。最初のアクセスではアカウント認証が求められます。

BigQuery Geo Viz

 Geo Vizで行う作業は以下3つです。Web UIの左メニューにある項目と一致させています。

  1. Select Data
  2. Define columns
  3. Style

 Select DataではGeo Vizで扱う情報をBigQueryから抽出するためのクエリを書いて実行します。先ほど作成したクエリと抽出元になるGCPのプロジェクトIDを選択してクエリを実行します。

f:id:dolphin-jump1011:20181007202041p:plain

GCPのプロジェクトID部分は塗りつぶしてます。

 

 Define columnsはクエリの実行結果の確認と、マップにプロットするためのカラム選択をします。今回はWKTカラムがそれに該当するので、WKTカラムを選択。正しければ、マップのプロットが表示されます。

f:id:dolphin-jump1011:20181007201610p:plain

赤枠がカラム選択をする場所。

 

 Styleではクエリで抽出した情報を使って色の勾配、ポイントのサイズ可変などビジュアライズの調整ができます。今回はentory_flg(参戦フラグ)を使って、参戦する会場は赤色、参戦しない会場は青色にしてみました。

f:id:dolphin-jump1011:20181007201917p:plain

完成!

所感

 BigQuery GISを初めて使ってみましたが、BigQueryでできることがまた一つ増えたなという感じです。(よく知らないけど)GeoJSONを扱えるので地理情報の蓄積や集計処理に使ったりもできそうです。あとは、この地理情報データを使って視覚化できるツールが増えてくれると良いなと思いますね。

 

参考

All BigQuery GIS Documentation  |  BigQuery  |  Google Cloud

測地系 - Wikipedia