はじめに
DuckDB に spatial extension をインストールすると地理空間データを扱うことが出来ます。
レガシーなフォーマットであるシェープファイル(ESRI Shapefile)を扱うことが出来るか検証しました。
参考記事
データの準備
サンプルとして国土数値情報ダウンロードサイトから行政区域データをダウンロードします。
北海道の2024年度を選択し、N03-20240101_01_GML.zip を取得します。
zipファイルを展開すると GeoJSON形式(N03-20240101_01.geojson) と シェープファイル形式のファイル群(N03-20240101_01.shp, N03-20240101_01.prj, N03-20240101_01.dbf,...)が現れます。
シェープファイルの読み込み
SELECT文にファイルのパスを指定して読み込み、CREATE TABLE でテーブルを作成しておきます。
CREATE TABLE hokkaido_admin_area AS SELECT * FROM 'N03-20240101_01_GML/N03-20240101_01.shp';
geometry まで無事に読み込めました！
SELECT * FROM hokkaido_admin_area;
┌─────────┬────────────────┬─────────┬─────────┬─────────┬─────────┬────────────────────────────────────────────────────────────────────┐
│ N03_001 │ N03_002 │ N03_003 │ N03_004 │ N03_005 │ N03_007 │ geom │
│ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ geometry │
├─────────┼────────────────┼─────────┼─────────┼─────────┼─────────┼────────────────────────────────────────────────────────────────────┤
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 中央区 │ 01101 │ POLYGON ((141.25694 42.997815279, 141.256304449 42.997857225, 14… │
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 北区 │ 01102 │ POLYGON ((141.333254449 43.075053892, 141.332598054 43.075720279… │
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 東区 │ 01103 │ POLYGON ((141.373410558 43.068399721, 141.373333606 43.068403892… │
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 白石区 │ 01104 │ POLYGON ((141.38201917 43.048316387, 141.381927782 43.048375, 14… │
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 豊平区 │ 01105 │ POLYGON ((141.363712866 42.941538225, 141.363775642 42.941666667… │
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 南区 │ 01106 │ POLYGON ((141.235418054 42.822725279, 141.235418054 42.822629171… │
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 西区 │ 01107 │ POLYGON ((141.169036109 43.082901387, 141.170361388 43.082216387… │
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 厚別区 │ 01108 │ POLYGON ((141.504765551 43.021098892, 141.504473891 43.021161387… │
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 手稲区 │ 01109 │ POLYGON ((141.227041946 43.083017505, 141.226979442 43.082814441… │
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 清田区 │ 01110 │ POLYGON ((141.413818612 42.89511, 141.414065837 42.894854721, 14… │
│ 北海道 │ 渡島総合振興局 │ NULL │ 函館市 │ NULL │ 01202 │ POLYGON ((140.719181582 41.749965748, 140.71918 41.749964667, 14… │
│ 北海道 │ 渡島総合振興局 │ NULL │ 函館市 │ NULL │ 01202 │ POLYGON ((140.711850726 41.770813505, 140.714516615 41.772745865… │
（中略）
│ 北海道 │ 根室振興局 │ 蘂取郡 │ 蘂取村 │ NULL │ 01700 │ POLYGON ((148.65702345 45.333785252, 148.656992802 45.333793306,… │
│ 北海道 │ 根室振興局 │ 蘂取郡 │ 蘂取村 │ NULL │ 01700 │ POLYGON ((148.894392646 45.502270081, 148.894372607 45.502251324… │
│ 北海道 │ 根室振興局 │ 蘂取郡 │ 蘂取村 │ NULL │ 01700 │ POLYGON ((148.772602412 45.556636964, 148.772593696 45.556603297… │
├─────────┴────────────────┴─────────┴─────────┴─────────┴─────────┴────────────────────────────────────────────────────────────────────┤
│ 9560 rows (40 shown) 7 columns │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
テーブルの新規作成
札幌市のレコードに絞り込んで、新しいテーブルを作成します。
CREATE TABLE sapporo_admin_area AS SELECT * FROM hokkaido_admin_area WHERE N03_004 LIKE '札幌市';
札幌市の10区が取り出せました。
SELECT * FROM sapporo_admin_area;
┌─────────┬────────────┬─────────┬─────────┬─────────┬─────────┬────────────────────────────────────────────────────────────────────────┐
│ N03_001 │ N03_002 │ N03_003 │ N03_004 │ N03_005 │ N03_007 │ geom │
│ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ geometry │
├─────────┼────────────┼─────────┼─────────┼─────────┼─────────┼────────────────────────────────────────────────────────────────────────┤
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 中央区 │ 01101 │ POLYGON ((141.25694 42.997815279, 141.256304449 42.997857225, 141.25… │
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 北区 │ 01102 │ POLYGON ((141.333254449 43.075053892, 141.332598054 43.075720279, 14… │
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 東区 │ 01103 │ POLYGON ((141.373410558 43.068399721, 141.373333606 43.068403892, 14… │
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 白石区 │ 01104 │ POLYGON ((141.38201917 43.048316387, 141.381927782 43.048375, 141.38… │
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 豊平区 │ 01105 │ POLYGON ((141.363712866 42.941538225, 141.363775642 42.941666667, 14… │
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 南区 │ 01106 │ POLYGON ((141.235418054 42.822725279, 141.235418054 42.822629171, 14… │
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 西区 │ 01107 │ POLYGON ((141.169036109 43.082901387, 141.170361388 43.082216387, 14… │
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 厚別区 │ 01108 │ POLYGON ((141.504765551 43.021098892, 141.504473891 43.021161387, 14… │
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 手稲区 │ 01109 │ POLYGON ((141.227041946 43.083017505, 141.226979442 43.082814441, 14… │
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 清田区 │ 01110 │ POLYGON ((141.413818612 42.89511, 141.414065837 42.894854721, 141.41… │
├─────────┴────────────┴─────────┴─────────┴─────────┴─────────┴────────────────────────────────────────────────────────────────────────┤
│ 10 rows 7 columns │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
テーブルにカラムを追加
テーブルに新しいカラムを追加してみます。
ALTER TABLE sapporo_admin_area ADD description varchar(64) DEFAULT NULL;
geomカラムの後ろにdescriptionカラムが出来ました。初期値はNULLになっています。
SELECT * FROM sapporo_admin_area;
┌─────────┬────────────┬─────────┬─────────┬─────────┬─────────┬──────────────────────────────────────────────────────────┬─────────────┐
│ N03_001 │ N03_002 │ N03_003 │ N03_004 │ N03_005 │ N03_007 │ geom │ description │
│ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ geometry │ varchar │
├─────────┼────────────┼─────────┼─────────┼─────────┼─────────┼──────────────────────────────────────────────────────────┼─────────────┤
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 中央区 │ 01101 │ POLYGON ((141.25694 42.997815279, 141.256304449 42.997… │ NULL │
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 北区 │ 01102 │ POLYGON ((141.333254449 43.075053892, 141.332598054 43… │ NULL │
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 東区 │ 01103 │ POLYGON ((141.373410558 43.068399721, 141.373333606 43… │ NULL │
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 白石区 │ 01104 │ POLYGON ((141.38201917 43.048316387, 141.381927782 43.… │ NULL │
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 豊平区 │ 01105 │ POLYGON ((141.363712866 42.941538225, 141.363775642 42… │ NULL │
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 南区 │ 01106 │ POLYGON ((141.235418054 42.822725279, 141.235418054 42… │ NULL │
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 西区 │ 01107 │ POLYGON ((141.169036109 43.082901387, 141.170361388 43… │ NULL │
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 厚別区 │ 01108 │ POLYGON ((141.504765551 43.021098892, 141.504473891 43… │ NULL │
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 手稲区 │ 01109 │ POLYGON ((141.227041946 43.083017505, 141.226979442 43… │ NULL │
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 清田区 │ 01110 │ POLYGON ((141.413818612 42.89511, 141.414065837 42.894… │ NULL │
├─────────┴────────────┴─────────┴─────────┴─────────┴─────────┴──────────────────────────────────────────────────────────┴─────────────┤
│ 10 rows 8 columns │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
値の編集
テーブルに追加したカラムへ値を書き込みます。
UPDATE sapporo_admin_area SET description = 'we love shapefile.'
値が NULL から変わりました！
SELECT * FROM sapporo_admin_area;
┌─────────┬────────────┬─────────┬─────────┬─────────┬─────────┬───────────────────────────────────────────────────┬────────────────────┐
│ N03_001 │ N03_002 │ N03_003 │ N03_004 │ N03_005 │ N03_007 │ geom │ description │
│ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ geometry │ varchar │
├─────────┼────────────┼─────────┼─────────┼─────────┼─────────┼───────────────────────────────────────────────────┼────────────────────┤
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 中央区 │ 01101 │ POLYGON ((141.25694 42.997815279, 141.256304449… │ we love shapefile. │
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 北区 │ 01102 │ POLYGON ((141.333254449 43.075053892, 141.33259… │ we love shapefile. │
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 東区 │ 01103 │ POLYGON ((141.373410558 43.068399721, 141.37333… │ we love shapefile. │
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 白石区 │ 01104 │ POLYGON ((141.38201917 43.048316387, 141.381927… │ we love shapefile. │
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 豊平区 │ 01105 │ POLYGON ((141.363712866 42.941538225, 141.36377… │ we love shapefile. │
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 南区 │ 01106 │ POLYGON ((141.235418054 42.822725279, 141.23541… │ we love shapefile. │
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 西区 │ 01107 │ POLYGON ((141.169036109 43.082901387, 141.17036… │ we love shapefile. │
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 厚別区 │ 01108 │ POLYGON ((141.504765551 43.021098892, 141.50447… │ we love shapefile. │
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 手稲区 │ 01109 │ POLYGON ((141.227041946 43.083017505, 141.22697… │ we love shapefile. │
│ 北海道 │ 石狩振興局 │ NULL │ 札幌市 │ 清田区 │ 01110 │ POLYGON ((141.413818612 42.89511, 141.414065837… │ we love shapefile. │
├─────────┴────────────┴─────────┴─────────┴─────────┴─────────┴───────────────────────────────────────────────────┴────────────────────┤
│ 10 rows 8 columns │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
ファイルへの書き出し
テーブルの内容をファイルへ書き出します。シェープファイルの場合は (FORMAT GDAL, DRIVER 'ESRI Shapefile')を指定します。
COPY sapporo_admin_area TO 'sapporo_admin_area.shp' WITH (FORMAT GDAL, DRIVER 'ESRI Shapefile');
以下の3つのファイルが出力されました。
- sapporo_admin_area.shp
- sapporo_admin_area.dbf
- sapporo_admin_area.shx
.prjファイルが出力されていないので座標参照系の扱いが不明ですね！？
とりあえず QGIS で表示させてみます。
さいごに
DuckDBでの、シェープファイルの読み込み、編集、書き出しが確認できました！
TODO
シェープファイルの仕様として、属性名（フィールド名）が半角10文字までなどいくつか制約があります。テーブルのカラム名などが長い場合などの挙動は確認する必要があります。