2023年10月31日火曜日

国土交通省のデータを使って簡易的な逆ジオコーディングを実装する

Oracle APEXのマップ上でクリックした位置で逆ジオコーディングを行い、住所を取得する要件がありました。Oracle APEXやOracle Autonomous Databaseは無料で使えるジオコーディングAPIを提供していますが、Oracle Maps Cloud(Oracle eLocation Service - このリンクを見る範囲ではHEREのデータ)を呼び出しているためか、日本語および日本の住所をサポートしていません。

GoogleやAzureが提供しているマップ・サービスに含まれるAPIを呼び出すこともできますが、それほど厳密に住所が得られなくてもよいので、国土交通省からダウンロードできる行政区域と位置参照情報を使って簡易的な逆ジオコーディングを実装してみました。

Oracle Spatialの機能を活用しています。


クイックSQLの以下のモデルから表を３つ作成します。表MLIT_DATAは、国土交通省からダウンロードした行政区域データのファイルを保存するために使用します。表MLIT_MUNICIPALITY_ADMINSは行政区域データ、表MLIT_LOCATION_REFERENCESは位置参照情報を保存します。

# prefix: mlit
# ondelete: cascade
data -- 行政区域のファイルを保存する
content file
municipality_admins -- 行政区域
prefecture_name vc20 /nn -- N03_001 都道府県名
branch_in_hokkaido vc40 -- N03_002 支庁・振興局名
major_city vc20 -- N03_003 郡・政令都市名
city_name vc40 -- N03_004 市区町村名
admin_code vc6 /nn -- N03_007 行政区域コード
geometry num -- 範囲 (geojsonを読み取った範囲)
geom_r num -- geometryをrectifyした結果
data_id /fk data
location_references -- 国土交通省の位置参照情報
region vc200 /nn -- 当該範囲の都道府県名
municipality vc200 /nn -- 当該範囲の市区町村名（郡部は郡名、政令指定都市の区名も含む）
settlement vc200 -- なし
street vc200 /nn -- 大字・丁目名
place_name vc200 -- 小字・通称名
sec_unit vc200 -- 街区符号・地番（通常は1以上の半角整数。ただし地域により漢字・アルファベット等もある）
latitude num /nn -- 緯度
longitude num /nn -- 経度
is_indicated_as_residence num /check 0,1 -- 1=住居表示実施, 0=住居表示未実施
is_represeted_address num /check 0,1 -- 1=代表する, 0=代表しない
operation_previous_year num /check 0,1,2,3 -- 1=新規作成,2=名称変更,3=削除,0=変更なし
operation_current_year num /check 0,1,2,3 -- 1=新規作成,2=名称変更,3=削除,0=変更なし
location num -- 緯度経度をsdo_geometry型に変換
municipality_id /fk municipality_admins
# 列geometry, geom_r, locationはDDLのnumberをsdo_geometryに変更する。
view raw milt-reverse-geocoding.model hosted with ❤ by GitHub
SQLの生成、SQLスクリプトを保存、レビューおよび実行をクリックします。


SQLスクリプトが開きます。表MLIT_MUNICIPALITY_ADMINSの列GEOMETRYと
GEOM_Rの型をNUMBERからOracle Spatialのオブジェクトを保存するSDO_GEOMETRYへ変更します。また表MLIT_LOCATION_REFERENCESの列LOCATIONもSDO_GEOMETRYへ変更します。

変更したDDLを実行します。確認画面では即時実行をクリックします。


すべての行が成功しエラーがなければ、３つの表が作成されています。

この画面からはアプリケーションの作成は行いません。


アプリケーション作成ウィザードを起動し、空のアプリケーションを作成します。アプリケーションの名前は逆ジオコーディングとします。

アプリケーションの作成をクリックします。


アプリケーションが作成されます。

行政区域のファイルをアップロードする画面を作成します。

ページの作成を実行します。


対話モード・レポートを選択します。


対話モード・レポートのページ番号は２、名前は行政区域ファイル一覧とします。ページ・モードは標準です。フォーム・ページを含めるをオンにします。

フォーム・ページ番号は３、フォーム・ページ名は行政区域ファイル編集とします。フォーム・ページ・モードはドロワーとします。

データ・ソースの表/ビューの名前としてMLIT_DATAを指定します。

