2014/08/05
PostgreSQL > 市町村合併のデータモデル（1）
PostgreSQL > 市町村合併のデータモデル（1）
以下、再帰クエリの単純な例と、日付の範囲型を初めて使ってみたのを含む、模索中の本当のメモ。実行環境は 2014/07/28 を参照。市町村（市区町村）合併を PostgreSQL で適切に格納＆利用するデータモデルを考えているところ。小規模な例として、さいたま市に関わる合併情報を文章で整理すると次のようになる。
1) 2001年5月1日、浦和市＋大宮市＋与野市 → さいたま市（旧）が発足。【新設合併】
2) 2003年4月1日、さいたま市（旧）→ 九つの政令区に分かれる。【分割】
3) 2005年4月1日、岩槻市 → さいたま市岩槻区になる。【移行】
この合併情報を、例えば下の二テーブルで表現してみる。データモデルは「エッジとノードのグラフ構造」で、各市町村の時系列の変化を道路に似たものと考える。合併や分割は交差点、移行は道路名の変わり目だろうか。一番目のテーブルがエッジで、両端のノード（node_org, node_des）が発足日と消滅日。その日付を、二番目のノードテーブルが持つ。
本来のグラフ構造と違うのは、エッジテーブルに、片方のノードが NULL の行があること。発足日は未調査、消滅日は本当にまだ存在しない。これで何か問題が出たら再検討する必要があるが、とりあえず進める。
例えば各市区町村の存在期間を出すには、↓ のように二テーブルを結合する。ここで存在期間を日付の範囲型にすると、検索や並べ替えで使い勝手がいい。二列の日付にすると、片方が NULL の行があるので面倒。
上の結果を一時テーブルに保存し、範囲型で簡単な検索を試した。↓ 範囲型を「以上・未満」で定義しているので WHERE 句をシンプルにでき、発足日と消滅日を間違える恐れが減る。下のように 2003年4月1日で検索すると、前日まで存在した旧さいたま市が自動的に除外される。
↓ 現時点で存在する市区町村の抽出も簡単。
範囲型の日本語ドキュメントは下記。
■ PostgreSQL 9.3.2文書 : 範囲型
http://www.postgresql.jp/document/9.3/html/rangetypes.html
■ PostgreSQL 9.3.2文書 : 範囲関数と演算子
http://www.postgresql.jp/document/9.3/html/functions-range.html
上の二例は一時点で検索する「断面型」だが、市町村合併では時系列変化を追うのも重要。例えば、現存する特定の市区町村が、過去のある年月日ではどの市区町村に当たるのかを調べる。その際 ↓ のように、再帰クエリで発足（消滅）ノードを順々につないでいくと簡単。
さいたま市は政令市移行前の「全市一つ」の時期があるのと、合併前の市と政令区とが一対一対応でないため、合併情報だけから時系列をさかのぼると三つの市に当たってしまう。これはデータモデルと関係ないので良しとする。
今回の小規模な例では断面検索・時系列追跡ともに概ね良さそうだが、全国すべての市町村合併情報をこのモデルで構築する場合、一つ懸念がある。同じ年月日で、全国あちこちで合併等が起きていたということ。例えば2003年4月1日は、さいたま市が政令区に分かれただけでなく、山梨県南アルプス市の発足等があった。
ということは、単に「合併ノードの年月日別に ID を振る」だけでは済まず、一つ一つの合併のまとまりを調べた上で一意の ID を振らなくてはいけない。特に平成の大合併では、同じ県内・同じ年月日に複数の組の合併があったりする。それらの調査と ID 付けを自力でやってまでこのデータモデルにする意味があるのか…で思案中。
1) 2001年5月1日、浦和市＋大宮市＋与野市 → さいたま市（旧）が発足。【新設合併】
2) 2003年4月1日、さいたま市（旧）→ 九つの政令区に分かれる。【分割】
3) 2005年4月1日、岩槻市 → さいたま市岩槻区になる。【移行】
この合併情報を、例えば下の二テーブルで表現してみる。データモデルは「エッジとノードのグラフ構造」で、各市町村の時系列の変化を道路に似たものと考える。合併や分割は交差点、移行は道路名の変わり目だろうか。一番目のテーブルがエッジで、両端のノード（node_org, node_des）が発足日と消滅日。その日付を、二番目のノードテーブルが持つ。
本来のグラフ構造と違うのは、エッジテーブルに、片方のノードが NULL の行があること。発足日は未調査、消滅日は本当にまだ存在しない。これで何か問題が出たら再検討する必要があるが、とりあえず進める。
例えば各市区町村の存在期間を出すには、↓ のように二テーブルを結合する。ここで存在期間を日付の範囲型にすると、検索や並べ替えで使い勝手がいい。二列の日付にすると、片方が NULL の行があるので面倒。
SELECT e.*, concat('[', n1.ymd, ',', n2.ymd, ')') :: daterange rng
FROM "201408"."05_sample_edges" e
LEFT JOIN "201408"."05_sample_nodes" n1 ON n1.nid = node_org
LEFT JOIN "201408"."05_sample_nodes" n2 ON n2.nid = node_des ;上の結果を一時テーブルに保存し、範囲型で簡単な検索を試した。↓ 範囲型を「以上・未満」で定義しているので WHERE 句をシンプルにでき、発足日と消滅日を間違える恐れが減る。下のように 2003年4月1日で検索すると、前日まで存在した旧さいたま市が自動的に除外される。
↓ 現時点で存在する市区町村の抽出も簡単。
範囲型の日本語ドキュメントは下記。
■ PostgreSQL 9.3.2文書 : 範囲型
http://www.postgresql.jp/document/9.3/html/rangetypes.html
■ PostgreSQL 9.3.2文書 : 範囲関数と演算子
http://www.postgresql.jp/document/9.3/html/functions-range.html
上の二例は一時点で検索する「断面型」だが、市町村合併では時系列変化を追うのも重要。例えば、現存する特定の市区町村が、過去のある年月日ではどの市区町村に当たるのかを調べる。その際 ↓ のように、再帰クエリで発足（消滅）ノードを順々につないでいくと簡単。
WITH RECURSIVE a AS (
SELECT * FROM "201408"."05_sample_edges"
WHERE ku = '桜区' -- 起点にする市区町村
UNION ALL
SELECT e.* FROM a, "201408"."05_sample_edges" e
WHERE a.node_org = e.node_des
)
SELECT a.*, concat('[', n1.ymd, ',', n2.ymd, ')') :: daterange rng
FROM a
LEFT JOIN "201408"."05_sample_nodes" n1 ON n1.nid = node_org
LEFT JOIN "201408"."05_sample_nodes" n2 ON n2.nid = node_des
ORDER BY rng DESC ;
さいたま市は政令市移行前の「全市一つ」の時期があるのと、合併前の市と政令区とが一対一対応でないため、合併情報だけから時系列をさかのぼると三つの市に当たってしまう。これはデータモデルと関係ないので良しとする。
今回の小規模な例では断面検索・時系列追跡ともに概ね良さそうだが、全国すべての市町村合併情報をこのモデルで構築する場合、一つ懸念がある。同じ年月日で、全国あちこちで合併等が起きていたということ。例えば2003年4月1日は、さいたま市が政令区に分かれただけでなく、山梨県南アルプス市の発足等があった。
ということは、単に「合併ノードの年月日別に ID を振る」だけでは済まず、一つ一つの合併のまとまりを調べた上で一意の ID を振らなくてはいけない。特に平成の大合併では、同じ県内・同じ年月日に複数の組の合併があったりする。それらの調査と ID 付けを自力でやってまでこのデータモデルにする意味があるのか…で思案中。