BigQuery で GeoJSON を活用した地理空間分析

※この投稿は米国時間 2022 年 3 月 23 日に、Google Cloud blog に投稿されたものの抄訳です。

多くの分析ワークロードで最初に行う手順は、分析に必要なデータをデータ ウェアハウスに取り込むことです。ポイント、ライン、ポリゴンデータが関係する地理空間分析では、地理空間データのデータ形式が多種多様であるため、取り込みが複雑になる場合があります。地理空間の形式で最も一般的なのは、GeoJSON と GeoJSON-NL（改行区切りの GeoJSON）の 2 つです。

このブログでは、BigQuery の新しい GeoJSON-NL 読み込み機能を使用して、GeoJSON-NL ファイルから直接、BigQuery テーブルにデータを読み込む方法について説明します。また、GeoJSON-NL 形式やそのメリットについても触れ、さまざまな空間データ形式を GeoJSON-NL に変換するシンプルなツールを紹介し、実践的なコード例についても詳しく説明していきます。

BigQuery の地理空間に関するサポートにより、ユーザーは専用の地理空間ツールではなく、データ ウェアハウスでそのまま大規模なポイント、ライン、ポリゴンデータから成る GEOGRAPHY データ型をインポートおよび分析できるようになります。BigQuery で地理空間データを簡単に使用できるように、Google では最近、bq load と外部テーブルを使用する GeoJSON-NL ファイルの直接サポートを追加しました。

GeoJSON-NL ファイルとは

GeoJSON-NL とは、改行区切りの GeoJSON の略称です。GeoJSON-NL ファイルは、各対象物が「\n」の改行文字で区切られている GeoJSON 地理的対象物で構成されています。GeoJSON-NL ファイルが GeoJSON ファイルと異なる点は、GeoJSON ではファイル内に複数の「Feature」オブジェクトを持つ単一の「FeatureCollection」オブジェクトがあり、その代わりに改行区切りの GeoJSON ファイルではそれぞれが固有のライン上にある「Feature」オブジェクトのみを保有しています。

GeoJSON 形式のファイル

上のコードブロックは、GeoJSON 形式のファイルです。以下は、同等の GeoJSON-NL 形式のファイルを示しています。

2 つの形式を見分ける簡単な方法は、ファイル内に「FeatureCollection」JSON オブジェクトがあるかを確認することで、それがあれば GeoJSON ということになります。また、各ライン上に「Feature」JSON オブジェクトが 1 つだけある場合は、GeoJSON-NL ということになります。

GeoJSON-NL が空間の形式である理由

GeoJSON は、数多くのプラットフォームにわたる幅広いアプリケーションで機能する優れた空間のファイル形式で、JSON のサブセットです。ですが、GeoJSON の問題は、すべての対象物が大規模な配列で「FeatureCollection」オブジェクトとして保存されることです。大規模な配列を大規模な JSON 文字列で解析することが、パフォーマンスのボトルネックとなっています。配列内に保存されたすべての対象物は、ソースファイルの単一 CPU スレッドによってメモリに読み込んで解析する必要があり、それがメモリ不足エラーによる ETL ジョブの失敗につながっています。もし FeatureCollection 内の 1 つの対象物に問題があれば、解析および読み込みジョブ全体が失敗する可能性があります。

改行区切りの GeoJSON では、配列内ではなく固有のラインに各対象物を保存することで、標準的な GeoJSON ファイルが持つ問題を解決し、IoT のストリーミングなどの大量の空間データを操作するアプリケーションのパフォーマンスと効率を高めることが可能となります（例）。対象物は複数のスレッドや分散プロセスから読み取りまたは書き込みが可能で、各対象物は個別に解析できるため、メモリ フットプリントを抑えながら、より堅牢なスキーマやジオメトリの検証が実現できます。

空間データの GeoJSON-NL への変換

空間データは、多様なオープンソースや専用ツールを使用して GeoJSON-NL 形式に変換できます。以下のセクションは、GeoJSON、shapefiles、GPX、KML、CSV ファイルを GeoJSON-NL に変換する際のコードの例です。GCP Cloud Shell（始めとしては最も簡単）、ローカル開発マシン（Linux ベースまたは MacOS ベース）、または Node.js か Python のランタイム環境を使用する GCP Cloud Functions の関数を活用することで以下のコードの例を使用することが可能です。

• jq（インストール）- JSON データを操作するコマンドライン ツールで、GeoJSON ファイルまたはオブジェクトを GeoJSON-NL ファイルに変換する際に使用します。

• geojson2ndjson - node.js のコマンドライン ツールで、GeoJSON を GeoJSON-NL に変換します。

その他のライブラリは jq や geojson2ndjson と組み合わせることができ、CSV、KML、shapefiles、GPX などの空間形式を BQ での使用のために GeoJSON-NL に変換します。

• shapefile - node.js のコマンドライン ツールで、--newline-delimited オプションを使用して shapefiles を geojson-nl などのその他の空間形式に変換します。

例:

• togeojson - node.js のコマンドライン ツールで、KML、TCX、GPX ファイルを GeoJSON に変換します。例:

