Contact Tracing SQL APIs in Oracle Database
Automatic – Scalable
(Included in Every Oracle Database License)
Daniel Geringer
Spatial Solutions Specialist
Oracle Corporation –Spatial and Graph Product Development
Oracle Spatial features
Included in Every Oracle Database License
Deployable Components Mapping Geocoding Routing Web Services (OGC) Studio
Points Raster
Lines 3D / LiDAR
Topologies
Polygons Location
Networks Contact Address Linear
Tracking
Tracing Geocoding Referencing
(Geofencing)
Copyright © 2020 Oracle and/or its affiliates
Spatial features in Oracle Database
Two Contact Tracing APIs
3 Copyright © 2020, Oracle and/or its affiliates
Two Free Contact Tracing SQL APIs
• API 1 – Swipe IN/OUT Contact Tracing - No GPS Tracking Data Required
- Sensors on buildings, floors, rooms
- Sensors record badge swipes – IN and OUT timestamps
- API reports users that shared the same room
- API reports how long they were together
• API 2 – Proximity and Time Based Contact Tracing – GPS Tracking Data Required
- User tracks are collected with GPS sensors over time
- Compares a user’s GPS track to other GPS tracks
- API reports all GPS tracks that coincide in both proximity and time.
- API report duration (how long GPS tracks coincide in both proximity and time)
4 Copyright © 2020, Oracle and/or its affiliates
What is Swipe IN/OUT Contact Tracing
5 Copyright © 2020, Oracle and/or its affiliates
Swipe IN/OUT Contact Tracing (No GPS Track Data Required)
What is it?
• Find people that shared the same room at the same time , and report metrics like duration
• Duration – How long did they share space
• Example:
- Installs sensors on buildings and rooms:
◦ A college installs sensors on classrooms
◦ A company installs sensors on floors, conference rooms and offices
◦ A nursing home installs sensors on bedrooms, activity rooms, dining areas..
- Users swipe in/out of rooms
- Each swipe records room id, and swipe in/out times
- Automatically identify anyone that shared a room with someone positive
- Reports how long they shared space (duration)
- Quickly notify anyone that was exposed (possibly to quarantine)
6 Copyright © 2020, Oracle and/or its affiliates
Simple Data Model
For Swipe IN/OUT Sensor
7 Copyright © 2020, Oracle and/or its affiliates
Simple Data Model - For data collected by sensor
USER_ID SWIPE TIME IN OR OUT BUILDING ID FLOOR ID ROOM ID
(NUMBER) (ORACLE DATE) (VARCHAR) (optional) (optional)
1 02/28/20 13:00:00 IN BUILDING 1 ROOM 1
1 02/28/20 14:00:00 OUT BUILDING 1 ROOM 1
2 02/28/20 12:00:00 IN BUILDING 1 ROOM 1
2 02/28/20 13:30:00 OUT BUILDING 1 ROOM 1
3 02/28/20 12:00:00 IN BUILDING 1 ROOM 1
3 02/28/20 15:00:00 OUT BUILDING 1 ROOM 1
• USER_ID – Unique user id
• SWIPE_TIME – Time of entry or exit
• IN OR OUT - Identify row as entry or exit
• BUILDING ID – Required – building id
• FLOOR ID – Optional – floor id
• ROOM ID - Optional – room id
8 Copyright © 2020, Oracle and/or its affiliates
Swipe IN/OUT Contact Tracing
SQL API
9 Copyright © 2020, Oracle and/or its affiliates
Swipe IN/OUT Contact Tracing SQL API - Available on every Oracle Database Edition
FUNCTION sdo_obj_tracing.get_all_swipe_io_durations (
user_id NUMBER,
start_time DATE,
end_time DATE,
track_table_name VARCHAR2,
user_id_column_name VARCHAR2,
building_id_column_name VARCHAR2,
floor_id_column_name VARCHAR2,
room_id_column_name VARCHAR2, Table name and column names as parameters
swipe_io_column_name VARCHAR2,
time_column_name VARCHAR2')
• Currently available by installing patch 31372664 – Works on database versions 18c, 19c
10 Copyright © 2020, Oracle and/or its affiliates
Swipe IN/OUT Contact Tracing
SQL Developer Demo
11 Copyright © 2020, Oracle and/or its affiliates
Swipe IN/OUT API SQL Developer Demo - SEGMENT_OR_ALL='SEGMENT'
Find anyone that shared a room with user 1, between 8 am and 8 pm – Show each occurrence
User 2 had two occurrences
12 Copyright © 2020, Oracle and/or its affiliates
Swipe IN/OUT API SQL Developer Demo - SEGMENT_OR_ALL='ALL'
Find anyone that shared a room with user 1, between 8 am and 8 pm – Aggregate results of all occurrences
User 2 had two occurrences
Aggregated duration
13 Copyright © 2020, Oracle and/or its affiliates
What is Proximity and Time Based
Contact Tracing
14 Copyright © 2020, Oracle and/or its affiliates
Proximity and Time Based Contact Tracing (with GPS tracks)
What is it?
• Find people that shared space at the same time, and report metrics like duration
• Example 1:
- A hospital/company can ask employees to opt into collecting their position while at work
- If someone calls in sick, identify all employees that were within 6 feet of the sick person over the last week, and
how long were they in contact (i.e.. duration)
- Duration is key reporting metric
• Example 2:
- Sensors on ships collect GPS tracks over time
- Find ships that shared the same space at the same time, and how long did they share space (duration)
• Tracking data collects the position and time of moving objects.
• Proximity and time based contact tracing can be performed on any tracking data.
15 Copyright © 2020, Oracle and/or its affiliates
Proximity and Time Based Contact Tracing
Proximity alone is not enough
• Match points that are 6 feet or less apart, and whose times coincide
• When matching two tracks, proximity checks are not enough
• Trach points must coincide in both proximity and time
Proximity alone – match example Proximity and time – no match example
08:14:50 08:14:55 08:15:30
08:13:00 08:13:52
08:23:00 08:23:33
08:22:00 08:22:30 08:24:01
16 Copyright © 2020, Oracle and/or its affiliates
Proximity and Time Based Contact Tracing
Time Tolerance
• For User 2 (in blue), find contacts within 6 feet and coincide in time.
• User 11 is a potential candidate
• When contact matching User 11 point to User 2, times almost never match exactly
• Time tolerance is a configurable parameter to consider contact matches
• For example, a 30 second time tolerance allows the first points of User 11 and User 2 to match
13:01:50 13:02:20 13:02:40
13:00:00 13:00:52 User 11 Track
13:02:30
13:01:30
13:00:28 13:01:00 13:04:00
User 2 Track
Configurable Time Tolerance
17 Copyright © 2020, Oracle and/or its affiliates
Proximity and Time Based Contact Matching - Duration
• Duration accumulated for consecutive matching points
2 minute 40 second Duration 1 minute 48 second Duration
Configurable Chaining Tolerance – 60 second example
User 11
13:01:50 13:02:20 13:02:40 22:00:52 22:01:50 22:02:20 22:02:40
13:00:00 13:00:52
13:02:30
13:01:30 22:01:00 22:01:30 22:02:30
13:00:28 13:01:00
13:04:00
User 2
18 Copyright © 2020, Oracle and/or its affiliates
Simple Data Model for Tracking Data
19 Copyright © 2020, Oracle and/or its affiliates
Simple Data Model for Tacking Data - (3 Required Columns, 2 Optional Columns)
USER_ID CAPTURE TIME POINT DATE AS NUMBER ACCURACY
(NUMBER) (ORACLE DATE) (SDO_GEOMETRY) (NUMBER) (NUMBER)
Optional Optional
1 02/28/20 13:10:00 (-122.5678, 45.1234) 423.048611 0
1 02/28/20 13:10:30 (-122.5623, 45.1256) 423.048958 3
1 02/28/20 13:11:00 (-122.5623, 45.1256) 423.048958 3
8 01/15/20 09:20:00 (-119.5698, 37.2468) 378.888889 45
8 01/15/20 09:20:25 (-119.5700, 37.2400) 378.889178 2
• 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 point to consider for matching
20 Copyright © 2020, Oracle and/or its affiliates
Some Optimizations
21 Copyright © 2020, Oracle and/or its affiliates
Optimizations
• Leverage Oracle native spatial indexing for scalable spatial searches
INSERT INTO user_sdo_geom_metadata ('TRACK_TABLE', 'GEOM',
SDO_DIM_ARRAY(SDO_DIM_ELEMENT(‘x‘, -180, 180,.05), SDO_DIM_ELEMENT(‘x‘, -180, 180,.05)), 4326);
CREATE INDEX track_table_sidx ON track_table (geom) INDEXTYPE IS mdsys.spatial_index;
• Create ordinary index (local if table is partitioned on time)
CREATE INDEX user_id_idx ON track_data (user_id);
CREATE INDEX time_idx ON track_data (capture_time);
• Partition large tables by time
• Interleaved Attribute clustering on (capture_time, longitude, latitude) – This really helps performance!
22 Copyright © 2020, Oracle and/or its affiliates
DATE_AS_NUMBER (Optional) - Optimization
If set to NULL,
• Oracle can perform date arithmetic
• SELECT to_date ('01-01-2020', 'MM-DD-YYYY') - to_date ('01-01-2019', 'MM-DD-YYYY') FROM dual;
• Result is 365, unit is in days
• From my profiling, NUMBER arithmetic is about 3x faster
• Tracking Table contains a DATE_AS_NUMBER column for faster date arithmetic
• Value is the number of days to an established reference date older than all your tracking data
UPDATE tracking_data
SET date_as_number = (capture_time – to_date('01-01-2019', 'MM-DD-YYYY'));
COMMIT;
23 Copyright © 2020, Oracle and/or its affiliates
Proximity and Time Based Contact Tracing
SQL API
24 Copyright © 2020, Oracle and/or its affiliates
Contact Tracing SQL API - Available on every Oracle Database Edition
FUNCTION sdo_obj_tracing.get_all_durations (
user_id NUMBER,
start_time DATE,
end_time DATE,
distance NUMBER,
time_tolerance_in_sec NUMBER,
chaining_tolerance_in_sec NUMBER,
track_table_name VARCHAR2,
geom_column_name VARCHAR2,
user_id_column_name VARCHAR2, Table name and column names as parameters
time_column_name VARCHAR2',
date_as_number_column_name VARCHAR2,
accuracy_column_name VARCHAR2,
accuracy_filter_value NUMBER,
must_match_columns SDO_STRING_ARRAY) For floor number and room number matching
• Currently available by installing patch 31372664 – Works on database versions 18c, 19c
25 Copyright © 2020, Oracle and/or its affiliates
Proximity and Time Based Contact Tracing
SQL Developer Demo
26 Copyright © 2020, Oracle and/or its affiliates
Find Contacts For One User ID - SEGMENT_OR_ALL = ‘ALL’
Find user 23’s contacts, within 15 meters, coincident in both time and proximity
Exclude accuracy > 8, and only return users with contact duration greater than 5 minutes
27 Copyright © 2020, Oracle and/or its affiliates
Find Contacts For One User ID - SEGMENT_OR_ALL = ‘SEGMENT’
Find user 23’s contacts, within 15 meters, coincident in both time and proximity
Exclude accuracy > 8
28 Copyright © 2020, Oracle and/or its affiliates
Two Contact Tracing SQL APIs
• NO COST – Spatial Features included in Oracle Database License
• AUTOMATIC – Automatically identifies contacts and reports duration
• SCALABLE – Leverage native Oracle Database partitioning,
parallel query, attribute clustering, spatial indexing,
and more…
29 Copyright © 2020, Oracle and/or its affiliates
Proximity and Time Based Contact Tracing
Map Visualization Component Demo
30 Copyright © 2020, Oracle and/or its affiliates
Proximity and Time Contact Tracing – Map Visualization Component Demo
31 Copyright © 2020, Oracle and/or its affiliates
Contact Tracing with APEX
32 Copyright © 2020, Oracle and/or its affiliates
Thank you
33 Copyright © 2020, Oracle and/or its affiliates
