以下の記事にて、政府CIOポータルから取得できる情報をもとにアプリケーションを作成しました。
全国医療機関の医療体制の状況をオープンデータをもとに地図上に表示する
オープンデータに限らず、CSV、Excel、JSONといった形式で提供されているデータは概ね正規化されていません。これらのデータは、それ自体で完結している方が扱いやすいためでしょう。しかし、保存する側はリレーショナル・データベースなので、データは正規化した方が情報としての活用範囲が広がります。
そのため、外部からのデータ・ロードは、一旦インターフェース表にロードし、その後、正規化された実表に転記するという作業が必要になることが多いように思います。
この記事では、正規化された表に医療体制の状況のデータをロードする実装をしてみます。
RESTデータ・ソースの同期先を変更する
RESTデータ・ソースcovid19DailySurveyのREST同期化の設定を開き、設定のクリアを実行します。
同期化の設定が削除されます。同期先を新規表とし、表名としてC19_MEDICAL_FACILITY_STATUSES_INTを指定し、保存をクリックします。この表がインターフェース表になります。
表の作成をクリックし、同期化表（インターフェース表）を作成します。
詳細の同期タイプとして置換を選択します。インターフェース表のデータは、実表に転記された後は不要です。詳細設定の置換タイプとしてDELETEもしくはTRUNCATEを選択します。今回はDELETEを選択しました。
一般にインターフェース表は、データの投入と削除が繰り返されます。そのため索引はつけません。その結果として、ほとんどの操作は全表スキャンになります。他のデータベースはわかりませんが、Oracle Databaseでの全表スキャンは保存されている行数ではなく、表が占有しているブロック数で処理時間が決まります。置換タイプがDELETEの場合は、データを削除してもブロック数は変わりません。TRUNCATEの場合はブロックが開放されます。
一度大量のデータをロードすると表は多数のブロックを取得します。その後の投入データが少ないと転記の処理を行う際に全ブロックが読み込み対象となるため、DELETEで削除していると性能面で不利になります。ほとんどの場合で投入するデータは少量ですが、たまに大量のデータが投入される、といった場合はTRUNCATEの方が性能面で有利になるでしょう。
データの削除については、トランザクションを維持しないためTRUNCATEの方が高速です。
データのロードについては、DELELEでデータを削除した場合はすでにブロックは取得済みであるため、より短時間で処理が終了します。TRUNCATEは新たにブロックを取得する必要があります。ブロックの取得は負荷の高い処理です。
ロードするデータの特性に応じて、DELETEかTRUNCATEのどちらを選択することになります。
RESTデータ・ソースとしては定期実行はしません。以上の設定を行った後、保存して実行をクリックします。
正規化した表を準備する
医療機関の状況(ANS_TYPE)、医療機関のタイプ(FACILITY_TYPE)はルックアップ表を定義します。医療機関はFACILITY_IDを主キーとした別表とし、また、自治体コードも別表とします。
医療機関の状況のルックアップ表C19_ANS_TYPESを作成します。以下のスクリプトをSQLワークショップのSQLスクリプトより実行します。
drop table c19_ans_types purge;
create table c19_ans_types(
ans_type_cd varchar2(1),
ans_type varchar2(16) not null,
constraint c19_ans_types_pk primary key (ans_type_cd)
);
insert into c19_ans_types(ans_type_cd, ans_type) values('N','通常');
insert into c19_ans_types(ans_type_cd, ans_type) values('L','制限');
insert into c19_ans_types(ans_type_cd, ans_type) values('H','停止');
insert into c19_ans_types(ans_type_cd, ans_type) values('V','設置なし');
insert into c19_ans_types(ans_type_cd, ans_type) values('X','未回答');
commit;
医療機関のタイプのルックアップ表C19_FACILITY_TYPESを作成します。
drop table c19_facility_types purge;
create table c19_facility_types(
facility_type_cd varchar2(1),
facility_type varchar2(16) not null,
constraint c19_facility_types_pk primary key(facility_type_cd)
);
insert into c19_facility_types(facility_type_cd, facility_type) values('M','外来');
insert into c19_facility_types(facility_type_cd, facility_type) values('E','救急');
insert into c19_facility_types(facility_type_cd, facility_type) values('H','入院');
commit;
自治体コードのマスター表C19_LOCAL_GOV_CODESを作成します。本来であれば総務省よりデータを入手するところですが、手順を簡略にするためインターフェース表に取り込まれたデータを使います。
drop table c19_local_gov_codes purge;
create table c19_local_gov_codes(
local_gov_code varchar2(6),
pref_name varchar2(20) not null,
city_name varchar2(40) not null,
constraint c19_local_gov_codes_pk primary key(local_gov_code)
);
insert into c19_local_gov_codes(local_gov_code, pref_name, city_name)
select distinct local_gov_code, pref_name, city_name from c19_medical_facility_statuses_int;
commit;
医療機関のマスター表C19_MEDICAL_FACILITIESを作成します。こちらもインターフェース表に取り込まれたデータを使います。緯度経度は数値列LATITUDE, LONGITUDEからSDO_GEOMETRY型へ変換しています。
drop table c19_medical_facilities purge;
create table c19_medical_facilities(
facility_id varchar2(16),
facility_code varchar2(16),
zip_code varchar2(8),
facility_name varchar2(255) not null,
facility_addr varchar2(255) not null,
facility_tel varchar2(16),
local_gov_code varchar2(6) not null,
longitude number,
latitude number,
geometry sdo_geometry,
constraint c19_medical_facilities_pk primary key(facility_id),
constraint c19_medical_facilities_fk foreign key(local_gov_code)
references c19_local_gov_codes
);
insert into c19_medical_facilities(
facility_id, facility_code, zip_code, facility_name, facility_addr,
facility_tel, local_gov_code, longitude, latitude)
select distinct
facility_id, facility_code, replace(zip_code,'〒'), facility_name, facility_addr,
facility_tel, local_gov_code, longitude, latitude
from c19_medical_facility_statuses_int
group by
facility_id, facility_code, zip_code, facility_name, facility_addr,
facility_tel, local_gov_code, longitude, latitude;
update c19_medical_facilities
set geometry = sdo_geometry(2001, 4326,
sdo_point_type(longitude, latitude, null),
null, null);
commit;
医療機関の状況を保存する表C19_MEDICAL_FACILITY_STATUSESを作成します。
drop table c19_medicaL_facility_statuses purge;
create table c19_medical_facility_statuses (
id number generated by default on null as identity
constraint c19_medical_facili_id_pk primary key,
ans_type varchar2(1)
constraint c19_medical_facili_ans_type_fk
references c19_ans_types on delete cascade,
submit_date date,
facility_type varchar2(1)
constraint c19_medical_fac_facility_ty_fk
references c19_facility_types on delete cascade,
facility_id varchar2(16)
constraint c19_medical_fac_facility_id_fk
references c19_medical_facilities on delete cascade
)
;
create index c19_medical_facili_i1 on c19_medical_facility_statuses (ans_type);
create index c19_medical_facili_i2 on c19_medical_facility_statuses (facility_id);
create index c19_medical_facili_i3 on c19_medical_facility_statuses (facility_type);
インターフェース表C19_MEDICAL_FACILITY_STATUSES_INTに投入済みのデータを、表C19_MEDICAL_FACILITY_STATUSESへ転記します。
insert into c19_medical_facility_statuses(ans_type, submit_date, facility_type, facility_id)
select a.ans_type_cd, i.submit_date, f.facility_type_cd, i.facility_id
from c19_medical_facility_statuses_int i
join c19_ans_types a on i.ans_type = a.ans_type
join c19_facility_types f on i.facility_type = f.facility_type;
正規化した表より、元々の形式でアクセスするためのビューC19_MEDICAL_FACILITY_STATUSES_Vを作成します。
create or replace view c19_medical_facility_statuses_v
as
select
a.ans_type
, '〒' || f.zip_code zip_code
, l.city_name
, f.latitude
, l.pref_name
, f.longitude
, f.facility_id
, s.submit_date
, f.facility_tel
, f.facility_addr
, f.facility_code
, f.facility_name
, t.facility_type
, f.local_gov_code
, f.geometry
from c19_medical_facility_statuses s
join c19_medical_facilities f on s.facility_id = f.facility_id
join c19_local_gov_codes l on f.local_gov_code = l.local_gov_code
join c19_ans_types a on s.ans_type = a.ans_type_cd
join c19_facility_types t on s.facility_type = t.facility_type_cd
;
作成したビューからインターフェース表の内容を引き算(minus)して行が選択されなければ、すべてのデータが転記されているといえます。
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
from c19_medical_facility_statuses_int
minus
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
from c19_medical_facility_statuses_v
アプリケーションを修正する
アプリケーションはRESTデータ・ソースを直接参照せず、転記済みのローカル表をデータ・ソースとします。
対話モード・レポートのページから、検索条件となるページ・アイテムを削除します。
対話モード・レポートのソースの表名としてC19_MEDICAL_FACILITY_STATUSES_Vを指定します。
マップソースのSQLを以下に変更します。同一の医療機関の複数の施設を１行にします。医療機関のタイプごとの状況はそれぞれ列ANS_TYPE_M(外来)、ANS_TYPE_E(救急)、ANS_TYPE_H(入院)とします。列ANS_TYPEは医療機関の代表となる状況を返し、その状況をレイヤーへの割り当てに使用します。
select f.zip_code, f.facility_name, f.facility_addr, f.geometry
, m.ans_type ans_type_m, e.ans_type ans_type_e, h.ans_type ans_type_h
,
case
when m.ans_type = 'H' or e.ans_type = 'H' or h.ans_type = 'H' then
'H'
when m.ans_type = 'L' or e.ans_type = 'L' or h.ans_type = 'L' then
'L'
when m.ans_type = 'N' or e.ans_type = 'N' or h.ans_type = 'N' then
'N'
when m.ans_type = 'V' or e.ans_type = 'V' or h.ans_type = 'V' then
'V'
else
'X'
end ans_type
from c19_medical_facilities f
left outer join (
select ans_type, facility_type, facility_id
from c19_medical_facility_statuses
where facility_type = 'M') m
on f.facility_id = m.facility_id
left outer join (
select ans_type, facility_type, facility_id
from c19_medical_facility_statuses
where facility_type = 'E') e
on f.facility_id = e.facility_id
left outer join (
select ans_type, facility_type, facility_id
from c19_medical_facility_statuses
where facility_type = 'H') h
on f.facility_id = h.facility_id
それぞれのレイヤーの設定は以下にように変更します。ツールチップのHTML式はtemplate directiveを使用することで状況ごとに色分けも行います。
<b>&FACILITY_NAME.</b>
<br>郵便番号: &ZIP_CODE.
<br>住所: &FACILITY_ADDR.
<br>
<table><tr><th>医療区分</th><th>医療提供状況</th></tr>
{case ANS_TYPE_M/}
{when N/}<tr class="u-success-bg"><td>外来</td><td >通常</td></tr>
{when L/}<tr class="u-warning-bg"><td>外来</td><td>制限</td></tr>
{when H/}<tr class="u-danger-bg"><td>外来</td><td>停止</td></tr>
{when V/}<tr class="u-color-44-bg"><td>外来</td><td >設置なし</td></tr>
{when X/}<tr class="u-color-45-bg"><td>外来</td><td>未回答</td></tr>
{endcase/}
{case ANS_TYPE_E/}
{when N/}<tr class="u-success-bg"><td>救急</td><td >通常</td></tr>
{when L/}<tr class="u-warning-bg"><td>救急</td><td>制限</td></tr>
{when H/}<tr class="u-danger-bg"><td>救急</td><td>停止</td></tr>
{when V/}<tr class="u-color-44-bg"><td>救急</td><td >設置なし</td></tr>
{when X/}<tr class="u-color-45-bg"><td>救急</td><td>未回答</td></tr>
{endcase/}
{case ANS_TYPE_H/}
{when N/}<tr class="u-success-bg"><td>入院</td><td >通常</td></tr>
{when L/}<tr class="u-warning-bg"><td>入院</td><td>制限</td></tr>
{when H/}<tr class="u-danger-bg"><td>入院</td><td>停止</td></tr>
{when V/}<tr class="u-color-44-bg"><td>入院</td><td >設置なし</td></tr>
{when X/}<tr class="u-color-45-bg"><td>入院</td><td>未回答</td></tr>
{endcase/}
</table>
その他、行割当てのこのレイヤーの値はN, L, H, V, Xといったルックアップで使うコードに置き換えます。列のマッピングのジオメトリ列のデータ型はSDO_GEOMETRY、ジオメトリ列はGEOMETRYに変更します。ポイント・オブジェクトのスタイルはデフォルトのSVGに戻します。
ツールチップのHTML式は上記のコードに変更します。
医療機関のタイプを１行にまとめたので、ファセットのP4_FACILITY_TYPEは不要になりました。削除しておきます。
アプリケーションの改変は以上で完了です。検索結果の行数が1/3になっているので、若干パフォーマンスが上がりました。
自動化の設定をする
RESTデータ・ソースからインターフェース表へデータの取り込みを行い、正規化された実表へ転記する一連の処理を自動化として実装します。
最初にロード対象とする自治体コードを保存する表C19_LOAD_TARGETSの作成を行います。また、対象となる自治体コードをいくつか登録します。
drop table c19_load_targets purge;
create table c19_load_targets(
local_gov_code varchar2(6) primary key
);
-- 東京都世田谷区
insert into c19_load_targets values('131121');
-- 神奈川県横浜市
insert into c19_load_targets values('141003');
-- 神奈川県川崎市
insert into c19_load_targets values('141305');
commit;
自動化を作成します。名前は医療提供状況の同期、静的IDをsyncdailysurveyとします（以前の記事の作業で作成済みであれば、設定を更新します）。
設定のタイプはオンデマンドにします。アクションの開始は問合せとします。ソースのデータ・ソースはローカル・データベース、ソース・タイプを表とし、表/ビューの名前として先ほど作成したC19_LOAD_TARGETSを選択します。
アクションはそれぞれPL/SQLコードで実装します。
最初のアクションは、RESTデータ・ソースから取得したデータをインターフェース表に保存します。アクションの名前は自治体ごとの更新としました。
declare
l_parameters apex_exec.t_parameters;
l_sync_id varchar2(200);
l_message varchar2(4000);
l_rows number;
begin
l_parameters.delete;
apex_exec.add_parameter(
p_parameters => l_parameters,
p_name => 'localGovCode',
p_value => :LOCAL_GOV_CODE );
apex_exec.add_parameter(
p_parameters => l_parameters,
p_name => 'since',
p_value => to_char(sysdate-1,'YYYYMMDD') );
l_sync_id := '同期 - 自治体コード: ' || :LOCAL_GOV_CODE;
apex_rest_source_sync.dynamic_synchronize_data(
p_module_static_id => 'covid19DailySurvey',
p_sync_static_id => l_sync_id,
p_sync_parameters => l_parameters );
-- 実行結果のログ
select count(*) into l_rows from c19_medical_facility_statuses_int;
l_message := 'Load data from REST data source, rows = ' || l_rows;
apex_automation.log_info(l_message);
end;
次に医療機関のマスター表C19_MEDICAL_FACILITIESを更新します。アクションの名前は医療機関の追加としました。長いコードに見えますが、実質は１行のMERGE文です。
merge into c19_medical_facilities f
using
(
select distinct
facility_id, facility_code, zip_code
, facility_name, facility_addr,facility_tel, local_gov_code
, longitude, latitude
from c19_medical_facility_statuses_int
group by
facility_id, facility_code, zip_code
, facility_name, facility_addr, facility_tel, local_gov_code
, longitude, latitude
) u
on (f.facility_id = u.facility_id)
when matched then
update set
facility_code = u.facility_code
, zip_code = replace(u.zip_code,'〒')
, facility_name = u.facility_name
, facility_addr = u.facility_addr
, facility_tel = u.facility_tel
, local_gov_code = u.local_gov_code
, longitude = u.longitude
, latitude = u.latitude
, geometry = sdo_geometry(2001, 4326,
sdo_point_type(u.longitude, u.latitude, null),
null, null)
when not matched then
insert (
facility_id, facility_code, zip_code
, facility_name, facility_addr, facility_tel, local_gov_code
, longitude, latitude
, geometry
)
values (
u.facility_id, u.facility_code, replace(u.zip_code,'〒')
, u.facility_name, u.facility_addr, u.facility_tel, u.local_gov_code
, u.longitude, u.latitude
, sdo_geometry(2001, 4326,
sdo_point_type(u.longitude, u.latitude, null),
null, null)
)
;
-- 実行結果のログ
apex_automation.log_info('Update/Insert Medical Facilities, rows = ' || SQL%ROWCOUNT );
最後に表C19_MEDICAL_FAICLITY_STATUSESへデータを転記します。アクションの名前は転記としました。
merge into c19_medical_facility_statuses s
using (
select
i.facility_id, i.submit_date
, t.facility_type_cd facility_type, a.ans_type_cd ans_type
from c19_medical_facility_statuses_int i
join c19_ans_types a on i.ans_type = a.ans_type
join c19_facility_types t on i.facility_type = t.facility_type
) u
on (s.facility_id = u.facility_id and s.facility_type = u.facility_type)
when matched then
update set
submit_date = u.submit_date
, ans_type = u.ans_type
when not matched then
insert (
facility_id, facility_type
, submit_date, ans_type
)
values (
u.facility_id, u.facility_type
, u.submit_date, u.ans_type
)
;
-- 実行結果のログ
apex_automation.log_info('Update/Insert Medical Facilities Statuses, rows = ' || SQL%ROWCOUNT );
アクションは実行順序の昇順で実行されます。レポートの表示はデフォルトでは実行順序の昇順になっていません。一般的な対話グリッドの操作により実行順序で昇順ソートできます。
保存して実行をクリックし、動作を確認します。
自動化の実行ログのタブを開きます。メッセージの数値をクリックします。
アクションのコード中で呼び出したAPEX_AUTOMATION.LOG_INFOによるログ出力を確認することができます。
以上で今回の作業は完了です。アプリケーションを実行すると、以下のようになります。
作成したアプリケーションのエクスポートを以下に置きました。
https://github.com/ujnak/apexapps/blob/master/exports/c19-medical-facilities.sql
自治体コードを表C19_LOCAL_GOV_CODESに投入するために、サポートするオブジェクトとしてインストール・スクリプトを登録しました。1200行を超えるスクリプト(INSERT文)だったのでインストール・スクリプトが内部的に分割され、その分割された位置のSQLが不正になりました。不具合だとは思いますが、インストール・スクリプトを200行ずつ、７つのファイルに分割することで、この不具合を回避しています。
それと記事には含めていませんが、エクスポートしたアプリケーションには表C19_LOAD_TARGETSへ自治体コードを挿入するページを含めています。
それではOracle APEXのアプリケーション作成の参考になれば幸いです。
完
追記
