BigQuery GIS データの操作

BigQuery GIS では、BigQuery の地理データを分析できます。地理データは地理空間データとも呼ばれます。

地理空間データを操作する場合の一般的なオブジェクトの種類は次のとおりです。

  • ジオメトリは、地表面を表します。多くの場合、点、線、ポリゴン、または点、線、ポリゴンのコレクションを使用して記述します。ジオメトリ コレクションは、コレクション内のすべての形状の空間的な結合を示すジオメトリです。
  • 空間フィーチャーは、論理空間オブジェクトを表します。これは、ジオメトリとアプリケーションに固有の追加属性を組み合わせたものです。
  • 空間フィーチャー コレクションは、空間フィーチャーのセットです。

BigQuery では、GEOGRAPHY データ型はジオメトリ値またはジオメトリ コレクションを表します。空間フィーチャーを表すには、ジオメトリの GEOGRAPHY 列と属性の追加列を持つテーブルを作成します。テーブルの各行は空間フィーチャーを表し、テーブル全体が空間フィーチャー コレクションになります。

GEOGRAPHY データ型は、地表上のポイントセットを表します。ポイントセットとは、測地線エッジを持つ WGS84 基準回転楕円体の点、線、ポリゴンのセットです。GEOGRAPHY データ型は、標準 SQL 地理関数のいずれかを呼び出して使用します。

地理空間データの読み込み

地表の単一点は経度と緯度のペアだけで記述できます。たとえば、経度と緯度の値が含まれる CSV ファイルを読み込んでから、ST_GEOGPOINT 関数を使用して GEOGRAPHY 値に変換できます。

より複雑な地形の場合、次の地理空間データ形式を GEOGRAPHY 列に読み込むことが可能です。

  • Well-known text(WKT)
  • Well-known binary(WKB)
  • GeoJSON

WKT データまたは WKB データの読み込み

WKT は、点、線、ポリゴン(オプションでホール)、または点、線、ポリゴンのコレクションを使用して個々のジオメトリ形状を記述するためのテキスト形式です。WKB は WKT 形式のバイナリ版です。

たとえば、以下では WKT に点を定義しています。

POINT(-121 41)

空間フィーチャーを記述するために、WKT は通常はコンテナ ファイル形式(CSV ファイルなど)またはデータベース テーブルに埋め込まれています。通常、ファイル行またはテーブル行は空間フィーチャーに対応し、ファイル全体またはテーブル全体はフィーチャー コレクションに対応します。WKT データを BigQuery に読み込むには、地理空間データの GEOGRAPHY 列を定義するスキーマを指定します。

たとえば、次のデータが含まれる CSV ファイルがあるとします。

"POLYGON((-124.49 47.35,-124.49 40.73,-116.49 40.73,-116.49 47.35,-124.49 47.35))",poly1
"POLYGON((-85.6 31.66,-85.6 24.29,-78.22 24.29,-78.22 31.66,-85.6 31.66))",poly2
"POINT(1 2)",point1

このファイルを読み込むには、bq コマンドライン ツールの load コマンドを実行します。

bq load --source_format=CSV \
  --schema="geography:GEOGRAPHY,name:STRING" \
  mydataset.mytable filename1.csv

BigQuery へのデータの読み込みの詳細については、データの読み込みの概要をご覧ください。

GEOGRAPHY 列を使用して WKT データを既存の BigQuery テーブルにストリーミングするには、API リクエストで文字列としてデータをシリアル化します。

bq

bq コマンドライン ツールの insert コマンドを実行します。

echo '{"geo": "LINESTRING (-118.4085 33.9416, -73.7781 40.6413)"}' \
    | bq insert my_dataset.geo_table

Python

このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Python の手順に従って設定を行ってください。詳細については、BigQuery Python API のリファレンス ドキュメントをご覧ください。

from google.cloud import bigquery
import shapely.geometry
import shapely.wkt

bigquery_client = bigquery.Client()

# This example uses a table containing a column named "geo" with the
# GEOGRAPHY data type.
table_id = "my-project.my_dataset.my_table"

# Use the Shapely library to generate WKT of a line from LAX to
# JFK airports. Alternatively, you may define WKT data directly.
my_geography = shapely.geometry.LineString(
    [(-118.4085, 33.9416), (-73.7781, 40.6413)]
)
rows = [
    # Convert data into a WKT string.
    {"geo": shapely.wkt.dumps(my_geography)},
]

