2023年10月27日金曜日

札幌市の熊出没情報の出没位置をマップ上に表示する

札幌市が公開している令和５年度ヒグマ出没情報をデータベースに保存し、出没した位置をマップ上に表示するAPEXアプリケーションを作ってみました。地図に表示する出没情報は、ファセット検索で絞り込めます。

元情報：札幌市ヒグマ出没情報

作成したアプリケーションは以下のように動作します。


以下よりアプリケーションの作り方を紹介します。

最初に熊出没情報を保存する表を作成します。表BEAR_HTML_DATAは、札幌市ヒグマ出没情報のページをそのままHTMLとして保存します。表BEAR_SIGHTINGSに、Webページから取り出した出没情報を保存します。

クイックSQLの以下のモデルより、表BEAR_HTML_DATAとBEAR_SIGHTINGSを作成します。

# prefix: bear
html_data
name vc80 /nn
url vc200
html clob
sightings
sighting_no num /nn
sighting_date date
address vc4000
location num -- SDO_GEOMETRYに変更する
attributes json
note vc4000
view raw bear_sightings.model hosted with ❤ by GitHub

SQLの生成、SQLスクリプトを保存、レビューおよび実行を順次実施します。


SQLスクリプトが開きます。列LOCATIONの型をnumberからOracle Spatialのオブジェクトを保存するSDO_GEOMETRY型に変更します。

実行をクリックします。


即時実行します。


表BEAR_HTML_DATAとBEAR_SIGHTINGSが作成されました。


札幌市ヒグマ出没情報のWebページを読み取り、表BEAR_SIGHTINGSに出没情報として保存します。

Webページを表BEAR_HTML_DATAに文字情報として保存します。以下のSQLを実行します。複数回実行すると、列NAMEがSAPPOROである行が複数保存されます。この後の作業で、列NAMEがSAPPOROとなっている行は１行だけ、という前提で書かれてるコードがあるので注意してください。

Webページが、丸ごと列HTMLにCLOBとして保存されます。

declare
l_url bear_html_data.url%type;
l_html clob;
begin
l_url := 'https://www.city.sapporo.jp/kurashi/animal/choju/kuma/syutsubotsu/';
l_html := apex_web_service.make_rest_request(
p_url => l_url
,p_http_method => 'GET'
);
insert into bear_html_data(name, url, html) values('SAPPORO',l_url,l_html);
commit;
end;
view raw load-html-data.sql hosted with ❤ by GitHub

 ヒグマ出没情報のWebページに含まれるHTMLの表データをパースし、行と列のデータをして返すパイプライン表関数bear_sapporo_parse_html_tableを作成します。

SQLワークショップのSQLスクリプトで、以下のスクリプトを実行します。確認画面が開いたら即時実行します。

drop type t_table;
create or replace type t_table_row as object(
col001 varchar2(200),
col002 varchar2(200),
col003 varchar2(200),
col004 varchar2(200),
col005 varchar2(200),
col006 varchar2(200),
col007 varchar2(200),
col008 varchar2(200),
col009 varchar2(200),
col010 varchar2(200),
col011 varchar2(200),
col012 varchar2(200),
col013 varchar2(200),
col014 varchar2(200),
col015 varchar2(200),
col016 varchar2(200),
col017 varchar2(200),
col018 varchar2(200),
col019 varchar2(200),
col020 varchar2(200)
);
/
create or replace type t_table as table of t_table_row;
/
create or replace function bear_sapporo_parse_html_table(
p_name varchar2 -- BEAR_HTML_DATAから取り出す列を特定
)
return t_table pipelined
as
l_html clob;
l_start number;
l_end number;
l_row t_table_row;
c_record sys_refcursor;
l_cell sys.xmltype;
/* ノードがNULLかどうかチェックして文字列に変換する */
function to_string(p_cell sys.xmltype)
return varchar2
as
begin
if p_cell is null then
return null;
end if;
return p_cell.getStringVal();
end to_string;
begin
/* 表に保存したHTMLを取り出す。 */
select html into l_html from bear_html_data where name = p_name;
-- <tbody>...</tbody>の間に表にロードするデータがある。
l_start := instr(l_html, '<tbody');
l_end := instr(l_html, '</tbody>');
l_html := substr(l_html, l_start, l_end - l_start + 8);
/* <tr>...</tr>を取り出し、行があるだけ繰り返す。 */
for r in
(
select tr from xmltable('tbody/tr' passing xmltype(l_html)
columns tr xmltype path './td')
)
loop
l_row := t_table_row(
null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null
);
/* <td>...</td>を取り出す。 */
open c_record for
select cell from xmltable('./td' passing r.tr
columns cell xmltype path '/td/child::node()');
fetch c_record into l_cell;
l_row.col001 := to_string(l_cell);
fetch c_record into l_cell;
l_row.col002 := to_string(l_cell);
fetch c_record into l_cell;
l_row.col003 := to_string(l_cell);
fetch c_record into l_cell;
l_row.col004 := to_string(l_cell);
fetch c_record into l_cell;
l_row.col005 := to_string(l_cell);
close c_record;
/* 番号がない行はヘッダーなので出力しない。 */
if l_row.col001 is not null then
pipe row(l_row);
end if;
end loop;
end bear_sapporo_parse_html_table;
/
view raw bear_sapporo_parse_html_table.sql hosted with ❤ by GitHub


