DuckDB v1.3のspatial join

https://duckdb.org/2025/05/21/announcing-duckdb-130.html#spatial-join-operator

v1.3で、spatial extensionのspatial joinが強化された。
今までもクエリの実行自体はできたはずだけど、それが早くなった、はず。

https://qiita.com/Kanahiro/items/7b4a3eb2f0ae68f16ddb

それが調子よくなったらしいので試してみる。

## データ投入

- school: 国土数値情報 - 学校データ
- admin: 国土数値情報 - 行政区域データ

```sql
CREATE TABLE school AS SELECT * FROM './P29-23.shp';
CREATE TABLE admin AS SELECT * FROM './N03-20240101.shp'
```

```sql
select * from school limit 5;
```
```
┌─────────┬───────────────┬─────────┬───┬─────────┬──────────────────────┐
│ P29_001 │    P29_002    │ P29_003 │ … │ P29_009 │         geom         │
│ varchar │    varchar    │ varchar │   │ varchar │       geometry       │
├─────────┼───────────────┼─────────┼───┼─────────┼──────────────────────┤
│ 01202   │ A101110000012 │ 16011   │ … │ NULL    │ POINT (140.7481043…  │
│ 01204   │ A101110000021 │ 16011   │ … │ NULL    │ POINT (142.3562340…  │
│ 01109   │ A101210000010 │ 16011   │ … │ NULL    │ POINT (141.2388015…  │
│ 01101   │ A101210110017 │ 16011   │ … │ NULL    │ POINT (141.3396656…  │
│ 01102   │ A101210220014 │ 16011   │ … │ NULL    │ POINT (141.3383923…  │
├─────────┴───────────────┴─────────┴───┴─────────┴──────────────────────┤
│ 5 rows                                            10 columns (5 shown) │
└────────────────────────────────────────────────────────────────────────┘
```

```sql
select * from admin limit 5;;
```
```
┌─────────┬────────────┬───┬─────────┬─────────┬──────────────────────┐
│ N03_001 │  N03_002   │ … │ N03_005 │ N03_007 │         geom         │
│ varchar │  varchar   │   │ varchar │ varchar │       geometry       │
├─────────┼────────────┼───┼─────────┼─────────┼──────────────────────┤
│ 北海道  │ 石狩振興局 │ … │ 中央区  │ 01101   │ POLYGON ((141.2569…  │
│ 北海道  │ 石狩振興局 │ … │ 北区    │ 01102   │ POLYGON ((141.3332…  │
│ 北海道  │ 石狩振興局 │ … │ 東区    │ 01103   │ POLYGON ((141.3734…  │
│ 北海道  │ 石狩振興局 │ … │ 白石区  │ 01104   │ POLYGON ((141.3820…  │
│ 北海道  │ 石狩振興局 │ … │ 豊平区  │ 01105   │ POLYGON ((141.3637…  │
├─────────┴────────────┴───┴─────────┴─────────┴──────────────────────┤
│ 5 rows                                          7 columns (5 shown) │
└─────────────────────────────────────────────────────────────────────┘
```

## spatial join

行政区域に含まれる学校の数をカウントする。

```sql
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;
```

