Bigtable で地理空間データを操作する

このページでは、GoogleSQL 地理関数を使用して Bigtable に地理空間データを保存し、クエリする方法について説明します。地理空間データには、地球の表面のジオメトリ表現が点、線、ポリゴンの形式で含まれます。これらのエンティティをコレクションにグループ化して、位置情報、ルート、その他の関心のあるエリアを追跡できます。

基本的なオペレーション

専用の GoogleSQL 地理関数を使用すると、Bigtable で地理空間計算とクエリ時間のデータベース パフォーマンスを最適化できます。基本的な使用例では、poi_data という名前の列ファミリーが 1 つある tourist_points_of_interest テーブルを使用します。

	poi_data
行キー	name	ロケーション
p1	エッフェル塔	POINT(2.2945 48.8584)

地域データを書き込む

地理空間データを Bigtable 列に書き込むには、GeoJSON 形式または Well-Known Text（WKT）形式で地理エンティティの文字列表現を指定する必要があります。Bigtable は基盤となるデータを RAW バイトとして保存するため、単一の列にエンティティの任意の組み合わせ（Points、LineStrings、Polygons、MultiPoints、MultiLineStrings、MultiPolygons など）を指定できます。指定する地理空間エンティティのタイプは、クエリ時に重要です。データを解釈するには、適切な地理関数を使用する必要があるためです。

たとえば、tourist_points_of_interest テーブルに観光スポットを追加するには、次のように cbt CLI ツールを使用します。

• WKT の場合:

  cbt set tourist_points_of_interest p2 poi_data:name='Louvre Museum'
  cbt set tourist_points_of_interest p2 poi_data:location='POINT(2.3376 48.8611)'
  
  cbt set tourist_points_of_interest p3 poi_data:name='Place Charles de Gaulle'
  cbt set tourist_points_of_interest p3 poi_data:location='POLYGON(2.2941 48.8733, 2.2941 48.8742, 2.2957 48.8742, 2.2958 48.8732, 2.2941 48.8733)'

• GeoJSON を使用する場合:

  cbt set tourist_points_of_interest p2 poi_data:name='Louvre Museum'
  cbt set tourist_points_of_interest p2 poi_data:location='{"type": "Point", "coordinates": [2.3376, 48.8611]}'
  
  cbt set tourist_points_of_interest p3 poi_data:name='Place Charles de Gaulle'
  cbt set tourist_points_of_interest p3 poi_data:location='{"type": "Polygon", "coordinates": [[ [2.2941, 48.8733], [2.2941, 48.8742], [2.2957, 48.8742], [2.2958, 48.8732], [2.2941, 48.8733] ]] }'

地域データをクエリする

Bigtable Studio で GoogleSQL を使用して tourist_points_of_interest テーブルをクエリし、地理関数で結果をフィルタできます。たとえば、トロカデロから 1,500 メートル以内のすべての観光名所を検索するには、ST_DWITHIN 関数を使用します。

SELECT _key, poi_data['name'], poi_data['location']
FROM points_of_interest
WHERE ST_DWITHIN(CAST(poi_data['location'] AS STRING), ST_GEOGPOINT(2.2874, 48.86322), 1500);

結果は次のようになります。

location 列には、書き込みオペレーションで指定されたデータの元のテキスト表現が表示されますが、Bigtable はこれをバイトとして保存します。この例では、シャルル ド ゴール広場が 1,500 メートルの制限を超えるポリゴンとして定義されていますが、クエリ結果に含まれています。これは、ST_DWITHIN 関数が、地理空間エンティティに含まれるいずれかのポイントが想定される距離内にある場合に true を返すためです。

高度な使い方

以降のセクションでは、ジオフェンシング分析などの複雑なシナリオで地理関数を使用する方法について説明します。クエリの最適化セクションでは、パフォーマンスを向上させるために継続的マテリアライズド ビューを使用する方法について説明します。

Bigtable での地理関数の高度な使用方法を説明するために、スノーヒル島の南側に生息するエンペラー ペンギンの行動をモニタリングする研究者を想定します。

機器はペンギンごとに数百もの位置情報 ping を提供するため、それらを保存する penguin_movements テーブルを作成します。

	penguin_details
行キー	penguin_id	timestamp	ロケーション
ping#123	pen_01	2025-12-06 08:15:22+00	POINT(-57.51 -64.42)
ping#124	pen_01	2025-12-06 10:22:05+00	POINT(-57.55 -64.43)
ping#125	pen_01	2025-12-07 12:35:45+00	POINT(-57.58 -64.41)
ping#126	pen_02	2025-12-12 06:05:11+00	POINT(-57.49 -64.39)

例: ジオフェンスを通過したユーザーを特定する

調査中に、特定の餌場周辺でのペンギンの行動を観察します。仮想境界で定義されたエリアは、一般的にジオフェンスと呼ばれます。餌場はジオフェンスで囲まれたエリアとみなすことができます。2025 年 12 月 3 日にペンギンが餌場に訪れたかどうかを確認したい。この質問に答えるには、次のように ST_CONTAINS 関数を使用します。

