Oracle Database の全てのエディションが標準で備えている地理空間機能「Oracle Spatial」は、大規模な地理空間情報を扱うアプリケーションのバックエンドとして政府機関や企業で長年利用されてきました。2020年には COVID-19 の世界的流行を受けて Oracle Spatial に接触追跡用の機能が追加されたので、これを紹介したいと思います。
技術的詳細については、こちらのブログおよびスライド（共に英語）に記載されているので、この記事ではサンプルデータを使ってこの機能を試してみる手順を簡潔に記載することにします。複数のユーザーの GPS データを使って、地図上に動きを表示し、位置情報と時間軸の解析を実行して接触イベントを検出します。
Autonomous Database 21c の作成
Oracle Database のバージョン 18c および 19c でこの機能を使うためにはパッチを当てる必要がありますが、最新のイノベーション・リリース 21c には既に含まれています。21c をダウンロードしてお手元の Linux にインストールすることも可能ですが、Oracle Cloud のいくつかのリージョン（Ashburn、Phoenix、Frankfurt、London）では 21c が先行して Always Free 枠 で提供されているので、今回はこれを使うことにします。
Oracle Cloud アカウントの作成から Always Free 枠を使用した Autonomous Database（ADB）までの手順についてはこちらのワークショップ（日本語）を参照してください。
ここでは、ADB1 という名前のインスタンスを Data Warehouse として作成していますが、ワークロードとして Transaction Processing を選択しても問題ありません。バージョンは 21c が選択されていることをご確認ください。
データベース・ユーザーの作成
ADB の管理画面から Database Actions に ADMIN ユーザーでログインし、SPATIALUSER というユーザーを作成します。表領域のクォータを割り当てる必要があるので、ここでは UNLIMITED（制限なし）としてしまいます。Web Access を有効にすることで、このユーザーで Database Actions にログインできるようになります。
さらに、SQL を用いて Spatial Studio を使用するために必要な権限を追加します。ここには既に与えられている権限も含まれていますが、この SQL をそのまま実行して問題ありません。
GRANT CONNECT,
CREATE SESSION,
CREATE TABLE,
CREATE VIEW,
CREATE SEQUENCE,
CREATE PROCEDURE,
CREATE SYNONYM,
CREATE TYPE,
CREATE TRIGGER
TO spatialuser
ユーザーに権限を追加できたら、一度 Database Actions からログアウトして、新しいユーザーで Database Actions にログインできることを確認しておきます。
データのロード
サンプルの CSV ファイルをダウンロード（右クリックして保存）します。このファイルには、9人のユーザーの GPS デバイスから取得された多数のデータポイントで、以下のように各レコードには緯度経度と取得日時が含まれています。
+---------+-------------+------------+---------------------+
| USER_ID | LONGITUDE | LATITUDE | CAPTURE_TIME |
+---------+-------------+------------+---------------------+
| 1 | -71.0836909 | 42.3629132 | 2020-05-31 00:02:39 |
| 1 | -71.0837229 | 42.3629156 | 2020-05-31 00:06:07 |
| 1 | -71.0836832 | 42.362915 | 2020-05-31 00:06:58 |
| ... | ... | ... | ... |
| 9 | -71.076882 | 42.369989 | 2020-05-31 22:12:14 |
| 9 | -71.076893 | 42.369996 | 2020-05-31 22:12:15 |
+---------+-------------+------------+---------------------+
Database Actions に上で作成したユーザーでログインし、先程ダウンロードした CSV ファイルを TRACK 表としてロードします。
LONGITUDE 列と LATITUDE 列は小数点以下最大 7桁を含んでいるので、Scale を 7 とします。また、CAPTURE_TIME 列は DATE 型を指定して（接触追跡のためのファンクションが DATE 型に対応しているため）フォーマットは YYYY-MM-DD HH24:MI:SS とします。
ジオメトリ・オブジェクトへの変換
Oracle Spatial では地理空間演算を利用するために SDO_GEOMETRY オブジェクトを使用します。データが SDO_GEOMETRY オブジェクトに変換されると、 その上でさまざまな地理空間操作や分析を実行できるようになります。ここでは、TRACK 表では通常の数値として格納されている緯度と経度を、測地座標系と合わせて二次元上の点として保存します。
Database Actions から以下の SQL を実行して、TRACK 表のデータを基に TRACK_GEOM 表を作成します。SDO_GEOMETRY オブジェクトを含む GEOM 列を追加している他、DATE 型の測定時刻を整数に直して CAPTURE_TIME_AS_NUMBER 列に格納しています。
CREATE TABLE track_geom AS
SELECT
t.user_id,
SDO_GEOMETRY(
2001, -- ジオメトリのタイプ（ここでは、2次元の点）
4326, -- 測地座標系のID（SRID）
SDO_POINT_TYPE(t.longitude, t.latitude, NULL),
NULL,
NULL
) AS geom,
t.capture_time,
t.capture_time - TO_DATE('2019-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
AS capture_time_as_number
FROM track t;
空間索引の作成
接触を検出するためには、空間と時間を考慮してその時間に近くにあったデータポイントを洗い出し、それらを束ねて一定時間連続しているケースを探し出す必要があります。これを実用的なレスポンスで計算するためには効率的な索引、特に空間的な距離を検索するためには空間索引が不可欠です。
空間索引を作成するには、まず対象カラムに格納されるジオメトリ・オブジェクトのメタデータを登録した上で、索引のタイプとして mdsys.spatial_index_v2 を指定して索引を作成します。
INSERT INTO user_sdo_geom_metadata VALUES (
'track_geom',
'geom',
SDO_DIM_ARRAY(
SDO_DIM_ELEMENT('longitude', -180.0, 180.0, 0.05),
SDO_DIM_ELEMENT('latitude', -90.0, 90.0, 0.05)
),
4326
);
COMMIT;
CREATE INDEX idx_geom ON track_geom (geom) INDEXTYPE IS mdsys.spatial_index_v2;
さらに、ユーザー ID と取得時間（計算を高速化するため日時を整数に直したもの）に対して通常の索引を作成しておきます。
CREATE INDEX idx_user_id ON track_geom (user_id);
CREATE INDEX idx_capture_time_as_number ON track_geom (capture_time_as_number);
Spatial Studio を用いた可視化
ジオメトリを含むデータを地図上に描画するツールのひとつが Oracle Spatial Studio です。Spatial Studio は Oracle Cloud のマーケットプレイスから Always Free 枠の Compute インスタンスを選択して起動することで、期限なく使い続けることができます。Spatial Studio のセットアップ方法については、以下の記事を参考にしてください。
- Spatial Studio で地理情報を扱おう
- Introduction to Oracle Spatial Workshop
- Install Oracle Spatial Studio from Cloud Marketplace
Spatial Studio がセットアップできたら、ADB に接続して TRACK_GEOM 表をデータセットとして追加します。空間索引が既に作成されているので、新しいプロジェクトにこのデータセットを追加して地図にドロップダウンするだけで、次のようにデータポイントが可視化されます。
じっと見てみると、同じ場所や経路を通っている複数のユーザーがいることがわかりますが、接触があったかどうかを知るためには時間も考慮する必要があるため、それらを簡単に見つけることはできません。
特定ユーザーの接触イベントの検出
いよいよ接触追跡 API である sdo_obj_tracing パッケージのファンクションを実行します。この際、あるユーザー（例えば感染の陽性者）の一定期間内の行動に着目しているので、引数としてユーザー ID と対象の時間枠を与えます。さらに、接触を検出する距離と時間の閾値、連続した接触を検出する時間の閾値を設定することができます。
SELECT
ROWNUM AS contact_id,
t.in_user_id,
t.out_user_id,
ROUND(t.duration / 60, 2) AS duration_in_minutes,
TO_CHAR(t.start_time, 'YYYY-MM-DD HH24:MI:SS') AS start_time,
TO_CHAR(t.end_time, 'YYYY-MM-DD HH24:MI:SS') AS end_time,
t.num_contact_times,
t.geom
FROM
TABLE(
sdo_obj_tracing.get_all_durations(
user_id => 7,
start_time => TO_DATE('2020-05-31 00:00:00','YYYY-MM-DD HH24:MI:SS'),
end_time => TO_DATE('2020-05-31 23:59:59','YYYY-MM-DD HH24:MI:SS'),
distance => 15,
time_tolerance_in_sec => 5,
chaining_tolerance_in_sec => 20,
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;
Database Actions でこのクエリを実行します。
クエリの結果は次の通りです。ユーザー 7 はユーザー 5、6、8 と接触があり、特にユーザー 6 とは 86分間という比較的長時間の接触があったことがわかります。接触があった地点（の集合）は GEOM 列にジオメトリ・オブジェクトとして返されています。
CONTACT_ID IN_USER_ID OUT_USER_ID DURATION_IN_MINUTES START_TIME END_TIME NUM_CONTACT_TIMES GEOM
---------- ---------- ----------- ------------------- ------------------- ------------------- ----------------- ---------------
1 7 5 0.2 2020-05-31 18:53:24 2020-05-31 18:53:36 1 [object Object]
2 7 6 86.08 2020-05-31 15:26:34 2020-05-31 21:42:57 34 [object Object]
3 7 8 0.07 2020-05-31 14:37:02 2020-05-31 14:37:06 1 [object Object]
接触イベントの可視化
接触があった地点を Spatial Studio で可視化するために、先程のクエリの結果を表に格納します。その方法は先程のクエリに次の一行目（CREATE TABLE ... AS）を加えるだけです。また、代わりにビューを作成（CREATE VIEW ... AS）して、そのビューがアクセスされたときに計算を実行することも可能です。
CREATE TABLE contacts AS
SELECT
ROWNUM AS contact_id,
t.in_user_id,
t.out_user_id,
ROUND(t.duration / 60, 2) AS duration_in_minutes,
TO_CHAR(t.start_time, 'YYYY-MM-DD HH24:MI:SS') AS start_time,
TO_CHAR(t.end_time, 'YYYY-MM-DD HH24:MI:SS') AS end_time,
t.num_contact_times,
t.geom
FROM
TABLE(
sdo_obj_tracing.get_all_durations(
user_id => 7,
start_time => TO_DATE('2020-05-31 00:00:00','YYYY-MM-DD HH24:MI:SS'),
end_time => TO_DATE('2020-05-31 23:59:59','YYYY-MM-DD HH24:MI:SS'),
distance => 15,
time_tolerance_in_sec => 5,
chaining_tolerance_in_sec => 20,
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;
Spatial Studio に新たに作成された CONTACTS 表を追加して、地図上にドロップダウンすると、接触のあった地点が描画されます。ここで赤色の丸印で示されている通り、複数のユーザーが一緒に移動している場合でも、連続した接触として追跡できていることがわかります。
様々なユースケース
GPS デバイスが普及した現在、地理空間情報が重要な社会インフラの一部であることが認知されてきました。上記のような空間データベース技術は、パンデミックにおける人同士の接触だけでなく、自動車や IoT デバイスの追跡にも応用することができます。今後、ジオマーケティングや MaaS（Mobility as a Service）といったサービスだけでなく、警察の捜査や交通の最適化といった多くの場面で社会に役立てられることが期待されています。