BigQuery GEOGRAPHY データ型と活用ガイド – ロード・関数・最適化

Contents

1 1. GEOGRAPHY データ型の基本
- 1.1 1‑1. 基本的なジオメトリ生成例
2 2. Cloud Storage から GEOGRAPHY データをインポートする手順
- 2.1 2‑1. GeoJSON（FeatureCollection）をロード
- 2.2 2‑2. CSV／TSV に格納された WKT / WKB をロード
3 3. 主な GoogleSQL 空間関数と実装例
- 3.1 3‑1. シナリオ別クエリ例
4 4. パフォーマンス最適化 ― パーティション・クラスタリング
- 4.1 4‑1. 日付パーティションと GEOGRAPHY クラスタリングの組み合わせ例
- 4.2 4‑2. 関数インデックスについて（現状）
5 5. 結果の可視化とセキュリティ
- 5.1 5‑1. Looker Studio（旧 Data Studio）でのマップ表示
  - 5.1.1 GeoJSON エクスポート例（SQL）
- 5.2 5‑2. IAM と暗号化のベストプラクティス
  - 5.2.1 実装例（CLI）
6 まとめ ― 実務で即活用できるチェックリスト

スポンサードリンク

1. GEOGRAPHY データ型の基本

項目	内容
座標系	WGS‑84 (SRID 4326) に固定。別途 SRID を指定する必要はありません。
サポート対象	`POINT`, `LINESTRING`, `POLYGON` などの単純ジオメトリと、`MULTI*` 系列、`GEOMETRYCOLLECTION` がすべて利用可能です。
内部実装	GEOS ライブラリをベースにした高速な空間アルゴリズムが組み込まれています（公式ドキュメント参照：BigQuery GIS Functions）。
単位	距離系関数はメートル、面積系関数は平方メートルで返します。

1‑1. 基本的なジオメトリ生成例

-- POINT（東京駅）
SELECT ST_GeogFromText('POINT(139.767125 35.681236)') AS tokyo_station;

-- LINESTRING（2 点間の直線）
SELECT ST_GeogFromText('LINESTRING(139.767125 35.681236, 139.70 35.68)') AS line_example;

-- POLYGON（簡易四角形エリア）
SELECT ST_GeogFromText(
         'POLYGON((139.70 35.65,
                   139.80 35.65,
                   139.80 35.75,
                   139.70 35.75,
                   139.70 35.65))') AS area_polygon;

-- POINT（東京駅）

SELECT ST_GeogFromText('POINT(139.767125 35.681236)') AS tokyo_station;

-- LINESTRING（2 点間の直線）

SELECT ST_GeogFromText('LINESTRING(139.767125 35.681236, 139.70 35.68)') AS line_example;

-- POLYGON（簡易四角形エリア）

SELECT ST_GeogFromText(

'POLYGON((139.70 35.65,

139.80 35.65,

139.80 35.75,

139.70 35.75,

139.70 35.65))') AS area_polygon;

ポイント
ST_GeogFromText（またはエイリアスの ST_GEOGFROMTEXT）が文字列 → GEOGRAPHY の変換入口です。lon lat の順序で記述し、必ず空白で区切ります。

2. Cloud Storage から GEOGRAPHY データをインポートする手順

2‑1. GeoJSON（FeatureCollection）をロード

手順概要

ファイルのアップロード
bash gsutil cp stores.geojson gs://my-bucket/geodata/
テーブルスキーマ定義（name:STRING, location:GEOGRAPHY）と同時にテーブル作成
データロード (bq load)

完全なコマンド例

# ① テーブル作成（自動検出は無効化し、明示的にスキーマを指定）
bq mk \
  --schema name:STRING,location:GEOGRAPHY \
  my_dataset.store_locations

# ② データロード
bq load \
  --source_format=NEWLINE_DELIMITED_JSON \
  --replace \
  my_dataset.store_locations \
  gs://my-bucket/geodata/stores.geojson

# ① テーブル作成（自動検出は無効化し、明示的にスキーマを指定）

bq mk \

--schema name:STRING,location:GEOGRAPHY \

my_dataset.store_locations

# ② データロード

bq load \

--source_format=NEWLINE_DELIMITED_JSON \

--replace \

my_dataset.store_locations \

gs://my-bucket/geodata/stores.geojson

補足
- GeoJSON の coordinates は必ず [lon, lat]（経度、緯度）の順です。逆順の場合はロード時にエラーになります。
- NEWLINE_DELIMITED_JSON では各行が独立した JSON オブジェクトである必要があります。配列形式のファイルは事前に jq 等で変換してください。

ロード結果確認

SELECT name,
       ST_AsText(location) AS wkt,
       ST_X(location)      AS longitude,
       ST_Y(location)      AS latitude
