はじめに

業務上OpenStreetMapのPBFファイル形式を受け取って扱うことが多いのですが、今回PBFファイルをAWS S3にデータを置き、DuckDB の spatial 拡張を活用して、直接クエリする方法 を解説します。

尚、PBFについてはこちらの記事も参照ください。

PostGIS や osm2pgsql をセットアップせずに、SQL だけで OSM データを処理できる ため、データ分析や可視化に便利です！🚀

1. AWS S3 に PBF ファイルをアップロード

1-1. S3 バケットを作成

AWS CLI または AWS マネジメントコンソールで S3 バケット を作成します。

AWS CLI を使う場合:

aws s3 mb s3://my-osm-data-bucket --region us-east-1

1-2. PBF ファイルをアップロード

aws s3 cp ./map.osm.pbf s3://my-osm-data-bucket/

2. DuckDB をセットアップ

DuckDB をインストールして、必要な拡張機能をロードします。

2-1. DuckDB のインストール

こちらよりインストールください。

3. DuckDB で PBF をクエリする

3-1. 拡張機能をロード

結論から言うと、DuckDB の spatial 拡張を使うことで、PBF を直接 SQL クエリできます。

INSTALL httpfs;
INSTALL spatial;
LOAD httpfs;
LOAD spatial;

3-2. AWS S3 の認証情報を設定

SET s3_region='us-east-1';
SET s3_access_key_id='YOUR_ACCESS_KEY';
SET s3_secret_access_key='YOUR_SECRET_KEY';

💡 AWS CLI の aws configure を設定しておけば、手動で SET しなくても自動認識できます

4. PBF を SQL でクエリ（例）

4-1. S3 上の PBF ファイルをクエリ

SELECT * FROM 's3://my-osm-data-bucket/map.osm.pbf' LIMIT 10;

4-2. OSM の道路データ（`highway` タグあり）を取得

SELECT id, tags['highway'], tags['name']
FROM 's3://my-osm-data-bucket/map.osm.pbf'
WHERE tags['highway'] IS NOT NULL
LIMIT 10;

4-3. 建物データ（`building` タグあり）を取得

SELECT id, tags['building'], tags['name']
FROM 's3://my-osm-data-bucket/map.osm.pbf'
WHERE tags['building'] IS NOT NULL
LIMIT 10;

4-4. 特定の緯度経度範囲のデータを取得

SELECT id, kind, lat, lon, tags
FROM 's3://my-osm-data-bucket/map.osm.pbf'
WHERE lat BETWEEN 35.6 AND 35.7
AND lon BETWEEN 139.6 AND 139.7
LIMIT 10;

5. まとめ

✅ DuckDB の spatial 拡張をロードすれば、PBF を直接 SQL でクエリ可能
✅ AWS S3 にある PBF を SELECT * FROM 's3://...' で直接取得できる
✅ PostGIS や osm2pgsql なしで、簡単に OSM データ分析が可能

参考情報

データの規模が大きい場合や、利用環境などによってどれくらいのパフォーマンスが出せるかはこれから試してみたいと思います。

この記事が位置情報データ活用の助けになれば幸いです！