Spatiotemporal pattern queries

Abstract

This paper presents a novel approach to express and evaluate the complex class of queries in moving object databases called spatiotemporal pattern queries (STP queries). That is, one can specify temporal order constraints on the fulfillment of several predicates. This is in contrast to a standard spatiotemporal query that is composed of a single predicate. We propose a language design for spatiotemporal pattern queries in the context of spatiotemporal DBMSs. The design builds on the well established concept of lifted predicates. Hence, unlike previous approaches, patterns are neither restricted to specific sets of predicates, nor to specific moving object types. The proposed language can express arbitrarily complex patterns that involve various types of spatiotemporal operations such as range, metric, topological, set operations, aggregations, distance, direction, and boolean operations. This work covers the language integration in SQL, the evaluation of the queries, and the integration with the query optimizer. We also propose a simple language for defining the temporal constraints. The approach allows for queries that were never available. We provide a complete implementation in C+ + and Prolog in the context of the Secondo platform. The implementation is made publicly available online as a Secondo Plugin, which also includes automatic scripts for repeating the experiments in this paper.

Appendices

A The Expr1Script.sec file

This is a commented version of the Expr1Script.sec script.

The script runs the first experiment with minimal user interaction. The experiment, as described in Section 9.1, is intended to evaluate the execution overhead of the STP predicates. This script first creates the required database objects, then executes the queries and logs the run times.

$$\begin{array}{lll}&&\texttt{ close database;}\\&&\texttt{ open database berlintest;}\\&&~\\&&\texttt{ let mb1 = randommbool(now( ));}\\&& \qquad\quad \vdots\\&&\texttt{ let mb30 = randommbool(now( ));}\\\end{array}$$

The commands open the database berlintest and creates 30 random mbool objects with the names mb1... mb30. These objects are needed for the queries. The randommbool operator works as described in Section 9.1.1.

