Google BigQuery GIS で空間分析を始めよう (1)

Google の BigQuery では、単にDWHの機能だけでなく 地理情報システム（GIS：Geographic Information System）　を利用した空間分析を行うことが出来ます。　このGISを利用するとBigQueryでの分析に地理的な情報を加えることが出来ます。

例えば、「ある位置情報（緯度・軽度）が入った走行データがあるときに、港区内で最大何キロで走行していたか？」ということや、例えば「この駅から500M以内にあるコンビニのリストは？」といったような検索をおこなうことが出来るようになります。　これらの検索は実際の実社会においてスマートフォンを利用したアプリなどで頻繁に行われており実際に便利に利用されている機能となります。 BigQueryでもこれらの処理を行うために BigQuery GIS という機能を提供しています。

ここでは初歩的な BigQuery GIS の使い方を説明します。

動作の概要

BigQuery GISを利用して上記のような検索を行いたい場合には2つのデータを用意します。一つは緯度経度が含まれている意味のあるデータ。もう一つは検索を行いたいと考える地図データの2つです。　この地図データ次第でどのような検索が出来るかが変わってきます。

この地図データテーブル（nic-tranning-enablement.car_data.v_land_tokyo_min）は例えばこのような形です。

これはBigQueryの一つのテーブルとして格納されているものとします。次に実際の緯度経度のデータテーブル（nic-tranning-enablement.car_data.v_car_01）は次のようなものであるとします

BigQueryで緯度経度情報は、このようにWKTフォーマットで記載されている必要があります（これらのデータの作り方はまた後ほど説明をします）

さてこの2つのテーブルをSQLで検索することを考えます。

SELECT land.PREF_NAME, land.CITY_NAME, land.S_NAME, car.data_gpsspeed  
FROM `nic-tranning-enablement.car_data.v_car_01` car  , `nic-tranning-enablement.car_data.v_land_tokyo_min` land
where 
 ST_WITHIN(car.WKT, land.polygon) and
 car.unixtimestamp = 1562558177865

この実行結果は以下のようになります。

ここでは、ST_WITHIN(a,b) という関数を利用しています。これを利用することで走行データの緯度経度(a) が 東京都の地区データ(b) に含まれているのを検索することが出来ています。

