Oracle Databaseにおける接触追跡API

本記事は、Oracle公式ブログにて掲載されたContact Tracing APIs in Oracle Databaseを翻訳した記事となります。

このパンデミックの時代に「みなさんがウイルスを感染させるには十分な時間・近い距離で接触したかどうか」ということは、とても重要なことです。この問題に関して、位置情報データは、人と人の接触を分析する上で潜在的に大きな役割を担っているのです。

そのような背景に応えるため、オラクルは、自動化された拡張性の高い接触者追跡のAPI（PL/SQL のファンクション）を２つリリースしてきました。この2つのAPIは、どのOracle Databaseライセンスにも無償で含まれていますし、Oracle Databaseのセキュリティ、拡張性、管理性に関する機能をネイティブに活用することができます。

・API 1 - 入退出による接触追跡
・API 2 - GPSを利用した接触追跡

記事後半の他のユースケースについての項では、COVID-19によるパンデミックに対する接触者追跡APIに留まらず、他の潜在的なユースケースについて触れていきます。

この記事の最後の追加情報には、更に詳しく知りたい方のために追加で役立つリソースのリンクを紹介しています。例えば、今回紹介している2つのAPIについてのサンプルデータとサンプルクエリなどです。

API 1 - 入退出による接触追跡

特にGPSデータは必要ありません。陽性の人と、同じ空間を共有した人が自動的に特定されます。速やかに陽性の人に接触した場合、接触していた時間などを通知します。場合によっては隔離の必要があるでしょう。

このAPIは、例えば、大学のキャンパスやオフィス、老人ホーム、病院など、swipe in/outセンサー(入退出センサー)が取り付けられている様々な場所での活用が期待できます。ユーザが入退出した全ての部屋を追うことができますし、追跡している他のユーザと滞在時間がオーバーラップしていた時間(同じ部屋を共有した時間)を通知します。

ユーザが入室した部屋単位で、オーバーラップした時間=接触時間を通知できることは既に紹介しました。加えて、指定された期間(8時間前、24時間前、先週、先月など)にどのユーザたちがどの部屋でオーバーラップした合計時間のサマリーも通知することができます。下記に、このswipe in/out API向けに使用できるサンプルのSQL文を記載しています。各部屋のオーバーラップした時間＝接触時間を通知するにはWHERE segment_or_all = 'SEGMENT'を、そして、オーバーラップした時間＝接触時間の集計を通知したい場合は、WHERE segment_or_all = 'ALL'という条件を追加して下さい。

Table columns:

・USER_ID – Unique user id (NUMBER)
・SWIPE_TIME – Time of entry or exit (Oracle DATE)
・IN OR OUT – Identify row as entry or exit (VARCHAR2)
・BUILDING ID – Required – building id (VARCHAR2)
・FLOOR ID – Optional – floor id (VARCHAR2)
・ROOM ID – Optional – room id (VARCHAR2)

API1の例１ - user1と同じ部屋にいた人たちの全ての接触を追跡し、部屋ごとに接触時間を通知する。クエリ結果は、下記のSQL文の後に、記載しています。
※下記の結果は、上記で紹介しているサンプルデータモデルで見られるものより、より多くのデータが反映された実行結果になります。

SELECT a.in_user_id,
       a.out_user_id,
       a.building_id,
       a.floor_id,
       a.room_id,
       to_char(a.start_time,'DDMMYY HH24:MI:SS') as start_time,
       to_char(a.end_time,'DDMMYY HH24:MI:SS')   as end_time,
       round(a.duration/60, 2)                   as duration_in_min
     FROM TABLE (sdo_obj_tracing.get_all_swipe_io_durations (
            user_id                  => 1,
            start_time               => to_date('010120 12:00:00','DDMMYY HH24:MI:SS'),
            end_time                 => to_date('010120 23:00:00','DDMMYY HH24:MI:SS'),
            track_table_name         => 'swipe_table',
            user_id_column_name      => 'user_id',
            building_id_column_name  => 'building_id',
            floor_id_column_name     => 'floor_id',    -- optional
            room_id_column_name      => 'room_id',     -- optional
            swipe_io_column_name     => 'swipe_in_out',
            time_column_name         => 'time',
            must_match_columns       =>  NULL)) a      -- optional
     WHERE segment_or_all = 'SEGMENT'
     ORDER BY in_user_id, out_user_id, start_time;

API1の例２ - user1と同じ部屋にいた人の全ての接触時間の合計を接触ごとに集計する。クエリ結果は、下記のSQL文の後に、記載しています。

SELECT a.in_user_id,
       a.out_user_id,
       a.building_id,
       a.floor_id,
       a.room_id,
       to_char(a.start_time,'DDMMYY HH24:MI:SS') as start_time,
       to_char(a.end_time,'DDMMYY HH24:MI:SS')   as end_time,
       round(a.duration/60, 2)                   as duration_in_min
     FROM TABLE (sdo_obj_tracing.get_all_swipe_io_durations (
            user_id                  => 1,
            start_time               => to_date('010120 12:00:00','DDMMYY HH24:MI:SS'),
            end_time                 => to_date('010120 23:00:00','DDMMYY HH24:MI:SS'),
            table_name               => 'swipe_table',
            user_id_column_name      => 'user_id',
            building_id_column_name  => 'building_id',
            floor_id_column_name     => 'floor_id',        -- optional
            room_id_column_name      => 'room_id',         -- optional
            swipe_io_column_name     => 'swipe_in_out',
            time_column_name         => 'time',
            must_match_columns       =>  NULL)) a          -- optional
     WHERE segment_or_all = 'ALL'
     ORDER BY in_user_id, out_user_id, start_time;

