MobilityDB is an open source software program that adds support for temporal and spatio-temporal objects to the PostgreSQL database and its spatial extension PostGIS.
This repository contains code and documentation for running the BerlinMOD benchmark on MobilityDB.
- Reviewing a pull request? See the
PR Reviewer Guide — tier ranking,
dependency chain (this repo + MobilityDB + MobilityDuck + MobilitySpark
- JMEOS) and the standards checklist. Reviewers landing in any of the five ecosystem repos find the same canonical structure at the same path.
Benchmark reports for the BerlinMOD query sets on each ecosystem
platform (MobilityDB, MobilityDuck, MobilitySpark) live in
BerlinMOD/benchmarks/. The cross-platform q01–q17 + qrt reproducer (MobilityDB + MobilityDuck + MobilitySpark) is in BerlinMOD/benchmarks/bench/. Start at the directory README, or jump directly to:
- CrossPlatform_timings.md
— cross-platform timings, with the
cross_platform_*.svgfigures. - streaming/ — the streaming benchmark (continuous / windowed / snapshot) with its figures.
- BETA_TESTING.md — entry point for testers: query files, expected row counts, report-back template.
- ThreePlatform_beta_status.md — cross-platform status.
- MobilityDB_rqueries.md — 17 R-queries × index matrix on MobilityDB.
Headline result (MobilityDB, BerlinMOD scalefactor 0.005, single run): 17 R-queries total wall-clock 334.30 s baseline → 173.23 s with GiST on trip + trajectory (~1.9× total speedup; per-query highlights up to Q14 51×, Q10 / Q15 8×, Q13 6×). Row counts identical across the three platforms.
- PostgreSQL 14 or later
- PostGIS 3.0 or later
- MobilityDB 1.1 or later
- pgRouting (for data generation)
- osm2pgrouting and osm2pgsql (for importing OSM road network data)
Create the database and load the road network:
createdb berlinmod
psql -d berlinmod -c 'CREATE EXTENSION MobilityDB CASCADE'
psql -d berlinmod -c 'CREATE EXTENSION pgRouting'Import OSM data for Brussels (or another city) using osm2pgrouting and
osm2pgsql:
osm2pgrouting -f brussels.osm --dbname berlinmod -c BerlinMOD/mapconfig.xml
osm2pgsql -c -d berlinmod brussels.osmPrepare the road network graph:
psql -d berlinmod -f BerlinMOD/brussels_preparedata.sqlAlternatively, use the optimized graph builder:
psql -d berlinmod -f BerlinMOD/brussels_creategraph.sqlGenerate BerlinMOD synthetic data:
Load the data generator and call it with a scale factor:
\i BerlinMOD/berlinmod_datagenerator.sql
SELECT berlinmod_datagenerator(scaleFactor := 0.005);Generate Deliveries synthetic data:
\i BerlinMOD/deliveries_datagenerator.sql
SELECT deliveries_datagenerator(scaleFactor := 0.005);Run all steps with the shell script:
cd BerlinMOD
bash berlinmod_runall.shRun benchmark queries:
After loading data (see Generated datasets below for pre-generated CSV files), execute the benchmark queries:
-- Chapter 1 ad-hoc queries (range, temporal aggregate, distance)
\i BerlinMOD/berlinmod_chapter1_queries.sql
-- Range queries (17 BerlinMOD/R queries)
\i BerlinMOD/berlinmod_load.sql
SELECT berlinmod_R_queries(1, true);
-- Nearest-neighbor queries (9 BerlinMOD/NN queries)
SELECT berlinmod_NN_queries(1, true);Load pre-generated CSV data:
\i BerlinMOD/berlinmod_load.sql
SELECT berlinmod_load('/path/to/csv/files/', true);BerlinMOD queries can run unchanged on all three platforms of the MobilityDB ecosystem using the portable SQL dialect (named functions only, no platform-specific operator symbols):
| Platform | Engine | Extension |
|---|---|---|
| MobilityDB | PostgreSQL | CREATE EXTENSION mobilitydb |
| MobilityDuck | DuckDB | LOAD mobilitydb |
| MobilitySpark | Apache Spark | MobilitySparkSession.create(spark) |
Run portable Chapter 1 queries (Q1–Q6) on MobilityDB:
\i BerlinMOD/berlinmod_chapter1_queries_portable.sqlExport data for MobilityDuck / MobilitySpark:
The berlinmod_portability_export() function writes five CSV files in the
shared cross-platform schema:
\i BerlinMOD/berlinmod_export.sql
SELECT berlinmod_portability_export('/path/to/output/');This produces:
| File | Contents |
|---|---|
vehicles.csv |
vehId, licence, type, model |
trips.csv |
tripId, vehId, trip — tgeompoint as WKT text |
query_licences.csv |
licenceId, licence |
query_instants.csv |
instantId, instant |
query_points.csv |
pointId, geom — geometry as WKT text |
These files can be loaded directly by the MobilitySpark cross-platform test
runner (berlinmod/run_mbdb.sh, run_mduck.sh) as described in the
MobilitySpark repository.
After running the benchmark queries, compare results against expected output to validate correctness. The documentation (see below) specifies expected result counts for each query at each scale factor.
Generate the documentation from source to obtain the full reference:
cd docs
dblatex -s texstyle.sty -T native -t pdf -o mobilitydb-berlinmod.pdf mobilitydb-berlinmod.xmlPre-generated documentation is available online:
- HTML: https://mobilitydb.github.io/MobilityDB-BerlinMOD/html/index.html
- PDF: https://mobilitydb.github.io/MobilityDB-BerlinMOD/mobilitydb-berlinmod.pdf
- EPUB: https://mobilitydb.github.io/MobilityDB-BerlinMOD/mobilitydb-berlinmod.epub
The generator produces two benchmark scenarios:
BerlinMOD — vehicles moving through the Brussels road network.
| Scale Factor | Vehicles | Days | Trips | File | Size |
|---|---|---|---|---|---|
| SF 0.1 | 632 | 11 | 18,910 | brussels_sf0.1.zip | 5.5 MB |
| SF 0.2 | 894 | 15 | 35,319 | brussels_sf0.2.zip | 9.6 MB |
| SF 0.5 | 1,414 | 22 | 81,584 | brussels_sf0.5.zip | 2.2 GB |
| SF 1 | 2,000 | 30 | 157,565 | brussels_sf1.zip | 4.2 GB |
Deliveries — vehicles making deliveries from warehouses to customers.
| Scale Factor | Warehouses | Vehicles | Customers | Days | Deliveries | File | Size |
|---|---|---|---|---|---|---|---|
| SF 0.1 | 32 | 632 | 3,162 | 11 | 6,320 | deliveries_sf0.1.zip | 1.4 GB |
| SF 0.2 | 45 | 894 | 4,472 | 15 | 11,622 | deliveries_sf0.2.zip | 2.6 GB |
| SF 0.5 | 71 | 1,414 | 7,071 | 22 | 26,866 | deliveries_sf0.5.zip | 6.1 GB |
| SF 1 | 100 | 2,000 | 10,000 | 30 | 26,866 | deliveries_sf1.zip | 11.8 GB |
Docker container:
A Docker image with all dependencies pre-installed is available:
docker pull mobilitydb/mobilitydb:15-3.4-1.1-BerlinMOD
docker volume create mobilitydb_data
docker run --name mobilitydb -e POSTGRES_PASSWORD=mysecretpassword \
-p 25432:5432 -v mobilitydb_data:/var/lib/postgresql \
-d mobilitydb/mobilitydb:15-3.4-1.1-BerlinMOD
psql -h localhost -p 25432 -U postgresBerlinMOD scripts are available in the BerlinMOD/ directory inside the
container. See the Docker documentation for further details.
MobilityDB-BerlinMOD/
├── BerlinMOD/
│ ├── berlinmod_datagenerator.sql # BerlinMOD data generator
│ ├── deliveries_datagenerator.sql # Deliveries data generator
│ ├── berlinmod_load.sql # Load pre-generated CSV data
│ ├── deliveries_load.sql # Load pre-generated deliveries CSV
│ ├── berlinmod_export.sql # Export data to CSV (incl. berlinmod_portability_export)
│ ├── deliveries_export.sql # Export deliveries data to CSV
│ ├── berlinmod_chapter1_queries.sql # Ad-hoc benchmark queries
│ ├── berlinmod_chapter1_queries_portable.sql # Portable dialect queries
│ ├── berlinmod_r_queries.sql # BerlinMOD/R range queries
│ ├── berlinmod_r_queries_citus.sql # Range queries for Citus
│ ├── berlinmod_nn_queries.sql # BerlinMOD/NN nearest-neighbor queries
│ ├── berlinmod_d_vehicleid.sql # Citus distribution by vehicle ID
│ ├── berlinmod_runall.sh # Shell script to run all steps
│ ├── brussels_preparedata.sql # Prepare Brussels road network
│ └── brussels_creategraph.sql # Optimized graph builder
├── docs/ # DocBook documentation source
├── docker/ # Docker setup files
└── README.md
This repository uses a single perennial master branch — the same model
as MobilityDB itself:
- Fork this repository.
- Create a feature or fix branch on your fork.
- Open a pull request against
MobilityDB/MobilityDB-BerlinMOD:master.
There are no long-lived release branches; tags mark stable snapshots.
The SQL scripts in this repository are provided under the PostgreSQL License.
The documentation of this benchmark is licensed under a Creative Commons Attribution-Share Alike 3.0 License.