Zenn
🗺️

OSMデータをPostgreSQLに取り込む：hstore拡張の有無による違い

2024/12/04に公開
postgis
OpenStreetMap
pbf
osm2pgsql
tech

はじめに

PBFで取得した位置情報データの取り扱いについての続編です。
下記の記事も併せてご覧になることをお勧めします。

QGISでPostGISでのPBFデータの読み込み方法
PBFデータをGeoJSONとMBTilesに変換する方法

今回上記記事のPostGISでの取り扱いの際（PostGIS拡張）、hstore拡張を使用するかどうかでデータに違いが生まれます。本記事では、その違いを解説するとともに、具体的なプロセスやクエリを用いて比較します。

PBFデータをPostgreSQLに取り込むには、osm2pgsqlというツールを使用します。osm2pgsqlは、データを以下の方法(オプション)でデータベースにインポートできます。

  • hstore拡張なし: タグデータをあらかじめ定義されたカラムに展開。
  • hstore拡張あり: タグデータをhstore型で保持（すべてのタグを動的に取得可能）。

どちらの方法を選択するかは、データの利用目的や柔軟性のニーズに応じて異なります。

インポートのプロセス

データベースの準備

Sample Data : us-midwest-lataset.osm.pbf

今回はサンプルとしてGeofabrikでダウンロード可能なNorth AmericaのUS Midwestのデータを活用します。(約2GB)

PostgreSQLに接続して、2つのデータベースを作成します。(SQL)

今回はあえて差があることを見るために、下記のDB名ですが、任意のものをご利用ください。

CREATE DATABASE db_without_hstore;
CREATE DATABASE db_with_hstore;

それぞれのデータベースで拡張を有効化します。(SQL)

db_without_hstore
\c db_without_hstore
CREATE EXTENSION postgis;
db_with_hstore
\c db_with_hstore
CREATE EXTENSION postgis;
CREATE EXTENSION hstore;

データのインポート

以下のコマンドで、PBFデータをインポートします。(bash)

hstore拡張なし

osm2pgsql -c -d db_without_hstore -U $(whoami) --slim -r pbf us-midwest-lataset.osm.pbf

hstore拡張あり

osm2pgsql -c -d db_with_hstore -U $(whoami) --hstore --hstore-add-index --slim -r pbf us-midwest-lataset.osm.pbf

これでPostgresデータベースにインポートできました。

違いの解説

以下で、両者がどのように違ってくるかを例を使って確認します。

共通するクエリ

まず、両者で共通して実行可能なクエリを確認します。以下のクエリは、デトロイト周辺（緯度経度指定）のデータを取得します。

SELECT osm_id, ST_AsText(way) AS geometry
FROM planet_osm_point
WHERE ST_Within(
    way,
    ST_MakeEnvelope(-83.3, 42.2, -82.9, 42.4, 3857)
);

結果例（共通）:

  osm_id   |            geometry
-----------+---------------------------------
  12345678 | POINT(-83.0458 42.3314)
  87654321 | POINT(-83.0377 42.3376)

hstore拡張あり：すべてのタグを動的に取得可能

hstoreを使用すると、すべてのタグがtagsカラムに格納され、動的に取得できます。

1. 特定のタグを取得

以下のクエリで、highwayタグを持つデータを取得し、nameタグの値も同時に確認します。

SELECT osm_id, tags -> 'highway' AS highway, tags -> 'name' AS name
FROM planet_osm_point
WHERE tags ? 'highway';

結果例：

  osm_id   |      highway       |        name
-----------+--------------------+-----------------
  12345678 | traffic_sign       | 
  87654321 | secondary          | Michigan Avenue

2. タグキー一覧の確認

データに含まれるすべてのタグキーを確認することも可能です。

SELECT DISTINCT skeys(tags) AS tag_key
FROM planet_osm_point;

結果例:

     tag_key
---------------
 highway
 name
 amenity
 shop

hstore拡張なし：事前定義されたカラムに展開

1. 定義済みカラムのデータを確認