地図を描画するアプリケーションは色々あります。ここでは BigQuery Geo Viz (https://bigquerygeoviz.appspot.com/) を利用して上記のような塗りつぶしを行いました。　このBigQuery Geo Vizというサービスの場合には、WKTフォーマットで示されたデータを塗り潰すという機能を持っています。そのためSQLの結果に

SELECT land.polygon, land.PREF_NAME, land.CITY_NAME, land.S_NAME, car.data_gpsspeed  
FROM `nic-tranning-enablement.car_data.v_car_01` car  , `nic-tranning-enablement.car_data.v_land_tokyo_min` land
where 
 ST_WITHIN(car.WKT, land.polygon) and
 car.unixtimestamp = 1562558177865

とすることで land.polygon の値を出力させ、BigQuery Geo Vizではその内容で塗りつぶします。塗りつぶし方は様々な方法通いされており例えば car.data_gpsspeed の値に応じて塗りつぶせは速さ次第で色を塗り分けたりすることも出来ます。このように、 (a)分析対象のデータ と (b) 地図データ　を突き合わせて検索を行い、結果をアプリケーションやサービスなどで描画するということが主な動きです。（描画しないで普通に処理することももちろんできます）

データの準備

BigQuery がサポートする地図データ

マニュアルを参照すると以下の3つのフォーマットをサポートしています。

• GeoJSON
• よく知られているテキスト（WKT）
• よく知られているバイナリ（WKB）

GeoJSONについては地理データがGeoJSON形式のものがBigQueryに直接インポートできるわけではありません。 地理データが入る列の 値として GeoJSONフォーマットを入れることが出来ます。つまりデータ自身はCSVですが、地理データの列の中身の文字列といて　GeoJSONフォーマット　でも動くということです。

「e-STAT 統計で見る日本」　から、東京都の小地区のデータをBigQueryで利用できる形式に変換してみたいと思います。

東京都全域のZipをダウンロードすると以下のファイルが格納されています。

tokida in ~/D/b/A002005212015DDSWC13_東京 ⓖ nic-tranning-enablement
❯❯❯ ls -la
total 67792
drwx------@  7 tokida  staff       224  7  8 13:05 .
drwxr-xr-x  22 tokida  staff       704  7 10 15:34 ..
-rw-r--r--   1 tokida  staff  22581395  7  8 13:05 h27ka13.csv
-rwxr-xr-x@  1 tokida  staff   2795804  5  2  2018 h27ka13.dbf
-rwxr-xr-x@  1 tokida  staff       147  5  2  2018 h27ka13.prj
-rwxr-xr-x@  1 tokida  staff   9153524  5  2  2018 h27ka13.shp
-rwxr-xr-x@  1 tokida  staff     48180  5  2  2018 h27ka13.shx

この図面は例えば QGISアプリを利用すると以下のようになっていることが確認できます

変換を実施するために以下のコマンドを実行します。

/Library/Frameworks/GDAL.framework/Versions/2.4/Programs/ogr2ogr -f csv -dialect sqlite -sql "select AsGeoJSON(geometry) AS geom, * from h27ka13" h27ka13.csv h27ka13.shp

• ここでは macos上で、ogr2ogr というコマンドを利用しています。このツールは brew install gdal で導入することが出来ます。
• 上記のコマンドであh sqlite の機能を利用して GeoJSON 形式に変換した geom という列を追加しています。

geom,KEY_CODE,PREF,CITY,S_AREA,PREF_NAME,CITY_NAME,S_NAME,KIGO_E,HCODE,AREA,PERIMETER,H27KAxx_,H27KAxx_ID,KEN,KEN_NAME,SITYO_NAME,GST_NAME,CSS_NAME,KIHON1,DUMMY1,KIHON2,KEYCODE1,KEYCODE2,AREA_MAX_F,KIGO_D,N_KEN,N_CITY,KIGO_I,MOJI,KBSUM,JINKO,SETAI,X_CODE,Y_CODE,KCODE1
"{""type"":""Polygon"",""coordinates"":[[[140.3422452007195,29.7935644381794],[140.3414870097204,29.7935644381757],[140.3410480570383,29.79402040176603],[140.3416399780816,29.79460333953882],[140.3426442486108,29.79453985143566],[140.3426708518048,29.79403771680564],[140.3422452007195,29.7935644381794]]]}","13","13","402","000000",東京都,青ヶ島村,,,"8101",13634.4,445.341,"5292","5291","13",東京都,八丈支庁,,青ヶ島村,"0000","-","00","402000000",,,,,,,,"0","0","0",140.34199,29.79429,0000-00

• 最初の1カラム目(geom) の内容がGeoJSON形式であることが見てかると思います。

以下のコマンドを利用してBigQueryへロードを行います（サイズが大きい場合には一度Google Cloud Storageへアップロードしてからロードしましょう）

bq load --autodetect nic-tranning-enablement:car_data.land_tokyo_min h27ka13.csv

この状態のデータ形式になっていることで BigQueryのGIS関数で処理を行うことが出来ます。

SELECT  ST_UNION_AGG(ST_GEOGFROMGEOJSON(geom)) as polygon  , PREF_NAME,GST_NAME, CITY_NAME, S_NAME, KEY_CODE  FROM `nic-tranning-enablement.car_data.land_tokyo_min`
WHERE (keycode2 is not null)
GROUP BY  PREF_NAME, GST_NAME, CITY_NAME, S_NAME, KEY_CODE 

例えば、そのあままでは非常にメッシュが細かいので上記のViewを作ることでもう少し大きな粒度（町単位）のポリゴンを作成することが出来ています。

まとめ

今回は、BigQuery GIS での使い方の概要とそれに必要となる地図のデータの用意を整理しました。
次回はこれらのデータを利用していくつかの統計情報を取得し、BigQuery Geo Vizを用いて簡易的に描画するところをご紹介したいと思います。