３つのタイプと１つのファンクションが作成されます。


ヒグマ出没情報の内容をファセット検索のファセットとして使えるように、JSON配列に変換するファンクションbear_get_sighting_attrsを作成します。

create or replace function bear_get_sighting_atts(
p_note in varchar2
)
return varchar2
as
type t_attr is table of varchar2(10);
/* 目撃情報の属性 */
C_ATTRS constant t_attr := t_attr(
'ヒグマ','親子','らしき','フン','足跡','枝折り','鳴き声','食痕','目撃','確認','駆除','掘り跡','被毛','3頭','2頭'
);
l_attrs json_array_t;
begin
l_attrs := json_array_t();
for i in C_ATTRS.first..C_ATTRS.last
loop
if instr(p_note, C_ATTRS(i)) > 0 then
/* 文字列が含まれていればJSON配列に含める */
l_attrs.append(C_ATTRS(i));
end if;
end loop;
return l_attrs.to_string();
end bear_get_sighting_atts;
/
view raw bear_get_sighting_atts.sql hosted with ❤ by GitHub

以下のINSERT文を実行し、ヒグマ出没情報のWebページの情報を表BEAR_SIGHTINGSに投入します。

insert into bear_sightings(sighting_no, sighting_date, address, location, attributes, note)
select
to_number(col001) sighting_no,
sighting_date,
col003 address,
apex_spatial.point(
p_lat => to_number(substr(col004,(idx1+4),(idx2-idx1-4))),
p_lon => to_number(substr(col004,(idx2+3),(idx3-idx2-3)))
) location,
bear_get_sighting_atts(col005) attributes,
col005 note
from
(
select
col001,
col002,
case when instr(col002,'時') > 0 then
/* 時刻が報告されている */
to_date(regexp_replace(col002,'^(\d+)年(\d+)月(\d+)日(\D+)(\d+)時(\d+)分','\1-\2-\3 \5:\6'),'YYYY-MM-DD HH24:MI')
else
/* 時刻は報告されていない */
to_date(regexp_replace(col002,'^(\d+)年(\d+)月(\d+)日(\D+)','\1-\2-\3'),'YYYY-MM-DD')
end sighting_date,
col003,
col004,
/* 緯度経度を取り出すための位置 */
instr(col004,';ll=') idx1,
instr(col004,'%2C') idx2,
instr(col004,'&amp;z=') idx3,
col005,
bear_get_sighting_atts(col005) attributes
from
(
select * from bear_sapporo_parse_html_table('SAPPORO')
)
)
view raw import-bear-sightings.sql hosted with ❤ by GitHub


2023年10月27日の実行では、190行のデータが表BEAR_SIGHTINGSに挿入されました。

表やデータの準備は以上で完了です。

APEXアプリケーションの作成を始めます。

アプリケーション作成ウィザードを起動します。アプリケーションの名前は熊出没情報とします。

ホーム・ページを削除し、代わりにファセット検索のページを追加します。


ファセット検索のページは、ページ名を熊出没情報、レポートを選択し、表としてBEAR_SIGHTINGSを指定します。


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

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

ページ・デザイナでページ番号１のファセット検索のページを開きます。


クラシック・レポートのリージョンBear Sightingsを選択します。

ソースのタイプをSQL問合せに変更し、SQL問合せに以下を記述します。ファセットとして使用するため、列ADDRESSから区の名前を抜き出しています。

select ID,
SIGHTING_NO,
SIGHTING_DATE,
substr(address, 1, instr(address,'区')) ward,
ADDRESS,
LOCATION,
ATTRIBUTES,
NOTE
from BEAR_SIGHTINGS
view raw bear-sightings-report.sql hosted with ❤ by GitHub

また、詳細の静的IDとしてbear_sightingsを設定します。


レポートBear Sightingsの列LOCATIONは座標値、ATTRIBUTES（元はNOTE）、WARD（元はADDRESS）はファセットとして使うために作成した列なので、これらのタイプを非表示に変更します。


列SIGHING_NOを選択し、ヘッダーをNoに変更します。


列SIGHTING_DATEを選択し、ヘッダーを日時、外観の書式マスクをDL TSに変更します。


列ADDRESSを選択し、ヘッダーを場所に変更します。


列NOTEを選択し、ヘッダーを内容に変更します。