ナビゲーションはデフォルトから変更せず、ブラッドクラムの使用、ナビゲーションの使用ともにオンとします。

次へ進みます。


主キー列1としてID (Number)を選択します。

ページの作成をクリックします。


ページが作成されます。

対話モード・レポートのページのリージョン行政区域ファイル一覧に含まれる列CONTENTを選択します。BLOB属性のMIMEタイプ列にCONTENT_MIMETYPE、ファイル名列にCONTENT_FILENAME、最終更新列にCONTENT_LASTUPD、文字セット列にCONTENT_CHARSETを設定します。

保存をクリックします。


ページ・デザイナでページ番号３のフォームのページを開きます。

タイプがファイル参照のページ・アイテムP3_CONTENTを選択します。

設定のMIMEタイプ列にCONTENT_MIMETYPE、ファイル名列にCONTENT_FILENAME、文字セット列にCONTENT_CHARSET、BLOB最終更新列にCONTENT_LASTUPDを設定します。これらの項目のオンライン・ヘルプには「大文字のページまたはアプリケーション・アイテム名を入力します。」と記述されていますが、アイテム名ではなく列名です。（つまりP3_といった接頭辞は付かない）。


ページ・アイテムP3_CONTENT_FILENAME、P3_CONTENT_MIMETYPE、P3_CONTENT_CHARSET、P3_CONTENT_LASTUPDはユーザーによってデータを入力されることはないため、すべて選択して構成のビルド・オプションを使ってコメント・アウトします。

以上でファイルのアップロードができるようになりました。

保存をクリックします。


国土交通省のWebページより、行政区域のデータをダウンロードします。

TOP > 国土数値情報 > 行政区域データ



Webページを下にスクロールします。今回は神奈川県のデータを使用することにしました。

N03-20230101_14_GML.zipをダウンロードします。アンケートのページが表示されるので、アンケートに答えてダウンロードに進むをクリックします。


ダウンロードしたN03-20230101_14_GML.zipを解凍すると、範囲がGEOJSON形式で表現されているファイルN03-23_14_230101.geojsonが見つかります。このファイルをアップロードします。

アプリケーションを実行し、ナビゲーション・メニューから行政区域ファイル一覧を開きます。

作成をクリックします。


ドロワーが開きます。

ContentとしてN03-23_14_230101.geojsonを選択します。

作成をクリックして、選択したファイルを表MLIT_DATAにアップロードします。


行政区域のファイルがアップロードされました。


行政区域のデータを、区域ごとの行として表MLIT_MUNICIPALITY_ADMINSへロードする処理を追加します。

ページ・デザイナでページ番号３のフォームのページを開きます。

リージョン行政区域ファイル編集のRegion Bodyにボタンを作成します。

識別のボタン名はLOAD、ラベルはLoad、外観のテンプレート・オプションを開き、詳細のWidthにStretchを選択します。動作のアクションはデフォルトのページの送信のまま、変更しません。


アップロード済みのファイルをロードの対象とするため、サーバー側の条件のタイプとしてアイテムはNULLではないを選択し、アイテムとしてP3_IDを指定します。


プロセス・ビューを開き、ボタンを押した時に実行されるロード処理を定義します。

新しくプロセスを作成し、プロセスプロセス・フォーム行政区域ファイル編集とダイアログを閉じるの間に配置します。

識別の名前を行政区域データのロードとし、タイプに実行チェーンを選択します。データ量によっては行政区域データのロードに時間がかかることも考えられるため、バックグラウンドで実行させます。設定のバックグラウンドで実行をオンにします。

サーバー側の条件のボタン押下時にLOADを設定します。


実行チェーン行政区域データのロード上でコンテキスト・メニューを表示させ、子プロセスの追加を実行します。


識別の名前をロードとし、タイプはコードを実行を選択します。ソースのPL/SQLコードとして以下を記述します。

