Distributed MobilityDB

Distributed MobilityDB is an open-source extension for PostgreSQL tailored to handle spatial and spatiotemporal data at scale on a distributed database cluster. It provides data and query distribution for PostGIS and MobilityDB.

Table of Contents

• Key Features
• Prerequisites
• Installation
• Using Distributed MobilityDB
  • Creating Distributed Tables
• Use Cases
  • OpenStreetMap (OSM) Data
  • Automatic Identification System (AIS) Data
  • BerlinMOD Benchmark Data
  • Global Surface Summary of the Day (GSOD) Data
• Contributing
• Contact Us

Key Features

Spatiotemporal Data Partitioning

• Transform the input relation into a multirelation, preserving spatiotemporal data locality and load balancing.
• Develop a two-level (global and local) distributed indexing scheme, effectively reducing the global transmission cost and local computation cost.

Spatiotemporal Processing

• Handle a wide array of MobilityDB types including tint, tfloat, and tgeompoint, alongside PostGIS types, such as point, linestring, and polygon.
• Provide an adaptive execution engine that transforms a SQL query into a distributed query plan, which can then be executed on either a single machine or a cluster.
• Support spatial-only, temporal-only, and spatiotemporal queries, where PostGIS and MobilityDB predicates can co-exist in a single query.
• Facilitate multiple types of queries including range, kNN, intersection, and distance joins.
• Offer an execution framework that readily enables distributed processing for both PostGIS and MobilityDB functionalities.

🚧 Please note that the extension is still under development, so stay tuned for more updates and features. 🚧

Prerequisites

Dependency	Minimum Version
PostgreSQL	13
Citus	10
PostGIS	3
MobilityDB	1.1

Installation

1. Build from source (Linux)

git clone https://github.com/mbakli/DistributedMobilityDB
cd DistributedMobilityDB
mkdir build && cd build
cmake ..
make
sudo make install

2. Create the extension in PostgreSQL

CREATE EXTENSION Distributed_MobilityDB CASCADE;

Using Distributed MobilityDB

Creating Distributed Tables

The create_spatiotemporal_distributed_table() function is utilized to define a distributed table that is partitioned using a Multidimensional Tiling method. It splits the input table into several tiles stored in separate PostgreSQL tables. It can also create a Citus reference table instead (a table replicated as-is to every node, with no tiling at all) via the is_reference_table flag -- useful for smaller lookup/dimension tables that need to be joined against a distributed table without any repartitioning.

Function: create_spatiotemporal_distributed_table

Argument	Required	Description
table_name_in	Yes	Name of the input table.
table_name_out	Yes	Name of the distributed (or reference) table to create. Must not already exist.
num_tiles	No	Number of generated tiles. Defaults to 1. Ignored when is_reference_table is true -- reference tables aren't tiled -- except that any value other than 1 is rejected outright rather than silently ignored, to catch accidental misuse.
tiling_method	No	Name of the tiling method: crange, hierarchical, grid. Defaults to crange. Ignored when is_reference_table is true.
tiling_granularity	No	The tiling granularity. Defaults to the value chosen by the tiling method's granularity selection process, which picks between shape- and point-based strategies to create load-balanced tiles. Set this to customize the tiling granularity. Ignored when is_reference_table is true.
tiling_type	No	The tiling type of the tiling method: temporal, spatial, or spatiotemporal. Defaults based on the given column type. Ignored when is_reference_table is true.
colocation_table	No	Colocate the input table with another table, e.g. to create tiles based on given boundaries such as province borders. Used together with colocation_column. Ignored when is_reference_table is true.
colocation_column	No	The colocation column to use with colocation_table. Ignored when is_reference_table is true.
spatiotemporal_col_name	No	Name of the spatiotemporal/geometry column to distribute on. Defaults to the column detected automatically from the input table's type. Ignored when is_reference_table is true.
physical_partitioning	No	Whether or not to physically partition data. Defaults to true. Ignored when is_reference_table is true.
shape_segmentation	No	Whether or not to segment the input spatiotemporal column across tiles. Defaults to true. Ignored when is_reference_table is true.
is_reference_table	No	If true, skip tiling entirely and create table_name_out as a Citus reference table (a full replica of table_name_in on every node) via create_reference_table(). Defaults to false.

