BigQueryで大きめの地理情報を取り扱う

　BigQueryのGIS機能について、実践的なデータ・セットを導入の方法について記述します。

　 BigQueryのGIS機能の導入についてはこちら、BigQuery GIS Alpha program を試してみた　~ 登録からGeoVizの地図表示まで ~

環境

PC : MacBook Pro (Retina, 13-inch, Mid 2014)
Webブラウザ : Google Chrome 68.0.3440.106 （64 ビット）
QGIS：2.18.15
PostgreSQL: 9.6.2

利用データ

国土数値情報　行政区域データ http://nlftp.mlit.go.jp/ksj/gml/datalist/KsjTmplt-N03-v2_3.html

CSVフォーマットに出力時のサイズは300MB程度のデータです

データの流れ

今回のデータフローを図示します

空間情報をWKTに変換するためにDBサーバ（PostgreSQL）を利用します
BigQueryでは１０MB以上は直接入力が出来ないので、Google Cloud Storage にデータをアップロードします
BigQueryにはGEOGRAPHY型は無く、String型で登録します

１. データ準備

1.1. 収集

BigQueryのGIS関数は、WKTまたはGeoJSON形式に対応しています。今回はWell Known Text( WKT )に変換する方法を列挙します

1.1.1. ダウンロード
行政区域データをダウンロードしてください ( アンケートには丁寧に回答しましょう）
1.1.2. ZIPファイルを解凍
1.1.3. QGISでshapeファイルの読み込み
1.1.4. QGISのデータベースツールを用いてPostgreSQLにデータロード

上記の手順は過去の記事を参考にしていただければと思います。

1.2. データ加工

BigQueryのGIS関数に読み込む為、WKTに加工します。
PostgreSQLに備わっているCOPYを用いて、SQLの実行結果をCSVファイルとして出力します。

SQL ： WKTに変換しCSVで出力

output_csvdata.sql

COPY ( 
    SELECT
         gid -- 行ID（QGISのDBツールで付与した）
        , n03_001 -- 都道府県名
        , n03_002 -- 支庁・振興局名
        , n03_003 -- 郡・政令都市名
        , n03_004 -- 市区町村名
        , n03_007 -- 行政区域コード
        ,ST_AsText(geom) AS wkt --WKTへ変換した空間情報
    FROM public.city_map
)
TO '/hoge/japan_city_polygon_h.csv' --ファイル出力先(パーミッションに注意）
WITH csv HEADER ; -- CSV形式かつ１行目に列名ヘッダを出力

2.データ転送

BigQueryでは大きなファイルをWebブラウザから直接読み込み出来ないため、オブジェクトストレージ（GCS）にCSVをアップロードします。

2.0. バケットの作成

最初に利用する場合や必要に応じて、GCSにバゲットを作成します。

2.1. データ転送

CSVをバケットにアップロードします。
BigQueryから参照しますが、「非公開」設定で問題ありません。
アップロードしたいバケットを指定して、ファイルをアップロードします。

GCSのGUIで操作が可能です。

3. BigQueryのデータベースの構築

BigQueryのデータベースの構築は、Webブラウザ上のGUIから操作可能です。

3.1. データセットの作成

複数のテーブルを１つにまとめる概念である「データセット」が無い場合は作成します。
データセットはRDBでいうデータベースに近い概念です。

サブウインドウが表示されたら、データセットID（名称）を入力します。
ロケーションは、GCSのロケーションと同じにしましょう。

3.2. テーブルの作成

データセットが作成されると、左側のリストにプロジェクトの下に、データセットが表示されます。
テーブル作成すると、テーブル名が表示されるようになります。

データセットが出来たらテーブルを作成します。
GCSにアップロードしたCSVを用いますので、ソースデータは「Google Cloud Storage」を選択し、アップロードしたCSVファイルを選択します。

・スキーマ設定は自動
テーブルの列（スキーマ）の定義は、1列ずつ定義することも出来ますが、今回のCSVは自動で問題なく動作しました。

・詳細オプション
今回は、1行目が列名が出力されたCSV形式なので、「詳細オプション」から「スキップするヘッダー行」を1に設定しました。

4. GeoVizでコロプレスマップの作成

BigQuery Geo Vizにアクセスして、コロプレスマップを作成ます。

4.1. SQLの作成

BigQueryに登録したテーブルを用いて、空間情報処理を実施するSQLを作成します。

例）面積と重心の計算を行うSQL

kanagawa_Choropleth_Map.sql

SELECT 
  gid 
  , n03_001 
  , n03_002 
  , n03_003 
  , n03_004 
  , ST_GeogFromText(wkt) AS geo --WKT文字列からGeographyに変換
  , FLOOR(ST_Area( ST_GeogFromText(wkt) ) /1000000)  AS area --ST_Areaで面積計算し、平方kmに変換
  , ST_Centroid( ST_GeogFromText(wkt) ) AS center_point --ST_Centroidで重心計算
FROM 
 gis.japan_city_polygon
WHERE 
 n03_001 ='神奈川県' --日本全国から神奈川県に限定
 AND
 FLOOR(ST_Area( ST_GeogFromText(wkt) ) /1000000) > 1 --1平方キロ未満の島嶼部を切り落とす

SQLを入力
「Run」を押下しSQLを実行
クエリが成功した場合、「See Results」を押下します

4.2. カラムの設定

空間情報(WKT形式)のカラムを選択します。
今回は「geo」がそれに該当します。

4.３. スタイルの設定

コロプレスマップ（階級区分図）として、M値（計測値）を地図表現に反映します。今回は、面積を色合いに反映してみます。

4.3.1. 塗りつぶしの設定

fillcolorのパラメータを設定します。

1) SQLの実行結果の値を利用する場合、Data-drivenをチェック
2) Fieldから地図表現に反映するカラム（area)を選択

完成

最後までご覧頂きありがとうございます。

BigQuery GIS GeoViz お試しレポート ~ 国土数値情報を用いたコロプレスマップ作成~