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では10MB以上は直接入力が出来ないので、Google Cloud Storage にデータをアップロードします
- BigQueryにはGEOGRAPHY型は無く、String型で登録します
1. データ準備
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で出力
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形式かつ1行目に列名ヘッダを出力
2.データ転送
BigQueryでは大きなファイルをWebブラウザから直接読み込み出来ないため、オブジェクトストレージ(GCS)にCSVをアップロードします。
2.0. バケットの作成
最初に利用する場合や必要に応じて、GCSにバゲットを作成します。
2.1. データ転送
CSVをバケットにアップロードします。
BigQueryから参照しますが、「非公開」設定で問題ありません。
アップロードしたいバケットを指定して、ファイルをアップロードします。
GCSのGUIで操作が可能です。
3. BigQueryのデータベースの構築
BigQueryのデータベースの構築は、Webブラウザ上のGUIから操作可能です。
3.1. データセットの作成
複数のテーブルを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
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.3. スタイルの設定
コロプレスマップ(階級区分図)として、M値(計測値)を地図表現に反映します。今回は、面積を色合いに反映してみます。
4.3.1. 塗りつぶしの設定
fillcolorのパラメータを設定します。
- SQLの実行結果の値を利用する場合、Data-drivenをチェック
- Fieldから地図表現に反映するカラム(area)を選択
完成
最後までご覧頂きありがとうございます。