By utilizing the create_spatiotemporal_distributed_table() function with these arguments, you can easily create a distributed table that suits your data management needs.

Use Cases

Below are examples of well-known datasets, where Distributed MobilityDB showcases its proficiency in managing large spatiotemporal data, offering users diverse query types suitable for a wide range of applications.

Distributed MobilityDB seamlessly converts PostGIS and MobilityDB tables into distributed tables, allowing users to execute their PostGIS and MobilityDB SQL queries in a distributed manner without any need for modification.

OpenStreetMap (OSM) Data

Description: OSM data refers to geographic data collected by the OpenStreetMap community. It includes information such as roads, buildings, parks, and other features.

Download: https://download.geofabrik.de/

-- Input tables
CREATE TABLE planet_osm_polygon (
  osm_id bigint,
  way geometry(polygon),
  ...
);

CREATE TABLE planet_osm_roads (
  osm_id bigint,
  way geometry(linestring),
  ...
);

CREATE TABLE planet_osm_point (
  osm_id bigint,
  way geometry(point),
  ...
);

-- Distribute the planet_osm_polygon table into 50 tiles using the spatial column: geometry(polygon)
SELECT create_spatiotemporal_distributed_table(table_name_in => 'planet_osm_polygon', num_tiles => 50,
  table_name_out => 'planet_osm_polygon_50t', tiling_method => 'crange');

-- Distribute the planet_osm_roads table into 30 tiles using the spatial column: geometry(linestring)
SELECT create_spatiotemporal_distributed_table(table_name_in => 'planet_osm_roads', num_tiles => 30,
  table_name_out => 'planet_osm_roads_30t', tiling_method => 'crange');

-- Distribute the planet_osm_point table into 12 tiles using the spatial column: geometry(point)
SELECT create_spatiotemporal_distributed_table(table_name_in => 'planet_osm_point', num_tiles => 12,
  table_name_out => 'planet_osm_point_12t', tiling_method => 'crange');

-- Distance-Join Query: Find buildings that are built within 1km of the primary highways.
SELECT DISTINCT t1.name
FROM planet_osm_polygon_50t t1, planet_osm_roads_30t t2
WHERE t1.building = 'yes'
  AND t2.highway = 'primary'
  AND ST_DWithin(t1.way, t2.way, 1000);

-- Intersection-Join Query: Find health centers POIs in Berlin.
SELECT t2.name
FROM planet_osm_polygon_50t t1, planet_osm_point_12t t2
WHERE t2.amenity IN ('hospital', 'clinic', 'doctors')
  AND t1.name = 'Berlin'
  AND ST_Intersects(t1.way, t2.way);

Automatic Identification System (AIS) Data

Description: AIS is a tracking system used on ships and vessels to provide information about their identification, course, speed, and dynamic data such as longitude, latitude, and time.

Download: https://web.ais.dk/aisdata/

-- Input tables
CREATE TABLE ships_tanker (
  mmsi int,
  trip tgeompoint(sequence),
  ...
);

CREATE TABLE ships_fishing (
  mmsi int,
  trip tgeompoint(sequence),
  ...
);

-- Distribute the ships_tanker table into 50 tiles using the spatiotemporal column: tgeompoint(sequence)
SELECT create_spatiotemporal_distributed_table(table_name_in => 'ships_tanker', num_tiles => 50,
  table_name_out => 'ships_tanker_50t', partitioning_method => 'crange', tiling_type => 'spatiotemporal');

-- Distribute the ships_fishing table into 15 tiles using the spatiotemporal column: tgeompoint(sequence)
SELECT create_spatiotemporal_distributed_table(table_name_in => 'ships_fishing', num_tiles => 15,
  table_name_out => 'ships_fishing_15t', partitioning_method => 'crange', tiling_type => 'spatiotemporal');

-- Distance-Join Query: Find fishing ships that were within 1km of tanker ships.
SELECT t1.mmsi AS Ship1ID, t2.mmsi AS Ship2ID
FROM ships_tanker_50t t1, ships_fishing_15t t2
WHERE eDWithin(t1.trip, t2.trip, 1000);

