Chapter 18. Writing Portable Queries

MobilityDB is used from several SQL engines: PostgreSQL through MobilityDB itself, DuckDB through MobilityDuck, Apache Spark through MobilitySpark and Apache Flink through MobilityFlink. The types and the functions are the same in all of them, but the operators are not: PostgreSQL lets MobilityDB define operators such as && or <<#, while the other engines accept only some of them or none. DuckDB, for instance, reserves # for positional column references, so it accepts no operator containing it.

For this reason every operator is also a function, which runs the same code and returns exactly the same result, and a query written with functions only runs unchanged on every engine. The bounding box overlap t1.trip && t2.trip, for example, is written stboxOverlaps(t1.trip, t2.trip), and the time position test p1.period <<# p2.period is written spanBefore(p1.period, p2.period). The sections below give the function of each operator, grouped as the chapters of each type group them.

The name of a function says what it operates on. An arithmetic or set operation takes the prefix of the family of its argument: set, span or spanset, and t for a temporal value, as in spanUnion or tAdd. A position test takes the prefix of what it compares: set, span or spanset, or, for a box or a temporal value, the type of its bounding box, tbox, stbox or tpcbox; a temporal Boolean, text or JSONB value is compared through the time span that bounds it, and so takes span. These names are also free in every engine: left and right are functions of Spark and Flink, so the position tests are named spanLeft or stboxLeft, and an ever or always comparison spells out its operation, as in eLessThan, because eLt would be read as the function elt of Spark and Flink, which ignore the case of a name.

Some operations have no operator and are written the same way everywhere: the spatial relationships (eIntersects, aIntersects, eTouches, eDwithin, …), the restrictions (atTime, atGeometry, atValue, …) and the ordering comparison cmp(a, b), which returns -1, 0 or 1.

Spark and Flink ask for a few more changes besides the functions, which the section called “Running a Query on Apache Spark and Apache Flink” describes.