/* 最初に以前のデータを全削除する */
delete from mlit_municipality_admins where data_id = :P3_ID;
/* データのロード */
insert into mlit_municipality_admins
(
prefecture_name,
branch_in_hokkaido,
major_city, city_name,
admin_code,
geometry,
data_id
)
select
jt.N03_001 prefecture_name
,jt.N03_002 branch_in_hokkaido
,jt.N03_003 major_city
,jt.N03_004 city_name
,nvl(jt.N03_007,'N/A') admin_code
,jt.geometry geometry
,mlit_data.id
from mlit_data,
json_table(content, '$.features[*]'
columns(
N03_001 varchar2(255) path '$.properties.N03_001',
N03_002 varchar2(255) path '$.properties.N03_002',
N03_003 varchar2(255) path '$.properties.N03_003',
N03_004 varchar2(255) path '$.properties.N03_004',
N03_007 varchar2(255) path '$.properties.N03_007',
geometry sdo_geometry path '$.geometry'
)
) jt
where mlit_data.id = :P3_ID;
/* recitifyする。 */
update mlit_municipality_admins set geom_r = sdo_util.rectify_geometry(geometry,0.05)
where data_id = :P3_ID;
view raw insert_area_data.sql hosted with ❤ by GitHub

以上で表MLIT_MUNICIPALITY_ADMINSへのデータのロード処理が実装できました。


プロセスダイアログを閉じるを選択し、サーバー側の条件の値にLOADを追加します。LOADを押した時に、ドロワーが閉じるようになります。

保存をクリックします。


バックグラウンドで実行されるプロセスを確認するための、簡単なページを作成します。

ページの作成を開始し、対話モード・レポートを選択します。


ページ番号は４、名前はバックグラウンド処理とします。データ・ソースのソース・タイプとしてSQL問合せを選択し、SQL SELECT文を入力に以下を記述します。

select * from APEX_APPL_PAGE_BG_PROC_STATUS

ページの作成をクリックします。


バックグランド処理を一覧するページが作成されました。

アプリケーションを実行し、表MLIT_MUNICIPALITY_ADMINSへのデータ・ロードを実行します。

ナビゲーション・メニューから行政区域ファイル一覧を開き、すでにアップロード済みのファイルを選択します。

ボタンLoadをクリックし、データのロードを開始します。バックグラウンドで処理されるため、処理の完了を待たずにドロワーは閉じます。


バックグラウンド処理のページを開き、処理が正常に終了していることを、列StatusやStatus Codeから確認します。


表MLIT_MUNICIPALITY_ADMINSの列GEOM_R（rectify実行後のデータ）に、行政区域を表す矩形データが準備できました。逆ジオコーディングを実行する際に、最初に指定した地点がどの行政区域に含まれるかを探し、見つけた行政区域内で住所を検索します。指定した地点が含まれる行政区域の検索を速くするために、列GEOM_Rに空間索引を作成します。

オブジェクト・ブラウザを開き、表MLIT_MUNICIPALITY_ADMINSを選択します。索引タブを開き、作成をクリックします。


索引のタイプとして空間を選択します。索引名はデフォルトでMLIT_MUNICIPALITY_ADMINS_SXになるので、そのまま使用します。索引列1にGEOM_Rを指定します。システム管理索引はオン、表にはポイント・オブジェクトのみが含まれるはオフです。

列GEOM_Rの空間メタ・データが未登録の場合、索引の作成の代わりに列の登録というボタンが表示されます。

列の登録をクリックします。


座標系や許容範囲の値が表示されます。通常、変更は不要です。

空間メタデータの追加をクリックします。


APEX 23.1ではエラーが発生し、空間メタデータの作成ができません。おそらく不具合です。


ワークアラウンドとして、SQLのプレビューをクリックし、表示されたSQLをSQLコマンドから実行します。

SQLをコピーします。


一旦、索引の作成を取消し、SQLコマンドからコピーしたSQLを実行します。


列GEOM_Rの空間メタデータが作成されました。先ほどと同じ手順で空間索引の作成を行います。

空間メタデータが作成済みなため、Spatialレジストリに列GEOM_Rの座標系IDと座標系名が表示されます。（実際は国土交通省の行政区域データの座標系IDは6668、座標系名はJGD2011ですが、4326、WGS84として扱っても問題ないと判断しています）。

索引の作成をクリックします。


空間索引MLIT_MUNICIPALITY_ADMINS_SXが作成されます。ステータスがVALIDであることを確認します。


以上で行政区域データの、データベースへのロードが完了しました。

続いて、位置参照情報をデータベースにロードします。

国土交通省のWebページから位置参照情報をダウンロードします。