SELECT
  penguin_details['penguin_id'],
  penguin_details['location'],
  penguin_details['timestamp']
FROM
  penguin_movements
WHERE
  penguin_details['timestamp'] >= '2025-12-03 00:00:00 UTC'
  AND penguin_details['timestamp'] < '2025-12-04 00:00:00 UTC'
  -- The feeding ground boundary is defined with a WKT POLYGON entity
  -- Polygon's last point must be equal to its first point to close the loop
  AND ST_CONTAINS(
        ST_GEOGFROMTEXT('POLYGON((-57.21 -64.51, -57.23 -64.55, -57.08 -64.56, -57.06 -64.51, -57.21 -64.51))'),
        ST_GEOGFROMTEXT(CAST(penguin_details['location'] AS STRING))
      )

次のような結果が表示されます。

+------------+----------------------+------------------------+
| penguin_id |       location       |       timestamp        |
+------------+----------------------+------------------------+
| pen_01     | POINT(-57.15 -64.53) | 2025-12-03 08:15:22+00 |
| pen_02     | POINT(-57.18 -64.54) | 2025-12-03 14:30:05+00 |
| pen_10     | POINT(-57.10 -64.52) | 2025-12-03 11:45:10+00 |
+------------+----------------------+------------------------+

例: ポイントをルートに変換する

ペンギンの移動をより視覚的に把握するために、次のように ST_MAKELINE 関数を使用して、個々の位置情報の ping を LineStrings に整理することにします。

SELECT
  penguin_id,
  DATE(timestamp, 'UTC') AS route_date,
  ST_MAKELINE(ARRAY_AGG(location_cast_to_geog)) AS route
FROM (
  -- Sub-expression to sort all location pings by timestamp
  -- This way you make sure individual points can form a realistic route
  SELECT
    penguin_details['penguin_id'] AS penguin_id,
    -- Extract and cast timestamp once
    CAST(CAST(penguin_details['timestamp'] AS STRING) AS TIMESTAMP) AS timestamp,
    -- Extract and cast location once
    ST_GEOGFROMTEXT(CAST(penguin_details['location'] AS STRING)) AS location_cast_to_geog
  FROM
    penguin_movements
  ORDER BY
    -- Pre-sorts location pings for the ARRAY_AGG function
    penguin_id, timestamp ASC
)
GROUP BY
  penguin_id,
  route_date

このクエリを使用すると、調査の高度な分析情報を取得できます。次のセクションを開いて、クエリと結果の例をご覧ください。

各ペンギンの 1 日の移動距離はどれくらいですか？

各ペンギンが 1 日に移動する距離を計算するには、ST_LENGTH 関数を使用します。

SELECT
  penguin_id,
  route_date,
  ST_LENGTH(route) AS daily_distance_meters
FROM (
  -- Subquery to aggregate points into daily routes
  SELECT
    penguin_id,
    DATE(timestamp, 'UTC') AS route_date,
    ST_MAKELINE(ARRAY_AGG(location_cast_to_geog)) AS route
  FROM (
    -- Sub-expression to sort all location pings by timestamp
    -- This way you make sure individual points can form a realistic route
    SELECT
      penguin_details['penguin_id'] AS penguin_id,
      -- Extract and cast timestamp once
      CAST(CAST(penguin_details['timestamp'] AS STRING) AS TIMESTAMP) AS timestamp,
      -- Extract and cast location once
      ST_GEOGFROMTEXT(CAST(penguin_details['location'] AS STRING)) AS location_cast_to_geog
    FROM
      penguin_movements
    ORDER BY
      -- Pre-sorts location pings for the ARRAY_AGG function
      penguin_id, timestamp ASC
  )
  GROUP BY
    penguin_id,
    route_date
) AS DailyRoutes;

このクエリは、次のような結果を返します。

+------------+------------+-----------------------+
| penguin_id | route_date | daily_distance_meters |
+------------+------------+-----------------------+
| pen_01     | 2025-12-02 | 15420.7               |
| pen_03     | 2025-12-03 | 22105.1               |
| pen_32     | 2025-12-03 | 9850.3                |
+------------+------------+-----------------------+

1 日のルートのうち、ジオフェンスで囲まれたエリア内で発生する割合はどのくらいですか？

この質問に答えるには、ST_INTERSECTION 関数で結果をフィルタして、ペンギンのルートのどの部分がジオフェンスで囲まれたエリアと交差しているかを確認します。次に、ST_LENGTH 関数を使用して、その日の移動距離全体に対する採餌場内の移動距離の比率を計算します。

SELECT
  penguin_id,
  route_date,
  ST_LENGTH(ST_INTERSECTION(route, ST_GEOGFROMTEXT('POLYGON((-57.21 -64.51, -57.23 -64.55, -57.08 -64.56, -57.06 -64.51, -57.21 -64.51))'))) / ST_LENGTH(route) AS proportion_in_ground