```
┌──────────┬────────────┬─────────┬──────────────┐
│ citycode │  cityname  │ subname │ school_count │
│ varchar  │  varchar   │ varchar │    int64     │
├──────────┼────────────┼─────────┼──────────────┤
│ 22138    │ 浜松市     │ 中央区  │          287 │
│ 46201    │ 鹿児島市   │ NULL    │          282 │
│ 28201    │ 姫路市     │ NULL    │          271 │
│ 13112    │ 世田谷区   │ NULL    │          267 │
│ 44201    │ 大分市     │ NULL    │          248 │
│ 34207    │ 福山市     │ NULL    │          247 │
│ 17201    │ 金沢市     │ NULL    │          243 │
│ 42201    │ 長崎市     │ NULL    │          240 │
│ 16201    │ 富山市     │ NULL    │          231 │
│ 09201    │ 宇都宮市   │ NULL    │          220 │
│ 45201    │ 宮崎市     │ NULL    │          218 │
│ 18201    │ 福井市     │ NULL    │          211 │
│ 28204    │ 西宮市     │ NULL    │          210 │
│ 13201    │ 八王子市   │ NULL    │          209 │
│ 33202    │ 倉敷市     │ NULL    │          207 │
│ 38201    │ 松山市     │ NULL    │          207 │
│ 37201    │ 高松市     │ NULL    │          203 │
│ 10202    │ 高崎市     │ NULL    │          197 │
│ 07204    │ いわき市   │ NULL    │          195 │
│ 10201    │ 前橋市     │ NULL    │          195 │
│   ·      │   ·        │  ·      │            · │
│   ·      │   ·        │  ·      │            · │
│   ·      │   ·        │  ·      │            · │
│ 19429    │ 鳴沢村     │ NULL    │            1 │
│ 06367    │ 戸沢村     │ NULL    │            1 │
│ 20306    │ 南相木村   │ NULL    │            1 │
│ 20307    │ 北相木村   │ NULL    │            1 │
│ 29385    │ 曽爾村     │ NULL    │            1 │
│ 39364    │ 大川村     │ NULL    │            1 │
│ 01696    │ 泊村       │ NULL    │            0 │
│ 01697    │ 留夜別村   │ NULL    │            0 │
│ 23000    │ 所属未定地 │ NULL    │            0 │
│ 46000    │ 所属未定地 │ NULL    │            0 │
│ 43507    │ 水上村     │ NULL    │            0 │
│ 30000    │ 所属未定地 │ NULL    │            0 │
│ 47000    │ 所属未定地 │ NULL    │            0 │
│ 13000    │ 所属未定地 │ NULL    │            0 │
│ 01699    │ 紗那村     │ NULL    │            0 │
│ 01700    │ 蘂取村     │ NULL    │            0 │
│ 12000    │ 所属未定地 │ NULL    │            0 │
│ 01695    │ 色丹村     │ NULL    │            0 │
│ 01698    │ 留別村     │ NULL    │            0 │
│ 40000    │ 所属未定地 │ NULL    │            0 │
├──────────┴────────────┴─────────┴──────────────┤
│ 1905 rows (40 shown)                 4 columns │
└────────────────────────────────────────────────┘
```

概ね3秒程度で結果が出力された。検証してないけど多分あってる。この手の集計がCLIでさっくり済むのは嬉しい。ややこしいSQLはGenAIに書いてもらえて良い時代である。

## メモ

遅いのは`ST_Union_Agg`でのディゾルブだったかもれねぇ
この手の集計関数はいまだ、かなり時間がかかる。

と思って調べたら、Issueにて`ST_CoverageUnion_Agg`という関数を実装したぜというメンテナーのコメント。

https://github.com/duckdb/duckdb-spatial/issues/579

```sql
-- 都道府県でディゾルブするクエリ
SELECT N03_001, ST_CoverageUnion_Agg(admin.geom) 
    FROM admin 
    GROUP BY N03_001;
```

