全国医療機関の医療体制の状況をオープンデータをもとに地図上に表示する

 政府CIOポータルより新型コロナウイルス感染症対策関連：全国医療機関の医療体制の状況(G-MISデータ)が、オープンデータとして公開されています。このデータをもとに、Oracle APEXのマップ・リージョンに医療機関の位置と状況を表示させてみました。

オープンデータの仕様はこちらに解説されています。

https://corona.go.jp/dashboard/#opendata

指定した日付以降を取得するデータの対象にできること、都道府県名による絞り込みができること、また、全国地方公共団体コード(6桁)による絞り込みができます。

https://opendata.corona.go.jp/api/covid19DailySurvey/日付?prefName=都道府県名

https://opendata.corona.go.jp/api/covid19DailySurvey/日付?localGovCode=全国地方公共団体コード

それぞれの検索条件の指定は任意です。検索条件がない場合、全件が対象(24,903件 - 2021年7月7日時点)になります。返されるデータの形式はJSONです。

マップに医療体制の状況を表示するために、以下の順番で作業を進めます。

1. 医療体制の状況を参照するRESTデータ・ソースを作成する。
2. RESTデータ・ソースをソースとしたレポートのページを作成する。
3. RESTデータ・ソースから取得したデータを保存する表を作成する。
4. RESTデータ・ソースとローカル表を同期させる。
5. ローカル表より、マップ・リージョンを含むページを作成する。

アプリケーションを作成する

今回の一連の作業を行うアプリケーションを、新規に作成します。アプリケーション作成ウィザードを起動します。アプリケーション・ビルダーを開いて、作成を実行します。

新規アプリケーションをクリックします。

アプリケーションの名前を医療提供体制の状況とします。それ以外の追加設定は行わず、空のアプリケーションを作ります。アプリケーションの作成をクリックします。

アプリケーションが出来上がりました。

RESTデータ・ソースを作成する

共有コンポーネントを開きます。

データ・ソースのグループにRESTデータ・ソースが含まれます。これを開きます。

作成済みのRESTデータ・ソースが一覧されます。アプリケーションを作成した直後なので、RESTデータ・ソースは１件もリストされません。作成をクリックして、新規作成を開始します。

RESTデータ・ソースの作成は最初から実施します。次へ進みます。

オープンデータを提供しているエンドポイントを指定します。RESTデータ・ソース・タイプに簡易HTTPを選択します。名前はcovid19DailySurveyとします。この名前よりデフォルトの静的IDが導出されます。英数字で指定しておくと、後で静的IDの修正が不要になります。

URLエンドポイントとして、検索パラメータの指定を含めた、実際にデータが返されるURLを指定します。

今回は以下のURLを指定しました。

https://opendata.corona.go.jp/api/covid19DailySurvey/20210701?localGovCode=131016

列の情報は、上記のURLより返されたデータを解析して得られます。そのため、ある程度のデータが返される必要があります。

HTTPSホスト名の指定は不要です。次へ進みます。

リモート・サーバー、ベースURLおよびサービスURLパスの確認画面が表示されます。通常は自動検出された情報を訂正する必要はありません。確認だけして、次へ進みます。

認証は不要なので、認証が必要ですはOFFにします。検出をクリックします。

取得したデータのプレビューが表示されます。

データ・プロファイルも確認してみます。FACILITYID、FACILITYCODEがNUMBERとして認識されていますが、全件を対象とすると数値以外もあります。ここで認識されたデータ型はRESTデータ・ソース作成後に修正できます。

RESTデータ・ソースの作成をクリックします。

RESTデータ・ソースが作成されました。これからRESTデータ・ソースに必要な修正を加えていきます。RESTソース名covid19DailySurveyをクリックして開きます。

最初にRESTデータ・ソースに３つの絞り込み条件を定義します。指定した日付以降を対象としるsince、都道府県名のprefName、全国地方公共団体コードのlocalGovCodeです。

URLパス接頭辞を20210701から:sinceへ変更します。

パラメータのlocalGovCodeを開き、値として設定されている131016を削除して無指定に変更します。詳細の値が空の場合に省略はONに変更します。RESTデータ・ソースの呼び出し時にlocalGovCodeの指定がなければ、URLにlocalGovCode=といった指定自体を含めません。

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

localGovCodeの修正は完了です。続いて、パラメータの追加をクリックします。

パラメータのタイプを問合せ文字列変数とし、名前をprefNameとします。都道府県名をデータ取得の条件とするパラメータです。詳細の値が空の場合に省略をONにします。追加後、さらに追加をクリックします。

パラメータのタイプをURLパターン変数とし、名前をsinceとします。

パラメータの追加をクリックします。

パラメータが３つ作成され、すべて目的は入力、デフォルト値はなし、必須はいいえであることを確認します。URLパス接頭辞は:sinceに変更し、変更の適用を行います。