FROM my_dataset.store_locations
LIMIT 5;

SELECT name,

ST_AsText(location) AS wkt,

ST_X(location) AS longitude,

ST_Y(location) AS latitude

FROM my_dataset.store_locations

LIMIT 5;

2‑2. CSV／TSV に格納された WKT / WKB をロード

サンプル CSV（`routes.csv`）

route_id,path_wkt
1,&quot;LINESTRING(139.767125 35.681236,139.70 35.68)&quot;
2,&quot;POLYGON((139.70 35.65,139.80 35.65,139.80 35.75,139.70 35.75,139.70 35.65))&quot;

route_id,path_wkt

1,"LINESTRING(139.767125 35.681236,139.70 35.68)"

2,"POLYGON((139.70 35.65,139.80 35.65,139.80 35.75,139.70 35.75,139.70 35.65))"

ロード手順

# テーブル作成（CSV 用にフィールド区切りはカンマ、ヘッダー行をスキップ）
bq mk \
  --schema route_id:INT64,path:GEOGRAPHY \
  my_dataset.routes

# データロード
bq load \
  --source_format=CSV \
  --skip_leading_rows=1 \
  --field_delimiter=&quot;,&quot; \
  my_dataset.routes \
  gs://my-bucket/geodata/routes.csv

# テーブル作成（CSV 用にフィールド区切りはカンマ、ヘッダー行をスキップ）

bq mk \

--schema route_id:INT64,path:GEOGRAPHY \

my_dataset.routes

# データロード

bq load \

--source_format=CSV \

--skip_leading_rows=1 \

--field_delimiter="," \

my_dataset.routes \

gs://my-bucket/geodata/routes.csv

ポイント
- GEOGRAPHY カラムは WKT（文字列）だけでなく、Base64 エンコードされた WKB も受け付けます。WKB を使用する場合は CSV の代わりに AVRO や PARQUET が推奨されます（スキーマが自動的にバイナリ型として認識されるため）。

ロード結果確認

SELECT route_id,
       ST_AsText(path) AS wkt_representation,
       ST_IsValid(path) AS is_valid_geom
FROM my_dataset.routes;

SELECT route_id,

ST_AsText(path) AS wkt_representation,

ST_IsValid(path) AS is_valid_geom

FROM my_dataset.routes;

3. 主な GoogleSQL 空間関数と実装例

以下に、業務シナリオで頻繁に使われる 5 種類の関数 とそれぞれのサンプルクエリを示します。すべて GEOGRAPHY 型を第一引数に取ります。

関数主な用途サンプルコード

ST_GeogFromText(text)

文字列 → GEOGRAPHY

SELECT ST_GeogFromText('POINT(139.767125 35.681236)');

1	SELECT ST_GeogFromText('POINT(139.767125 35.681236)');

ST_Contains(polygon, point)

ポリゴンが点を包含か判定

SELECT store_id FROM stores WHERE ST_Contains(area_geom, location);

1	SELECT store_id FROM stores WHERE ST_Contains(area_geom, location);

ST_Distance(g1, g2)

２ジオメトリ間の直線距離（m）

SELECT ST_Distance(customer_loc, depot_loc)/1000 AS km FROM trips;

1	SELECT ST_Distance(customer_loc, depot_loc)/1000 AS km FROM trips;

ST_Union(geog_array)

複数ジオメトリの統合ポリゴン生成

SELECT ST_AsText(ST_Union(array_agg(location))) FROM stores;

1	SELECT ST_AsText(ST_Union(array_agg(location))) FROM stores;

ST_Buffer(geom, radius_meters)

指定半径のバッファ（円形）ポリゴン作成

SELECT store_id, ST_AsGeoJSON(ST_Buffer(location, 5000)) FROM stores;

1	SELECT store_id, ST_AsGeoJSON(ST_Buffer(location, 5000)) FROM stores;

3‑1. シナリオ別クエリ例

(a) エリア内店舗数の集計

WITH target_area AS (
  SELECT ST_GeogFromText(
           'POLYGON((139.70 35.65,139.80 35.65,139.80 35.75,139.70 35.75,139.70 35.65))'
         ) AS geom
)
SELECT COUNT(*) AS store_cnt
FROM my_dataset.store_locations s
CROSS JOIN target_area a
WHERE ST_Contains(a.geom, s.location);

WITH target_area AS (

SELECT ST_GeogFromText(

'POLYGON((139.70 35.65,139.80 35.65,139.80 35.75,139.70 35.75,139.70 35.65))'

) AS geom

)

SELECT COUNT(*) AS store_cnt

FROM my_dataset.store_locations s

CROSS JOIN target_area a

