Introduction
Contact Tracing is a concept that helps us identify two objects (e.g. humans, animals, trains, airplanes, etc.) that came into contact with each other.
It is specifically useful in this COVID-19 pandemic to stop its spread. For example, governments can use contact tracing for targeted lockdowns in specific areas of a city or state. This way it will not affect the whole economy of a country.
Now, the question is how do we collect this data, and is it really feasible?
In my opinion, two options could be considered for data collection which are as follows.
- Mobile Phone
- GPS Tracking Unit
For example, we can install an app that continuously tracks the location of a mobile. A database will be used to keep a record of each device. This way if someone is identified as infected by coronavirus then we can test all other individuals that came into contact with him.
Without a proper system of contact tracing, it is very difficult to stop a pandemic because it spreads exponentially. Meaning that if one person transferred the virus to 5 others then those 5 people could also infect anyone who came in contact with them.
Benefits and Uses in the Modern Era
After the quick spread of coronavirus, two tech giants Google and Apple had joined hands to create a framework for digital contact tracing. They named it as Exposure Notification.
It works on decentralized architecture and uses Bluetooth Low EnergyΒ technology to detect encounters between people. Whereas the infection reporting mechanism can be centralized and handled at an individual app level.
Basically, they invented this specification because contact tracing is one of the most efficient ways to predict infected people.
This system can easily be used in every field of life, whether it is an enterprise, school, military base, or even a marriage function.
How Can We Use in Oracle APEX
The Oracle ecosystem is equipped with everything we need to set up a contact tracing application.
First of all, the Oracle database has a free option component called βOracle Spatial and Graphβ. It adds the capabilities of generating graphs and working with them using the data inside the database.
All supported versions of the Oracle database have access to it. You can either use it in the cloud services or run on-premises.
APEX_SPATIALΒ is a package that helps us connect the features of βOracle Spatial and GraphβΒ with βOracle APEXβ. We can then easily display graphical reports based on different queries.
An interesting point is that this package comes with some utilities like data types, procedures for metadata, and functions to generate different shapes.
For example, you can make use of shapes like circles, polygons, points, and rectangles to effectively mark data on a graph, chart, map, etc. In simple words, you can add data visualization functionality right inside an Oracle APEX app.
Affordable Oracle APEX Shared Hosting in Cloud
SQL API for Proximity and Time-Based Contact Tracing
These days, every Oracle database can add the functionality of contact tracing. You just need to install patch # 31372664.
Basically, after installing this patch you will get access to an SQL API that can be used for Proximity and Time-Based Contact Tracing.
Letβs have a look at this API and its available parameters.
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,
sequence_column_name VARCHAR2,
time_column_name VARCHAR2,
date_as_number_column_name VARCHAR2,
accuracy_column_name VARCHAR2,
accuracy_filter_value NUMBER
)
The parameters from track_table_nameΒ to accuracy_column_nameΒ which are highlighted above are just there to hold your table names and column names.
This means that you can have tables and columns with any name but you have to map those names in this API using these parameters.
Usage Example:-
SELECTΒ a.in_user_id,
a.out_user_id,
round(a.duration/60,2) duration_in_minutes,
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,
a.num_contact_times, geom
FROMΒ TABLE(sdo_obj_tracing.get_all_durations(
user_id => 18,
start_time => to_date(’04/25/20 22:16′, ‘MM/DD/YY HH24:MI’),
end_time => to_date(’04/25/20 22:49′, ‘MM/DD/YY HH24:MI’),
distance => 15,
track_table_name => ‘track_data_w_accuracy’,
accuracy_filter_value => NULL)) a
WHEREΒ a.segment_or_allΒ = ‘ALL’
ORDERΒ BYΒ a.in_user_id, a.out_user_id, a.start_time;
Did you notice that we are using the API inside FROM TABLE()?
When we execute this query, what it does is that it retrieves all the users who got in contact with user # 18 within the start and end time.
Using the API in Oracle APEX
At this point, you are familiar with the contact tracing API. Itβs time to consume it using the Oracle APEX app. Basically, Oracle APEX will be used to create the user interface and retrieve the data from the database.
You can see that when we increase the distance from 5 meters to 10 meters then an encounter between user # 2261Β and user # 541Β is found. Whereas when the distance is only 5 meters then there is no match.
As this is APEX, so we can add all sorts of additional charts and reports to further summarize this data. But, the point is that the SQL API will perform the actual processing while the APEX is used to display the data in a user-friendly manner.
Letβs try an example where we check the encounters of a specific user within 5 and 10 meters.
Contact Tracing for Employees and Staff
We can’t just shut everything down and wait for this COVID-19 epidemic to end. Instead, we need to take some reasonable steps to minimize disruption.
Because without a sound economy and cash flow, a country canβt progress. They might even go Bankrupt one after the other.
So, a recommended approach is that each business organization may continue its operations. But, they must implement a contact tracing system for its employees. It could be a public office, factory, warehouse, data center, airport, railway station, port, etc.
This way, if an employee gets infected then a business can efficiently quarantine all other staff members who encounter him during a specific duration.
Its major benefit is that an enterprise-level organization doesnβt have to completely stop its operations.
This is the time for business owners to step forward and take the responsibility to implement adequate measures to stop this novel virus.
What Type of Data Should We Collect?
Letβs talk a bit more about the type of data needed for contact tracing.
Apart from personal data, we need accurate geospatial data.
In case you are not familiar with geospatial data then let me introduce you to it. Geospatial data is anything that provides some information about a location.
For example, a zip code is used to identify specific zones or cities in a country. Similarly, the longitude and latitude information of a person can give us his exact location on a map.
These days, every smartphone has a built-in GPS (Global Positioning System) tracker. It can be used by an app to programmatically collect the current coordinates (e.g. longitude and latitude) of a user.
Geospatial data is the key to effectively implementing proximity tracing. Without the exact location of a person, we canβt actually solve this complex puzzle.
Time-based and Distance Tracing
As we just saw in the example above, another important point is to keep a record of time for each individual. It will help us find out where the person was at any specific point in time.
This way, if two people are in the same area at the same time then we can calculate the distance between them to make the prediction a bit more accurate.
A point to be noted is that a small difference in time (e.g. 10 seconds) between two people could mean that they never encountered each other.
So, it is very important to make decisions based on the exact time. Otherwise, we might have to test/quarantine a lot of individuals who donβt even get in contact with the infected person.
Meaning that our contact tracing mechanism will be very inefficient. As it is just wasting resources for no reason.
Time-based trackingΒ also enables us to measure the duration of contact. If it is just for one second then the chances are that the virus may not be transferred. But, an increase in the duration of contact also increases the possibility of getting infected.
Conclusion
Different inventions have been made since the start of COVID-19. We can make use of something like the Exposure Notifications System (ENS) to collect data for contact tracing.
This data can then be used by βOracle Spatial and Graphβ to generate meaningful reports and finally integrate into Oracle APEX.
I would also suggest you may have a look at the DBSCAN (Density-Based Spatial Clustering of Applications with Noise) algorithm. It can be pretty useful in predicting individuals who might get infected by the coronavirus