Example for spatiotemporal searching

In this example, you create and load a time series that contains location data. You start spatiotemporal indexing and then run spatiotemporal searches.

About this task

The following table lists the time series properties that are used in this example.

Table 1. Time series properties used in this example
Time series property	Definition
Timepoint size	1 second
When timepoints are valid	Any second, with no invalid times
Data in the time series	Timestamp FLOAT column for longitude readings in the spatial reference system 4326 FLOAT column for latitude readings in the spatial reference system 4326 FLOAT column for altitude readings FLOAT column for velocity readings
Time series table	An object ID column of type VARCHAR(60) A TimeSeries data type column A type of vehicle column of type VARCHAR(60) A color of the vehicle column of type VARCHAR(60) An owner name of the vehicle column of type VARCHAR(60)
Origin	2014-01-01 00:00:00.00000
Regularity	Irregular
Metadata	No metadata
Where to store the data	In a container that you create
How to load the data	Through a loader program

Procedure

To create a time series for moving objects and run spatiotemporal queries:

Create a calendar that is named cal_1s by running the following SQL statement:

INSERT INTO Calendartable (c_name, c_calendar) values ('cal_1s',
        'startdate(2014-01-01 00:00:00.00000), 
         pattstart(2014-01-01 00:00:00.00000), 
         pattern({1 on} second)');

Create a TimeSeries subtype that is named rt_track in a database by running the following SQL statement:
```
CREATE ROW TYPE rt_track(
            tstamp DATETIME YEAR TO FRACTION(5),
            longitude FLOAT,
            latitude FLOAT,
            altitude FLOAT,
            velocity FLOAT);
```
This example is using the default TimeSeries subtype for spatiotemporal searching. Therefore, the longitude and latitude columns are FLOAT data types and in the second and third columns, respectively. Otherwise, you can identify the location of your position data when you start spatiotemporal indexing.

Create a time series table that is named t_vehicle by running the following SQL statement:

CREATE TABLE t_vehicle(modid VARCHAR(60),
                    ts_track TimeSeries(rt_track),
                    type VARCHAR(60),
                    color VARCHAR(60),
                    owner VARCHAR(60),
                    PRIMARY KEY (modid) CONSTRAINT pk_modid);

Create a container that is named c_track in a dbspace by running the following SQL statement:
```
EXECUTE PROCEDURE
   TSContainerCreate('c_track', 'tsdbs1', 'rt_track', 0, 0);
```
Substitute the name of your dbspace for tsdbs1.

Create two time series instances by running the following SQL statements:

INSERT INTO t_vehicle VALUES('1001',
       'calendar(cal_1s), origin(2014-01-01 00:00:00),
       threshold(0), container(c_track), irregular', 
       'taxi', 'yellow', 'J.Q. Public');

INSERT INTO t_vehicle VALUES('2002',
       'calendar(cal_1s), origin(2014-01-01 00:00:00),
       threshold(0), container(c_track), irregular', 
       'bus', 'red', 'Municipality of Star City');

Create a pipe-delimited file in any directory with the name sts.unl that contains the following data to load:

1001|2014-06-07 20:51:28|-79.099042|43.812510|9.53|2.694|
1001|2014-06-07 20:51:30|-79.099042|43.812510|9.41|2.694|
1001|2014-06-07 20:51:31|-79.099042|43.812510|9.37|2.694|
1001|2014-06-07 20:51:32|-79.099042|43.812510|9.35|2.694|
1001|2014-06-07 20:51:33|-79.099042|43.812510|9.35|0|
1001|2014-06-07 20:51:35|-79.099042|43.812510|9.38|0|
1001|2014-06-07 20:52:42|-79.098993|43.812542|9.85|1.138|
1001|2014-06-07 20:52:43|-79.098993|43.812542|9.78|1.138|
1001|2014-06-07 20:52:45|-79.098993|43.812542|9.70|0.861|
1001|2014-06-07 20:52:46|-79.098993|43.812542|9.68|0.888|
1001|2014-06-07 20:52:47|-79.098993|43.812542|9.66|0.861|
1001|2014-06-07 20:52:48|-79.098993|43.812542|9.65|0|
1001|2014-06-07 20:52:50|-79.098993|43.812542|9.71|0|
1001|2014-06-07 20:52:51|-79.098993|43.812542|9.78|0|
1001|2014-06-07 20:52:52|-79.098993|43.812542|9.85|0|
1001|2014-06-07 20:52:53|-79.098993|43.812542|9.87|0|
1001|2014-06-10 16:49:36|-78.752405|43.922683|152.72|1|
1001|2014-06-10 16:49:37|-78.752405|43.922683|152.73|1.027|
1001|2014-06-10 16:49:38|-78.752405|43.922683|152.75|0.777|
1001|2014-06-10 16:49:40|-78.752405|43.922683|152.77|0.888|
1001|2014-06-10 16:49:41|-78.752405|43.922683|152.78|0.861|
1001|2014-06-10 16:49:42|-78.752428|43.922628|152.80|1.527|
1001|2014-06-10 16:49:43|-78.752428|43.922628|152.81|1.777|
1001|2014-06-10 16:50:00|-78.752416|43.922656|152.81|0|
1001|2014-06-10 16:51:00|-78.752416|43.922656|152.81|0|
1001|2014-06-10 16:52:00|-78.752416|43.922656|152.81|0|
1001|2014-06-10 16:53:00|-78.752416|43.922656|152.81|0|
1001|2014-06-10 16:54:00|-78.752416|43.922656|152.81|0|
1001|2014-06-10 16:55:00|-78.752416|43.922656|152.81|0|
1001|2014-06-10 16:55:36|-78.752405|43.922683|152.72|1|
1001|2014-06-10 16:55:37|-78.752405|43.922683|152.73|1.027|
1001|2014-06-10 16:55:38|-78.752405|43.922683|152.75|0.777|
1001|2014-06-10 16:55:40|-78.752405|43.922683|152.77|0.888|
1001|2014-06-10 16:55:41|-78.752405|43.922683|152.78|0.861|
1001|2014-06-10 16:55:42|-78.752428|43.922628|152.80|1.527|
1001|2014-06-10 16:55:43|-78.752428|43.922628|152.81|1.777|
1001|2014-06-10 16:55:45|-78.752482|43.922552|152.85|1.722|
1001|2014-06-10 16:55:46|-78.752482|43.922552|152.90|3.333|
1001|2014-06-10 16:55:47|-78.752555|43.922515|152.95|3.527|
1001|2014-06-10 16:55:48|-78.752555|43.922515|153.01|3.833|
1001|2014-06-10 16:55:50|-78.752717|43.922478|153.16|4.388|
1001|2014-06-10 16:55:51|-78.752778|43.922463|153.23|4.777|
1001|2014-06-10 16:55:52|-78.752842|43.922448|153.31|5.055|
1001|2014-06-10 16:55:53|-78.752908|43.922433|153.37|5.444|
1001|2014-06-10 16:55:55|-78.753053|43.922400|153.47|5.916|
1001|2014-06-10 16:55:56|-78.753130|43.922383|153.51|6.138|
1001|2014-06-10 16:55:57|-78.753205|43.922363|153.55|6.277|
1001|2014-06-10 16:55:58|-78.753285|43.922345|153.58|6.472|
1002|2015-06-13 11:51:04|-78.703865|43.919748|124.35|1.388|
1002|2015-06-13 11:51:05|-78.703923|43.919730|124.38|1.5|
1002|2015-06-14 12:34:13|-78.703923|43.919730|124.52|1.166|
1002|2015-06-14 12:34:15|-78.704003|43.919710|124.62|1.388|
1002|2015-06-14 12:34:16|-78.704003|43.919710|124.67|1|
1002|2015-06-14 12:34:17|-78.704003|43.919710|124.75|1.055|
1002|2015-06-14 12:34:18|-78.704003|43.919710|124.81|0.916|
1002|2015-06-14 12:34:20|-78.704122|43.919747|124.98|1.777|
1002|2015-06-14 12:34:21|-78.704183|43.919757|125.10|2.5|
1002|2015-06-14 12:34:22|-78.704250|43.919752|125.21|2.555|
1002|2015-06-14 12:34:23|-78.704323|43.919735|125.38|5.583|
1002|2015-06-14 12:34:25|-78.704498|43.919695|125.93|6.666|
1002|2015-06-14 12:35:00|-78.704498|43.919695|125.93|0.6|
1002|2015-06-14 12:36:00|-78.704498|43.919695|125.93|0.0|
1002|2015-06-14 12:37:00|-78.704498|43.919695|125.93|0.0|
1002|2015-06-14 12:38:00|-78.704498|43.919695|125.93|0.0|
1002|2015-06-14 12:39:00|-78.704498|43.919695|125.93|0.0|
1002|2015-06-14 12:40:00|-78.704498|43.919695|125.93|0.0|
1002|2015-06-14 12:41:00|-78.704498|43.919695|125.93|0.0|
1002|2015-06-14 12:42:00|-78.704498|43.919695|125.93|0.0|
1002|2015-06-14 12:43:23|-78.704498|43.919695|125.93|5.666|
1002|2015-06-14 12:43:24|-78.704600|43.919672|126.31|7.472|
1002|2015-06-14 12:43:26|-78.704708|43.919647|127.77|8.194|
1002|2015-06-14 12:43:27|-78.704708|43.919647|127.77|0.0|

Initialize a global context and open a database session by running the TSL_Init and the TSL_Attach functions:
```
EXECUTE FUNCTION TSL_Init('t_vehicle','rt_track');
EXECUTE FUNCTION TSL_Attach('t_vehicle','rt_track');
```
Load the data by running the TSL_Put function with an SQL statement that selects the data from the file:
```
EXECUTE FUNCTION TSL_Put('t_vehicle|rt_track',
         "FILE:path/sts.unl");
```
Substitute path with the directory for the sts.unl file.

Save the data to disk by running the TSL_FlushAll function:

BEGIN;
EXECUTE FUNCTION TSL_FlushAll('t_vehicle|rt_track');
COMMIT WORK;

Close the session and remove the global context by running the TSL_SessionClose and TSL_Shutdown functions:

EXECUTE FUNCTION TSL_SessionClose('t_vehicle|rt_track');
EXECUTE PROCEDURE TSL_Shutdown('t_vehicle|rt_track');

Start spatiotemporal search indexing by running the STS_SubtrackCreate function:
```
BEGIN WORK;
EXECUTE FUNCTION STS_SubtrackCreate('t_vehicle', 'rt_track', 
              't_vehicle_subtracks');
COMMIT WORK;
```
The subtrack table is created and populated with default properties. A Scheduler task is started with default properties.
Run any of the queries in the examples of the spatiotemoral search functions.
For example, you can run the following query for find the location of the object 1001 at a specific time:
```
SELECT STS_GetPosition(ts_track, '2014-06-07 20:51:35') 
        FROM t_vehicle 
        WHERE modid = 1001; 

(expression)  
      4326 POINT (-79.099042 43.81251) 
1 row(s) retrieved.
```
All the examples for the spatiotemporal search functions use the subtrack table and data from this tutorial.
Optional: Stop spatiotemporal indexing and remove the subtrack table by running the STS_SubtrackDestroy function:
```
BEGIN WORK;
EXECUTE FUNCTION STS_SubtrackDestroy('t_vehicle_subtracks');
COMMIT WORK;
```