オラクルへの空間データの収容2

今回は、オラクルへの空間データの収容方法の第二回目として、街区レベル位置参照情報をオラクルに収容してみたいと思います。

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

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

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

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

データの構成は、以下のようになっています。

項目備考
都道府県名当該範囲の都道府県名"福島県"
市区町村名当該範囲の市区町村名"本宮市"
大字・丁目名当該範囲の大字・丁目名"岩根"
小字・通称名当該範囲の小字・通称名"下桶"
街区符号・地番当該範囲の街区符号・地番"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"
国土交通省 位置参照情報ダウンロードサービスのホームページより引用

オラクルへ収容する流れ

オラクルにデータを収容するまでの流れを以下に示します。

1.データのダウンロード
データは、下記URLからダウンロードします。今回は東京都のデータを使います。
http://nlftp.mlit.go.jp/cgi-bin/isj/dls/_choose_method.cgi
位置参照情報のダウンロード画面
2.オラクルテーブルの作成
座標点を収容できるオラクルテーブルを作成します。
同時にSRID(空間参照情報、EPSGと同じ値)や座標範囲などを示すメタデータを作成します。

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

ジオメトリカラム用メタデータ作成用SQL(SRID=4612)
INSERT INTO USER_SDO_GEOM_METADATA (
TABLE_NAME,
COLUMN_NAME,
DIMINFO,
SRID
) VALUES (
‘GAIKU’,
‘GEOMETRY’,
SDO_DIM_ARRAY(
SDO_DIM_ELEMENT(‘X’, 120, 160, 0.0000001),
SDO_DIM_ELEMENT(‘Y’, 20, 60, 0.0000001)
),
4612
);

3.SQL*Loader制御ファイルの作成
今回は、SQL*LoaderにてCSVファイルから直接テーブルにロードします。SQL*Loaderには、CSVファイルとテーブルの対応やロード方法を示す制御ファイルが必要です。
以下に今回の制御ファイルの内容を示します。

SQL*Loader制御ファイルの内容(CSVの緯度経度をXYに逆転)
OPTIONS(SKIP=1)
LOAD DATA
INFILE ’13_2018.csv’
BADFILE ’13_2018.bad’
TRUNCATE
INTO TABLE gaiku
FIELDS TERMINATED BY “,”
OPTIONALLY ENCLOSED BY ‘”‘
TRAILING NULLCOLS (
pref_name ,
city_name ,
aza_name ,
“koaza_name” filler ,
gaiku_no ,
“kei” filler,
“lat” filler,
“lon” filler,
GEOMETRY COLUMN OBJECT
(
SDO_GTYPE constant 2001,
SDO_SRID constant 4612,
SDO_POINT COLUMN OBJECT
(Y FLOAT EXTERNAL,
X FLOAT EXTERNAL)
),
“flag1” filler,
“flag2” filler,
“flag3” filler,
status
)

4.SQL*Loaderの実行
コマンドラインからSQL*Loaderを実行します。
SQL*Loaderは、Instant ClientのToolsに含まれています。
コマンドの実行イメージは、以下の通りです。今回の約27万件は5分程度でロードできます。

>sqlldr takamoto/takamoto@sakuradb control=.\gaiku.ctl data=’.\13_2018.csv’ BAD=13_2018.bad
SQL*Loader: Release 12.2.0.1.0 – Production on 日 2月 2 09:16:13 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
使用パス: 従来型
コミット・ポイントに達しました – 論理レコード件数64
コミット・ポイントに達しました – 論理レコード件数128
コミット・ポイントに達しました – 論理レコード件数192

 ~ 中略 ~

コミット・ポイントに達しました – 論理レコード件数273429
コミット・ポイントに達しました – 論理レコード件数273454
表GAIKU:
273454 行は正常にロードされました。
確認するログ・ファイル:
gaiku.log
ロードの詳細を参照してください。

5.空間索引の作成
ロードしたデータは、SDO_GEOMETRYオブジェクト型に座標が収容されている状態です。このままでは空間演算(範囲内検索とか領域のANDとか)を使用できません。
以下のSQLで空間索引を作成することで、空間演算が実施できるようになります。

空間索引作成用のSQL(索引名はgaiku_sdx)
CREATE INDEX gaiku_sdx ON gaiku (GEOMETRY) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

動作確認

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

SELECT
city_name||aza_name||gaiku_no jusho,sdo_nn_distance(1) dist FROM gaiku g
WHERE
SDO_NN(
g.geometry,
sdo_geometry(2001, 4612, sdo_point_type(139.7670944,35.6806302,NULL), NULL, NULL),
‘sdo_num_res=5’ ,1) = ‘TRUE’ and
status <> 3
order by dist;

SDO_NN関数該当点の近隣レコードを検索し、sdo_nn_distance関数にて距離を表示します。status<>3は、廃止レコードを省くための条件です。
実行結果は以下になります。

JUSHO         DIST
千代田区丸の内一丁目9   70.1548945586559
中央区八重洲二丁目2   229.930937920896
千代田区丸の内一丁目10  233.396680149324
中央区八重洲二丁目1   242.432153800126
中央区八重洲一丁目9   253.928303430044

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

OpenJUMPにて地図表示した状態

”POINT”と表示されている点が検索の基準点( 139.7670944,35.6806302 )です。黒文字の住所が街区、赤文字がポリゴンの属性である字名になります。
丸数字が検索された順番を示しており、検索結果が正しそうなことが確認できます。
実務で逆ジオコーダを実装する場合は、ポリゴンの中だけから検索する例もあります。

まとめ

ここまで、街区レベル位置参照情報をダウンロードしSQL*PlusとSQL*Loaderを使ってオラクルデータ化し検索までを行いました。
号レベルまでの住所データ(今回は街区レベルなので、番レベル)があれば、本格的なジオコーダが作成できますが、番レベルまででよければ今回使ったデータでジオコーダ(住所名や住所コードから位置検索)、逆ジオコーダ(位置から住所検索)ができます。