FROM (
  -- Subquery to aggregate points into daily routes
  SELECT
    penguin_id,
    DATE(timestamp, 'UTC') AS route_date,
    ST_MAKELINE(ARRAY_AGG(location_cast_to_geog)) AS route
  FROM (
    -- Sub-expression to sort all location pings by timestamp
    -- This way you make sure individual points can form a realistic route
    SELECT
      penguin_details['penguin_id'] AS penguin_id,
      -- Extract and cast timestamp once
      CAST(CAST(penguin_details['timestamp'] AS STRING) AS TIMESTAMP) AS timestamp,
      -- Extract and cast location once
      ST_GEOGFROMTEXT(CAST(penguin_details['location'] AS STRING)) AS location_cast_to_geog
    FROM
      penguin_movements
    ORDER BY
      -- Pre-sorts location pings for the ARRAY_AGG function
      penguin_id, timestamp ASC
  )
  GROUP BY
    penguin_id,
    route_date
) AS DailyRoutes
WHERE
  ST_INTERSECTS(route, ST_GEOGFROMTEXT('POLYGON((-57.21 -64.51, -57.23 -64.55, -57.08 -64.56, -57.06 -64.51, -57.21 -64.51))'))

このクエリは、次のような結果を返します。

+------------+------------+----------------------+
| penguin_id | route_date | proportion_in_ground |
+------------+------------+----------------------+
| pen_01     | 2025-12-03 | 0.652                |
| pen_03     | 2025-12-03 | 0.918                |
| pen_32     | 2025-12-04 | 0.231                |
+------------+------------+----------------------+

継続的マテリアライズド ビューでクエリを最適化する

大規模なデータセットに対する地理空間クエリは、多くの行のスキャンが必要になるため、遅くなることがあります。Bigtable は専用の地理空間インデックスをサポートしていませんが、継続的マテリアライズド ビューを使用すると、クエリのパフォーマンスを改善できます。

このようなビューの一意の行キーを作成するには、GEOGRAPHY 型を S2 セルに変換します。S2 は、球面ジオメトリの複雑な三角関数を実行できるライブラリです。地理座標に基づいて距離を計算すると、計算費用が高くなる可能性があります。S2 セルは、地球の表面上の特定の領域を 64 ビットの整数として表すことができるため、継続的なマテリアライズド ビューを使用した地理空間インデックス作成に最適です。

S2 セルを定義するには、S2_CELLIDFROMPOINT(location, level) 関数を呼び出し、粒度 level 引数を指定します。この引数は 0 から 30 までの数値で、各セルのサイズを定義します。数値が大きいほどセルは小さくなります。

ペンギンの調査の例では、各ペンギンの位置とタイムスタンプでインデックス登録された継続的マテリアライズド ビューを作成できます。

-- Query used to create the continuous materialized view
SELECT
  -- Create S2 cells for each penguin's location.
  -- Note that the `level` value is the same for each location so that
  -- every cell is the same size. This ensures consistency for your data.
  S2_CELLIDFROMPOINT(ST_GEOGFROMTEXT(CAST(penguin_details['location'] AS STRING)), level => 16) AS s2_cell_id,
  penguin_details['timestamp'] AS observation_time,
  penguin_details['penguin_id'] AS penguin_id,
  penguin_details['location'] AS location
FROM
  penguin_movements

このビューにクエリを実行して「ジオフェンスを通過したのは誰か？」という質問に答えるには、2 段階のアプローチがはるかに効率的です。まず、継続的マテリアライズド ビューの cell_id インデックスを使用して、近傍内の候補行をすばやく見つけ、次に、縮小されたデータセットに正確な ST_CONTAINS 関数を適用します。

SELECT
  idx.penguin_id,
  idx.location,
  idx.observation_time
FROM
  penguin_s2_time_index AS idx
WHERE
  -- Part one: use an approximate spatial filter and timestamp filter
  -- for fast scans on continuous materialized view keys.
  -- Use the S2_COVERINGCELLIDS function to create an array of S2 cells
  -- that cover the feeding ground polygon. The `level` argument must be the
  -- same value as the one you used to create the continuous materialized view.
  idx.s2_cell_id IN UNNEST(S2_COVERINGCELLIDS(
    ST_GEOGFROMTEXT('POLYGON((-57.21 -64.51, -57.23 -64.55, -57.08 -64.56, -57.06 -64.51, -57.21 -64.51))'),
    min_level => 16,
    max_level => 16,
    max_cells => 500
  ))
  AND idx.observation_time >= '2025-12-03 00:00:00 UTC'
  AND idx.observation_time < '2025-12-04 00:00:00 UTC'
  -- Part two: use ST_CONTAINS() on the returned set to ensure precision.
  -- S2 cells are squares, so they don't equal arbitrary geofence polygons.
  -- You still need to check if a specific point is contained within the area,
  -- but the filter applies to a smaller data set and is much faster.
  AND ST_CONTAINS(ST_GEOGFROMTEXT('POLYGON((-57.21 -64.51, -57.23 -64.55, -57.08 -64.56, -57.06 -64.51, -57.21 -64.51))'), idx.location);

制限事項

Bigtable は、地理データを使用する場合、次の機能をサポートしていません。

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

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

次のステップ

• 利用可能な地理関数について学習する。GoogleSQL 地理関数をご覧ください。