国土数値情報ダウンロードサイトの位置参照情報を開き、都道府県単位をクリックします。
https://nlftp.mlit.go.jp/cgi-bin/isj/dls/_choose_method.cgi


今回の作業は神奈川県に限定しているため、都道府県選択として神奈川県にのみチェックを入れます。データは全てを選択し、データ整備年度は令和４年にします。

選択をクリックします。


全ての街区レベルを選択、全ての大字・町丁目レベルを選択にチェックを入れます。

選択をクリックします。


利用約款の同意を求められます。利用約款を読み、同意できる場合は同意します。

利用約款に同意すると、以下のようなダウンロード画面が表示されます。今回は、市区町村全域の街区を含むデータをダウンロードします。


ダウンロードの確認を求められます。OKをクリックします。


今回の対象（神奈川県）では、ファイル14000-21.0a.zipがダウンロードされました。このファイルを解凍すると14_2022.csvというCSVファイルが含まれており、これに位置参照情報（住所と座標値）が記録されています。

このCSVファイルを表MLIT_LOCATION_REFRENCESにロードするために、データ・ロード定義を作成します。

共有コンポーネントのデータ・ロード定義を開きます。


作成をクリックします。


データ・ロードの作成として最初からを選択します。

次へ進みます。


ターゲットの名前は位置参照情報とします。ターゲット・タイプは表、表名にMLIT_LOCATION_REFERENCESを指定します。

次へ進みます。


サンプル・データとして先ほどダウンロードしたファイルに含まれているCSVファイル（今回の例では14_2022.csv）を選択します。

次へ進みます。


列見出しの最初の行にヘッダーが含まれるにチェックを入れ、ファイル・エンコーディングとして日本語(Shift JIS)を選択します。

列のマッピングとして、以下を指定します。

ソース列　　　　　 マップ先
都道府県名             REGION
市区町村名             MUNICIPALITY
大字_丁目名           STREET
小字_通称名           PLACE_NAME
街区符号_地番       SEC_UNIT


座標系番号、X座標、Y座標はロード対象から外します（マップ先を設定しない）。

ソース列　　　　　   マップ先
緯度                        LATITUDE
経度                        LONGITUDE
住居表示フラグ         IS_INDICATED_AS_RESIDENCE
代表フラグ               IS_REPRESENTED_ADDRESS
変更前履歴フラグ      OPERATION_PREVIOUS_YEAR
変更後履歴フラグ      OPERATION_CURRENT_YEAR

データ・ロードの作成をクリックします。


データ・ロード定義位置参照情報が作成されました。

表MLIT_LOCATION_REFERENCESの列LOCATIONおよびMUNICIPALITY_IDにデータを投入する設定（データ・プロファイル列）を追加します。

データ・ロード定義位置参照情報を開きます。


データ・プロファイルの編集をクリックします。


データ・プロファイルの定義画面が開きます。

列の追加をクリックします。


列LOCATIONのデータ・プロファイルを定義します。

列タイプにSQL式を選択します。名前はLOCATIONです。データ型としてジオメトリ(SDO_GEOMETRY)を選択します。

SQL式として以下を記述します。ロードされる行に含まれるLATITUDEとLONGITUDEの値から、SDO_GEOMETRY型のポイントのデータを生成しています。

sdo_geometry(2001, 4326,sdo_point_type("LONGITUDE","LATITUDE",null),null,null)

作成をクリックします。


同様の手順で列MUNICIPALITY_IDのデータ・プロファイルを定義します。MUNICIPALITY_IDには、この位置参照情報を含む行政区域のIDを設定します。

列タイプにSQL問合せ(単一の値を返す)を選択します。名前はMUNICIPALITY_IDです。データ型としてNUMBERを選択します。

SQL問合せとして以下を記述します。
select id from mlit_municipality_admins
where sdo_contains(geom_r,sdo_geometry(2001, 4326,sdo_point_type("LONGITUDE","LATITUDE",null),null,null)) = 'TRUE'
作成をクリックします。


以上で、データ・プロファイルの設定は完了です。

変更の適用をクリックします。


変更の適用をクリックし、データ・ロード定義の編集を終了します。


作成したデータ・ロード定義を使って、データ・ロードのページを作成します。

ページの作成をクリックします。


データ・ロードを選択します。