WHERE ST_Contains(a.geom, s.location);

(b) 顧客までの配送距離（km）を算出

SELECT customer_id,
       ROUND(ST_Distance(customer_loc, depot_loc)/1000, 2) AS distance_km
FROM my_dataset.customers;

SELECT customer_id,

ROUND(ST_Distance(customer_loc, depot_loc)/1000, 2) AS distance_km

FROM my_dataset.customers;

(c) 複数エリアの統合ポリゴンと面積取得

SELECT ST_Area(ST_Union(geom)) / 1e6 AS area_sqkm   -- 平方メートル → 平方キロメートル
FROM (
  SELECT location AS geom FROM my_dataset.store_locations
);

SELECT ST_Area(ST_Union(geom)) / 1e6 AS area_sqkm -- 平方メートル → 平方キロメートル

FROM (

SELECT location AS geom FROM my_dataset.store_locations

);

(d) 5 km バッファポリゴンの GeoJSON 出力（可視化用）

SELECT store_id,
       ST_AsGeoJSON(ST_Buffer(location, 5000)) AS buffer_geojson
FROM my_dataset.store_locations;

SELECT store_id,

ST_AsGeoJSON(ST_Buffer(location, 5000)) AS buffer_geojson

FROM my_dataset.store_locations;

注意
- ST_Buffer の半径は必ず メートル 単位です。
- 大規模テーブルで頻繁に ST_Contains を実行する場合は、クラスタリング（後述）を活用するとスキャン量が大幅に削減できます。

4. パフォーマンス最適化 ― パーティション・クラスタリング

4‑1. 日付パーティションと GEOGRAPHY クラスタリングの組み合わせ例

bq mk \
  --time_partitioning_type=DAY \
  --time_partitioning_field=transaction_date \
  --clustering_fields=location \
  --schema=transaction_id:STRING,transaction_date:DATE,store_id:STRING,location:GEOGRAPHY \
  my_dataset.transactions

bq mk \

--time_partitioning_type=DAY \

--time_partitioning_field=transaction_date \

--clustering_fields=location \

--schema=transaction_id:STRING,transaction_date:DATE,store_id:STRING,location:GEOGRAPHY \

my_dataset.transactions

項目	効果
日付パーティション	クエリで `WHERE transaction_date BETWEEN ...` を指定すれば、対象日のみをスキャン。
GEOGRAPHY クラスタリング	同一緯度経度帯のレコードが同じブロックに集まり、`ST_Contains`, `ST_Distance` 等の空間フィルタが早期に適用される。

ベストプラクティス
- クラスタリングキーは 単一カラム が推奨（複数指定も可能だが、ジオメトリ型は 1 カラムだけに留める）。
- データ挿入時に INSERT を大量に行う場合は、バッチ書き込みでパーティション単位のデータ量を 10 GB 未満 に抑えるとスロット使用が安定します。

4‑2. 関数インデックスについて（現状）

2024 年 11 月時点では BigQuery は 関数ベースのインデックス（例: CREATE INDEX … USING FUNCTION）をサポートしていません。代わりに次のテクニックで同等効果が得られます。

事前計算カラム
例えば、エリアの重心 (ST_Centroid) やバッファポリゴンを別テーブルに保持し、JOIN のキーとして使用する。
マテリアライズドビュー
sql CREATE MATERIALIZED VIEW my_dataset.store_with_buffer AS SELECT store_id, location, ST_Buffer(location, 5000) AS buffer_5km FROM my_dataset.store_locations;
ビューは自動的に更新され、ST_Contains(buffer_5km, point) のようなクエリが高速化します。

5. 結果の可視化とセキュリティ

5‑1. Looker Studio（旧 Data Studio）でのマップ表示

手順	内容
データソース作成	Looker Studio → 「データソース」→「Google BigQuery」から対象テーブルを選択。
ジオメトリ列設定	`location` カラムが自動的に「地理情報」タイプとして認識され、マップウィジェットで使用可能になる。
カスタム計算フィールド	必要に応じて `ST_AsGeoJSON(location)` を SQL クエリのサブクエリとして組み込み、属性別に色分けできる。

GeoJSON エクスポート例（SQL）

SELECT store_id,
       ST_AsGeoJSON(location) AS geojson
FROM my_dataset.store_locations;

SELECT store_id,

ST_AsGeoJSON(location) AS geojson

FROM my_dataset.store_locations;

活用シナリオ
- 上記結果を CSV でエクスポートし、gsutil cp で Cloud Storage に保存 → Google Maps Platform の Data レイヤとしてインポート。これにより、Web アプリ上でインタラクティブなマップが実装できます。

5‑2. IAM と暗号化のベストプラクティス

