接触追跡のファンクションを表関数から呼び出す

 以前の記事で、Oracle Spatialの接触追跡のファンクションを呼び出す方法について説明しました。その際にAPEXのワークスペースから呼び出すと行が返されないため（sdo_obj_tracing.get_all_durationsの引数track_table_nameとして与える表名を所有者で修飾することにより、異なるユーザーから呼び出すことができました）、RESTサービスにラップしています。

RESTサービスで実装する前に、表関数による実装も試していたので、その作業を記録として残しておきます。

データベース・ユーザーSPATIALUSERで以下のコードを実行し、表関数contact_tracingを作成しました。

	drop type t_cf_tab;
	drop type t_cf_row;
	-- 表関数contact_tracingの行となるタイプ
	create type t_cf_row as object (
	contact_id number
	, in_user_id number
	, out_user_id number
	, duration_in_minutes number
	, start_time date
	, end_time date
	, num_contact_times number
	, x number
	, y number
	);
	/
	-- 表関数contact_tracingの表となるタイプ
	create type t_cf_tab is table of t_cf_row;
	/
	-- 接触追跡の結果を返す表関数
	create or replace function contact_tracing(
	p_user_id in number
	, p_start_time in date
	, p_end_time in date
	, p_distance in number
	, p_time_tolerance_in_sec in number
	, p_chaining_tolerance_in_sec in number
	) return t_cf_tab
	as
	l_tab t_cf_tab := t_cf_tab();
	begin
	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 => p_user_id
	, start_time => p_start_time
	, end_time => p_end_time
	, distance => p_distance
	, time_tolerance_in_sec => p_time_tolerance_in_sec
	, chaining_tolerance_in_sec => p_chaining_tolerance_in_sec
	-- 異なるユーザーから呼び出す場合は、スキーマ名による修飾が必要。
	, track_table_name => sys_context('userenv','current_schema') || '.track_geom'
	-- , 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'
	)
	loop
	for p in (
	select p.x, p.y from table(sdo_util.getvertices(c.geom)) p
	)
	loop
	l_tab.extend;
	l_tab(l_tab.last) := t_cf_row(
	c.contact_id
	, c.in_user_id
	, c.out_user_id
	, c.duration_in_minutes
	, c.start_time
	, c.end_time
	, c.num_contact_times
	, p.x
	, p.y
	);
	end loop;
	end loop;
	return l_tab;
	end;
	/

view raw contact_tracing_table_func.sql hosted with ❤ by GitHub

データベース・アクションにて実行しています。

表関数contact_tracingを使った接触追跡は、以下のSELECT文で行います。

select * 
from table(contact_tracing(
    7
    , to_date('2020-05-31 00:00:00','YYYY-MM-DD HH24:MI:SS')
    , to_date('2020-05-31 23:59:59','YYYY-MM-DD HH24:MI:SS')
    , 15
    , 5
    , 20
))

座標値を除いた結果を確認してみます。

select distinct contact_id, in_user_id, out_user_id, duration_in_minutes, start_time, end_time, num_contact_times 
from table(contact_tracing(
    7
    , to_date('2020-05-31 00:00:00','YYYY-MM-DD HH24:MI:SS')
    , to_date('2020-05-31 23:59:59','YYYY-MM-DD HH24:MI:SS')
    , 15
    , 5
    , 20
))

続いて、ユーザーADMINから検索します。

ユーザーadminに表関数contact_tracingの実行権限を与えます。ユーザーSPATIALUSERにて、以下のコマンドを実行します。

grant execute on contact_tracing to admin;

SELECT文を変更しなくて済むように、シノニムを作成します。ユーザーADMINにて、以下のコマンドを実行します。

create synonym contact_tracing for spatialuser.contact_tracing;

ユーザーADMINにて、SELECT文を実行します。

ユーザーSPATIALUSERで実行した場合と同じ結果が得られます。

APEXのレポートでデータ・ソースが複雑になる場合に、表関数を利用する一例として紹介してみました。

以上になります。

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

完