データ・プロファイルの修正は後ほど実施することにします。まずは作成したRESTデータ・ソースをソースとする、対話モード・レポートを作成してみます。

対話モード・レポートのページを作成する

ページ作成ウィザードを起動します。ページの作成をクリックします。

ページ・タイプとしてコンポーネントを選び、レポートをクリックします。

対話モード・レポートをクリックします。

ページ名を医療提供状況詳細とし、次へ進みます。

ナビゲーションのプリファレンスとして新規ナビゲーション・メニュー・エントリの作成を選択し、次へ進みます。

データ・ソースとしてRESTデータ・ソースを選択し、RESTデータ・ソースとしてcovid19DailySurveyを選択します。作成をクリックします。

ページが作成されます。ページを実行し、レポートを表示させてみます。

サインインの画面が表示されます。ワークスペースにサインインしたユーザー名をパスワードにて、アプリケーションにサインインします。

レポートが表示されるまでに、数十秒程度の時間がかかるはずです。

次のページを表示させます。レポートの下に表示されているページめくりのボタンをクリックします。

次ページを開くのにも数十秒程度の時間がかかります。実際に発行されているRESTサービスの呼び出しを確認してみます。SQLワークショップのSQLコマンドを開き、以下のSELECT文を実行します。

select URL, HTTP_METHOD,STATUS_CODE, RESP_CONTENT_LENGTH, ELAPSED_SEC, REQUEST_DATE

from apex_webservice_log order by request_date desc

Oracle APEXのAPIを経由したRESTサービスの呼び出し履歴は、ビューAPEX_WEBSERVICE_LOGから確認できます。

直近のログを見ると、単にページ送りであっても毎回RESTサービスが呼び出されていることが確認できます。これら呼び出しでは、パラメータは与えられていないため、8.3MBにおよぶデータを毎回読み出しています。

この状況は実用的ではなく、また、公共の資源の無駄遣いにもなります。

２つの対策を取ることができます。RESTサービスの呼び出し時にパラメータの指定を必須とすること、または、取得したデータをローカルの表に保存することです。

RESTデータ・ソースの呼び出しにパラメータを与える

対話モード・レポートのページを、ページ・デザイナにて開きます。

Content Bodyの上でコンテキスト・メニューを開き、リージョンの作成を実行します。対話モード・レポートのリージョンの上に配置します。名前を検索条件とします。タイプは静的コンテンツです。

作成したリージョンに、RESTデータ・ソースへのパラメータとなるページ・アイテムを作成します。

ページ・アイテムの作成を実行します。識別の名前をP2_SINCE、タイプを日付ピッカーとします。ラベルはこの日以降とし、外観の書式マスクにYYYYMMDDを指定します。

ページ・アイテムP2_PREF_NAMEを作成します。タイプはテキスト・フィールドです。ラベルを都道府県とし、レイアウトの新規行の開始をOFFにします。P2_SINCEの右隣にページ・アイテムが配置されます。

ページ・アイテムP2_LOCAL_GOV_CODEを作成します。タイプはテキスト・フィールドです。ラベルを全国地方公共団体コードとし、レイアウトの新規行の開始をOFFにします。

ページ・アイテムに指定した値でRESTデータ・サービスの呼び出しが行われるよう、送信ボタンを作成します。

リージョン検索条件にてコンテキスト・メニューを表示させ、ボタンの作成を実行します。識別のボタン名をB_SUBMITとし、ラベルを送信とします。

都道府県名もしくは全国地方公共団体コードのどちらかの指定がないと、対話モード・レポートを表示しない(つまり、RESTデータ・ソースの呼び出しを行わない - 全件でのデータ取得を抑制する)よう、サーバー側の条件を設定します。

リージョンの医療提供状況詳細を選択し、サーバー側の条件のタイプを式、言語を SQL、SQL式として以下を指定します。

:P2_PREF_NAME is not null or :P2_LOCAL_GOV_CODE is not null

ページ・アイテムの入力値がRESTサービスの呼び出しに使われるよう、リージョンのパラメータをそれぞれ設定します。

パラメータlocalGovCodeを選択し、値のタイプをアイテム、アイテムとしてP2_LOCAL_GOV_NAMEを選択します。

パラメータprefNameの値のタイプもアイテム、アイテムにはP2_PREF_NAMEを指定します。

パラメータsinceの値のタイプもアイテム、アイテムにはP2_SINCEを指定します。

以上で対話モード・レポートでのRESTデータ・ソースの呼び出しで、パラメータの指定を必須とする設定が完了しました。ページを実行し、結果を確認してみます。

検索条件がないと、対話モード・レポート自体が表示されません。

全国地方公共団体コードとして012106を指定し、送信をクリックします。北海道岩見沢市の医療機関のみが一覧されます。