• csv2geojson - node.js のコマンドライン ツールで、CSV ファイルを GeoJSON に変換します。また、--lat と --lon のパラメータを指定して、緯度列と経度列のヘッダー名を指定します。例:

• Fiona - Python の空間パッケージで、コンバージョン、分析、スクリプトのワークロード向けの最も一般的な空間データ形式で読み取りおよび書き込みを行います。

• GDAL - 強力なコマンドラインの空間ツールセットで、最も一般的な地理空間ファイル形式を、座標系の変換といったより複雑な地理空間機能に変換する ogr2ogr コマンドを含みます。

• FME - パートナー ツールで、多種多様な型や形式の読み込みを可能にします。  非常に有用です。商用ライセンスが必要です。

BigQuery で GeoJSON-NL を操作

BigQuery のコマンドライン ツールである「bq load」を使うか、外部テーブルを使用することで、BQ テーブルに GeoJSON-NL データを読み込むことができます。

bq load は、開発マシンにインストールされた GCP Cloud SDK で操作するか、GCP Cloud Console を介して操作します。bq load を使用して bq テーブルに GeoJSON-NL ファイルをインポートする際の一般的な構造は、以下の通りです。

たとえば、以下を使って単一の Feature を含む GeoJSON-NL ファイルを取り込むために --autodetect フラグを伴う bq load を使用すると、結果として以下の BQ テーブルとスキーマが得られます。

GeoJSON-NL 形式のファイルをインポートするには、ソース形式と json の拡張子を指定します。

GeoJSON-NL ファイルからインポートしたテーブルの自動検出を行うか、スキーマの指定を行うことができます。対象物の「geometry」キー内のすべてのデータは、「GEOGRAPHY」データ型の「geometry」という名前のテーブル列に読み込まれます。その他のテーブルのフィールド名やデータ型は、「properties」オブジェクトと「id」オブジェクトで分析されます。

それとは別に、Bigtable、Cloud Storage、Google ドライブに保存された GeoJSON-NL ファイルから外部テーブルを作成する方法もあります。外部テーブルは、1 つのコマンドでデータの読み込みとクリーニングを行う必要がある「ELT」（抽出、読み込み、変換）のワークロード向けとして役に立つだけではなく、リアルタイムの天気予報といった頻繁に更新される外部データの結合にも便利です。外部テーブルにはたくさんの制限事項があるため、ユースケースに適している場合にのみ使用するようにしましょう。

ハンズオンのコード例 - Airbnb の掲載施設の分析

それでは、例を用いて BigQuery での GeoJSON-NL データの分析を開始しましょう。  もし説明に沿って取り組む場合は、こちらから BigQuery ワークスペースにログインしてください。まだお持ちでない場合は、BigQuery サンドボックスで 1 分もかからずに無料で設定していただけます。

たとえば、ハワイ旅行を計画中であると想定します。そして、希望する価格帯で掲載施設の選択肢が最も多いのは、どの島のどの地区であるかを把握したいと考えています。この分析を行うためには、Airbnb の掲載施設に関する統計情報と、地区の境界データが必要です。幸いにも、Airbnb は opendatasoft のようなデータハブ上でクリエイティブ・コモンズのライセンスに従って全体にデータを提供しており、今回の例ではそれを使用します。

前提事項

1. BigQuery サンドボックスと Cloud Console へアクセスします

BigQuery へのデータの読み込み

1. 新しい GCP プロジェクトを作成し、請求が有効になっているか、BigQuery サンドボックスが使用可能であることを確認します

2. すべてデフォルトの設定でプロジェクト内に固有の名前の新しい GCS バケットを作成します。このコードの例では「geojson-bq-example」という名前を使用しますが、自分で固有のバケット名を作成し、ご自身の例で参照する必要があることに留意してください。

3. 「geojson_examples」と名前を付けた BQ ワークスペース内にデータセットを作成します

4. BigQuery UI を開き、Cloud Shell を有効にします

5. 次に、分析用にデータをダウンロードし、geojson-nl 形式に変換した後、Cloud Storage に保存します。この作業はすべて、cURL と gsutil を使用して Cloud Shell で行うことができます。

5.1 ソースデータのファイルは以下の 2 つです。

5.1.a 「listings data」のファイルには、空き状況や価格に加え、経度と緯度のペアによる掲載施設のロケーションといった特定の指標を含む、CSV 形式のハワイでの Airbnb の掲載施設情報が含まれています。

5.1.b GeoJSON 形式の「neighborhood data」ファイルには、ハワイの各地区の名前がポリゴン ジオメトリとともに収められています。

5.2 まずは、cURL で opendatasoft からハワイの Airbnb の掲載施設のデータを取得し、gsutil で Cloud Storage バケットにアップロードします。

5.2.a すでに単一の列内に経度と緯度のデータとして「ポイント」のデータを保有しているため、掲載施設のデータの CSV を GeoJSON-NL 形式に変換する必要はありません。BQ にファイルを読み込んだ後で、次の手順で列を GEOGRAPHY データ型に変換します。

5.2.b Cloud Console に以下のコマンドを入力します。

5.3. これで、cURL を使用して opendatasoft から Geojson 形式で Airbnb のハワイの地区データが獲得できるため、jq を使って Geojson から GeoJSON-NL 形式に変換し、gsutil で Cloud Storage バケットにアップロードします。

5.3.a 次のコマンドを Cloud Shell に入力します。

6. これでソースデータが Cloud Storage にステージングされ、bq load を使用して BigQuery にデータを読み込めるようになりました。

6.2. その後で GCS バケットから「airbnb-hi-listings.csv」ファイルを、geojson_examples.airbnb-hi-listings という名前の BigQuery テーブルに読み込みます。以下のコマンドを Cloud Shell に入力します。

BigQuery でのデータの分析

これでハワイ州の Airbnb のデータが BQ に読み込まれ、BQ の地理空間機能を活用して分析できるようになりました。まず初めに、一覧の中から地区を確認してみましょう。

1. 「airbnb-hi-locations」テーブルの geography 列の上でクラスタリングして、ネイティブの「geography」ポイント列を持つテーブルを作成します。これで、WHERE 句内で ST_Contains() を使用する「ポイント イン ポリゴン」クエリのような地理空間分析がスピードアップします。これは、次の地区のジオメトリで掲載施設の指標を集計する手順で使用します。

1a. BQ ドキュメント内の地理空間データの変換と不適切な形式の空間データの処理について詳しく確認します。

ジオメトリ データはソースファイル内では「latitude,longitude」形式であるため、ST_GEOGPOINT で GEOGRAPHY 型のポイントを作成するときに、上記の SQL クエリで順序を逆にすることに留意してください。

2. GeoViz でビジュアリゼーションを作成してクエリの結果を確認します。

3. クエリを実行して、地理関数である「ST_Contains」を使用してハワイの各地区の Airbnb での掲載施設数をカウントします。

3.1. 「ST_Contains」関数は、地理関数間で空間を結合します。今回のケースでは、Airbnb の各掲載施設の「ポイント」を確認し、ハワイの地区の地理的な「ポリゴン」内であるかを見極めます。ポイントが地区のポリゴン内である場合、関数は「true」を返し、そうでない場合は「false」を返します。

3.2. クエリを「neighborhood」でグループ化して集計し、「count」関数を使用して句によってグループ内の掲載施設数を降順で返します。

4. ここで、地区ごとに 1 行のクリーンな地区のジオメトリ テーブルを作成し、新しいテーブルとして保存して、GeoViz でテーブル出力を可視化します。

5. これで、ハワイの Airbnb で最も掲載施設が多く、最も平均宿泊料金が高く、最適な空き状況で、最もレビューが多い地区がどこであるかを「entire home/apt」掲載施設から把握するためのクエリが作成できるようになり、GeoViz で結果の可視化が可能となりました。

データポータル ダッシュボードの作成

ここからは、データポータルにインタラクティブ ダッシュボードを作成し、データに対して別の質問も探索して、休暇に最適な地区を決定します。

1. 上記の手順 5 のクエリを実行した後で、BigQuery の結果パネルで [データを探索] -> [データポータル] をクリックします。

2. たった今作成した BigQuery カスタム SQL データソース向けのデータポータル GUI では、「Table」チャートを作成し、「available fields」列から「Metric」列に指標をドラッグ＆ドロップします。

3. ここで、地区のポリゴン ジオメトリから塗り分けマップチャートを作成していきます。[グラフを追加] -> [塗り分けマップ] をクリックし、以下のスクリーンショットのように、フィールドのロケーション、地理空間フィールド、カラーの指標を構成します。

4. データポータル ダッシュボードに 2 つのフィルタを追加します。1 つは「avg price」で、もう 1 つは「num listings」です。こうしたフィルタによってユーザーは、休暇を過ごす場所の選定基準に合う地区を見つけるために、データに関する質問ができるようになります。

5. このデータポータル ダッシュボードを使うことで、希望する価格帯で Airbnb の最高の選択肢を得ることができるのはどの島のどの地区なのか、という元々の疑問に答えを出すことができます。1 泊 $300 以下という予算に合う平均掲載価格と、Airbnb での掲載施設の選択肢が多くある地区（300 以上の掲載施設が利用可能）でフィルタします。

価格と人気の基準をすべて満たしていた地区は、オアフのプライマリー アーバン センター、ハワイ島のプナ、カウアイのカパア - ワリスであったため、Airbnb での民泊施設探しはこれらの地区を中心に行うことにします。

結論

BigQuery の新しい機能により、ユーザーは bq load を使って GEOMETRY 列で直接、テーブルに GeoJSON-NL ファイルを読み込めるようになります。GeoJSON-NL ファイルのサポートにより、ポイント、linestring、ポリゴン空間データの BigQuery 分析ワークロードへの読み込みが、より迅速かつ簡単になります。

- ソリューション エンジニアリング マネージャー Ryan Baumann
- ソフトウェア エンジニア Mariëtte Luke