API 2 - GPSを利用した接触追跡

ユーザのGPSトラッキングデータ(タイムスタンプと紐付いて蓄積された位置情報)を使って、このAPIは迅速に位置と時間を関連づけて、入室したユーザと接触した他のユーザを自動的に検知します。「接触」と考えるには、位置情報とタイムスタンプ情報が両方とも一致している必要があります。連続した位置情報が接触として分類されたとき、接触時間もまた計算および通知できます。

また、接触位置を示した平面情報は、地図上に反映して表示できます。

Table columns:

・USER_ID – Unique user identifier
・CAPTURE_TIME – Capture time of point
・POINT – Location of point
・DATE_AS_NUMBER – Optional - Optimization, captured date as a normalized number
・ACCURACY – Optional - Accuracy value to filter points to consider for matching. 0 means exact match. 3 means accuracy +-3 meters.

・API 2 - 例1 - ユーザ7の半径15mに入ったユーザの検知。追加でパラメータを指定することもできます。例えば、5分以上の接触時間があったユーザだけを返すことも可能です。

SELECT a.in_user_id
       ,a.out_user_id
       ,to_char(a.start_time,'MM/DD/YY HH24:MI:SS') start_time
       ,to_char(a.end_time, 'MM/DD/YY HH24:MI:SS')  end_time
       ,round(a.duration/60,2) duration_in_minutes
   --  ,a.geom
    FROM TABLE(sdo_obj_tracing.get_all_durations(
           user_id                    => 7,
           start_time                 => to_date('31-MAY-2020 00.00.03','DD-MON-YYYY HH24.MI.SS'),
           end_time                   => to_date('31-MAY-2020 23.59.54','DD-MON-YYYY HH24.MI.SS'),
           distance                   => 15,
           time_tolerance_in_sec      => 5,
           chaining_tolerance_in_sec  => 60,
           track_table_name           => 'osm_tracks',
           geom_column_name           => 'geom',
           user_id_column_name        => 'user_id',
           time_column_name           => 'time_adjusted',
           date_as_number_column_name => 'time_as_number')) a
    WHERE a.segment_or_all = 'SEGMENT'
    ORDER BY a.in_user_id, a.out_user_id, a.start_time;

・API 2 - 例2 - ユーザ同士の接触時間を集計する。ユーザは通常、複数回の接触があるかもしれません。例１で確認できるユーザ6は、複数回接触しています。このAPIは、WHERE句の中にsegment_or_all = 'ALL'を入れることで、複数回の接触があったとしても、それぞれのユーザの接触時間の集計が可能です。下記の表で確認できるように、ユーザ6は、合計97.63分もの時間、ユーザ7と接触しています。もしユーザ7が感染していたら、ユーザ6に連絡するのが賢明でしょう。(一方、ユーザ5とユーザ8は、合計1分未満の時間、ユーザ7を接触しています。ユーザ5とユーザ8には、連絡する必要性は低いはずです。)

SELECT a.in_user_id
       ,a.out_user_id
       ,to_char(a.start_time,'MM/DD/YY HH24:MI:SS') start_time
       ,to_char(a.end_time, 'MM/DD/YY HH24:MI:SS')  end_time
       ,round(a.duration/60,2) duration_in_minutes
  --   ,a.geom
     FROM TABLE(sdo_obj_tracing.get_all_durations(
            user_id                    => 7,
            start_time                 => to_date('31-MAY-2020 00.00.03','DD-MON-YYYY HH24.MI.SS'),
            end_time                   => to_date('31-MAY-2020 23.59.54','DD-MON-YYYY HH24.MI.SS'),
            distance                   => 15,
            time_tolerance_in_sec      => 5,
            chaining_tolerance_in_sec  => 60,
            track_table_name           => 'osm_tracks',
            geom_column_name           => 'geom',
            user_id_column_name        => 'user_id',
            time_column_name           => 'time_adjusted',
            date_as_number_column_name => 'time_as_number')) a
     WHERE a.segment_or_all = 'ALL'
     ORDER BY a.in_user_id, a.out_user_id, a.start_time;

今回紹介した2つのAPIをOracle 18cや19cで使用するためには、パッチ31372664を適用する必要があります。19c以降の新しいリリースにはデフォルトで含まれています。

他のユースケースについて

今回の接触追跡についての記事は、COVID-19によるパンデミックに照準を当てて書いたものです。しかし、今回紹介した2つのAPIは、多様なシナリオでの活用が期待できます。様々なエンティティ(人、車、船などの動くもの)が同一の位置に1回か複数回存在するシナリオです。例えば、警察などの法執行機関からIoTなど様々なものが想定されます。インストールされたセンサーによって、何者か、もしくは何かが、同一の位置に1回か複数回存在し、入退出したことは検知できます。更に、GPSの位置情報に紐付いた時間情報を使えば、どのくらいの距離でどのくらいの時間いたのか、ということも検知可能です。

追加情報

下記の追加情報もダウンロード可能です:

・Contact_Tracing_SQL_API_overview.pdf – A more detailed overview of the two contact tracing APIs.
・Contact_tracing_data_and_queries.zip – Sample data and queries for both Contact Tracing APIs.
・GPS_track_api_performance_optimizations.txt – Recommendations to optimize performance when using the GPS Track contact tracing API.

合わせて読みたい記事

下記の最近のブログ記事も、今回の記事を読んで頂いた方は楽しんで頂けるはずです:

・Performing spatial analyses on latitude/longitude data
・Options for Visualizing Spatial Data