地域を限定してデータを取得するようになりましたが、レポートが表示されるたびにRESTサービスの呼び出しが行われている状況は変わりません。

次にローカル表を使うことにより、RESTサービスの呼び出しを抑制します。

ローカル表と同期させる

RESTデータ・ソースcovid19DailySurveyを開き、データ・プロファイルの編集を行います。

全部で14列あるデータの定義を変更します。それぞれの列の左端にある鉛筆アイコンをクリックします。

名前がANSTYPEの列は、以下のように変更します。同期表はあらかじめ作成することもできますが、今回はデータ・プロファイルの設定をもとに自動生成させます。

名前はANS_TYPEに変更します。これが同期表の列名になります。主キーはOFF、フィルタ可能はOFFにします。表示可能はONです。データ型はVarchar2、最大長は16に変更します。

以上の変更を行なって、変更の適用をクリックします。

ANSTYPEも含めて、以下の指定にて列のデータ・プロファイルを変更します。主キーとなるのはFACILITY_IDとFACILITY_TYPE、フィルタ可能はすべてOFFです。

データ・プロファイルの設定後、同期化の管理を呼び出します。

同期先として新規表を選び、表名としてC19_MEDICAL_FACILITY_STATUSESを入力します。保存をクリックします。

REST同期化のページが開きます。同期化表が存在しません。とメッセージが表示されています。SQLを表示して内容を確認し、表の作成を実行します。

表C19_MEDICAL_FACILITY_STATUSESを生成するDDLとして、以下が実行されます。

create table "C19_MEDICAL_FACILITY_STATUSES"(

"ANS_TYPE" VARCHAR2(16)

,"ZIP_CODE" VARCHAR2(12)

,"CITY_NAME" VARCHAR2(40)

,"LATITUDE" NUMBER

,"PREF_NAME" VARCHAR2(20)

,"LONGITUDE" NUMBER

,"FACILITY_ID" VARCHAR2(16)

,"SUBMIT_DATE" DATE

,"FACILITY_TEL" VARCHAR2(16)

,"FACILITY_ADDR" VARCHAR2(255)

,"FACILITY_CODE" VARCHAR2(16)

,"FACILITY_NAME" VARCHAR2(255)

,"FACILITY_TYPE" VARCHAR2(16)

,"LOCAL_GOV_CODE" VARCHAR2(6)

,"APEX$SYNC_STEP_STATIC_ID" VARCHAR2(255)

,"APEX$ROW_SYNC_TIMESTAMP" TIMESTAMP WITH TIME ZONE

,constraint "C19_MEDICAL_FACILITY_STATUSES_PK" primary key ("FACILITY_ID","FACILITY_TYPE"))

/

データ・プロファイルとしてFACILITY_IDとFACILITY_TYPEに主キーを設定しています。そのため、同期タイプにマージを選択することができます。同期タイプを変更して、保存して実行をクリックします。

ステップを設定していないため、パラメータsince、prefName、localGovCodeのすべてが無指定でRESTサービスの呼び出しが行われます。結果としてすべてのデータが取得され、表C19_MEDICAL_FACILITY_STATUSESへ保存されまます。

表示上の問題ですが、上記のページは同期化の処理が完了していても、ジョブのステータスは実行中のままで更新されません。この表示は気にせず、作業を続けます。

通常は同期スケジュールを設定し、ローカル表との同期化処理を定期的に実行させるようにします。また、ステップの追加を行うとsince、prefNameおよびlocalGovCodeといったパラメータを指定することができます。

RESTデータ・ソースの一覧より、同期化済の表示を確認します。はいであれば同期化は完了しています。表C19_MEDICAL_FACILITY_STATUSESにRESTサービスを呼び出して取得したデータが保存されています。

表C19_MEDICAL_FACILITY_STATUSESの内容を最新に保つには、定期的にRESTデータ・ソースの同期化を実行する必要があります。定期的な同期化については、本記事では実装しません。

対話モード・レポートで同期化表を使用する

対話モード・レポートのリージョンを選択し、ソースの同期化表の使用をONに変更します。変更後、保存をクリックします。

同期化表では列の名前が変更されているため(例えばANSTYPEはANS_TYPEへ変更)、列の同期化を実行します。

列の同期化を実行するとなぜか同期表の使用がOFFに戻ります。これは再度ONへ変更します。変更したら保存をクリックします。

ローカル表にたいして検索条件のページ・アイテムが有効になるよう、ローカル後処理のタイプをWHERE/ORDER BY句に変更し、以下の条件を設定します。

(:P2_SINCE is null or submit_date >= :P2_SINCE)

and

(:P2_PREF_NAME is null or pref_name = :P2_PREF_NAME)

and

(:P2_LOCAL_GOV_CODE is null or local_gov_code = :P2_LOCAL_GOV_CODE)

