BigQuery GIS データの操作

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

BigQuery GIS は GEOGRAPHY データ型のサポートを標準 SQL に追加します。GEOGRAPHY データ型は、地表上のポイントセットを表します。ポイントセットとは、測地線エッジを持つ WGS84 基準回転楕円体の点、線、ポリゴンのセットです。

GEOGRAPHY データ型は、標準 SQL 地理関数のいずれかを呼び出して使用します。地理関数の出力は、WKT(よく知られているテキスト)としてレンダリングされます。WKT は最初が経度で、2 番目が緯度という形式を使用します。

地理空間データ形式

地表の単一点は (longitude, latitude) のペアだけで記述できます。データが次のサポート対象形式のいずれかである場合、ラインやポリゴンなど、より複雑な地理を説明するため、BigQuery を使用して GEOGRAPHY 列に地理空間データを読み込めます。

WKT と GeoJSON の違い

ジオメトリ オブジェクトと空間フィーチャーおよびフィーチャー コレクション

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

  • 個々のジオメトリまたは GEOGRAPHY 値は、地表面を表します。ポイント、ライン、ポリゴン、またはポイント、ライン、ポリゴンのコレクションを使用して記述されることがよくあります。ジオメトリ コレクションは、コレクションに含まれるすべての形状の空間的な結合体を表します。
  • 空間フィーチャーは、論理空間オブジェクトを表します。これは、ジオメトリと追加のアプリケーション固有の属性(任意)を組み合わせたものです。
  • 空間フィーチャー コレクションは、フィーチャー オブジェクトのセットです。

WKT は、ポイント、ライン、ポリゴン(オプションでホールを配置可能)、またはポイント、ライン、ポリゴンのコレクションを使用して個々のジオメトリ形状を記述するためのテキスト形式です。たとえば、WKT のポイントは次のようになります。

POINT(-121 41)

WKT は空間フィーチャーの記述を目的とし、通常はなんらかのコンテナ ファイル形式(CSV ファイルがよくみられる)、またはデータベース テーブルに埋め込まれています。ファイル行またはテーブル行は通常、空間フィーチャーに対応しています。ファイル全体またはテーブルは、フィーチャー コレクションに対応しています。

WKB は WKT 形式のバイナリ版です。

GeoJSON は、ジオメトリと空間フィーチャー用の、より複雑な JSON ベースの形式です。たとえば、GeoJSON のポイントは次のようになります。

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

GeoJSON は、次のいずれかを記述するために使用されます。

  • 個々のジオメトリ オブジェクト。1 つのオブジェクトで複雑な空間形状をとることができます。これは、ポイント、ライン、ポリゴン(オプションでホールを配置可能)の結合体として記述されます。WKT 形式と同様の使用方法です。
  • フィーチャー オブジェクト。フィーチャー オブジェクトは、ジオメトリと任意の名前付きプロパティを持つオブジェクトです。CSV ファイルの 1 行に WKT 列を含んだようなものになります。
  • FeatureCollection。フィーチャー コレクションは、データベース内のテーブルまたは多くの行と列を持つ CSV ファイルに類似した、一連のフィーチャー オブジェクトです。

BigQuery GIS は GeoJSON 内の個々のジオメトリ オブジェクトのみサポートします。BigQuery GIS は現在、GeoJSON フィーチャー オブジェクト、フィーチャー コレクション、または GeoJSON ファイル形式をサポートしていません。

座標系とエッジ

WKT 形式では座標系が提供されないため、BigQuery GIS で座標系を定義します。BigQuery GIS では、WKT ポイントは WGS84 回転楕円面の表面上の位置であり、経度および測地緯度として表されます。エッジは、2 つの端点間の球状の測地線です。GeoJSON では、座標系は平面エッジを持つ明示的な WGS84 座標となります。

この 2 種類のエッジ間の変換を行うため、BigQuery GIS は必要に応じてラインにポイントを追加し、変換されたエッジのシーケンスが元のラインの 10 m 以内に収まるようにします。これは、テッセレーションまたは不均一な緻密化と呼ばれるプロセスです。現在、テッセレーション プロセスを直接制御することはできません。

球面エッジの地形をインポートするには、次の例のように WKT を使用します。

SELECT
  *,
  ST_GeogFromText(wkt) AS g
FROM
  mytable

平面エッジの地形(「ジオメトリ」と呼ばれることが多い)をインポートするには、GeoJSON を次の例のように使用します。

SELECT
  *,
  ST_GeogFromGeoJSON(geocol) AS g