#  table already exists and has a column
# named "geo" with data type GEOGRAPHY.
errors = bigquery_client.insert_rows_json(table_id, rows)
if errors:
    raise RuntimeError(f"row insert failed: {errors}")
else:
    print(f"wrote 1 row to {table_id}")

BigQuery でのデータのストリーミングの詳細については、BigQuery へのデータのストリーミングをご覧ください。

ST_GeogFromText 関数を使用して、WKT テキスト文字列を GEOGRAPHY 値に変換することもできます。

GeoJSON データの読み込み

GeoJSON は、ジオメトリと空間フィーチャー用の JSON ベースの形式です。たとえば、以下では GeoJSON に点を定義しています。

{ "type": "Point", "coordinates": [-121,41] }

GeoJSON データには、次のいずれかのオブジェクト タイプを含めることが可能です。

  • ジオメトリ オブジェクト。ジオメトリ オブジェクトは、点、線、ポリゴン(オプションでホール)の結合体として記述される空間形状です。
  • フィーチャー オブジェクト。フィーチャー オブジェクトにはジオメトリと追加の名前と値のペアが含まれています。これはアプリケーションに固有であることを意味します。
  • フィーチャー コレクション。フィーチャー コレクションはフィーチャー オブジェクトのセットです。

BigQuery GIS では、他のファイル形式で埋め込まれた個々の GeoJSON ジオメトリ オブジェクトの読み込みがサポートされます。たとえば、いずれかの列に GeoJSON ジオメトリ オブジェクトが含まれる CSV ファイルを読み込むことが可能です。

BigQuery GIS では、GeoJSON フィーチャー オブジェクト、フィーチャー コレクション、GeoJSON ファイル形式での読み込みがサポートされていません。

GeoJSON データを BigQuery に読み込むには、GeoJSON データの GEOGRAPHY 列を定義するスキーマを指定します。データファイルが改行区切りの JSON ファイルでも、JSON オブジェクトではなくテキスト文字列としてジオメトリ オブジェクトをフォーマットします。

GEOGRAPHY 列を使用して GeoJSON データを既存の BigQuery テーブルにストリーミングするには、API リクエストで文字列としてデータをシリアル化します。

bq

bq コマンドライン ツールの insert コマンドを実行します。

echo '{"geo": "{\"type\": \"LineString\", \"coordinates\": [[-118.4085, 33.9416], [-73.7781, 40.6413]]}"}' \
  | bq insert my_dataset.geo_table

Python

このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Python の手順に従って設定を行ってください。詳細については、BigQuery Python API のリファレンス ドキュメントをご覧ください。

import geojson
from google.cloud import bigquery

bigquery_client = bigquery.Client()

# This example uses a table containing a column named "geo" with the
# GEOGRAPHY data type.
table_id = "my-project.my_dataset.my_table"

# Use the python-geojson library to generate GeoJSON of a line from LAX to
# JFK airports. Alternatively, you may define GeoJSON data directly, but it
# must be converted to a string before loading it into BigQuery.
my_geography = geojson.LineString([(-118.4085, 33.9416), (-73.7781, 40.6413)])
rows = [
    # Convert GeoJSON data into a string.
    {"geo": geojson.dumps(my_geography)}
]

#  table already exists and has a column
# named "geo" with data type GEOGRAPHY.
errors = bigquery_client.insert_rows_json(table_id, rows)
if errors:
    raise RuntimeError(f"row insert failed: {errors}")
else:
    print(f"wrote 1 row to {table_id}")

ST_GEOGFROMGEOJSON 関数を使用して、GeoJSON ジオメトリ オブジェクトを GEOGRAPHY 値に変換することもできます。

座標系とエッジ

BigQuery GIS では、ポイントは WGS84 回転楕円面の表面上の位置であり、経度および測地緯度で表されます。エッジは、2 つの端点間の球状の測地線です(エッジは球面上の最短パスです)。

WKT 形式では座標系が提供されません。WKT データを読み込む際、BigQuery GIS は球面エッジで WGS84 座標を使用することを前提としています。球面エッジと平面エッジの差が無視できるほどの差である場合を除き、ソースデータが座標系と一致することを確認してください。

GeoJSON では、平面エッジで WGS84 座標を明示的に使用します。GeoJSON データを読み込むと、BigQuery GIS によって平面エッジが球面エッジに変換されます。BigQuery GIS は、必要に応じて線に点を追加し、変換されたエッジのシーケンスが元の線の 10 m 以内に収まるようにします。これは、テッセレーションまたは不均一な緻密化と呼ばれるプロセスです。テッセレーション プロセスを直接制御することはできません。

