CYBERTEC PostgreSQL Logo

Intersecting GPS-Tracks to identify infected individuals

04.2020 / Category: / Tags:

In times of COVID-19, governments contemplate tough measures to identify and trace infected people. These measures include the utilization of mobile phone data to trace down infected individuals and subsequently contacts to curb the epidemic. This article shows how PostGIS’ functions can be used to identify “overlapping” sections of infected and healthy individuals by analysing tracks spatio-temporally.
This time we don’t focus on performance and tuning, rather strive boosting your creativity on PostgreSQL’s spatial extension and its functionalities.

The article is structured as follows:

  1. Set up data structures within PostgreSQL
  2. Set up sample tracks via QGIS
  3. Segment sample tracks to retrieve induvial track points
  4. Intersection of infected individual to retrieve possible contacts

Data structures of GPS tracks

Let’s start by defining tables representing tracks and their points.
Table mobile_tracks acts as a helper table storing artificial tracks of individuals, which have been drawn via QGIS.
Table mobile_points stores points, which result from segmenting tracks. Finally, they have been enriched by timestamps, which are used to identify temporal intersections on top of spatial intersections. 

The test area is located in Vienna/Austria; therefore I chose MGI/Austria 34 (EPSG as 31286) as appropriate projection.

GPS tracks and points

As mentioned in the beginning, for points I decided to generate artificial ones from tracks, I previously digitized via QGIS. Figure 1 shows tracks of infected people in red, healthy individuals are represented as green line strings as foundation.

GPS tracks of healthy and infected individuals, GPS-Tracks
Figure 1 GPS tracks of healthy and infected individuals

For our simple example, I made the following assumptions:

  • Individuals are moving with the same speed
  • Tracks of individuals start at the same time

To extract individual points for tracks, I utilized PostGIS’s ST_Segmentize function as follows:

The query creates points every meter, timestamps are subsequently increased by 1000 milliseconds.

Extracted GPS-Points, GPS-Tracks
Figure 2 Extracted GPS-Points

Identification of intersection points

Now it’s time to start with our analysis. Did our infected individual meet somebody?
Let’s start with an easy approach and select points of healthy individuals, which have been within 2 meters to infected individuals while respecting a time interval of 2 seconds. 

Figure 3 and 4 show results for the given query by highlighting points of contact for our individuals in blue. As mentioned before, this query covers the most basic solution to identify people who met.
Alternatively, PostGIS’ ST_CPAWithin and ST_ClosestPointOfApproach functions could be used here as well to solve this in a similar manner. To do so, our points must get modelled as trajectories first.

Contact points, GPS-Tracks
Figure 3 Contact points
Contact points - Zoom
Figure 4 Contact points - Zoom

To refine our solution, let’s identify temporal coherent segments of spatial proximity and respective passage times. The goal is to understand, how long people have been close enough together to inherit possible infections more realistically. The full query is attached to the end of the post. 

Step 1 - aggregStep1

Based on our first query results, for each point we calculate the time interval to its predecessor. Considering our initial assumptions, a non-coherent temporal segment will lead to gaps >1 second.

Temporal gaps between gps points, GPS-Tracks
Figure 5 Temporal gaps between gps points

Step 2 – aggregStep2

With our new column indicating the temporal gap between current and previous point, we cluster segments 

Step 3 – aggregStep3

For each cluster, we subtract min from max timestamp to extract a passage interval.

Coherent segments + passage time by infected/healthy customer, GPS-Tracks
Figure 6 Coherent segments + passage time by infected/healthy customer

Step 4

Finally, for each combination of infected/healthy individual, the segment with the maximum passage time is being extracted and a linestring is generated by utilizing st_makeline (see figure 7).

Max passage time by infected/healthy customer
Figure 7 Max passage time by infected/healthy customer

Even though results can serve as foundation for further analysis, our approach still remains constrained by our initially taken assumptions. 

Recently, a very promising PostgreSQL extension named MobilityDB has been published, which offers a lot of functionalities to solve all kinds of spatio-temporal related questions. Take a look at this blogpost to learn more.

Leave a Reply

Your email address will not be published. Required fields are marked *

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram