OvertureMapsをDuckDBで探索してみよう / FOSS4G Hokkaido 2025

Transcript

1. OvertureMapsをDuckDBで探索してみよう FOSS4G Hokkaido 2025 2025/09/27 1
2. Who am I Kanahiro Iguchi MapLibre User Group Japan AWS Community Builder - Serverless 『位置エン本』 『位置ベロ本』 『実践QGIS』著者 FOSS4G Hokkaido 2025 2025/09/27 2
3. Contents 1. OvertureMapsについて 2. DuckDBについて 3. OvertureMapsをDuckDBで探索する FOSS4G Hokkaido 2025 2025/09/27 3
4. 1. OvertureMapsについて FOSS4G Hokkaido 2025 2025/09/27 4
5. FOSS4G Hokkaido 2025 2025/09/27 5
6. Amazon, Meta, Microsoft, Tomtomにより2022年に設立されたプロジェクト 参加企業によるデータや、OpenStreetMap、各国のオープンデータをベースとしている さまざまなデータが、OvertureMapsが定義したデータスキーマに基づいて提供される FOSS4G Hokkaido 2025 2025/09/27 6
7. OpenStreetMapと比べると OpenStreetMapは生データのままではアプリケーションで利用するのが困難。目的に応 じて前処理が必要。OvertureMapsはデータスキーマに基づいたParquet形式で頒布され ていて、すぐにアプリケーションで利用できる。 OvertureMapsの places （地点名）の品質はまだ厳しい感じ。 OvertureMapsの transportation （道路）は、OpenStreetMapの道路データをもとに しているが、ネットワークデータとして使いやすいよう前処理されていて便利。 FOSS4G Hokkaido 2025 2025/09/27 7
8. さぁ、データを使ってみよう… FOSS4G Hokkaido 2025 2025/09/27 8
9. なんとAmazon S3かAzure Blob StorageのURLがデフォルトっぽく記載されている FOSS4G Hokkaido 2025 2025/09/27 9
10. そのほかの選択肢としてドキュメントに書いてあるのは... DuckDB（裏側ではAmazon S3と通信している） Python CLI（ダウンロードする方法だけが書いてある） Athena (AWS) Synapse (Azure) FOSS4G Hokkaido 2025 2025/09/27 10
11. そこはかとないマッチョ以外お断り感 FOSS4G Hokkaido 2025 2025/09/27 11
12. Athena/Synapseはアカウント（＋課金）が必要なので ふつうのケースでは、DuckDB と Python CLIの2択と考えてよいです 今回は私が最も実践的と考える： 1. Python CLIでダウンロード 2. DuckDBでデータの読み書き という手順で学んでいきましょう。 FOSS4G Hokkaido 2025 2025/09/27 12
13. Pythonユーザーは多いでしょうけど、DuckDBはこれからという人もいると思います あなた向けのトークです🫵 FOSS4G Hokkaido 2025 2025/09/27 13
14. 2. DuckDBについて FOSS4G Hokkaido 2025 2025/09/27 14
15. FOSS4G Hokkaido 2025 2025/09/27 15
16. OLAP向けのSQLクライアント 簡単にインストールでき、コマンドラインでさっくり起動でき、あらゆる表形式データ （あるいは構造化されたデータ）をテーブルに展開し、SQLで操作できる Amazon S3などのリモートのデータを扱うことも出来て強力 オフィシャルでメンテナンスされている spatial extension により、地理空間データ の操作も可能 FOSS4G Hokkaido 2025 2025/09/27 16
17. 「GISエンジニア必携」 FOSS4G Hokkaido 2025 2025/09/27 17
18. なぜDuckDBがオレの心を震わせるのか FOSS4G Hokkaido 2025 2025/09/27 18
19. 言葉はいらない SELECT * FROM './N03-20240101.shp'; FOSS4G Hokkaido 2025 2025/09/27 19
20. -- 市区町村に含まれる学校の数を集計するクエリ CREATE TABLE school AS SELECT * FROM './P29-23.shp'; CREATE TABLE admin AS SELECT * FROM './N03-20240101.shp' SELECT a.N03_007 as citycode, a.N03_004 as cityname, a.N03_005 as subname, count(s.P29_002) as school_count FROM admin a LEFT JOIN school s ON ST_Contains(a.geom, s.geom) GROUP BY a.N03_007, a.N03_004, a.N03_005 ORDER BY school_count DESC; FOSS4G Hokkaido 2025 2025/09/27 20
21. -- 市区町村ポリゴンを都道府県単位にマージするクエリ（いわゆるディゾルブ） SELECT N03_001, ST_CoverageUnion_Agg(admin.geom) FROM admin GROUP BY N03_001; FOSS4G Hokkaido 2025 2025/09/27 21
22. 地理空間データを加工する際は、Pythonでnotebookを書いたり、あるいはPostGISにデ ータを投入してSQLを実行したり、あるいはQGISでGUIで操作したり…、というのが典 型的な方法だと思います DuckDBはこれら全てを置き換えられる道具です。SQLさえ書ければ地理空間データを 簡単にSQLで操作出来ます。 SQLはポピュラーな言語なので知識移転しやすく、処理の再現性にも優れています。こ の時代では、GenAIに書かせやすいという点も見逃せません。 GISエンジニア必携です。必ず携えると書いて必携です。携えましょう。 FOSS4G Hokkaido 2025 2025/09/27 22
23. 3. OvertureMapsをDuckDBで探索する FOSS4G Hokkaido 2025 2025/09/27 23
24. データのダウンロード FOSS4G Hokkaido 2025 2025/09/27 24
25. の前に OvertureMapsの Theme と Type について知っておくべきです FOSS4G Hokkaido 2025 2025/09/27 25
26. これらはデータセットのカテゴリを表しています Theme が大分類、 Type が小分類。 https://docs.overturemaps.org/schema/ FOSS4G Hokkaido 2025 2025/09/27 26
27. OvertureMapsのデータのダウンロードにはPythonの overturemaps-py を使うのが簡単で す。S3だとかを考えなくて済みます。 pip install overturemaps-py overturemaps download --bbox=139,41,146,46 -f geoparquet --type=segment -o segment.parquet # bbox とtype を指定してダウンロード、GeoParquet 形式で保存 # type=segment は道路のラインデータ（theme=transportation ） https://docs.overturemaps.org/getting-data/overturemaps-py/ FOSS4G Hokkaido 2025 2025/09/27 27
28. あるいはDuckDBを通じてデータを読むことも出来る LOAD spatial; SET s3_region='us-west-2'; CREATE TABLE places AS SELECT * FROM read_parquet('s3://overturemaps-us-west-2/release/2025-09-24.0/theme=places/type=place/*') -- だいたい北海道の範囲 WHERE bbox.xmin BETWEEN 139 AND 146 AND bbox.ymin BETWEEN 41 AND 46; FOSS4G Hokkaido 2025 2025/09/27 28
29. GeoParquet形式 Parquet形式にGEOMETRY型サポートを加えたもの 巨大な地理空間データをひとまとめに保存したい場合に最適 ソフトウェアのサポートが追いついていなかったり、典型的な空間インデックスを持っ ていなかったりするので、地図で表示するための形式としては今のところあまりおすす めしない ただし最新のQGISなら読める FOSS4G Hokkaido 2025 2025/09/27 29
30. DuckDBでデータを見る FOSS4G Hokkaido 2025 2025/09/27 30
31. duckdb # DuckDB のCUI を起動 -- DuckDB shell -- spatial extension のインストール INSTALL spatial; LOAD spatial; FOSS4G Hokkaido 2025 2025/09/27 31
32. -- DuckDB shell SELECT * FROM './segment.parquet' LIMIT 10; ┌──────────────────────┬──────────────────────┬──────────────────────┬───┬──────────────────────┬──────────────────────┬──────────┬──────────────────────┐ │ id │ geometry │ bbox │ … │ speed_limits │ width_rules │ subclass │ rail_flags │ │ varchar │ geometry │ struct(xmin float,… │ │ struct(min_speed s… │ struct("value" dou… │ varchar │ struct("values" va… │ ├──────────────────────┼──────────────────────┼──────────────────────┼───┼──────────────────────┼──────────────────────┼──────────┼──────────────────────┤ │ 3fbad638-370e-4187… │ LINESTRING (136.07… │ {'xmin': 135.91985… │ … │ NULL │ NULL │ NULL │ NULL │ │ a2b267dc-986d-4645… │ LINESTRING (135.39… │ {'xmin': 135.32495… │ … │ NULL │ NULL │ NULL │ NULL │ │ b6dbd360-8068-4e3b… │ LINESTRING (139.06… │ {'xmin': 139.06557… │ … │ NULL │ NULL │ NULL │ NULL │ │ 8a4648fe-7af9-4d5b… │ LINESTRING (139.06… │ {'xmin': 139.06422… │ … │ NULL │ NULL │ NULL │ NULL │ │ 65381f0b-c615-4728… │ LINESTRING (139.81… │ {'xmin': 139.81155… │ … │ NULL │ NULL │ NULL │ NULL │ │ 149615d9-bee9-4f0b… │ LINESTRING (139.81… │ {'xmin': 139.81319… │ … │ NULL │ NULL │ NULL │ NULL │ │ 5d4c7ca3-0019-4f40… │ LINESTRING (139.81… │ {'xmin': 139.81427… │ … │ NULL │ NULL │ NULL │ NULL │ │ afc633a3-f572-4398… │ LINESTRING (139.81… │ {'xmin': 139.81448… │ … │ NULL │ NULL │ NULL │ NULL │ │ af268f3c-3002-4c5b… │ LINESTRING (139.81… │ {'xmin': 139.81422… │ … │ NULL │ NULL │ NULL │ NULL │ │ 00ea0d16-64dd-4f73… │ LINESTRING (140.07… │ {'xmin': 140.07211… │ … │ NULL │ NULL │ NULL │ NULL │ ├──────────────────────┴──────────────────────┴──────────────────────┴───┴──────────────────────┴──────────────────────┴──────────┴──────────────────────┤ │ 10 rows 21 columns (7 shown) │ └────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ FOSS4G Hokkaido 2025 2025/09/27 32
33. これだけだと実際どんなデータなのかよくわからない OvertureMapsの Schema を読もう https://docs.overturemaps.org/schema/reference/transportation/segment/ FOSS4G Hokkaido 2025 2025/09/27 33
34. propertiesを読み解く subtype/connectors/routesは必ず持つ subtype: road/rail/water のいずれかで、この種別によって持ちうるデータ が異なる subtype=railならrail_flagsというデータを持つ subtype=roadならspeed_limitsやwidth_rulesなど… 名前を表すデータ（names）ももちろんある thme/type/version/sourcesはOvertureMapsの全てのデータが持っている → OvertureMapsで共通のデータ/typeで共通のデータ/subtypeごとに固有のデ ータ と読み分けられると、見かけよりも複雑ではないことがわかります。 FOSS4G Hokkaido 2025 2025/09/27 34
35. では各レコードの名称を取得してみます。 SELECT names FROM './segment.parquet' LIMIT 10; ┌─────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ names │ │ struct("primary" varchar, common map(varchar, varchar), rules struct(variant varchar, "language" … │ ├─────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ {'primary': 新日本海フェリー（敦賀― 苫小牧東）, 'common': NULL, 'rules': [{'variant': common, 'lan… │ │ {'primary': Shin Nihonkai Ferry, 'common': {en=Shin Nihonkai Ferry, ja=' 新日本海フェリー( 舞鶴- 小… │ │ {'primary': 新日本海フェリー 秋田－苫小牧東, 'common': NULL, 'rules': [{'variant': common, 'langu… │ │ {'primary': 新日本海フェリー（新潟― 小樽）, 'common': NULL, 'rules': [{'variant': common, 'languag… │ │ NULL │ │ NULL │ │ NULL │ │ NULL │ │ NULL │ │ {'primary': 国道228 号, 'common': {en=National Highway Route 228, ja= 国道228 号}, 'rules': [{'varia… │ ├─────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ 10 rows │ └─────────────────────────────────────────────────────────────────────────────────────────────────────┘ わ、わぁ。 。 。 FOSS4G Hokkaido 2025 2025/09/27 35
36. OvertureMapsはしばしば「ネストした構造」を持つ（object） たとえば names はさらに子要素として primary （代表名）, common （多言語 名）, rules （命名規則）を持つ なんなら rules はobjectの配列である こういうデータは古典的なGISソフトウェアやRDBでは取扱いづらいものだったが… FOSS4G Hokkaido 2025 2025/09/27 36
37. DuckDBではスクリプト言語ライクに、子要素にアクセスできる SELECT names.primary FROM './segment.parquet' WHERE names.primary IS NOT NULL LIMIT 10; ┌───────────────────────────────────┐ │ primary │ │ varchar │ ├───────────────────────────────────┤ │ 新日本海フェリー（敦賀― 苫小牧東） │ │ Shin Nihonkai Ferry │ │ 新日本海フェリー 秋田－苫小牧東 │ │ 新日本海フェリー（新潟― 小樽） │ │ 国道228 号 │ │ 国道228 号 │ │ 大尽内橋( 落橋) │ │ 国道228 号 │ │ 国道228 号 │ │ 国道228 号 │ ├───────────────────────────────────┤ │ 10 rows │ └───────────────────────────────────┘ FOSS4G Hokkaido 2025 2025/09/27 37
38. SELECT names.rules[1] FROM './segment.parquet' WHERE names.rules[1] IS NOT NULL LIMIT 10; ┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ "names".rules[1] │ │ struct(variant varchar, "language" varchar, perspectives struct("mode" varchar, countries varchar[]), "value" varchar, "between" double[], side varchar) │ ├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ {'variant': common, 'language': NULL, 'perspectives': NULL, 'value': 新日本海フェリー（敦賀― 苫小牧東）, 'between': NULL, 'side': NULL} │ │ {'variant': common, 'language': en, 'perspectives': NULL, 'value': New Nihonkai Ferry Niigata - Akita, 'between': [0.0, 0.357819262], 'side': NULL} │ │ {'variant': common, 'language': NULL, 'perspectives': NULL, 'value': 新日本海フェリー（新潟― 小樽）, 'between': NULL, 'side': NULL} │ │ {'variant': common, 'language': NULL, 'perspectives': NULL, 'value': 国道228 号, 'between': NULL, 'side': NULL} │ │ {'variant': common, 'language': NULL, 'perspectives': NULL, 'value': 国道228 号, 'between': NULL, 'side': NULL} │ │ {'variant': common, 'language': NULL, 'perspectives': NULL, 'value': ' 大尽内橋( 落橋)', 'between': [0.275924468, 0.423304726], 'side': NULL} │ │ {'variant': common, 'language': NULL, 'perspectives': NULL, 'value': 国道228 号, 'between': NULL, 'side': NULL} │ │ {'variant': common, 'language': NULL, 'perspectives': NULL, 'value': 国道228 号, 'between': NULL, 'side': NULL} │ │ {'variant': common, 'language': NULL, 'perspectives': NULL, 'value': 国道228 号, 'between': NULL, 'side': NULL} │ │ {'variant': common, 'language': NULL, 'perspectives': NULL, 'value': 国道228 号, 'between': NULL, 'side': NULL} │ ├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ 10 rows │ └──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ -- 子要素の一つ目の要素のさらに子要素へのアクセス SELECT names.rules[1].value FROM './segment.parquet' WHERE names.rules[1] IS NOT NULL LIMIT 10; DuckDBならネストした構造も怖くない！ DuckDBでデータを加工する FOSS4G Hokkaido 2025 2025/09/27 38
39. ここまでで、データをダウンロードしてDuckDBのSQLでデータをこねくり回せること がわかりました。 一般のGISソフトウェアや何らかのアプリケーションで利用するために、何かしらの形式 で出力することを学びましょう。 FOSS4G Hokkaido 2025 2025/09/27 39
40. テーブルをGISデータとして出力するクエリの基本 -- GDAL がサポートするDriver で出力できる COPY sometable TO 'output.gpkg' WITH (FORMAT GDAL, DRIVER 'GPKG'); FOSS4G Hokkaido 2025 2025/09/27 40
41. ではsegmentをそのままGeoPackage形式で出力してみましょう… COPY (SELECT * FROM './segment.parquet') TO 'segment_road.gpkg' WITH (FORMAT gdal, DRIVER 'GPKG'); Not implemented Error: Unsupported type for OGR: STRUCT(xmin FLOAT, xmax FLOAT, ymin FLOAT, ymax FLOAT) エラーが出ました。GISデータとして書き出す際はネストした構造はサポートされていませ ん。 FOSS4G Hokkaido 2025 2025/09/27 41
42. 実際、大概のアプリケーションではネストした構造を持つGISデータは扱いづらさがあり ます。 OvertureMapsのデータはおそらくはそのまま使うことは難しい一方、ユースケースに応 じて加工しやすいというのが強みとだと考えています FOSS4G Hokkaido 2025 2025/09/27 42
43. 例えば単に名前だけ欲しいのであれば SELECT id, geometry, names.primary as name FROM './segment.parquet' LIMIT 10; ┌──────────────────────┬──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬───────────────────────────────────┐ │ id │ geometry │ name │ │ varchar │ geometry │ varchar │ ├──────────────────────┼──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼───────────────────────────────────┤ │ 3fbad638-370e-4187… │ LINESTRING (136.0724078 35.6781738, 136.0714872 35.6774911, 136.0708546 35.6773502, 136.0562844 35.6771601, 136.0551859 35.6772434, 136.054188… │ 新日本海フェリー（敦賀― 苫小牧東） │ │ a2b267dc-986d-4645… │ LINESTRING (135.3924806 35.4810064, 135.3926007 35.4810548, 135.3924076 35.4818758, 135.3929703 35.4824157, 135.3895211 35.4913722, 135.388748… │ Shin Nihonkai Ferry │ │ b6dbd360-8068-4e3b… │ LINESTRING (139.068916 37.9380752, 139.0686053 37.9381215, 139.0679884 37.9382725, 139.0669819 37.9388734, 139.0661581 37.9397157, 139.0655783… │ 新日本海フェリー 秋田－苫小牧東 │ │ 8a4648fe-7af9-4d5b… │ LINESTRING (139.068916 37.9380752, 139.0686652 37.938054, 139.0680246 37.9381443, 139.066842 37.93856, 139.066758 37.938584, 139.066639 37.938… │ 新日本海フェリー（新潟― 小樽） │ │ 65381f0b-c615-4728… │ LINESTRING (139.8131968 41.3621521, 139.8131768 41.3620655, 139.8131731 41.3620245, 139.8131586 41.3619671, 139.8132023 41.3618905, 139.813195… │ NULL │ │ 149615d9-bee9-4f0b… │ LINESTRING (139.8131968 41.3621521, 139.8132783 41.3622055, 139.8134782 41.3621445, 139.8135594 41.3621639, 139.8136136 41.3621939, 139.813613… │ NULL │ │ 5d4c7ca3-0019-4f40… │ LINESTRING (139.8142738 41.3622985, 139.8145067 41.3623022, 139.8149919 41.3614536, 139.8153607 41.3615884, 139.8159769 41.3618069) │ NULL │ │ afc633a3-f572-4398… │ LINESTRING (139.8145067 41.3623022, 139.8151714 41.3629941) │ NULL │ │ af268f3c-3002-4c5b… │ LINESTRING (139.8169874 41.3618487, 139.8167201 41.3621814, 139.8162498 41.363248, 139.8162769 41.3634105, 139.8168081 41.3638574, 139.8160637… │ NULL │ │ 00ea0d16-64dd-4f73… │ LINESTRING (140.0721254 41.4323727, 140.0726904 41.4321355, 140.0731097 41.4319041, 140.073495 41.4315905, 140.0737722 41.4313069, 140.0740002… │ 国道228 号 │ ├──────────────────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴───────────────────────────────────┤ │ 10 rows 3 columns │ └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ FOSS4G Hokkaido 2025 2025/09/27 43
44. 多言語対応したければ SELECT id, geometry, names.primary as name, names.common['ja'] as 'name:ja', names.common['en'] as 'name:en' FROM './segment.parquet' WHERE names.common IS NOT NULL LIMIT 10; ┌──────────────────────┬────────────────────────────────────┬─────────────────────┬─────────────────────────────┬────────────────────────────┐ │ id │ geometry │ name │ name:ja │ name:en │ │ varchar │ geometry │ varchar │ varchar │ varchar │ ├──────────────────────┼────────────────────────────────────┼─────────────────────┼─────────────────────────────┼────────────────────────────┤ │ a2b267dc-986d-4645… │ LINESTRING (135.3924806 35.48100… │ Shin Nihonkai Ferry │ 新日本海フェリー( 舞鶴- 小樽) │ Shin Nihonkai Ferry │ │ 00ea0d16-64dd-4f73… │ LINESTRING (140.0721254 41.43237… │ 国道228 号 │ 国道228 号 │ National Highway Route 228 │ │ f316ca67-f276-40b7… │ LINESTRING (140.0610952 41.43800… │ 国道228 号 │ 国道228 号 │ National Highway Route 228 │ │ 6fd32869-4a56-45de… │ LINESTRING (140.0662285 41.43503… │ 国道228 号 │ 国道228 号 │ National Highway Route 228 │ │ b879fe74-598f-4802… │ LINESTRING (140.0787414 41.42627… │ 国道228 号 │ 国道228 号 │ National Highway Route 228 │ │ fc8c58cc-5793-4f63… │ LINESTRING (140.0794556 41.42617… │ 国道228 号 │ 国道228 号 │ National Highway Route 228 │ │ e8de8a00-ea38-4a44… │ LINESTRING (140.0797186 41.42613… │ 国道228 号 │ 国道228 号 │ National Highway Route 228 │ │ 54c41d4f-59f2-4d4f… │ LINESTRING (140.0847363 41.42536… │ 国道228 号 │ 国道228 号 │ National Highway Route 228 │ │ a7838cc3-475a-4c39… │ LINESTRING (140.0887076 41.42121… │ 松前港線 │ 松前港線 │ Matsumae Port Line │ │ bcd2397e-6f09-4b94… │ LINESTRING (140.0895534 41.42216… │ 松前港線 │ 松前港線 │ Matsumae Port Line │ ├──────────────────────┴────────────────────────────────────┴─────────────────────┴─────────────────────────────┴────────────────────────────┤ │ 10 rows 5 columns │ └────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ FOSS4G Hokkaido 2025 2025/09/27 44
45. ユースケースに適したフラットな構造に変換したうえで再度エクスポート COPY ( SELECT id, geometry, names.primary as name, names.common['ja'] as 'name:ja', names.common['en'] as 'name:en' FROM './segment.parquet' ) TO 'segment_named.gpkg' WITH (FORMAT gdal, DRIVER 'GPKG'); FOSS4G Hokkaido 2025 2025/09/27 45
46. 66万地物の加工・出力もそこそこさっくりできる GeoParquetの生データが164.3MB、加工後のGeoPackageが218.8MBなことは特筆す べき点。GeoParquetは圧縮効率も高い。ただし現状はGISソフトウェアから使う場合 （≒空間インデックスを活用したい場合）はGeoPackage形式を推奨する。 FOSS4G Hokkaido 2025 2025/09/27 46
47. 地図上でデータを眺めながら、OvertureMapsのデータについて語る transportation places buildings FOSS4G Hokkaido 2025 2025/09/27 47
48. まとめ OvertureMapsは利用方法・スキーマが一見わかりづらいけど、実際のデータを操作して 要点を掴めば難しくない DuckDBはGISエンジニア必携のツール。OvertureMapsも怖くない。 OvertureMapsはそのまま使うのではなく、ユースケースに沿った加工が重要！ FOSS4G Hokkaido 2025 2025/09/27 48