2014/08/06
PostgreSQL >  市町村合併のデータモデル（2）
2014/08/05 の続き。栃木県のウェブサイト ↓ で近年の市町村合併状況が簡潔にまとまっていたので、今回思い付いたデータモデルを試す。実行環境は 2014/07/28 を参照。

http://www.pref.tochigi.lg.jp/a02/pref/shichouson/gappei/1174633899527.html


↓ まず 2014/08/05 と同様、エッジとノードの二テーブルを作る。ノード ID の数字に意味はなく、市町村合併（編入）の組を一意の数字で区別できればいい。



↓ 一応テーブルデータを CSV で置いておく。市町村コードや合併年月日の打ち間違いがあるかもしれない。特に平成を西暦に変換した時…。

https://kenpg.up.seesaa.net/image/20140806_sample_edges.csv
https://kenpg.up.seesaa.net/image/20140806_sample_nodes.csv


上の二テーブルを別々に見る限り役に立たないが、↓ のようにノード ID で結合して市町村の存続期間を PostgreSQL の範囲型で作ると、県内の変遷が一応まとまる。
SELECT e.*, concat('[', n1.ymd, ',', n2.ymd, ')') :: daterange rng
FROM "201408"."06_sample_edges" e
LEFT JOIN "201408"."06_sample_nodes" n1 ON nid_org = n1.nid
LEFT JOIN "201408"."06_sample_nodes" n2 ON nid_des = n2.nid
ORDER BY jcode, nid_org NULLS FIRST ;


↑ ちょっと長いが全行載せた。町村の大半が2005年頃に一気に消滅し既存の市に編入されたりした。ところで元のエッジテーブルからそうだったが、町村を編入した市は同じコードで複数行ある。編入時に市町村コードが変わらないため（他の県ではそうでない例もある）。それらを区別するには、例えば ↓ のようにウィンドウ関数の row_number で期間ごとに古い順に番号を振る。
SELECT jcode, row_number() over w :: int, jname
	, concat('[', n1.ymd, ',', n2.ymd, ')') :: daterange rng
FROM "201408"."06_sample_edges" e
LEFT JOIN "201408"."06_sample_nodes" n1 ON nid_org = n1.nid
LEFT JOIN "201408"."06_sample_nodes" n2 ON nid_des = n2.nid
WINDOW w AS (PARTITION BY jcode
	ORDER BY jcode, nid_org NULLS FIRST) ;


↑ 栃木市は数年の間に3回編入を行った。他県でも、県庁所在市やそれに次ぐ地方都市は同じように短期間に何回も編入を行った例がある。このような市について国勢調査など数年に一回の統計を使うと、毎回、市のバウンダリが違ったりして面倒。

↓ 今日最後のクエリ例で、年月日を指定して市町村数の内訳を算出するもの。冒頭に挙げた栃木県のウェブサイトにある数字と合うか確認。年月日を変えたい時は b ブロックの VALUES 句を適宜書き変えるだけ。
WITH a AS (
	SELECT e.*, concat('[', n1.ymd, ',', n2.ymd, ')') :: daterange rng
	FROM "201408"."06_edges" e
	LEFT JOIN "201408"."06_nodes" n1 ON nid_org = n1.nid
	LEFT JOIN "201408"."06_nodes" n2 ON nid_des = n2.nid
), b AS (
	VALUES ('2004-4-1' :: date)
		, ('2005-4-1')
		, ('2006-4-1')
		, ('2007-4-1')
		, ('2009-3-23')
		, ('2010-3-29')
		, ('2013-10-1')
		, ('2014-4-5')
), c AS (
	SELECT column1 ymd, right(jname, 1) cat, count(*) cnt
	FROM a, b
	WHERE jcode LIKE '09%' AND rng @> column1
	GROUP BY 1, 2
)
SELECT ymd, concat(sum(cnt), '自治体')
	, array_agg(concat(cnt, cat)
	ORDER BY CASE cat WHEN '市' THEN 1 WHEN '町' THEN 2 END)
FROM c
GROUP BY ymd
ORDER BY ymd ;


↑↓ 同じ結果なので多分大丈夫。2014/08/05 で書いた任意年月日の市町村一覧（断面検索）や任意市町村の変遷（時系列追跡）に加え、このような市町村数の集計にも一応使えそう。


エッジテーブルの主キーを考えると、ノードに NULL がよく入るのは再考すべきかもしれない。ゼロか何かを入れて「存在しないノード」を明示するとか。このへんはまだまだ考え中で、いろいろ実際のデータを入れてクエリを試しながら検討する。

他県についても少しずつテーブルに入力して試しており、しばらくこのデータモデルのメモを続けるつもりだったが、7月下旬に PostgreSQL 9.4 Beta 2 がリリースされたり pgAdmin の新バージョンが出たりした。そちらの方が重要なので、この件は少し後で再開する予定。
<< pgAdmin 1.20 Beta1 をインストール
市町村合併のデータモデル（1） >>