FROM
  mytable

結果から元の GeoJSON 列を除外することもできます。

SELECT
  * EXCEPT(geocol),
  ST_GeogFromGeoJSON(geocol) AS geocol
FROM
  mytable

必ず正しい形式を使用してください。ほとんどのシステムは、ジオメトリとは対照的に、WKT から地形を解析するためのサポートをアドバタイズするか、平面エッジと推定します。この場合、GeoJSON は交換形式として使用する必要があります。

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

ポリゴンの向き

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

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

ST_GeogFromText 関数を使用して WKT 文字列から地理オブジェクトを作成する際、WKT 文字列により記述されたポリゴンを特定する方法は 2 つあります。

  1. (デフォルト)入力を、より小さい領域のポリゴンとして解釈する。有向ポリゴンを想定しないでください(oriented = FALSE)。

  2. 有向ポリゴンが半球よりも大きい領域のポリゴンを読み込み可能にすることを想定します(oriented = TRUE)。

GeoJSON 文字列を読み込む際、これらの規則は適用されません。GeoJSON 文字列は平面マップで定義されているため、入力が GeoJSON RFC 7946 のセクション 3.1.6 で定義されている方向規則に従わなくても、明確に向きを特定できます。ポリゴン: 反時計回りの外側リング、時計回りの内側リング。

BigQuery GIS データの読み込み

BigQuery に BigQuery GIS データを読み込む際、テーブルのスキーマ定義GEOGRAPHY 列を指定できます。列のデータ型を GEOGRAPHY に指定すると、BigQuery GIS はデータが WKT 形式であるか、GeoJSON 形式であるかを検出できます。

GeoJSON ジオメトリ オブジェクトを GEOGRAPHY 列に読み込む際は、JSON オブジェクトではなくテキスト ストリング形式にする必要があります。これは、オブジェクトが改行で区切られた JSON ファイルから読み込まれている場合にも該当します。

スキーマの自動検出を使用してデータを読み込むと、地理値は STRING として読み込まれます。現在、スキーマの自動検出では GEOGRAPHY 列を検出できません。

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

BigQuery GIS データの変換

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

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

BigQuery は現在、WKT 文字列と GeoJSON 文字列の地理タイプへの変換をサポートしています。シェイプファイルや他の多くの形式は、外部ツールを使用して変換する必要があります。

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

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

フォーマット設定の問題を回避するため、標準準拠の出力を生成する関数を使用できます。たとえば、PostGIS からデータをエクスポートするときは、ST_MakeValid 関数を使用して出力を標準化できます。

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

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

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

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

パーティショニングまたはクラスタリングされたテーブルに GEOGRAPHY データを保管する際に、クエリが空間述部を使用してデータをフィルタリングする場合、地理データが空間的にコンパクトであることを確認してください。空間述語は論理地理関数を呼び出し、引数の 1 つとして GEOGRAPHY 列が含まれています。次のサンプルは、ST_DWithin 関数を使用する空間述部を示しています。

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

たとえば、COUNTRYSTATEZIP の列を含むテーブルがある場合は、この列の連結バージョンを格納する列をテーブルに追加します。次のクエリ フラグメントは、この連結を示しています。

CONCAT(country, '+', IFNULL(state, ''), '+', IFNULL(zip, '')) as loc

この例では、欠損値を除外するために IFNULL が使用されています。連結列を作成したら、この列を使用してテーブルをクラスタリングできます。

空間データで結合を使用

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

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

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

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

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

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

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

  • 左結合(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 はこのポイントを加え、GeoJSON ラインがグラウンド上の元のラインと同じ経路に従うようにします。

BigQuery クライアント ライブラリでの地理の処理

現在 GEOGRAPHY データ型をサポートしているのは、Python 用の BigQuery クライアント ライブラリだけです。他のクライアント ライブラリの場合は、ST_ASTEXT または ST_ASGEOJSON 関数を使用して GEOGRAPHY 値を文字列に変換してください。たとえば、次のように ST_AsText 関数を使用します。 ST_AsText(ANY_VALUE(zip_regions_geometry.geometry)) AS geometry

ST_AsText を使用してテキストへ変換すると 1 つの値しか格納されず、WKT へ変換するとデータが GEOGRAPHY 型ではなく STRING 型としてアノテーションを付けられます。

次のステップ

このページは役立ちましたか?評価をお願いいたします。

フィードバックを送信...

ご不明な点がありましたら、Google のサポートページをご覧ください。