PostGISへの空間データの収容2

今回は、PostGISへの空間データの収容方法の第二回目として、街区レベル位置参照情報(CSV)を収容してみたいと思います。psqlを使ってCSVファイルを直接テーブルにロードします。

街区レベル位置参照情報とは

街区レベル位置参照情報は、国土交通省が公開している街区レベルまでの位置情報を示したデータで、全国を網羅しており、以下のように説明されています。

街区レベル位置参照情報とは、全国の都市計画区域相当範囲を対象に、街区単位(「○○町△丁目□番」)の位置座標(代表点の緯度・経度、平面直角座標)を整備したデータです。このデータを利用することで、住所などを含む表や台帳データに位置座標(緯度経度等)を付け、GISで地図上に展開して空間的な分析をすることができるようになります。

国土交通省 位置参照情報ダウンロードサービスのホームページより引用
http://nlftp.mlit.go.jp/isj/index.html

データはCSVテキスト形式で提供され、以下の構造となっています。

項目備考
都道府県名当該範囲の都道府県名"福島県"
市区町村名当該範囲の市区町村名"本宮市"
大字・丁目名当該範囲の大字・丁目名"岩根"
小字・通称名当該範囲の小字・通称名"下桶"
街区符号・地番当該範囲の街区符号・地番"126"
座標系番号平面直角座標系の座標系番号(1~19までの半角整数)"9"
X座標平面直角座標系の座標系原点からの距離"164065.1"
Y座標平面直角座標系の座標系原点からの距離"43616.6"
緯度十進経緯度"37.477545"
経度十進経緯度"140.326492"
住居表示フラグ1:住居表示実施、0:住居表示未実施(半角)"0"
代表フラグ1:代表する、0:代表しない(半角)"1"
更新前履歴フラグ1:新規作成、2:名称変更、3:削除、0:変更なし(半角)"0"
更新後履歴フラグ1:新規作成、2:名称変更、3:削除、0:変更なし(半角)"1"
国土交通省 位置参照情報ダウンロードサービスのホームページより引用

PostGISへのデータの収容

PostGISにデータを収容するまでの流れを以下に示します。
・一時テーブルを作成してpsqlのCOPYコマンドでCSVファイルをロード
・一時テーブルから本テーブルへコピー(同時にGEOMETRY変換)
・空間索引の設定
それでは作業を進めましょう。

1.データのダウンロード
データは、下記URLからダウンロードします。今回は東京都のデータを使います。
http://nlftp.mlit.go.jp/cgi-bin/isj/dls/_choose_method.cgi
位置参照情報のダウンロード画面
2.PostGISテーブルの作成
座標点を収容するテーブルを作成します。
ざっと調べたところではCOPYコマンドにてCSVファイル上の座標(緯度、経度)をGEOMETRYカラムに変換できないようです。そこで、CSVファイルをそのまま収容する”一時テーブル”(temp_gaiku)と実際に使用する”本テーブル”(gaiku)を作成しGEOMETRYカラム変換はSQLにて実施します。

一時テーブル作成用のSQL(テーブル名”temp_gaiku”)
create temporary table temp_gaiku(
都道府県名 VARCHAR(20),
市区町村名 VARCHAR(50),
大字・丁目名 VARCHAR(50),
小字・通称名 VARCHAR(20),
街区符号・地番 VARCHAR(20),
座標系番号 VARCHAR(2),
X座標 FLOAT8,
Y座標 FLOAT8,
緯度 FLOAT8,
経度 FLOAT8,
住居表示フラグ CHAR(1),
代表フラグ CHAR(1),
更新前履歴フラグ CHAR(1),
更新後履歴フラグ CHAR(1)
);

今回一時テーブルを使った理由は以下となります。
・専用の表領域にデータが収容されるのでユーザー表領域のデフラグ等を心配しなくてよい
・セッションを終了すると自動的に削除される
なお、一時テーブルのカラム名はCSVファイルのヘッダーに合わせています。

本テーブル作成用のSQL(テーブル名”gaiku”、座標収容カラム名”geometry”)
create table gaiku
(
pref_name VARCHAR(20),
city_name VARCHAR(20),
aza_name VARCHAR(50),
gaiku_no VARCHAR(20),
geometry GEOMETRY(POINT,4612),
status CHAR(1)
);

