Oracle Spatialを使った接触追跡という記事を参照して、この結果をOracle APEXのマップ・リージョンを使って可視化しようとすると、sdo_obj_tracing.get_all_durationsが行を返してくれない、という現象がありました。（2022年5月9日追記 - sdo_obj_tracing.get_all_durationsの引数track_table_nameにスキーマ名を付けることにより、正常に動作します。そのため、RESTサービスよりは表関数で実装する方が効果的でしょう。）
Oracle APEXは少し特殊なSQLの実行方法なので（以前に説明記事を書いています）、おそらくそれが理由だと思われるのですが、Oracle Spatialの接触追跡を使える環境は限られているため、ワークアラウンドを実装してみました。
APEXから呼ぶと行が返されないのですが、RESTサービスとして呼ぶとsdo_obj_tracing.get_all_durationsは行を返します。なので、RESTサービスとして接触追跡を実装します。
Always FreeのAutonomous DatabaseでOracle Database 21cが使えるのは北米のリージョンだけなので、Ashburnでインスタンスを作成しました。APEXのワークスペースはAPEXDEV、Oracle Spatialの記事の作業はSPATIALUSERではなく、APEXのワークスペース・ユーザーとなるAPEXDEVで作業を行なっています。
以下、実装になります。
RESTサービスは、APEXのRESTfulサービスのUIを使って実装しました。
モジュールをcom.oracle.contacttracing、ベース・パスを/contacts/として作成しています。認証による保護は省略しています。
URIテンプレートはtraceとしています。ハンドラはGETだけを作成しています。
GETハンドラを作成します。ソース・タイプをPL/SQLとし、レスポンスはPL/SQLのコードで生成します。
ソースは以下になります。
| declare | |
| l_user_id number; | |
| l_start_time date; | |
| l_end_time date; | |
| l_distance number; | |
| l_time_tolerance_in_sec number; | |
| l_chaining_tolerance_in_sec number; | |
| begin | |
| /* NVLで値がないときに結果が返るようにすることで、APEXのRESTの自動検出が使える */ | |
| l_user_id := nvl(:user_id,7); | |
| l_start_time := nvl(:start_time,to_date('2020-05-31 00:00:00','YYYY-MM-DD HH24:MI:SS')); | |
| l_end_time := nvl(:end_time,to_date('2020-05-31 23:59:59','YYYY-MM-DD HH24:MI:SS')); | |
| l_distance := nvl(:distance,15); | |
| l_time_tolerance_in_sec := nvl(:time_tolerance_in_sec,5); | |
| l_chaining_tolerance_in_sec := nvl(:chaining_tolerance_in_sec,20); | |
| /* JSONオブジェクトの配列で結果を返す。 */ | |
| apex_json.open_object; | |
| apex_json.open_array('items'); | |
| for c in ( | |
| SELECT | |
| ROWNUM AS contact_id, | |
| t.in_user_id, | |
| t.out_user_id, | |
| ROUND(t.duration / 60, 2) AS duration_in_minutes, | |
| t.start_time, | |
| t.end_time, | |
| t.num_contact_times, | |
| t.geom | |
| FROM | |
| TABLE( | |
| sdo_obj_tracing.get_all_durations( | |
| user_id => l_user_id, | |
| start_time => l_start_time, | |
| end_time => l_end_time, | |
| distance => l_distance, | |
| time_tolerance_in_sec => l_time_tolerance_in_sec, | |
| chaining_tolerance_in_sec => l_chaining_tolerance_in_sec, | |
| track_table_name => 'track_geom', | |
| geom_column_name => 'geom', | |
| user_id_column_name => 'user_id', | |
| time_column_name => 'capture_time', | |
| date_as_number_column_name => 'capture_time_as_number' | |
| ) | |
| ) t | |
| WHERE | |
| t.segment_or_all = 'ALL' | |
| ORDER BY | |
| t.in_user_id, | |
| t.out_user_id, | |
| t.start_time | |
| ) | |
| loop | |
| for p in ( | |
| select p.x, p.y | |
| from table(sdo_util.getvertices(c.geom)) p | |
| ) | |
| loop | |
| apex_json.open_object; | |
| apex_json.write('contact_id', c.contact_id); | |
| apex_json.write('in_user_id', c.in_user_id); | |
| apex_json.write('out_user_id', c.out_user_id); | |
| apex_json.write('duration_in_minutes', c.duration_in_minutes); | |
| apex_json.write('start_time', c.start_time); | |
| apex_json.write('end_time', c.end_time); | |
| apex_json.write('num_contact_times', c.num_contact_times); | |
| apex_json.write('x', p.x); | |
| apex_json.write('y', p.y); | |
| apex_json.close_object; | |
| end loop; | |
| end loop; | |
| apex_json.close_array; | |
| apex_json.close_object; | |
| end; |
APEXのアプリケーションからRESTサービスを検出する際に、パラメータを指定できないケースがあります。実行結果が返されないと、想定されるレスポンスを手作業で登録する必要が出てきます。それは避けたいため、パラメータの指定が無い場合に、必ず実行結果が返される値に置き換えています。
URLの問合せ文字列が、ソースで使用しているバインド変数に渡されるよう、パラメータを設定します。user_id、start_time、end_time、distance、time_tolerance_in_sec、chaining_tolerance_in_secをURLの問合せ文字列として受け付けます。
select
X,
Y,
to_char(END_TIME, 'YYYY-MM-DD HH24:MI') end_time,
CONTACT_ID,
IN_USER_ID,
to_char(START_TIME, 'YYYY-MM-DD HH24:MI') start_time,
OUT_USER_ID,
NUM_CONTACT_TIMES,
DURATION_IN_MINUTES,
case
when contact_id = 1 then
'red'
when contact_id = 2 then
'green'
when contact_id = 3 then
'yellow'
when contact_id = 4 then
'blue'
else
'black'
end col
from #APEX$SOURCE_DATA#