ローカル表であれば、対話モード・レポートの機能でフィルタできます。そのため、このような検索条件自体、不要になっています。検索条件のリージョンを非表示にしても良いでしょう。

ページを実行してみます。レポートの表示やページの移動など、ストレスなく実行できます。

地図上に医療機関を表示する

オープンデータには医療機関の位置を示す緯度経度が含まれています。この位置情報を使って、医療機関をマップ上に表示します。

ページ作成ウィザードを起動します。ページの作成を実行します

ページ・タイプとしてコンポーネントを選択し、マップをクリックします。

ページ名を医療機関とし、次へ進みます。

ナビゲーションのプリファレンスとして、新規ナビゲーション・メニュー・エントリの作成を選択し、次へ進みます。

データ・ソースとしてローカル・データベースを選択し、ソース・タイプは表、表/ビューの名前として同期化表であるC19_MEDICAL_FACILITY_STATUSESを直接指定します。RESTデータ・ソースのcovid19DailySurveyを指定することも可能ですが、公共のサイトに不必要な負荷をかけることになります。できるだけ同期化表を使いましょう。（対話モード・レポートと同様に、タイプとしてRESTデータ・ソースを選択し、同期化表の使用をONにする方法もあります。)

レイヤーを定義します。マップ上にポイントとして医療機関を表示させます。ジオメトリ列タイプとして、２つの数値列を選択します。経度にはLONGITUDE (Number)、緯度にはLATITUDE (Number)、ツールチップ列にはFACILITY_NAME (Varchar2)を指定します。ファセット検索ページの作成はONにし、作成をクリックします。

ページが作成されたら、実行して確認します。マップ上に医療機関の位置が表示されていることが確認できます。これから表示を調整していきます。

最初にファセットを作成します。左ペインのレンダリング・ツリーにあるファセット上でコンテキスト・メニューを表示させ、ファセットの作成を実行します。

識別の名前をP4_FACILITY_TYPE、タイプをチェック・ボックス・グループとします。ラベルは医療機関のタイプとします。LOVのタイプとして個別値を選択します。

ページを実行します。医療機関のタイプとして入院、外来、救急を選択できるようになっています。

医療提供状況別にポイントを色分けして表示します。

