オラクルへの空間データの収容2
今回は、オラクルへの空間データの収容方法の第二回目として、街区レベル位置参照情報をオラクルに収容してみたいと思います。
街区レベル位置参照情報とは
街区レベル位置参照情報は、国土交通省が公開している街区レベルまでの位置情報を示したデータで、全国を網羅しており、以下のように説明されています。
街区レベル位置参照情報とは、全国の都市計画区域相当範囲を対象に、街区単位(「○○町△丁目□番」)の位置座標(代表点の緯度・経度、平面直角座標)を整備したデータです。このデータを利用することで、住所などを含む表や台帳データに位置座標（緯度経度等）を付け、GISで地図上に展開して空間的な分析をすることができるようになります。
国土交通省 位置参照情報ダウンロードサービスのホームページより引用
http://nlftp.mlit.go.jp/isj/index.html
データの構成は、以下のようになっています。
[table id=3 /]
国土交通省 位置参照情報ダウンロードサービスのホームページより引用
オラクルへ収容する流れ
オラクルにデータを収容するまでの流れを以下に示します。
- 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にて表示した結果が以下になります。
”POINT”と表示されている点が検索の基準点（ 139.7670944,35.6806302 ）です。黒文字の住所が街区、赤文字がポリゴンの属性である字名になります。
丸数字が検索された順番を示しており、検索結果が正しそうなことが確認できます。
実務で逆ジオコーダを実装する場合は、ポリゴンの中だけから検索する例もあります。
まとめ
ここまで、街区レベル位置参照情報をダウンロードしSQL*PlusとSQL*Loaderを使ってオラクルデータ化し検索までを行いました。
号レベルまでの住所データ（今回は街区レベルなので、番レベル）があれば、本格的なジオコーダが作成できますが、番レベルまででよければ今回使ったデータでジオコーダ（住所名や住所コードから位置検索）、逆ジオコーダ（位置から住所検索）ができます。