本テーブルは、座標をGEOMETRYカラムとして定義しています。SRIDは4612(JGD2000 緯度経度)としています。

3.COPYコマンドにより一時表へのデータロード
psqlから以下のCOPYコマンドを発行してCSVファイルを一時表へロードします。(273,454件)
ネットワークの速度にもよりますがロードは5分程度で終了します。

\copy temp_gaiku from ’13_2018.csv’ with csv header

4.一時テーブルから本テーブルへデータをコピー
次に、INSERT文を使って一時テーブルから本テーブルへデータをコピーします。
この時、緯度、経度カラムGEOMETRYカラムへ変換します。

insert into gaiku
(
pref_name,
city_name,
aza_name,
gaiku_no,
geometry,
status
)
select
都道府県名,
市区町村名,
大字・丁目名,
街区符号・地番,
ST_GeometryFromText(‘POINT(‘ || 経度 || ‘ ‘ || 緯度 || ‘)’ ,4612),
更新後履歴フラグ
from temp_gaiku;

5.空間索引の作成
最後に、以下のSQLで空間索引(gaiku_sdx)を作成します。

空間索引作成用のSQL(索引名はgaiku_sdx)
create index gaiku_sdx on gaiku using gist (geometry);

動作確認

では、動作確認をしましょう。
まずは、ある点から近い順に5件の街区を検索してみましょう。以下のSQLになります。

SELECT
city_name||aza_name||gaiku_no jusho,
ST_Distance(ST_Transform(g.geometry,2451), ST_Transform(ST_GeometryFromText(‘POINT(139.767 35.680)’,4612),2451)) as dist
FROM gaiku g
WHERE ST_DWithin(ST_Transform(g.geometry,2451), ST_Transform(ST_GeometryFromText(‘POINT(139.767 35.680)’,4612),2451), 300.0) and
g.status <> ‘3’
order by dist
fetch first 5 rows only;

ST_DWithin関数該当点の近隣レコードを検索し、sdo_nn_distance関数にて距離を表示します。status<>3は、廃止レコードを省くための条件です。
今回は、gaikuテーブル上のGEOMETRYデータがJGD2000緯度経度のため距離を正しく算出するために平面直角9系(SRID:2451)に変換したうえで、300m以内の点データを距離の近い順に5件取得しています。
結果は以下となり、オラクルでの実施結果とほぼ同じとなります。

   jusho       |  dist
------------+----------
千代田区丸の内一丁目9  | 70.1478926837428
中央区八重洲二丁目2  | 229.908052400422
千代田区丸の内一丁目10 | 233.373442665297
中央区八重洲二丁目1  | 242.408014781077
中央区八重洲一丁目9   | 253.903020591526
(5 rows)

上記SQLを、OpenJUMPにて表示した結果が以下になります。

OpenJUMPにて地図表示した状態

”POINT”と表示されている点が検索の基準点( 139.767,35.680 )です。黒文字の住所が街区、赤文字がポリゴンの属性である字名になります。
丸数字が検索された順番を示しており、検索結果が正しそうなことが確認できます。

まとめ

今回は、街区レベル位置参照情報をダウンロードしpsqlのCOPYコマンドを使ってPostGIS化して検索を実施しました。
作業自体は、オラクルのSQL*Loaderを使ったデータロードと大差ない内容でした。

オラクルのSQL*Loaderには様々な機能があって、今回のCSVであれば一時表を作ることなくGEOMETRYカラムを持つテーブルへのロードが可能です。私の調べが足りないかもしれませんが、psqlのCOPYコマンドはちょっと非力かなと感じます。
一方で空間関数について比較すると、オラクルよりPostGISの方が直感的にわかりやすいと感じています。この理由は、オラクルはSQL/MMにて空間関数が標準化される前からの独自実装の名残があるためだと思われます。パイオニアならではの辛さでしょうか。

余談ですが、私は前述の一時テーブルから本テーブルにコピーするINSERT文にて、緯度(Y)と経度(X)をGEOMETRY化する指定を逆にしてしまい、検索結果が正しく得られず無駄な時間を使ってしまいました。。

以下の記事では、今回使用した街区レベル位置参照データをオラクルに取り込む方法を紹介しています。