BigQueryで測定系の地理関数を確認してみた(BigQuery GIS)

当エントリは、『クラスメソッド BigQuery Advent Calendar 2020』15日目のエントリです。
本アドベントカレンダーでは、12月01日〜12月25日までの25日間、弊社DA（データアナリィクス）事業本部のメンバーがBigQueryに関連するブログを公開していきます。

BigQueryはDWHとしての機能だけでなく、BigQuery GIS（地理情報システム）を用いて地理データ型と標準のSQL地理関数を使用して、地理空間データを分析および可視化を行うことが出来ます。
当エントリでは、標準のSQL地理関数の一部(測定系のみ)を紹介をしていきます。

測定系の地理関数

現時点(2020/12/15)で利用できる測定系の地理関数は5つあり、それぞれ簡単にまとめます。

• 標準 SQL の地理関数

ST_AREA

ST_AREA(geography_expression[, use_spheroid])

• 戻り値(FLOAT64)：GEOGRAPHYの面積(平方メートル)
• pointやlineが入力された場合は0を返却
• collectionが入力された場合はpolygonの面積の合計を返却
• use_spheroid=FALSEの場合は完全な球体の表面上の距離として計算される、現時点でFALSEのみサポート

使用例

select ST_AREA(ST_GeogFromText('POLYGON((-179 26, -179 48, -10 48, -10 26, -100 -10.1, -179 26))')) as area 

+-----------------------+
|        area           | 
+-----------------------+
| 9.564128913265508E13  | 
+-----------------------+

使用例では、下記を施しています。

• WKT(Well-known text)データである'POLYGON((-179 26, -179 48, -10 48, -10 26, -100 -10.1, -179 26))'をST_GeogFromText関数でGEOGRAPHY型データに変換
• 変換されたGEOGRAPHY型データの面積をST_AREA関数で算出

ST_DISTANCE

ST_DISTANCE(geography_1, geography_2[, use_spheroid])

• 戻り値(FLOAT64)：二つのGEOGRAPHY間の最短距離(m)
• pointやlineが入力された場合は0を返却
• use_spheroid=FALSEの場合は完全な球体の表面上の距離として計算される、現時点でFALSEのみサポート

使用例

select ST_DISTANCE(ST_GEOGPOINT(139.7454316, 35.658584),ST_GEOGPOINT(139.8108103, 35.7100069))　as distance

+-------------------+
|      distance     | 
+-------------------+
| 8219.632821167155 | 
+-------------------+

使用例では、下記を施しています。

• それぞれの緯度(latitude),経度(longitude)をST_GEOGPOINT関数でGEOGRAPHY型データに変換
• 変換されたそれぞれのGEOGRAPHY型データの最短距離をST_DISTANCE関数で算出

ST_LENGTH

ST_LENGTH(geography_expression[, use_spheroid])

• 戻り値(FLOAT64)：入力されたGEOGRAPHYに含まれるラインの長さの合計(m)
• GEOGRAPHYのどちらか片方がemptyの場合は、NULLを返却
• use_spheroid=FALSEの場合は完全な球体の表面上の距離として計算される、現時点でFALSEのみサポート

使用例

select ST_LENGTH(ST_MAKELINE(ST_GEOGPOINT(139.7454316, 35.658584),ST_GEOGPOINT(139.8108103, 35.7100069))) as length

+-------------------+
|     length        | 
+-------------------+
| 8219.632821166997 | 
+-------------------+

使用例では、下記を施しています。

• それぞれの緯度(latitude),経度(longitude)をST_GEOGPOINT関数でGEOGRAPHY型データに変換
• 変換されたそれぞれのGEOGRAPHY型データからST_MAKELINE関数でライン(GEOGRAPHY型)を生成
• 生成されたライン(GEOGRAPHY型)の合計の長さをST_LENGTH関数で算出

ST_MAXDISTANCE

ST_MAXDISTANCE(geography_1, geography_2[, use_spheroid])

• 戻り値(FLOAT64)：距離が最大となる二点間の距離(m)
• GEOGRAPHYのどちらか片方がemptyの場合は、NULLを返却
• use_spheroid=FALSEの場合は完全な球体の表面上の距離として計算される、現時点でFALSEのみサポート

使用例

select ST_MAXDISTANCE(ST_GEOGPOINT(139.7454316, 35.658584),ST_GEOGPOINT(139.8108103, 35.7100069))　as max_distance

+-------------------+
|   max_distance    | 
+-------------------+
| 8219.632821167155 | 
+-------------------+

使用例では、下記を施しています。

• それぞれの緯度(latitude),経度(longitude)をST_GEOGPOINT関数でGEOGRAPHY型データに変換
• 変換されたそれぞれのGEOGRAPHY型データからST_MAXDISTANCE関数で二点間の最大距離を算出

ST_PERIMETER

ST_PERIMETER(geography_expression[, use_spheroid])

• 戻り値(FLOAT64)：入力されたGEOGRAPHYの外周の長さの合計(m)
• pointやlineが入力された場合は0を返却
• collectionが入力された場合はpolygonの面積の合計を返却
• use_spheroid=FALSEの場合は完全な球体の表面上の距離として計算される、現時点でFALSEのみサポート

使用例

select ST_PERIMETER(ST_GeogFromText('POLYGON((-179 26, -179 48, -10 48, -10 26, -100 -10.1, -179 26))')) as perimeter

+---------------------+
|      perimeter      | 
+---------------------+
| 3.409889315220319E7 | 
+---------------------+

使用例では、下記を施しています。

• WKT(Well-known text)データである'POLYGON((-179 26, -179 48, -10 48, -10 26, -100 -10.1, -179 26))'をST_GeogFromText関数でGEOGRAPHY型データに変換
• 変換されたGEOGRAPHY型データからST_PERIMETER関数で外周の長さの合計を算出

おまけ：ST_DISTANCE関数でデータを絞ってみた

上記の関数紹介を踏まえて、実際に一般公開データセットからST_DISTANCE関数を使ってデータを絞ってみました。

select
    station_id,
    name
from
    `bigquery-public-data.new_york.citibike_stations`
where
    ST_DISTANCE(ST_GEOGPOINT(longitude, latitude), ST_GEOGPOINT(-73.99404649, 40.72903917)) >= 10000

Washington Pl & Broadway(緯度：40.72903917、経度：-73.99404649)という駅との距離が10km以上離れている自転車ステーションを抽出してみました。

おわりに

今回は測定系の地理関数に絞って、簡単に確認してみました。
基本的に他のGIS機能（PostGISなど）と同等の関数が使えるようですね、あまりGIS自体を触ってなかった自分にとっては新鮮でした。

地理関数は他のカテゴリもあり、いろんな空間分析する際に利用できそうなので引き続き確認していこうと思います。
また今回は試せなかったのですが、実際は地理関数などで抽出したデータをBigQuery Geo Viz可視化するまでが一般的な使い方だと思うので、BigQuery Geo Vizについても確認していこうと思います。
BigQuery Geo Vizは下記のブログなどでも紹介されているので、あわせてご確認ください。

『クラスメソッド BigQuery Advent Calendar 2020』 16日目は、みかみさんです。お楽しみに！
以上、DA(データアナリティクス)事業本部のナガマサでした。

参考

• GIS×BigQuery　〜2地点間の距離を算出〜
• BigQuery GISを用いた位置情報データ分析の入門