項目	推奨設定
IAM ロール	データ閲覧は `roles/bigquery.dataViewer`、データ編集は `roles/bigquery.dataEditor`。機密データへのアクセスは最小権限のカスタムロールで制御。
CMEK（Customer‑Managed Encryption Key）	テーブル作成時に `--encryption_key=projects/PROJECT_ID/locations/us/keyRings/RING/cryptoKeys/KEY` を指定し、キーは Cloud KMS で管理。定期的なローテーションとアクセス監査ログの有効化を忘れずに。
データセットレベルのポリシー	`bq show --format=prettyjson my_dataset` で現在のアクセスポリシーを確認し、必要に応じて `gcloud projects add-iam-policy-binding` で追加。

実装例（CLI）

# 1. データセットに閲覧権限付与
gcloud projects add-iam-policy-binding $PROJECT_ID \
  --member=user:analyst@example.com \
  --role=roles/bigquery.dataViewer

# 2. CMEK を使用してテーブル作成（例：顧客情報テーブル）
bq mk \
  --encryption_key=projects/$PROJECT_ID/locations/us/keyRings/my-ring/cryptoKeys/geodata-key \
  my_dataset.secure_customers

# 1. データセットに閲覧権限付与

gcloud projects add-iam-policy-binding $PROJECT_ID \

--member=user:analyst@example.com \

--role=roles/bigquery.dataViewer

# 2. CMEK を使用してテーブル作成（例：顧客情報テーブル）

bq mk \

--encryption_key=projects/$PROJECT_ID/locations/us/keyRings/my-ring/cryptoKeys/geodata-key \

my_dataset.secure_customers

ポイント
- CMEK はテーブル単位だけでなく、データセット全体にも適用可能です。bq update --encryption_key=... で既存テーブルに対して後からキーを設定できます（ただし再暗号化には時間がかかります）。
- IAM の変更は Propagation に最大数分の遅延があるため、権限付与直後は bq show で確認してください。

まとめ ― 実務で即活用できるチェックリスト

項目	実施状況
✅ GEOGRAPHY 型の基本的な作成・変換 (`ST_GeogFromText`) を理解したか
✅ GeoJSON、CSV（WKT/WKB）から Cloud Storage 経由でロードできる手順を把握したか
✅ 代表的空間関数（`ST_Contains`, `ST_Distance`, `ST_Union`, `ST_Buffer`）の使用例を実装できたか
✅ 日付パーティション＋GEOGRAPHYクラスタリングでテーブル設計したか
✅ Looker Studio でマップ可視化し、必要に応じて GeoJSON エクスポートができるか
✅ IAM ロールと CMEK によるセキュリティ対策を設定したか

次のステップ
1. 本稿で紹介したサンプルを自プロジェクトにコピーし、データ投入 → クエリ実行 → 可視化 のフローを体感してください。
2. 大規模データセット（数十億レコード）に対しては クラスタリングのキー選定 と マテリアライズドビュー を組み合わせ、クエリコスト削減を実証しましょう。

本稿の情報は 2024 年 10 月時点の公式ドキュメント（Google Cloud → BigQuery GIS）に基づいています。機能追加や仕様変更があった場合は、最新のリファレンスをご確認ください。

スポンサードリンク

BigQuery GEOGRAPHY データ型と活用ガイド – ロード・関数・最適化

1. GEOGRAPHY データ型の基本

1‑1. 基本的なジオメトリ生成例

2. Cloud Storage から GEOGRAPHY データをインポートする手順

2‑1. GeoJSON（FeatureCollection）をロード

手順概要

完全なコマンド例

ロード結果確認

2‑2. CSV／TSV に格納された WKT / WKB をロード

サンプル CSV（routes.csv）

ロード手順

ロード結果確認

3. 主な GoogleSQL 空間関数と実装例

3‑1. シナリオ別クエリ例

(a) エリア内店舗数の集計

(b) 顧客までの配送距離（km）を算出

(c) 複数エリアの統合ポリゴンと面積取得

(d) 5 km バッファポリゴンの GeoJSON 出力（可視化用）

4. パフォーマンス最適化 ― パーティション・クラスタリング

4‑1. 日付パーティションと GEOGRAPHY クラスタリングの組み合わせ例

4‑2. 関数インデックスについて（現状）

5. 結果の可視化とセキュリティ

5‑1. Looker Studio（旧 Data Studio）でのマップ表示

GeoJSON エクスポート例（SQL）

5‑2. IAM と暗号化のベストプラクティス

実装例（CLI）

まとめ ― 実務で即活用できるチェックリスト

サンプル CSV（`routes.csv`）

(d) 5 km バッファポリゴンの GeoJSON 出力（可視化用）