ページ番号は５、名前は位置参照情報ロードとします。データ・ロードとして先ほど作成した、位置参照情報を選択します。データのアップロード元はファイル、最大ファイル・
サイズ(MB)は100に上限を引き上げます。

ページの作成をクリックします。


データ・ロードのページが作成されます。

処理は時間がかかるため、ロード処理がバックグランドで実行されるように設定を追加します。

左ペインでプロセス・ビューを表示します。

プロセスを新規に作成します。識別の名前はバックグラウンド処理、タイプは実行チェーンとします。設定のバックグラウンドで実行をオンにします。

一時ファイル処理にMove、一時ファイル・アイテムとしてF5_FILEを選択します。

サーバー側の条件のボタン押下時にLOADを指定します。


プロセスデータのロードを選択し、識別の実行チェーンとしてバックグラウンド処理を指定します。


以上でデータ・ロードのページは完成です。

アプリケーションを実行し、作成した位置参照情報ロードのページを開きます。

ファイルの選択から、ダウンロードした位置参照情報のファイルを選択します。


プレビューが表示されます。データのロードをクリックします。


バックグラウンド処理を開いて、列StatusやSofarを見て実行状況を確認します。


データのロードが完了すると、神奈川県で逆ジオコーディングを行うための準備は完了です。

ホーム・ページにマップ・リージョンを作成し、逆ジオコーディングを実装します。

ページ・デザイナでホーム・ページを開きます。

マップ上で選択した位置の緯度経度を保持するためのページ・アイテムを作成します。

緯度を保持するページ・アイテムを作成します。識別の名前はP1_LATITUDE、タイプは数値フィールド、ラベルは緯度とします。


経度を保持するページ・アイテムを作成します。識別の名前はP1_LONGITUDE、タイプは数値フィールド、ラベルは経度とします。P1_LATITUDEの右に配置されるよう、レイアウトの新規行の開始をオフにします。


マップ・リージョンを作成します。

識別のタイトルは地図、タイプにマップを選択します。詳細の静的IDにmapを設定します。


レイヤーを選択します。

識別の名前を位置、レイヤー・タイプにポイントを選択します。ソースのタイプにSQL問合せを選択し、SQL問合せに以下を記述します。ページ・アイテムP1_LATITUDEとP1_LONGITUDEに設定されている座標にマーカーを表示します。

select to_number(:P1_LATITUDE) lat, to_number(:P1_LONGITUDE) lon from dual

送信するページ・アイテムとしてP1_LATITUDEとP1_LONGITUDEを設定します。

列のマッピングのジオメトリ列のデータ型として経度/緯度を選択し、経度にLON、緯度にLATを選択します。


地図上をクリックしたときに、クリックした位置の緯度経度をページ・アイテムP1_LATITUDEとP1_LONGITUDEに設定する動的アクションを作成します。クリックした位置にマーカーを表示するため、座標をページ・アイテムに設定した後に、リージョン地図をリフレッシュします。

リージョン地図上でコンテキスト・メニューを表示させ、動的アクションの作成を実行します。


作成された動的アクションの識別の名前は、地図をクリックとします。タイミングのイベントとして、マップがクリックされました[マップ]を選択します。選択タイプはリージョン、リージョンは地図です。


TRUEアクションとしてJavaScriptコードの実行を選択し、設定のコードに以下を記述します。
apex.items.P1_LATITUDE.setValue(this.data.lat);
apex.items.P1_LONGITUDE.setValue(this.data.lng);
apex.region("map").refresh();


この状態で、マップ上の任意の地点をクリックすると、その位置の緯度経度の値が取得され、マーカーが移動します。


今回は神奈川県に限定して逆ジオコーディングを行うので、初期位置を横浜駅にします。

リージョン地図を選択し、プロパティ・エディタの属性タブを開きます。

初期位置およびズームのタイプに静的値、経度に139.62261961841、緯度に35.46606942124を指定します。ズーム・レベルは８にします。

レイヤーは１つだけなので、凡例の表示はオフにします。


今まで準備したデータを使って逆ジオコーディングを行うパイプライン表関数do_reverse_encodingを作成します。以下のスクリプトを実行します。