ファセットのP1_NOTEとP1_SIGHTING_NOを削除します。


ファセット上でコンテキスト・メニューを開き、ファセットの作成を実行します。


作成されたファセットを選択します。最初に区のファセットを作ります。

識別の名前はP1_WARDとします。タイプはチェック・ボックス・グループです。ラベルは区になります。LOVのタイプとして個別値を選択します。


もうひとつ同じ手順でファセットを作成します。

識別の名前はP1_ATTRIBUTES、タイプはチェック・ボックス・グループを選択します。LOVのタイプに個別値を指定します。


プロパティ・エディタを下の方にスクロールさせます。

複数の値のタイプにJSON配列を選択し、フィルタの結合はAND (論理積）にします。


以上で、ファセット検索とクラシック・レポートの部分は完成です。アプリケーションを実行して、これまでの作業結果を確認します。


これから、このページにマップを追加します。

ファセット検索が適用されたクラシック・レポートをマップのソースとして扱うために、パイプライン表関数facet_report_keysを作成します。

drop type t_facet_report_tab;
create or replace type t_facet_report_row as object(
pid number
);
/
create or replace type t_facet_report_tab as table of t_facet_report_row;
/
create or replace function facet_report_keys(
p_app_id in number
, p_page_id in number
, p_region in varchar2
, p_column in varchar2
) return t_facet_report_tab pipelined
is
l_region_id number;
l_context apex_exec.t_context;
l_idx_key pls_integer;
begin
-- リージョンの静的IDから、リージョンIDを見つける。
select region_id into l_region_id from apex_application_page_regions
where application_id = p_app_id and page_id = p_page_id
and static_id = p_region;
-- リージョン検索結果にて、スマート・フィルタが適用された結果を取り出す。
-- l_contextはいわゆる結果セットと同じ。
l_context := apex_region.open_query_context(
p_page_id => p_page_id
, p_region_id => l_region_id
);
-- 主キー列の位置を取得する。
l_idx_key := apex_exec.get_column_position(l_context, p_column);
while apex_exec.next_row(l_context)
loop
pipe row(
t_facet_report_row(
apex_exec.get_number(l_context, l_idx_key)
)
);
end loop;
apex_exec.close(l_context);
end facet_report_keys;
/
view raw facet_report_keys.sql hosted with ❤ by GitHub
SQLスクリプトを使って実行します。


ページ・デザイナでの作業に戻ります。

リージョンボタン・バーの上でコンテキスト・メニューを表示させ、下にリージョンを作成を実行します。


新規に作成されたリージョンを選択します。

識別のタイトルを地図とし、タイプとしてマップを選択します。外観のテンプレートにBlank with Attributesを選択します。


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

初期位置およびズームとして札幌市の中心を指定します。タイプとして静的値を選択し、経度に141.3543、緯度に43.062、ズーム・レベルに9を設定します。

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


右ペインのレンダリング・ビューで、リージョン地図にひとつだけあるレイヤーを選択します。

識別の名前を出没場所、レイヤー・タイプとしてポイントを選択します。ソースのタイプにSQL問合せを選択し、SQL問合せとして以下を記述します。

select
id,
sighting_no,
sighting_date,
address,
location,
/* なぜかTool Tipの拡張フォーマットが効かない */
'No: ' || sighting_no || ' / ' ||
to_char(sighting_date,'DL TS') || ' / ' ||
address || ' / ' ||
note info,
note
from bear_sightings
where
sighting_no in
(
select pid from table(
facet_report_keys(
p_app_id => :APP_ID
,p_page_id => :APP_PAGE_ID
,p_region => 'bear_sightings'
,p_column => 'SIGHTING_NO'
)
)
)
view raw bear-sightings-map.sql hosted with ❤ by GitHub

列INFOとして情報ウィンドウに表示するデータを定義していますが、これは情報ウィンドウの拡張フォーマットが思ったように動かないためのワークアラウンドです。


情報ウィンドウの拡張フォーマットをオンにし、HTML式として&INFO.を設定しています。原因は不明ですが、&INFO.以外の列をHTML式に含めても置換されません。情報ウィンドウだけでなく、ツールチップも同じ症状です。


クラシック・レポートがリフレッシュした後にマップもリフレッシュするよう、動的アクションを作成します。

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


作成された動的アクションを選択します。

識別の名前は地図をリフレッシュとします。タイミングのイベントはデフォルトでリフレッシュ後、選択タイプはリージョン、リージョンはBear Sightingsになります。


TRUEアクションを選択します。

識別のアクションとしてリフレッシュを選択し、影響を受ける要素の選択タイプにリージョン、リージョンに地図を選択します。


以上で、熊出没情報のアプリケーションは完成です。アプリケーションを実行すると、記事の先頭のGIF動画のように動作します。

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

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

完
投稿者 時刻:
ラベル: ,