$$\begin{array}{lll}&&\texttt{ let later = vec("aabb", "a.abb", "aab.b", "a.ab.b");}\\&&\texttt{ let follows = vec("aa.bb", "a.a.bb", "aa.b.b", "a.a.b.b");}\\&&\texttt{ let immediately = vec("a.bab", "a.bba", \ldots}\\&&\texttt{ let meanwhile = vec( \ldots}\\&&\texttt{ let then = vec( \ldots}\end{array}$$

The five vector temporal connectors are used in the queries as examples for vector temporal connectors. They are used together with the 26 simple temporal connectors to generate the queries.

$$\begin{array}{lll}&&\texttt{ let STPQExpr1Query=}\\&&\;\;\;\texttt{ [const rel(tuple([no:int, queryText: text,}\\&&\qquad\;\;\texttt{ numPreds: int, numConstraints: int])) value ()]}\\&&\;\;\;\texttt{ csvimport['STPQExpr1Query.csv', 0, "", "\$"] consume;}\end{array}$$

The query imports the experiment queries from the comma separated file STPQExpr1Query.csv and stores them in a Secondo relation called STPQExpr1Query. The [const . value .] operator tells the cvsimport operator the schema of the relation, which is shown in Table 7.

Table 7 The schemas of the STPQExpr1Query.csv file and the STPQExpr1Query table

The file contains 4,900 queries that were randomly generated as described in Section 9.1.2. The queries represent 49 experimental settings, each of which have 100 queries. The following query executes them and logs the results in the relation STPQExpr1Result:

$$\begin{array}{lll}&&\texttt{let STPQExpr1Result =}\\&&\;\;\;\texttt{STPQExpr1Query feed}\\&&\;\;\;\texttt{loopjoin[fun(queryTuple: TUPLE)}\\&&\;\;\quad\texttt{evaluate(attr(queryTuple, queryText))}\\&&\;\;\;\texttt{project[ElapsedTimeReal, ElapsedTimeCPU]]}\\&&\;\;\;\texttt{consume;}\end{array}$$

This query can take half an hour depending on your machine. You can query the results relation in any of the Secondo user interfaces [24] and create aggregations for the charts. Additionally, the following query exports the relation to the comma separated file STPQExpr1Result.csv in the Secondo bin directory.

$$\begin{array}{lll} &&\texttt{query STPQExpr1Result feed}\\&&\;\;\;\texttt{projectextend[; Serial: .no,}\\&&\qquad\;\texttt{NumberOfPredicates: .numPreds,}\\&&\qquad\;\texttt{NumberOfConstraints: .numConstraints,}\\&&\qquad\;\texttt{ResponseTime: .ElapsedTimeReal,}\\&&\qquad\;\texttt{CPUTime: .ElapsedTimeCPU]}\\&&\texttt{csvexport[$^\prime$STPQExpr1Result.csv$^\prime$, FALSE, TRUE]}\\&&\texttt{count}\end{array}$$

NOTE We encourage the reader to get information about the Secondo operators by using the built-in operator descriptions. For example, to get help on the operator csvimport, write the following query at the Secondo prompt:

$$\begin{array}{lll}&&\texttt{query SEC2OPERATORINFO feed}\\&&\;\;\;\texttt{filter[.Name contains "csvimport"]}\\&&\texttt{consume}\end{array}$$

B The Expr2Script.sec file

This is a commented version for the Expr2Script.sec script.

The script is used to generate the data required for running the second experiment in this paper without executing the queries. The queries need to be executed in the SecondoPL environment afterwards.

$$\begin{array}{lll}&&\texttt{close database;}\\&&\texttt{open database berlintest;}\end{array}$$

$$\begin{array}{lll}&&\texttt{let RestaurantsNumbered =}\\&&\;\;\;\texttt{Restaurants feed addcounter[no, 1] head[300] consume;}\\&&\texttt{let point1 =}\\&&\;\;\;\texttt{RestaurantsNumbered feed filter[.no = 1]}\\&&\qquad\texttt{extract[geoData];}\\&&\;\;\vdots\\&&\texttt{let point300 =}\\&&\;\;\;\texttt{RestaurantsNumbered feed filter[.no = 300]}\\&&\qquad\texttt{extract[geoData];}\\&&\texttt{delete RestaurantsNumbered;}\\\end{array}$$

First, the commands open the database berlintest. The geometries of the first 300 restaurants in the Restaurants table are then copied to point objects (point1... point300) to be used in the queries.

$$\begin{array}{lll}&&\texttt{let later = vec("aabb", "a.abb", "aab.b", "a.ab.b");}\\&&\texttt{let follows = vec("aa.bb", "a.a.bb", "aa.b.b", "a.a.b.b");}\\&&\texttt{let immediately = vec("a.bab", "a.bba", ...}\\&&\texttt{let meanwhile = vec( ...}\\&&\texttt{let then = vec( ...}\\\end{array}$$

The five vector temporal connectors, that are also created in Expr1Script.sec, are included here so that the two experiments can be run independently.

$$\begin{array}{lll}&&\texttt{let Trains20 = thousand feed head[20]}\\&&\qquad\;\;\texttt{Trains feed product consume;}\\\end{array}$$

This query creates the Trains20 relation by replicating the tuples of the Trains relation 20 times. In the following query, we create an index on the Trains20 relation to test the proposed STP predicate optimization. The index is a spatial R-tree on the units of the Trip attribute. Instead of indexing the complete movement, the index is built on the units (i.e. a bounding box is computed for every unit in the Trip). This is done so that the bounding boxes better approximate the moving point.

$$\begin{array}{lll}&&\texttt{let Trains20\_Trip\_sptuni =}\\&&\;\;\;\texttt{Trains20 feed }\\&&\qquad\texttt{projectextend[Trip; TID: tupleid(.)]}\\&&\qquad\texttt{projectextendstream[TID; MBR: units(.Trip)}\\&&\qquad\;\;\texttt{use[fun(U: upoint) bbox2d(U) ]]}\\&&\qquad\texttt{sortby[MBR asc] }\\&&\texttt{bulkloadrtree[MBR]; }\end{array}$$

C The expr2Queries.pl file

This Prolog file is used to run the queries of the second experiment and log the execution times. It defines four prolog predicates:

• 1. runSTPQExpr2DisableOptimization/0: switches off STP predicate optimization by setting the optimizer options, and executes the queries.

• 2. runSTPQExpr2EnableOptimization/0: switches on STP predicate optimization, and executes the queries.

• 3. executeSQL/4: helper predicate for executing queries.

• 4. runSTPQExpr2/4: the facts table that stores the queries. The file contains 490 such facts, 10 queries for each of the 49 experimental settings. The queries are randomly generated as described in Section 9.2.2. For every query, the fact also stores its serial, number of lifted predicates, and number of constraints.

D Running the Berlintest application example

To execute the queries in the berlintest example, you need first to run the script BerlintestScript.sec from the SecondoTTYNT prompt. The script is installed within the STPattern Plugin. You also need to have the berlintest database restored in your system. The script file creates the required database objects but it doesn’t execute the queries. It first defines some temporal connectors:

$$\begin{array}{lll}&&\texttt{ close database;}\\&&\texttt{ open database berlintest;}\\&&\texttt{ let later= vec("aabb", "a.abb", "aab.b", "a.ab.b");}\\&&\texttt{ let follows= vec(\ldots}\\&&\texttt{ let immediately= vec(\ldots}\\&&\texttt{ let meanwhile= vec(\ldots}\\&&\texttt{ let then= vec(\ldots}\\&&\texttt{ let together= vec(\ldots}\end{array} $$

Then it restores the SnowStorms relation from the SnowStorms file in the Secondo/bin directory, which is installed with the Plugin.

$$\texttt{ restore SnowStorms from SnowStorms;}$$

The following command creates the relation TrainsMeet, that is used in the example in Section 10.2.3. Every tuple in the relation is a different combination of an up train, down train of the same line, and the stations where the train line stops.

$$\begin{array}{lll} &&\texttt{let TrainsMeet =}\\&&\;\;\;\texttt{ Trains feedproject[Line, Trip, Up] \{t2\}}\\&&\;\;\;\;\;\texttt{ filter[.Up\_t2 = FALSE]}\\&&\;\;\;\texttt{ Trains feedproject[Line, Trip, Up] \{t1\}}\\&&\;\;\;\;\;\texttt{filter[.Up\_t1 = TRUE]}\\&&\;\;\;\texttt{ hashjoin[Line\_t2 , Line\_t1 , 99997]}\\&&\;\;\;\texttt{ extend[Line: .Line\_t1, Uptrip: .Trip\_t1,}\\&&\;\;\;\;\;\texttt{ Downtrip: .Trip\_t2,}\\&&\;\;\;\;\;\;\texttt{ Stations: ((breakpoints(.Trip\_t1,}\\&&\qquad\qquad\;\texttt{ create\_duration(0,5000) )}\\&&\qquad\qquad\;\texttt{ union val(initial(.Trip\_t1)))}\\&&\qquad\qquad\;\texttt{ union val(f\/inal(.Trip\_t1)))]}\\&&\;\;\;\texttt{ project[Line, Uptrip, Downtrip, Stations]}\\&&\;\;\;\texttt{ consume;}\\\end{array}$$

Next we create the relation TrainsDelay, used in the example in Section 10.2.4. Every tuple has a schedule and an actual moving point. The schedule movement is a copy from the Trip attribute in the Trains relation. The actual movement should have delays of about half an hour. We shift the Trip 1,795 s forward, and apply a random positive or negative delay up to 10 s to the result. This creates actual movements with random delays between 29:45 and 30:05 min.

$$\begin{array}{lll}&&\texttt{ let TrainsDelay=}\\&&\;\;\;\texttt{ Trains feed}\\&&\;\;\;\texttt{ extend[Schedule: .Trip,}\\&&\qquad\;\texttt{ Actual: randomdelay(}\\&&\qquad\;\texttt{ .Trip translate[create\_duration(0, 1795000) ,}\\&&\qquad\;\texttt{ 0.0, 0.0], create\_duration(0,10000) ) ]}\\&&\;\;\;\texttt{ project[Id, Line, Actual, Schedule]}\\&&\;\;\;\texttt{ consume;}\\\end{array} $$

After running the BerlintestScript.sec script, use the Javagui to execute the queries. It is the graphical user interface for Secondo. To launch it:

• 1. Start the Secondo kernel in server mode, the optimizer server, and the GUI:

  In a new shell, go to $SECONDO_BUILD_DIR/bin, and type SecondoMonitor -s.

  In a new shell, go to $SECONDO_BUILD_DIR/Optimizer, and type StartOptServer.

  In a new shell, go to $SECONDO_BUILD_DIR/Javagui, and type sgui. The Javagui will start and connect to both the kernel and the optimization server.

• 2. Open the database. In the Javagui type:

  open database berlintest.

• 3. Set the optimizer options. The Secondo optimizer maintains a list of options that controls the optimization. The examples in this paper require the options improvedcosts, determinePredSig, autoSamples, rewriteInference, rtreeIndexRules, and autosave. To set each of these options, type in the Javagui:

  optimizer setOption(option)

• 4. View the underlying network. Type:

  select * from ubahn to display the underground trains network.

  select * from trains to display the moving trains. Use the slider to view the results.

  Select the last query in the top-right panel and press hide to hide the trains.

  select * from snowstorms to display the moving snow storms.

  hide the snow storms.

• 5. Type the example queries as in Section 10.2, and make sure to type everything in lower case.