drop type t_reverse_geocoding_tab;
create or replace type t_reverse_geocoding_row as object(
id number
,region varchar2(200)
,municipality varchar2(200)
,settlement varchar2(200)
,street varchar2(200)
,place_name varchar2(200)
,sec_unit varchar2(200)
,location sdo_geometry
,municipality_id number
);
/
create or replace type t_reverse_geocoding_tab as table of t_reverse_geocoding_row;
/
create or replace function do_reverse_geocoding(
p_latitude in number
,p_longitude in number
,p_count in number
) return t_reverse_geocoding_tab pipelined
as
l_point sdo_geometry;
l_id number;
begin
/* p_latitude, p_longitudeのどちらかまたは両方がnullであれば、行を返さない */
if p_latitude is null or p_longitude is null then
return;
end if;
/* 緯度経度からOracle Spatialのポイントを作成する。 */
l_point := apex_spatial.point(p_lat => p_latitude, p_lon => p_longitude);
/*
* 最初に行政区分を見つける。
* 見つからない場合は一行も返さず終了する。
*/
begin
select id into l_id
from mlit_municipality_admins
where sdo_contains(geom_r, l_point) = 'TRUE';
exception
when no_data_found then
return;
end;
/*
* 行政区分内で指定した座標に近い住所をp_count分だけ
* 返す。
* 行政区分がわからない位置参照は常に検索の対象としている。
*/
for r in (
select * from mlit_location_references
where (municipality_id = l_id or municipality_id is null)
and operation_current_year <> 3
order by sdo_geom.sdo_distance(location, l_point) asc
fetch first p_count rows only
)
loop
pipe row(
t_reverse_geocoding_row(
r.id
,r.region
,r.municipality
,r.settlement
,r.street
,r.place_name
,r.sec_unit
,r.location
,r.municipality_id
)
);
end loop;
end do_reverse_geocoding;
/
view raw do_reverse_geocoding.sql hosted with ❤ by GitHub

SQLスクリプトなどを使って実行します。


逆ジオコーディングを行なって得られた住所をページ・アイテムの選択リストに表示します。

ページ・アイテムを作成します。

識別の名前をP1_ADDRESS、タイプは選択リスト、ラベルは住所とします。

LOVのタイプをSQL問合せとし、SQL問合せとして以下を記述します。クリックした地点に近い順の住所を５つまで取得しています。
select address d, address r
from (
    select region || ' ' || municipality || ' ' || street || ' ' || sec_unit address
    from table(do_reverse_geocoding(
        p_latitude => to_number(:P1_LATITUDE)
        ,p_longitude => to_number(:P1_LONGITUDE)
        ,p_count => 5
    ))
)
追加値の表示はオフ、NULL値の表示もオフにします。

座標が変わった時に住所が更新されるよう、カスタケードLOVの親アイテムとしてP1_LATITUDEとP1_LONGITUDEを指定します。親が必要はオンです。


以上で、逆ジオコーディングを行うアプリケーションは完成です。アプリケーションを実行すると、記事の先頭のGIF動画のように動作します。

今回は神奈川県の行政区域と位置参照情報を紐づけて、行政区域で絞り込んでから座標に近い住所を探しています。そのため、行政区域と位置参照情報の紐付けを行なっていますが、いくつかの住所は行政区域に紐づけられませんでした。

以下のSELECT文の結果をマップに表示して確認しました。
select location, municipality, street, sec_unit from mlit_location_references where municipality_id is null and operation_current_year <> 3

地図の表示は以下になります。


一部を拡大すると、位置参照情報が県境を超えている場合があるようです。


地図データの扱いも色々と考慮しないといけないことが多いみたいです。

今回のアプリケーションはAlways FreeのAutonomous Databaseに実装しています。地域を神奈川に限定しているため、なんとか応答が返ってきていますが、全国のデータを扱うとどの程度の応答時間になるかは分かりません。そのような場合、表MLIT_MUNICIPALITY_ADMINSおよびMLIT_LOCATION_REFERENCESをパーティション分割し、パラレル問合せを発行することで応答時間が短縮できるでしょう。パーティションは単純なハッシュ分割で良いかと思います。

今回作成したAPEXアプリケーションのエクスポートを以下に置きました。
https://github.com/ujnak/apexapps/blob/master/exports/sample-reverse-geocoding.zip

Oracle APEXのアプリケーション作成の参考になれば幸いです。

完
投稿者 時刻:
ラベル: ,
登録: 投稿 (Atom)