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

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

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

　 BigQueryのGIS機能の導入についてはこちら、[BigQuery GIS Alpha program を試してみた　~ 登録からGeoVizの地図表示まで ~](https://qiita.com/takahi/items/d37dce265c28528bb3a5)

## 環境
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程度のデータです

# データの流れ
今回のデータフローを図示します
![image.png](https://qiita-image-store.s3.amazonaws.com/0/53918/c4717fba-b9e4-b4cc-2be8-eba8e14f9467.png)

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

# １. データ準備
## 1.1. 収集
BigQueryのGIS関数は、WKTまたはGeoJSON形式に対応しています。今回はWell Known Text( WKT )に変換する方法を列挙します

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

上記の手順は[過去の記事](https://qiita.com/takahi/items/9d44966b10d6257d8b2d)を参考にしていただければと思います。


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

### SQL ： WKTに変換しCSVで出力
```sql: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にバゲットを作成します。
<img src="https://qiita-image-store.s3.amazonaws.com/0/53918/fb0c5af3-de64-5be7-3997-2306d3dec9f1.png" with='200px'>

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

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

<img src="https://qiita-image-store.s3.amazonaws.com/0/53918/0c708a37-8e11-02e3-abbd-d67fcd312190.png" with='200px'>

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


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

![image.png](https://qiita-image-store.s3.amazonaws.com/0/53918/23345b81-e970-8553-3f36-a4e676da7bca.png)

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

<img src="https://qiita-image-store.s3.amazonaws.com/0/53918/1bab9f5d-2051-dc64-7ffd-d46b6dcce345.png" width='350px' />


## 3.2. テーブルの作成

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

<img src="https://qiita-image-store.s3.amazonaws.com/0/53918/6ef69456-174c-8e79-7bef-1a525bf4a8a5.png" width='350px' />

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

<img src="https://qiita-image-store.s3.amazonaws.com/0/53918/9617a752-f428-186f-cfd7-af71baec0e54.png" width='380px' />


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

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

# 4. GeoVizでコロプレスマップの作成
[BigQuery Geo Viz](https://bigquerygeoviz.appspot.com/)にアクセスして、コロプレスマップを作成ます。

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

例）面積と重心の計算を行う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」がそれに該当します。

<img src="https://qiita-image-store.s3.amazonaws.com/0/53918/4fb98ccb-9ee8-a31d-de6b-cd75dc7d5d0b.png" width='300px' />


## 4.３. スタイルの設定

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

### 4.3.1. 塗りつぶしの設定

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

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

<img src="https://qiita-image-store.s3.amazonaws.com/0/53918/d356086c-a96c-e5b6-e449-25c5d6974b89.png" width='300px' />


## 完成 
<img src="https://qiita-image-store.s3.amazonaws.com/0/53918/2dfcfe9b-9a38-ca89-4ffd-7e1a85fdf5b8.png" width='４00px' />

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