球面エッジを持つ地形を読み込むには、WKT を使用します。平面エッジを持つ地形(ジオメトリ)を読み込む場合は、GeoJSON を使用するのが最も簡単です。ただし、ジオメトリ データがすでに WKT 形式になっている場合は、データを STRING 型として読み込んでから、ST_GEOGFROMTEXT 関数で GEOGRAPHY 値に変換することもできます。データを平面として解釈するには、planar パラメータを TRUE に設定します。

交換形式を選択する際に、ソースデータで使用される座標系を確認してください。ほとんどのシステムでは、WKT から地形(ジオメトリではなく)を解析するか、平面エッジがあることを前提としています。

座標は経度、緯度の順に表します。地形にある長いセグメントやエッジは、テッセレーションしなければなりません。これは、BigQuery GIS が長いセグメントやエッジをデータ送信元の座標系では表すことのできない球状測地線として解釈するためです。

ポリゴンの向き

球体では、すべてのポリゴンに相補的ポリゴンがあります。たとえば、地球の大陸を表すポリゴンには、地球の海を表す相補的なポリゴンがあります。2 つのポリゴンは同じ境界リングによって表されるため、特定の WKT 文字列が表すポリゴンがどちらのポリゴンかわかりにくい、というあいまいさを解決するための規則が必要になります。

ファイルやストリーミング取り込みを使用して WKT、WKB 文字列を読み込むと、BigQuery GIS は入力内のポリゴンが次のように方向付けられていると想定します。すなわち、ポリゴンの境界を入力頂点の順に多角測量する場合、左側がポリゴンの内部であると想定されます。BigQuery GIS では、地理オブジェクトを WKT、WKB 文字列にエクスポートするときも同じ規則が適用されます。

ST_GeogFromText 関数を使用して WKT 文字列を GEOGRAPHY 値に変換する場合、この関数がポリゴンを特定する方法を oriented パラメータで指定します。

  • FALSE: 入力をより小さい領域のポリゴンとして解釈します。これがデフォルトの動作です。

  • TRUE: 前述のように、左向きのルールを使用します。このオプションを使用すると、半球よりも大きい領域のポリゴンを読み込むことが可能です。

GeoJSON 文字列は平面マップで定義されているため、入力が RFC 7946 の GeoJSON 形式の仕様で定義されている方向規則に従わなくても明確に向きを特定できます。

不適切な形式の空間データの処理

他のツールから空間データを BigQuery に読み込むと、WKT データまたは GeoJSON データが無効であるために変換エラーが発生する可能性があります。たとえば、Edge K has duplicate vertex with edge N などのエラーは、ポリゴンに重複した頂点(最初と最後の頂点を除く)があることを示します。

フォーマット設定の問題は、標準準拠の出力を生成する関数を使用すると回避できます。たとえば、PostGIS からデータをエクスポートするときに、PostGIS ST_MakeValid 関数を使用して出力を標準化できます。または、データをテキストとしてインポートし、make_valid パラメータを使用して ST_GEOGFROMTEXT または ST_GEOGFROMGEOJSON を呼び出します。make_validTRUE の場合、これらの関数では無効なポリゴンを修復しようとします。

不適切な形式のデータを検索または無視するには、SAFE 関数の接頭辞を使用して問題のあるデータを出力します。たとえば、次のクエリでは、SAFE 接頭辞を使用して不適切な形式の空間データを取得します。

SELECT
  geojson AS bad_geojson
FROM
  mytable
WHERE
  geojson IS NOT NULL
  AND SAFE.ST_GeogFromGeoJson(geojson) IS NULL

制約

BigQuery GIS では、地理空間形式の以下の機能がサポートされません。

  • 3 次元ジオメトリ。これには WKT 形式の「Z」接尾辞と GeoJSON 形式の標高座標が含まれます。
  • リニア参照システム。これには WKT 形式の「M」接尾辞が含まれます。
  • WKT ジオメトリ オブジェクト(ジオメトリ プリミティブまたはマルチパート ジオメトリを除く)。具体的には、Point、MultiPoint、LineString、MultiLineString、Polygon、MultiPolygon、GeometryCollection のみがサポートされます。

GeoJson と WKT の入力形式に固有の制約については、ST_GeogFromGeoJsonST_GeogFromText をご覧ください。

BigQuery GIS データの変換

経度と緯度が別々の列になっているテーブルでは、ST_GeogPoint などの標準 SQL 地理関数を使用して GEOGRAPHY 値に変換できます。たとえば、経度と緯度の 2 つの DOUBLE 列がある場合は、次のクエリを使用して GEOGRAPHY 列を作成できます。

