In times of a pandemic it is crucial to understand if people have been close enough to each other for long enough to transmit the disease. This means that location-based data in potentially huge volumes need to be analyzed for pair-wise interaction.

Oracle has released two AUTOMATIC, SCALABLE contact tracing APIs (PL/SQL table functions) for this purpose, included in every Oracle Database license. These APIs natively leverage the security, scalability, and manageability features of Oracle Database. We will refer to these APIs here as:

A Variety of Other Use Cases discusses some other possible use cases for the contact tracing APIs besides the current focus on the COVID-19 pandemic.

The last section of this blog post is titled Supplement and contains additional resources, including sample data and queries for both APIs.

API 1 – Swipe IN/OUT contact tracing

No GPS data required.  Automatically identify anyone that shared a room with someone positive.  Report how long they shared space (duration).  Quickly notify anyone that was exposed, possibly to quarantine.

This API can be used by college campuses, corporate offices, nursing homes, hospitals, and other places with swipe in/out sensors installed on rooms.  Given any user to trace, and a time period, this API detects all rooms where users’ swipe in/swipe out times overlap the user being traced, and it quickly reports duration (how long the users were in contact in the same room).

The API can report the time overlap (that is, contact duration) for each individual room a user entered, and It can also report a summary, the total time users overlapped in any room over a specified time period (for example, last 8 hours, or last 24 hours, or last week, or last month).  Below are sample SQL statements for the swipe in/out API. To report time overlap for each room, specify WHERE segment_or_all = ‘SEGMENT’.  To report aggregated time overlap, specify 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)

API 1, Example 1 – Find all contacts who were in the same room as user 1, and report duration by room.  Query results are shown in a table after the example.  Note, the results below reflect a run against more data than depicted in sample data model described above.


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;

 

API 1 –  Example 2 – Aggregate (by contact) duration computations for all contacts who were in the same room as user 1. Query results are shown in the table after the example.


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 tracking contact tracing

Given a user’s GPS tracking data (a series of locations with associated time stamps), this API quickly correlates both location and time to automatically detect other users who, based on their GPS tracking data, came in contact with an input user. To be considered a “contact”, both the location and time stamps of the track points must coincide. When consecutive locations are classified as contacts, duration is also computed and reported.

Optionally, the geometry that denotes the contact points can also be returned for display on a map.

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 –  Example 1 – Detect anyone who came within 15 meters of user 7. Additional parameters can be specified, for example, only return users whose contact duration is greater than 5 minutes.


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 –  Example 2 – Aggregate (by contact) the duration for each user.  Users may come in contact multiple times, like user 6 in the previous query. The API can also aggregate (by contact) the duration for each user as follows, by specifying the predicate segment_or_all = ‘ALL’ to the WHERE clause.  In the table after the example, the result displays user 6 as having a 97.63 minute aggregated duration with user 7. If user 7 is infected, then user 6 should probably be contacted for tracing. (On the other hand, users 5 and 8 had aggregated durations of much less than a minute each with user 7, and thus users 5 and 8 probably do not need to be contacted.)


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;

 

To use the APIs in Oracle 18c or 19c it is necessary to install Patch 31372664. Newer releases will include the functionality by default.

A Variety of Other Use Cases

While current discussion of contact tracing has focused on the COVID-19 pandemic, the APIs can be applied in many scenarios where entities (people, vehicles, movable objects, ship tracks, etc.) can coexist at a location for one or more time periods, be it for the purpose of law enforcement, be it in the Internet of Things, or otherwise. The coexistence can be registered by anyone or anything that detects entry and exit to a location with sensors installed, and/or by anyone or anything that detects close proximity and correlated time of gps track positions.

Supplement

The following supplemental information can also be downloaded:

Related Posts

The following recent blog posts may also be relevant: