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

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

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

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

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

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

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

[table id=3 /]

国土交通省 位置参照情報ダウンロードサービスのホームページより引用

PostGISへのデータの収容

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

一時テーブル作成用のSQL（テーブル名”temp_gaiku”）
create temporary table temp_gaiku(
都道府県名 VARCHAR(20),
市区町村名 VARCHAR(50),
大字・丁目名 VARCHAR(50),
小字・通称名 VARCHAR(20),
街区符号・地番 VARCHAR(20),
座標系番号 VARCHAR(2),
Ｘ座標 FLOAT8,
Ｙ座標 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 緯度経度）としています。

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

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

空間索引作成用の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にて表示した結果が以下になります。

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

まとめ

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

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

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

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

[blogcard url=”https://takamoto.biz/gis/oraspatial2″]