Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

Avatar for Kanahiro Iguchi Kanahiro Iguchi
September 26, 2025
49

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

Avatar for Kanahiro Iguchi

Kanahiro Iguchi

September 26, 2025
Tweet

Transcript

  1. Who am I Kanahiro Iguchi MapLibre User Group Japan AWS

    Community Builder - Serverless 『位置エン本』 『位置ベロ本』 『実践QGIS』著者 FOSS4G Hokkaido 2025 2025/09/27 2
  2. -- 市区町村に含まれる学校の数を集計するクエリ 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
  3. 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
  4. あるいは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
  5. duckdb # DuckDB のCUI を起動 -- DuckDB shell -- spatial

    extension のインストール INSTALL spatial; LOAD spatial; FOSS4G Hokkaido 2025 2025/09/27 31
  6. -- 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
  7. では各レコードの名称を取得してみます。 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
  8. OvertureMapsはしばしば「ネストした構造」を持つ(object) たとえば names はさらに子要素として primary (代表名), common (多言語 名), rules

    (命名規則)を持つ なんなら rules はobjectの配列である こういうデータは古典的なGISソフトウェアやRDBでは取扱いづらいものだったが… FOSS4G Hokkaido 2025 2025/09/27 36
  9. 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
  10. 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
  11. では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
  12. 例えば単に名前だけ欲しいのであれば 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
  13. 多言語対応したければ 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
  14. ユースケースに適したフラットな構造に変換したうえで再度エクスポート 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