はじめに
業務上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 データ分析が可能
参考情報
データの規模が大きい場合や、利用環境などによってどれくらいのパフォーマンスが出せるかはこれから試してみたいと思います。
この記事が位置情報データ活用の助けになれば幸いです！