v1.3で、spatial extensionのspatial joinが強化された。
今までもクエリの実行自体はできたはずだけど、それが早くなった、はず。
それが調子よくなったらしいので試してみる。
データ投入
- school: 国土数値情報 - 学校データ
- admin: 国土数値情報 - 行政区域データ
CREATE TABLE school AS SELECT * FROM './P29-23.shp';
CREATE TABLE admin AS SELECT * FROM './N03-20240101.shp'
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) │
└────────────────────────────────────────────────────────────────────────┘
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
行政区域に含まれる学校の数をカウントする。
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という関数を実装したぜというメンテナーのコメント。
-- 都道府県でディゾルブするクエリ
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 │
└──────────────────────────────────────────────────────────────────────────────┘
正しく都道府県でマージされている
すぐに結果が得られた。これをST_UnionAggでやると、全く完了しない。
裏ではGEOSに投げているようす。命名はこれで良いのか…？という感じで、そのうちST_Unionに統合されるべきものな気もする。ネイティブで高速な集計関数が実装されるようになったら、そっちを使うようになるのかもしれない。