-- Temporal Query: What is the total travelled distance of ships that spent more than 5 days to reach the port of Kalundborg in Sept 19?
SELECT mmsi AS ShipID, length(Trip) / 1000 AS travelledKms
FROM ships_tanker_50t
WHERE Destination = 'Kalundborg'
  AND Trip && Period('2019-09-01', '2019-09-30')
  AND timespan(Trip) > '5 days';

BerlinMOD Benchmark Data

Description: BerlinMOD is a standard benchmark for moving object databases: a synthetic data generator producing vehicle trip trajectories across a road network, together with the 17 standard BerlinMOD/R benchmark queries. The full set of queries, adapted to run against a distributed Trips table, is available in demo_queries/berlinmod, along with the distribution/setup script.

Download: https://github.com/MobilityDB/MobilityDB-BerlinMOD

Reference: https://github.com/MobilityDB/MobilityDB-BerlinMOD/blob/master/BerlinMOD/berlinmod_r_queries.sql

-- Input table
CREATE TABLE Trips (
  TripId int,
  VehicleId int,
  Trip tgeompoint
);

-- Distribute the trips table into 4 tiles using the spatiotemporal column: tgeompoint(sequence)
SELECT create_spatiotemporal_distributed_table(table_name_in => 'trips', num_tiles => 4,
  table_name_out => 'trips_4t', tiling_method => 'crange', tiling_type => 'spatiotemporal');

-- Query 4: Which vehicles have passed the points from Points?
SELECT DISTINCT p.PointId, p.Geom, v.Licence
FROM trips_4t t, Vehicles v, Points p
WHERE t.VehicleId = v.VehicleId
  AND ST_Intersects(trajectory(t.Trip), p.Geom)
ORDER BY p.PointId, v.Licence;

-- Query 6 (Distance-Join): What are the pairs of licence plate numbers of "trucks"
-- that have ever been as close as 10m or less to each other?
WITH Temp(Licence, VehicleId, Trip) AS (
  SELECT v.Licence, t.VehicleId, t.Trip
  FROM trips_4t t, Vehicles v
  WHERE t.VehicleId = v.VehicleId AND v.VehicleType = 'truck'
)
SELECT t1.Licence, t2.Licence
FROM Temp t1, Temp t2
WHERE t1.VehicleId < t2.VehicleId
  AND t1.Trip && expandSpace(t2.Trip, 10)
  AND eDwithin(t1.Trip, t2.Trip, 10.0)
ORDER BY t1.Licence, t2.Licence;

Global Surface Summary of the Day (GSOD) Data

Description: GSOD data is a collection of daily weather observations from weather stations around the world. It includes information such as temperature, time, location, humidity, and atmospheric pressure.

Download: https://www.ncei.noaa.gov/

-- Input tables
CREATE TABLE gsod_temp (
  loc geometry,
  temperature_tfloat tfloat(sequence),
  ...
);

-- Distribute the GSOD table into 32 tiles using the temporal float column: tfloat(sequence)
SELECT create_spatiotemporal_distributed_table(table_name_in => 'gsod_temp', num_tiles => 32,
  table_name_out => 'gsod_temp_32t', partitioning_method => 'crange', tiling_type => 'temporal');

-- Temporal Query: Identify the hottest areas observed within the past 24 hours
SELECT station, loc
FROM gsod_temp_32t
WHERE temperature_tfloat && tstzspan '[2024-01-01, 2024-01-01]'
  AND temperature_tfloat ?> 95; -- Fahrenheit

-- Aggregate Query: Retrieve the maximum temperature for each location
SELECT loc, xmax(extent(temperature_tfloat))
FROM gsod_temp_32t
GROUP BY loc;

Contributing

We are most definitely open to contributions of any kind: bug reports, feature requests, and documentation.

If you'd like to contribute code via a Pull Request, please make it against our develop branch.

Wrapping Postgres' internals to create a distributed version of MobilityDB is a complex undertaking that requires a significant amount of time and effort. However, the distributed version of MobilityDB is now available for use, and it will continue to evolve as development progresses. We welcome your feedback on how you would like to use Distributed MobilityDB and what features you would like to see added to it.

Please also see our Code of Conduct.

Contact Us

We hope you find our project helpful and easy to use! If you have any questions, comments, or concerns, please don't hesitate to reach out to us at mohamed.bakli@ulb.be.

License

Distributed MobilityDB is released under the MIT License.