CYBERTEC PostgreSQL Logo

Intersecting Tracks of individuals – MobilityDB

04.2020 / Category: / Tags:

Last time I announced we would check out MobilityDB to improve our approach to extract overlapping passage times of healthy and infected individuals – here we go!

MobilityDB itself is a PostgreSQL extension built on top of PostGIS, specializing on processing and analysing spatio-temporal data. To do so, the extension adds a bunch of types and functions on top of PostGIS to solve different kinds of spatial-temporal questions.

Please check out Documentation (mobilitydb.com) to get an impression of what you can expect here.
The extension is currently available for PostgreSQL 11, PostGIS 2.5 as v1.0-beta version, whereby I understood from the announcements that we can definitely expect a first version to be released in early 2020. To quick start, I definitely recommend using their docker container codewit/mobility.

The blog-post is structured as follows:

  1. Set up data structures within PostgreSQL (MobilityDB enabled)
  2. Set up trips based on our initial mobile_points
  3. Intersection of infected individual to retrieve possible contacts

Data structures of MobilityDB

As reminder, table mobile_points is a relic of our first blog-post and contains points of individuals. This table and its contents will be used to set up trajectories forming our trips. Table mobile_trips represent trips of individuals, whereas trips are modelled as trajectories utilizing MobilityDB’s tgeompoints data type. Instances of traj are generated from trip’s geometry and subsequently used for visualization.

Trip generation

Let’s start by generating trips out of points:

For each customer, a trip as sequence of instants of tgeompoint is generated. tgeompoint acts as continuous, temporal type introduced by MobilityDB. A sequence of tgeompoint interpolates spatio-temporally between our fulcrums. Here I would like to refer to MobilityDB’s documentation with emphasis on temporal types to dig deeper.

Figure 1 shows, not surprisingly, a visualization of resulting trip geometries (traj).

Figure 1 Customer trips as sequence of tgeompoint instants with mobilitydb
Figure 1 Customer trips as sequence of tgeompoint instants

Analysis and results MobilityDB

Let’s start with our analysis and identify spatio-temporally overlapping segments of individuals. The following query returns overlapping segments (within 2 meters) by customer pairs represented as geometries.

To do so and to utilize spatio-temporal indexes on trips, expanded bounding-boxes are intersected first.

Next, we evaluate if trips overlap spatio-temporally by filtering:

getTime returns a detailed temporal profile of overlapping sections as set of periods constrained by tdwithin. A period is hereby a custom type introduced by MobilityDB, which is a customized version of tstzrange. Subsequently to extract intersecting spatial segments of our trip only, periodset restricts our trip by utilizing atPeriodSet. Finally, getValues extracts geometries out of tgeompoint returned by atPeriodSet.

But we’re not done. Multiple disjoint trip segments result in multi-geometries (Figure 2, 2 disjoint segments for customer 1 and 2). To extract passage times by disjoint segment and customer, multi-geometries must be “splitted up” first. This action can be carried out utilizing st_dump. To extract passage times for resulting simple, disjoint geometries, periods of periodset must be related accordingly.

This can be accomplished as follows:

First we turn our periodset into an array of periods. Next, we flatten the array utilizing unnest.
In the end, we call timespan on periods to gather passage times by disjoint segment.

Please find attached to the end of the article an even more elegant, improved way to extract passage times by disjoint trip segment utilizing MobilityDB’s functions only.

Visualization of results

The image below now shows results for both queries by highlighting segments of contact for our individuals in blue, labelled by its passage times.

Figure 2 Overlapping segments of healthy/infected individuals with mobilitydb
Figure 2 Overlapping segments of healthy/infected individuals

So far so good – results correspond with our initial approach from my last blogpost.

What happens if we now remove some of our fulcrums and re-generate our trips?

Remember what I mentioned in the beginning regarding interpolation?

Figure 3 represents our generalized sequence of points, Figure 4 presents resulting trips, whose visualization already indicates that interpolation between points worked as expected. So even though we removed a bunch of fulcrums, resulting passage times correspond with our initial assessment (see figure 5).

Figure 3 Generalized points
Figure 3 Generalized points
Figure 4 Generated trips
Figure 4 Generated trips
Figure 5 Overlapping segments of healthy/infected individuals, reduced input set, mobilitydb
Figure 5 Overlapping segments of healthy/infected individuals, generalized points

Let’s go one step further...

and change the customers' speeds by the manipulation point’s timestamps in the beginning of one of ours trips only (figure 6 and 7). Figure 8 gives an impression, how this affects our results.

Figure 6 Generalized points
Figure 6 Generalized points
Figure 7 Different start times
Figure 7 Different start times
Figure 8 Overlapping segments of healthy/infected individuals, diverging speeds
Figure 8 Overlapping segments of healthy/infected individuals, diverging speeds

I just scratched the surface to showcase MobilityDB’s capabilities, but hopefully I made you curious enough to take a look by yourselves.
I (to be honest), already have felt in love with this extension and definitely will continue exploring.

Big respect for this great extension and special thanks goes to Esteban Zimanyi and Mahmoud Sakr, both main contributors, for their support!

Check out my other GIS posts:

You may also be interested in free OpenStreetMap data:


In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook or LinkedIn.

One response to “Intersecting Tracks of individuals – MobilityDB”

  1. Hi, I have a question.
    By TDWITHIN you define a third parameter used as a distance threshold. But, how tdwithin considere time span?

    Thank you

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