事前定義されたカラム（例: highway, name）のみが格納されます。

SELECT osm_id, highway, name
FROM planet_osm_point
WHERE highway = 'traffic_signals';

結果例：

osm_id | highway | name
-----------+--------------------+-----------------
12345678 | traffic_signals |
87654321 | secondary | Michigan Avenue

2. カスタムタグは無視される

traffic_zone:direction(例)のようなカスタムタグは取り込まれないため、以下のクエリは実行できません。

SELECT osm_id, tags -> 'traffic_zone:direction'
FROM planet_osm_point
WHERE tags ? 'traffic_zone:direction';

メリットとデメリット

特徴 hstoreなし hstoreあり
タグの扱い 事前定義されたカラムに展開 すべてのタグをhstore型で保持
柔軟性 低い：カスタムタグは無視される 高い：すべてのタグを動的に利用可能
データサイズ 小さい 大きい：すべてのタグを保持するため
検索速度 事前定義されたカラムに最適化され高速 大量データの場合、tagsインデックスが必要
利用目的 既定のタグに基づく解析が主な目的の場合に最適 カスタムタグを頻繁に利用する場合に最適

他環境での補足

  • Windows: コマンドにおけるパスの記述が異なる場合があります。たとえば、osm2pgsqlのファイルパス指定はフルパス（例: C:\path\to\file.pbf）で記述する必要があります。
  • Linux: 手順はmacOSとほぼ同じですが、osm2pgsqlのインストール方法が異なる場合があります。

参考QGISでの表示例

以下は、QGISでhstoreあり・なしのデータをそれぞれ表示したスクリーンショットです。

  1. hstoreなし: 固定されたカラム（例: highway）のみ表示可能。

without_hstore.png

  1. hstoreあり: すべてのタグデータを参照可能。Attributeにtagsが表示されています。

hstore.png

追加補足

インポートにかかる時間の目安

osm2pgsqlでPBFデータをPostgreSQLにインポートする際、データのサイズやオプションの設定によって処理時間が大きく異なります。以下は、本記事のために私が実行した実際の変換時間の例です。マシンやメモリの状況によっても変わってきますので、あくまでとある事例としてご参考ください。

変換時間の例

データサイズ hstoreなし（--hstore未使用） hstoreあり（--hstore使用）
約2GB 約59分 約1時間6分

ログ：

osm2pgsql took 3543s (59m 3s) overall.

osm2pgsql took 4007s (1h 6m 47s) overall.

処理時間に影響する要因

  • データ量
    データが大きくなるほど、処理時間も長くなります。特に大規模のデータは数時間以上かかる場合があります。
  • hstoreの有無
    --hstoreを使用すると、すべてのタグデータをインポートするため、処理時間が長くなりがちです。
  • ハードウェア性能
    CPU、メモリ、ストレージ（特にSSDかHDDか）により、処理時間が大きく異なります。
  • オプションの設定
    --slimモードを使用すると、中間データの処理が効率化されますが、ディスクI/Oの速度に依存します。

変換時間を短縮する方法(今後の記事として想定)

  • エリアを絞り込む
    全データではなく、対象エリア（例: 州や都市）のみをダウンロードして使用します。たとえば、Geofabrikから対象エリアのPBFを取得します。
  • インポートオプションの調整
    必要なデータのみをインポートするようフィルタリングを行います。
  • ハードウェアの強化
    処理速度を短縮するために、SSDや大容量メモリを活用します。

まとめ

  • hstore拡張なし: シンプルで効率的なデータ処理が可能。ただし、カスタムタグは無視される。
  • hstore拡張あり: すべてのタグを保持するため、柔軟性が高い。データサイズと検索速度には注意。
  • インポートにかかる時間は、データ量や設定によって異なります。
  • 対象エリアを絞り込む、オプションを調整することで処理時間を短縮（できそう）

利用目的やご自身の環境応じて適切な方法を選択してください。本記事がOSMデータを有効活用していただくことに少しでも役立てば嬉しいです。

Discussion