Intersecting Tracks of individuals – MobilityDB

MobilityDB is a PostgreSQL extension layered on top of PostGIS that targets the processing and analysis of spatio-temporal data. It contributes additional types and functions to PostGIS so that spatial-temporal questions can be answered directly in the database. At the time of writing it is available for PostgreSQL 11 and PostGIS 2.5 as a v1.0-beta, with a first release expected in early 2020. The codewit/mobility Docker container is the quickest way to get started.

Setting up the trips

The starting point is the mobile_points table, which holds points of individuals. It is used to build trajectories, and those trajectories are stored in mobile_trips using MobilityDB's tgeompoints data type. Instances of traj are derived from a trip's geometry and used for visualization.

PgSQL

PgSQL

Generating trips from points

Trips are produced from the stored points with the following statement:

PgSQL

Each customer gets a trip expressed as a sequence of instants of tgeompoint, MobilityDB's continuous temporal type. Such a sequence interpolates spatio-temporally between the fulcrums. The traj visualization of the resulting trip geometries is shown in Figure 1.

Finding overlapping segments

The analysis begins by identifying segments where individuals overlap in space and time. The query below returns overlapping segments within 2 meters as geometries, grouped by customer pairs. Expanded bounding boxes are intersected first so that spatio-temporal indexes on the trips can be used.

PgSQL

Whether two trips overlap spatio-temporally is then decided by filtering:

PgSQL

getTime returns a detailed temporal profile of the overlapping sections as a set of periods, constrained by tdwithin. A period is a MobilityDB type derived from tstzrange. To keep only the intersecting spatial segments, periodset restricts the trip through atPeriodSet, and getValues then extracts the geometries from the tgeompoint that atPeriodSet returns.

This is not yet the final result. Disjoint trip segments yield multi-geometries (Figure 2 shows two disjoint segments for customers 1 and 2), so passage times per disjoint segment and customer require splitting those multi-geometries first with st_dump. The periods of the periodset must then be matched to the resulting simple, disjoint geometries:

1. Turn the periodset into an array of periods.
2. Flatten the array with unnest.
3. Call timespan on the periods to collect passage times per disjoint segment.

PgSQL

An improved variant that extracts passage times per disjoint trip segment using MobilityDB functions only is attached at the end of the article.

Visualizing the contacts

The figure below shows the results of both queries, with contact segments for the individuals highlighted in blue and labelled with their passage times.

These results match those of the earlier approach.

Removing fulcrums, changing speeds

Because tgeompoint interpolates between instants, trips can be regenerated from a reduced set of points and still produce meaningful results. After removing a number of fulcrums, Figure 3 shows the generalized point sequence and Figure 4 the resulting trips, whose visualization indicates that the interpolation behaved as expected. Despite the removed fulcrums, the passage times still agree with the initial assessment (Figure 5).

Another step is to alter the customers' speeds by manipulating the timestamps of points at the beginning of one of the trips only (Figures 6 and 7). Figure 8 illustrates the effect on the results.

The examples here only scratch the surface of MobilityDB's capabilities. The main contributors are Esteban Zimanyi and Mahmoud Sakr.

PgSQL