```
┌──────────┬───────────────────────────────────────────────────────────────────┐
│ N03_001  │                st_coverageunion_agg("admin".geom)                 │
│ varchar  │                             geometry                              │
├──────────┼───────────────────────────────────────────────────────────────────┤
│ 茨城県   │ MULTIPOLYGON (((140.627379974 36.483490324, 140.628061271 36.48…  │
│ 群馬県   │ POLYGON ((138.648303061 36.407809559, 138.648187497 36.40808927…  │
│ 山口県   │ MULTIPOLYGON (((130.873989196 34.089793748, 130.874022776 34.08…  │
│ 佐賀県   │ MULTIPOLYGON (((130.16885166 33.467620829, 130.169811388 33.467…  │
│ 栃木県   │ POLYGON ((139.423003087 36.324999252, 139.423003359 36.325, 139…  │
│ 島根県   │ MULTIPOLYGON (((132.903193333 35.511952505, 132.903214163 35.51…  │
│ 徳島県   │ MULTIPOLYGON (((134.607264721 34.100537225, 134.607028392 34.1,…  │
│ 秋田県   │ MULTIPOLYGON (((140.059670726 39.597872, 140.05967131 39.598018…  │
│ 福島県   │ MULTIPOLYGON (((140.237429339 37.74658264, 140.237671284 37.746…  │
│ 新潟県   │ MULTIPOLYGON (((139.133333333 37.962404153, 139.134281751 37.96…  │
│ 山梨県   │ POLYGON ((138.621640558 35.870444198, 138.621870804 35.87043836…  │
│ 岐阜県   │ POLYGON ((136.378939364 35.241666667, 136.378701089 35.24238691…  │
│ 静岡県   │ MULTIPOLYGON (((138.087722477 35.33811064, 138.087999144 35.338…  │
│ 三重県   │ MULTIPOLYGON (((136.206484449 34.450060559, 136.20645834 34.450…  │
│ 宮城県   │ MULTIPOLYGON (((140.563380246 38.377195414, 140.563550246 38.37…  │
│ 山形県   │ MULTIPOLYGON (((140.52167 38.349633, 140.521893606 38.349224387…  │
│ 長野県   │ POLYGON ((138.005161193 36.825729721, 138.005721751 36.82546944…  │
│ 京都府   │ MULTIPOLYGON (((135.797058885 35.320624171, 135.797353606 35.32…  │
│ 兵庫県   │ MULTIPOLYGON (((135.301544423 34.719446225, 135.301477004 34.71…  │
│ 奈良県   │ POLYGON ((135.75559834 34.727522225, 135.755647224 34.727541667…  │
│   ·      │                                 ·                                 │
│   ·      │                                 ·                                 │
│   ·      │                                 ·                                 │
│ 青森県   │ MULTIPOLYGON (((140.66079939 40.948426802, 140.660786641 40.948…  │
│ 大阪府   │ MULTIPOLYGON (((135.425 34.656402216, 135.423537834 34.65713849…  │
│ 岡山県   │ MULTIPOLYGON (((133.99576869 34.61353264, 133.995774189 34.6135…  │
│ 愛媛県   │ MULTIPOLYGON (((132.685506109 33.805503198, 132.685557302 33.80…  │
│ 埼玉県   │ MULTIPOLYGON (((139.327879222 36.239924171, 139.327873943 36.24…  │
│ 東京都   │ MULTIPOLYGON (((140.289170272 30.481429721, 140.288851388 30.48…  │
│ 福井県   │ MULTIPOLYGON (((135.964380389 35.997519694, 135.964405162 35.99…  │
│ 愛知県   │ MULTIPOLYGON (((136.814393891 34.999106667, 136.813943606 34.99…  │
│ 滋賀県   │ POLYGON ((135.874233333 34.888871108, 135.874006109 34.88887694…  │
│ 高知県   │ MULTIPOLYGON (((133.499791971 33.467006306, 133.499758029 33.46…  │
│ 大分県   │ MULTIPOLYGON (((131.518091816 33.265298739, 131.518242244 33.26…  │
│ 鹿児島県 │ MULTIPOLYGON (((129.73329463 31.448573108, 129.733288366 31.448…  │
│ 沖縄県   │ MULTIPOLYGON (((127.459235837 26.240204162, 127.459255837 26.24…  │
│ 岩手県   │ MULTIPOLYGON (((141.97870821 39.830672964, 141.978893217 39.830…  │
│ 千葉県   │ MULTIPOLYGON (((139.958153541 35.670235649, 139.958170986 35.67…  │
│ 石川県   │ MULTIPOLYGON (((136.557097613 36.567782171, 136.557156498 36.56…  │
│ 和歌山県 │ MULTIPOLYGON (((135.961767497 33.606327694, 135.961770052 33.60…  │
│ 鳥取県   │ MULTIPOLYGON (((134.000222218 35.316328333, 134.000272218 35.31…  │
│ 福岡県   │ MULTIPOLYGON (((131.00828655 33.82746836, 131.008270778 33.8274…  │
│ 宮崎県   │ MULTIPOLYGON (((131.467949702 31.74937718, 131.46789869 31.7493…  │
├──────────┴───────────────────────────────────────────────────────────────────┤
│ 47 rows (40 shown)                                                 2 columns │
└──────────────────────────────────────────────────────────────────────────────┘
```

![Screenshot 2025-06-01 at 11.48.53.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/73197/06bf12f1-26fe-469b-acf6-767fb7208b24.png)*正しく都道府県でマージされている*


すぐに結果が得られた。これを`ST_UnionAgg`でやると、全く完了しない。
裏ではGEOSに投げているようす。命名はこれで良いのか…？という感じで、そのうち`ST_Union`に統合されるべきものな気もする。ネイティブで高速な集計関数が実装されるようになったら、そっちを使うようになるのかもしれない。