SELECT
  *,
  ST_GeogPoint(longitude, latitude) AS g
FROM
  mytable

BigQuery では WKT 文字列と GeoJSON 文字列を GEOGRAPHY 型に変換できます。データが Shapefile などの他の形式である場合は、外部ツールを使用して、サポートされている入力ファイル形式(CSV ファイルなど)にデータを変換します。その際、GEOGRAPHY 列が WKT 文字列または GeoJSON の文字列としてエンコードされます。

BigQuery GIS データのパーティショニングとクラスタリング

GEOGRAPHY 列を含むテーブルはパーティショニングクラスタリングが可能です。ただし、GEOGRAPHY 列をクラスタリング列として使用することはできますが、GEOGRAPHY 列をパーティショニング列として使用することはできません。

空間述部を使用してテーブルの GEOGRAPHY データやクエリのフィルタデータを保存する場合、GEOGRAPHY 列によりテーブルがクラスタ化されていることを確認してください。これにより、通常はクエリのパフォーマンスが向上し、コストが削減されます。空間述語は論理地理関数を呼び出し、引数の 1 つとして GEOGRAPHY 列を含みます。次のサンプルは、ST_DWithin 関数を使用する空間述部を示しています。

WHERE ST_DWithin(geo, ST_GeogPoint(longitude, latitude), 100)

空間データで結合を使用

空間結合とは、(WHERE)節における述部地理関数による 2 つのテーブルの結合です。例:

-- how many stations within 1 mile range of each zip code?
SELECT
    zip_code AS zip,
    ANY_VALUE(zip_code_geom) AS polygon,
    COUNT(*) AS bike_stations
FROM
    `bigquery-public-data.new_york.citibike_stations` AS bike_stations,
    `bigquery-public-data.geo_us_boundaries.zip_codes` AS zip_codes
WHERE ST_DWithin(
         zip_codes.zip_code_geom,
         ST_GeogPoint(bike_stations.longitude, bike_stations.latitude),
         1609.34)
GROUP BY zip
ORDER BY bike_stations DESC

地理データが永続化されると、空間結合がうまく機能するようになります。上記の例では、クエリに GEOGRAPHY 値が作成されています。GEOGRAPHY 値を BigQuery テーブルに格納すると、パフォーマンスが向上します。

たとえば、次のクエリは経度と緯度のペアを取得し、それらを地理ポイントに変換します。このクエリを実行する際は、クエリ結果を保管する新しい宛先テーブルを指定してください。

SELECT
  *,
  ST_GeogPoint(pLongitude, pLatitude) AS p
FROM
  mytable

BigQuery では、次の標準 SQL 述部関数を使って、INNER JOIN 演算子と CROSS JOIN 演算子に対して最適化された空間結合が実装されます。

以下の場合、空間結合は最適化されません。

  • 左結合(LEFT)、右結合(RIGHT)、完全外部結合(FULL OUTER)の場合
  • アンチ結合(ANTI)を含む場合
  • 空間述部が否定されている場合

ST_DWithin 述部を使用する JOIN は、距離パラメータが定数式の場合にのみ最適化されます。

空間データのエクスポート

BigQuery から空間データをエクスポートすると、GEOGRAPHY 列の値は常に WKT 文字列の形式になります。GeoJSON 形式でデータをエクスポートするには、ST_AsGeoJSON 関数を使用します。

エクスポートされたデータの分析に使用しているツールが GEOGRAPHY データ型を認識しない場合は、ST_AsTextST_AsGeoJSON などの地理関数を使用して列の値を文字列に変換できます。BigQuery GIS は、必要に応じてラインにポイントを追加し、変換されたエッジのシーケンスが元の測地線の 10 m 以内に収まるようにします。

たとえば、次のクエリは ST_AsGeoJSON を使用して GeoJSON 値を文字列に変換します。

SELECT
  ST_AsGeoJSON(ST_MakeLine(ST_GeogPoint(1,1), ST_GeogPoint(3,2)))

得られたデータは次のようになります。

{ "type": "LineString", "coordinates": [ [1, 1], [1.99977145571783, 1.50022838764041], [2.49981908082299, 1.75018082434274], [3, 2] ] }

GeoJSON ラインにはさらに 2 つのポイントがあります。BigQuery GIS がこの 2 つのポイントを加えることで、GeoJSON ラインはグラウンド上の元のラインとほぼ同じになります。

次のステップ