レイヤーの医療機関を選択します。識別の名前を通常に変更します。行割当ての列にANS_TYPEを選択し、このレイヤーの値は通常にします。外観の塗りつぶしの色は緑(#00ff00)を指定します。 

上で作成したレイヤーを重複させます。名前は制限、行割当てのこのレイヤーの値も制限、外観の塗りつぶしの色は黄色(#ffff00)を指定します。

同様にレイヤーを重複させます。名前は停止、行割当てのこのレイヤーの値も停止、外観の塗りつぶしの色は赤(#ff0000)を指定します。

レイヤーを重複させます。名前は設置なし、行割当てのこのレイヤーの値も設置なし、外観の塗りつぶしの色は明るい灰色(#c0c0c0)を指定します。

最後です。レイヤーを重複させ、名前を未回答、行割当てのこのレイヤーの値も未回答、外観の塗りつぶしの色は暗い灰色(#808080)を指定します。

 以上で医療提供状況を色分けして表示する設定ができました。ページを実行して確認してみます。

ポイントのツールチップに医療機関の名前でけでなく、住所などのその他の情報も表示されるように変更します。レイヤーのツールチップの拡張フォーマットをONに変更し、HTML式として以下を指定します。

<b>&FACILITY_NAME.</b>

<br>郵便番号: &ZIP_CODE.

<br>住所: &FACILITY_ADDR.

<table><tr><th>医療区分</th><th>医療提供状況</th></tr>

<tr><td>&FACILITY_TYPE.</td><td>&ANS_TYPE.</td></tr>

</table>

５つのレイヤーそれぞれに同じ設定を行います。政府CIOポータルでは、ひとつの医療機関のポイントがひとつであるため、ツールチップに医療区分(外来、入院、救急)がまとまって表示されています。今回行なっている実装は医療区分でポイントは別になっているため（重なって表示される）、ツールチップの表示は若干異なります。

それぞれの医療機関のタイプ別に、地図上に表示するマーカーを変更します。マップのソースのタイプをSQL問合せに変更し、SQL問合せとして以下のSELECT文を指定します。

select ANS_TYPE,

ZIP_CODE,

CITY_NAME,

LATITUDE,

PREF_NAME,

LONGITUDE,

FACILITY_ID,

SUBMIT_DATE,

FACILITY_TEL,

FACILITY_ADDR,

FACILITY_CODE,

FACILITY_NAME,

FACILITY_TYPE,

LOCAL_GOV_CODE,

case facility_type

when '入院' then

'fa fa-hospital-o'

when '救急' then

'fa fa-ambulance'

when '外来' then

'fa fa-medication'

else

'fa fa-map-marker'

end facility_icon,

APEX$SYNC_STEP_STATIC_ID,

APEX$ROW_SYNC_TIMESTAMP

from C19_MEDICAL_FACILITY_STATUSES

列FACILITY_ICONを追加しています。入院はfa-hospital-o、救急はfa-ambulance、外来はfa-medication、それ以外にはfa-map-markerというアイコンでポイントを表示させます。

ソースを変更すると列のマッピングが無効になるため、再設定が必要です。作成済みの５つのレイヤーをすべて選択します。すべてのレイヤーの列のマッピングのジオメトリ列のデータ型に経度/緯度、経度列にLONGITUDE、緯度列にLATITUDEを指定します。ポイント・オブジェクトのスタイルにアイコンを選択し、アイコン・ソースとしてアイコン・クラス列、アイコン列にFACILITY_ICONを選択します。

最後にマップ・リージョンのAttributesの設定変更を行います。コントロールのオプションの、ブラウザの位置の取得、円形ツール、距離ツールにチェックを入れます。また、初期位置およびズームのブラウザから位置を取得をONに変更します。

位置情報を取得するには、ブラウザが動作している機器上で許可する必要があります。

以上の設定を行なったのち、ページを表示してみます。

一番最初のアクセスでは、現在位置を中心としてマップが表示されます。

オプションで許可をした、現在位置へ移動する機能、距離を測る機能、地図上で領域を円で選択するツールなどが利用可能になっています。

ここまでの実装では、地図上に表示されるかされないかに関わらず、マップ・リージョンまたはレイヤーのソースとして選択された(SELECT文の結果として返される)ポイントはすべて、マップ・リージョンのコンポーネントに取り込まれます。そのため、一部の領域しか表示しない場合がパフォーマンス面で不利になります。

マップ・リージョンのサンプルには色々な動的アクションが含まれています。それらを活用することにより、マップ・リージョンをもっと使いやすくできます。サンプル・アプリケーションのインストールと確認をお勧めします。

以上で今回の記事は完了です。

RESTデータ・ソースの同期化の定期実行、および、マップ・リージョンの活用については別の機会にまとめてみようと思います。

今回作成したアプリケーションのエクスポートを以下に置きました。

https://github.com/ujnak/apexapps/blob/master/exports/map-g-mis-facility-statuses.sql

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

完

行政区域データのまとめ - 追記

 こちらの記事で行政区域データを、行政区域コードや都道府県でまとめる作業を行いました。以下に、追加で確認したことを書き留めておきます。

SDO_UTIL.RECTIFY_GEOMETRYの結果

GeoJSONのデータを表JAR_JAPAN_REGION_ADMINSの列GEOMETRYに取り込んだ後に、SDO_UTIL.RECTIFY_GEOMETRYを実行しています。修正したジオメトリは列GEOM_Rへアップデートしています。

列GEOM_Rを確認すると、ジオメトリのSDO_GTYPEに2003(ポリゴン)だけではなく、2007(マルチポリゴン)も含まれています。確認に使用したコードは以下です。

set serveroutput on
declare
    l_geom sdo_geometry;
    l_elem_cnt number;
begin
    for c in (
        select * from jar_japan_region_admins
        where geom_r is not null order by id
    )
    loop
        l_geom := c.geom_r;
        l_elem_cnt := sdo_util.getnumelem(l_geom);
        if l_geom.sdo_gtype <> 2003 then
            dbms_output.put_line('id ' || c.id || ' is ' || l_geom.sdo_gtype || ', contains '
                || l_elem_cnt || ' elements.');
        end if;
    end loop;
end;
/

実行結果は以下のようになりました。

id 922 is 2007, contains 2 elements.

id 67318 is 2007, contains 2 elements.

id 78966 is 2007, contains 2 elements.

id 83266 is 2007, contains 2 elements.

id 94185 is 2007, contains 2 elements.

id 99633 is 2007, contains 2 elements.

id 100483 is 2007, contains 2 elements.

id 100866 is 2007, contains 2 elements.

id 101181 is 2007, contains 2 elements.

このままSDO_UTIL.SIMPLIFYを実行すると、含まれているポリゴンが非常に小さい場合、そのポリゴンが点または線のジオメトリに変換されます。結果としてOracle APEXのマップ・リージョンで表示されないジオメトリになります。

マルチポリゴンのジオメトリを分解し、別のIDを割り振って保存します。実行には以下のコードを使いました。

set serveroutput on
declare
    l_geom sdo_geometry;
    l_elem_cnt number;
    l_geom_in sdo_geometry;
    l_max number;
begin
    for c in (
        select * from jar_japan_region_admins
        where geom_r is not null order by id
    )
    loop
        -- rectifyされたジオメトリが処理の対象。
        l_geom := c.geom_r;
        --
        if l_geom.sdo_gtype in (2004,2007) then
            -- コレクション(2004)とマルチポリゴンが(2007)が修正の対象。
            dbms_output.put_line('id ' || c.id 
                || ' was converted into collection(2004) or multipolygon(2007). try to fix.');
            l_elem_cnt := sdo_util.getnumelem(l_geom);
            select max(id) into l_max from jar_japan_region_admins;
            for i in 1..l_elem_cnt
            loop
                l_geom_in := sdo_util.extract(l_geom,i);
                if l_geom_in.sdo_gtype = 2003 then
                    -- 含まれているポリゴンを新たなIDで追加する。
                    dbms_output.put_line('store inner polygon with new id ' || (l_max+i));
                    insert into jar_japan_region_admins(
                        id, prefecture_name, branch_in_hokkaido, major_city,
                        city_name, admin_code, geom_r, vertices)
                    values(
                        l_max+i, c.prefecture_name, c.branch_in_hokkaido, c.major_city,
                        c.city_name, c.admin_code, l_geom_in, sdo_util.getnumvertices(l_geom_in));
                else
                    -- それ以外は削除される。
                    dbms_output.put_line('ignore inner geometry. ' || l_geom_in.sdo_gtype);
                end if;
            end loop;
            -- 元々のジオメトリは重複したデータになるためNULLにする。
            dbms_output.put_line('nullify original rectified geometry.');
            update jar_japan_region_admins set geom_r = null, vertices = null  where id = c.id;
        end if;
    end loop;
end;
/
commit;

実行結果は以下のようになりました。

id 922 was converted into collection(2004) or multipolygon(2007). try to fix.

store inner polygon with new id 121159

store inner polygon with new id 121160

nullify original rectified geometry.

id 67318 was converted into collection(2004) or multipolygon(2007). try to fix.

store inner polygon with new id 121161

store inner polygon with new id 121162

nullify original rectified geometry.

id 78966 was converted into collection(2004) or multipolygon(2007). try to fix.

store inner polygon with new id 121163

store inner polygon with new id 121164

nullify original rectified geometry.

id 83266 was converted into collection(2004) or multipolygon(2007). try to fix.

store inner polygon with new id 121165

store inner polygon with new id 121166

nullify original rectified geometry.

id 94185 was converted into collection(2004) or multipolygon(2007). try to fix.

store inner polygon with new id 121167

store inner polygon with new id 121168

nullify original rectified geometry.

id 99633 was converted into collection(2004) or multipolygon(2007). try to fix.

store inner polygon with new id 121169

store inner polygon with new id 121170

nullify original rectified geometry.

id 100483 was converted into collection(2004) or multipolygon(2007). try to fix.

store inner polygon with new id 121171

store inner polygon with new id 121172

nullify original rectified geometry.

id 100866 was converted into collection(2004) or multipolygon(2007). try to fix.

store inner polygon with new id 121173

store inner polygon with new id 121174

nullify original rectified geometry.

id 101181 was converted into collection(2004) or multipolygon(2007). try to fix.

store inner polygon with new id 121175

store inner polygon with new id 121176

nullify original rectified geometry.

含まれているジオメトリはすべて、表JAR_JAPAN_ADMIN_REGIONSに追記できました。

SDO_UTIL.SIMPLIFYの結果

SDO_UTIL.RECTIFY_GEOMETRYの結果を対象として、SDO_UTIL.SIMPLIFYを実行します。閾値が10m、許容差として0.05(5cm)を指定しています。

update jar_japan_region_admins set geom_s = sdo_util.simplify(geom_r,10,0.05);
update jar_japan_region_admins set vert_s = sdo_util.getnumvertices(geom_s);
commit;

列GEOM_Rに含まれているポリゴンが小さいと、結果として点(SDO_GTYPEが2001)、または線(SDO_GTYPEが2002)になります。この時点でポリゴンではなくなるため、これ以降の処理の対象にする必要がありません。また、SDO_UTIL.SIMPLIFYでもコレクション(2004)やマルチポリゴン(2007)となる場合があります。

以下のコードでジオメトリの確認を行います。

set serveroutput on
declare
    l_geom sdo_geometry;
    l_elem_cnt number;
    l_point_cnt integer := 0;
    l_line_cnt  integer := 0;
begin
    for c in (
        select * from jar_japan_region_admins
        where geom_s is not null
        order by id
    )
    loop
        l_geom := c.geom_s;
        l_elem_cnt := sdo_util.getnumelem(l_geom);
        if l_geom.sdo_gtype = 2001 then
            l_point_cnt := l_point_cnt + 1;
        elsif l_geom.sdo_gtype = 2002 then
            l_line_cnt := l_line_cnt + 1;
        elsif l_geom.sdo_gtype = 2003 then
            -- polygon is valid
            continue; 
        else
            dbms_output.put_line('id ' || c.id || ' is ' || l_geom.sdo_gtype || '. contains '
                || l_elem_cnt || ' elements.');
        end if;
    end loop;
    dbms_output.put_line('number of point exists. ' || l_point_cnt);
    dbms_output.put_line('number of line exists. '  || l_line_cnt);
end;
/

実行結果は以下のようになりました。

id 658 is 2007. contains 2 elements.

id 789 is 2007. contains 2 elements.

id 1286 is 2007. contains 4 elements.

[中略]

id 117307 is 2007. contains 3 elements.

id 119059 is 2004. contains 2 elements.

id 121174 is 2007. contains 2 elements.

number of point exists. 50702

number of line exists. 40409

多くのデータがコレクションまたはマルチポリゴンに変換されています。また、点や線になったポリゴンも多数あります。

列GEOM_Sで点や線になっているジオメトリをNULLに置き換えます。また、コレクションやマルチポリゴンは分解して、新たなIDで表JAR_JAPAN_REGION_ADMINSに保存します。実行のために以下のコードを使用しました。

set serveroutput on
declare
    l_geom sdo_geometry;
    l_elem_cnt number;
    l_geom_in sdo_geometry;
    l_max number;
begin
    for c in (
        select * from jar_japan_region_admins
        where geom_s is not null order by id
    )
    loop
        -- simplifyされたジオメトリが処理の対象。
        l_geom := c.geom_s;
        --
        if l_geom.sdo_gtype in (2001,2002) then
            /* 大量にあるので表示しない。
            dbms_output.put_line('id ' || c.id 
                || ' is converted into point(2001) or line(2002). nullfiy.');
            */
            update jar_japan_region_admins set geom_s = null, vert_s = null where id = c.id;
        elsif l_geom.sdo_gtype in (2004,2007) then
            -- コレクション(2004)とマルチポリゴンが(2007)が修正の対象。
            dbms_output.put_line('id ' || c.id 
                || ' was converted into collection(2004) or multipolygon(2007). try to fix.');
            l_elem_cnt := sdo_util.getnumelem(l_geom);
            select max(id) into l_max from jar_japan_region_admins;
            for i in 1..l_elem_cnt
            loop
                l_geom_in := sdo_util.extract(l_geom,i);
                if l_geom_in.sdo_gtype in (2001,2002) then
                    -- 点と線は削除する。
                    dbms_output.put_line('ignore inner geometry. ' || l_geom_in.sdo_gtype);
                else
                    -- それ以外は追加する。
                    dbms_output.put_line('store inner polygon with new id ' || (l_max+i));
                    insert into jar_japan_region_admins(
                        id, prefecture_name, branch_in_hokkaido, major_city,
                        city_name, admin_code, geom_s, vert_s)
                    values(
                        l_max+i, c.prefecture_name, c.branch_in_hokkaido, c.major_city,
                        c.city_name, c.admin_code, l_geom_in, sdo_util.getnumvertices(l_geom_in));
                end if;
            end loop;
            -- 元々のジオメトリはNULLにする。
            dbms_output.put_line('nullify original simplified geometry.');
            update jar_japan_region_admins set geom_s = null, vert_s = null where id = c.id;
        end if;
    end loop;
end;
/
commit;

再度、列GEOM_Sのチェックスクリプトを実行すると以下の結果になりました。

number of point exists. 0

number of line exists. 0

列GEOM_Sには、１行につき１つの単純なポリゴンが含まれています。

行政区域コード単位での集計

列GEOM_Sに含まれていた集計対象にならないジオメトリは、すでにNULLになっています。そのため面積を計算して、集計対象を制限する必要はありません。列GEOM_SがNULLの行を集計対象から外します。

MERGE文は以下になります。

merge into jar_japan_region_cities c
using
(
    select
        prefecture_name, branch_in_hokkaido, major_city, city_name
        ,admin_code, geom_s geometry, vert_s vertices
    from jar_japan_region_admins
    where
       geom_s is not null
       and
       (prefecture_name, admin_code)
       in
       (
            select prefecture_name, admin_code
            from jar_japan_region_admins
            group by prefecture_name, admin_code
            having count(*) = 1
        )
) a
on (c.prefecture_name = a.prefecture_name and c.admin_code = a.admin_code)
when matched then
    update set
        branch_in_hokkaido = a.branch_in_hokkaido
        ,major_city = a.major_city
        ,city_name = a.city_name
        ,geometry = a.geometry
        ,row_count = 1
        ,vertices = a.vertices
when not matched then
    insert (prefecture_name, branch_in_hokkaido, major_city, city_name
        ,admin_code, geometry, row_count, vertices)
    values (a.prefecture_name, a.branch_in_hokkaido, a.major_city, a.city_name
        ,a.admin_code, a.geometry, 1, a.vertices)
;

２つ以上のデータがある行政区域コードをまとめるために使用するコードは以下になります。

declare
    l_geom   sdo_geometry;
    l_geom_a sdo_geometry;
    l_row_count number;
    l_vertices number;
    l_message varchar2(4000);
begin
    delete from jar_validate_cities;
    commit;
    for c in (
        -- まとめる数が少ない行政区分からひとつひとつまとめていく。
        select prefecture_name, branch_in_hokkaido, major_city, city_name, admin_code
        from jar_japan_region_admins
        where 
        geom_s is not null
        and
        (prefecture_name, admin_code) not in (
              -- すでにまとめ済みの行政区分は除外する
              select prefecture_name, admin_code from jar_japan_region_cities
        )
        group by prefecture_name, branch_in_hokkaido, major_city, city_name, admin_code
        order by count(*) asc
    )
    loop
        l_geom := null;
        for r in
        (
             select prefecture_name, admin_code, geom_s geometry, vert_s vertices
             from jar_japan_region_admins
             where prefecture_name = c.prefecture_name
               and admin_code = c.admin_code
               and geom_s is not null
        )
        loop
            if l_geom is null then
                -- 最初の１行は初期化に使用する。
                l_geom := r.geometry;
                l_row_count := 1;
                l_vertices := r.vertices;
                continue;
            end if;
            -- ポリゴン、行数、頂点の数を集計する
            if sdo_geom.relate(l_geom, 'disjoint', r.geometry, 0.05) = 'TRUE' then
                l_geom_a := sdo_util.append(l_geom, r.geometry);
            else
                l_geom_a := sdo_geom.sdo_union(l_geom, r.geometry, 0.05);
            end if;
            l_geom := l_geom_a;
            l_row_count := l_row_count + 1;
            l_vertices := l_vertices + r.vertices; 
         end loop;
         -- 結果を保存する。途中で停止できるよう毎回commitする。
         l_message := sdo_geom.validate_geometry_with_context(l_geom, 0.05);
         insert into jar_validate_cities(prefecture_name, branch_in_hokkaido,
             major_city, city_name, admin_code, message)
         values(
             c.prefecture_name, c.branch_in_hokkaido,
             c.major_city, c.city_name, c.admin_code, l_message);
         insert into jar_japan_region_cities
            (prefecture_name, branch_in_hokkaido, major_city, city_name
            ,admin_code, geometry, row_count, vertices)
         values(
            c.prefecture_name, c.branch_in_hokkaido, c.major_city, c.city_name,
            c.admin_code, l_geom, l_row_count, l_vertices);
         commit;
     end loop;
end;
/

集計の対象外になったデータは、SDO_UTIL.SIMPLIFYを使って閾値10m、許容差0.05(5cm)で簡略化した結果がポリゴンにならなかったもの、ということになります。

行政区域の隙間

以下の倍率程度であれば、行政区域が接する部分に隙間は見えません。

拡大すると、行政区域の接する部分に隙間があることがわかります。

単純にSDO_UTIL.SIMPLIFYを使用して頂点を間引く方法では、このような隙間が発生することは避けられないです。

ジオメトリのエラーを見つける

ジオメトリに限りませんが、エラーが発生していると開発者ツール・バーに通知されます。

エラーのアイコンをクリックすると、ブラウザのJavaScriptコンソールを開いて、エラーを確認してください。とあります。

開いてみます。Consoleタブを見ると、Server debug log message written: View Identifier = 9001, Level = ERROR. とあり、リンクが続いています。リンクをクリックします。

エラー・メッセージを確認することができます。今回のエラーはSDO_GEOMETRY column for row id with seq# 1 is of the wrong geometry type. ということで、ジオメトリのタイプが適切でないためにエラーが発生していることがわかります。

ブラウザのJavaScirptコンソールを開いたり、ひと手間ありますが、デバッグの役に立つ機能です。

簡略化されたデータの比較

宮城県、東松島市の波島を見てみます。

OpenStreetMapのバックグランドは以下です。

Oracle World Mapのバックグランドは以下です。島がないです。

表示している場所に間違いはありません。

SDO_UTIL.RECTIFY_GEOMETRYを、許容差0.05で実行した結果です。

SDO_UTIL.SIMPLIFYを、閾値1m、許容差0.05で実行した結果です。

もっと拡大して確認してみます。簡略化前です。

簡略化後です。それほど違いが見られません。

頂点の数は1/2程度になっています。それほど精度は落ちていません。

SQL> select sum(vertices), sum(vert_s) from jar_japan_region_admins;

SUM(VERTICES) SUM(VERT_S)

------------- -----------

     14748036   6734337

SDO_UTIL.SIMPLIFYを、閾値10m、許容差0.05で実行した結果です。小さな島が無くなり、簡略化されていることが見て分かります。

頂点の数は1/10程度になっています。

SQL> select sum(vertices), sum(vert_s) from jar_japan_region_admins;

SUM(VERTICES) SUM(VERT_S)

------------- -----------

     14748036   1647760

完