STS_SubtrackCreate function

The STS_SubtrackCreate function creates the subtrack table for the specified time series and can start a Scheduler task to build the spatiotemporal index and periodically index new spatiotemporal data.

Syntax

STS_SubtrackCreate(
          base_table_name           VARCHAR(128),
          ts_column_name            VARCHAR(128),
          subtrack_name             VARCHAR(128))
returns VARCHAR(128)

STS_SubtrackCreate(
          base_table_name           VARCHAR(128),
          ts_column_name            VARCHAR(128),
          subtrack_name             VARCHAR(128),
          flags                     INTEGER DEFAULT 1)
returns VARCHAR(128)

STS_SubtrackCreate(
          base_table_name           VARCHAR(128),
          ts_column_name            VARCHAR(128),
          subtrack_name             VARCHAR(128),
          flags                     INTEGER DEFAULT 1,
          parameters                BSON)
returns VARCHAR(128)

STS_SubtrackCreate(
      base_table_name               VARCHAR(128),
      ts_column_name                VARCHAR(128),
      subtrack_name                 VARCHAR(128),
      flags                         INTEGER DEFAULT 1,
      parameters                    BSON,
      ts_data_first_timestamp       DATETIME HOUR TO SECOND DEFAULT NULL,
      ts_data_lag_to_current        INTERVAL DAY TO SECOND DEFAULT NULL,
      task_nschsessions             INTEGER DEFAULT 1,
      task_frequency                INTERVAL DAY TO SECOND DEFAULT NULL  
                                                        DEFAULT "0 01:00:00",
      task_start_time               DATETIME HOUR TO SECOND DEFAULT NULL,
      task_data_interval_to_process INTERVAL DAY TO SECOND DEFAULT NULL)
returns VARCHAR(128)

base_table_name: The name of the time series table.
ts_column_name: The name of the time series column in the time series table.
subtrack_name: The name to give the resulting the subtrack table for this time series. Must be unique in the database. Maximum length is 124 bytes.
flags (Optional): Whether to create a Scheduler task to automatically index new data:; 1 = Default. Create a Scheduler task.; 2 = Do not create a Scheduler task.
parameters (Optional): A BSON document that contains parameters for building the subtrack. If you omit the parameters document, the parameter values that are in the STS_DefaultParametersTable table are used. If the STS_DefaultParametersTable table is empty, the default parameters document is used. For the syntax of the parameters document, see Spatiotemporal indexing parameters.
ts_data_first_timestamp (Optional): The first time stamp in the time series to index. Default is the origin of the time series.
ts_data_lag_to_current (Optional): The indexing lag time. The time interval before the current time to avoid indexing. Default is the time stamp of the latest element in the time series.
task_nschsessions (Optional): The number of Scheduler tasks to start to update the index in parallel. Default is 1.
task_frequency (Optional): How frequently to index new data with the Scheduler task. Default is every hour.
task_start_time (Optional): The first time to start the Schedule task. Default is NULL, which means to start the task when the STS_SubtrackCreate function is run.
task_data_interval_to_process (Optional): The time interval in the time series to process each time that the Scheduler task is run. If this value is not the same as the value of the task_frequency parameter, the index might be incomplete. Default is that new data is subject to the ts_data_lag_to_current setting.

Usage

Run the STS_SubtrackCreate function to start the indexing process by creating the subtrack table for the time series and optionally starting a Scheduler task to index the data.

If you set the value of the flags parameter to 2 and do not create a Scheduler task, you must run the STS_SubtrackBuild function to start indexing or configure indexing with the time series loader functions.

The values of the ts_data_first_timestamp and the ts_data_lag_to_current parameters control the time range of the data to index for all indexing methods. You can change the indexing time range by running the STS_SubtrackAlterFirstTimeStamp and the STS_SubtrackAlterLagToCurrent functions.

If you set the value of the flags parameter to 1, a Scheduler task is created to start indexing the data. The Scheduler task, which has a prefix of autosts, starts at the specified time, indexes the initial set of data, and periodically indexes new data. The task prints messages in the database server message log when indexing starts and completes. You can index the data faster by starting multiple Scheduler task sessions.

You can create, remove, or change a Scheduler task by running the STS_SubtrackAlterProcessing function.

The Scheduler task is started at the time that is specified by the task_start_time parameter. The first run of the task processes the data in the time interval that is defined by the value of the ts_data_first_timestamp parameter to the time calculated by subtracting the value of the ts_data_lag_to_current parameter from the current time. Subsequent runs of the task start based on the value of the task_frequency parameter and index the data between the last end time and the earlier of the following times:

The last end time plus the value of the task_data_interval_to_process parameter
The current time minus the value of the ts_data_lag_to_current parameter

Any data that you insert with timepoints that are earlier than the last end time are not indexed.

Important: If you run the Scheduler task the first time on an empty time series, the recorded last end time is the current time minus the value of the ts_data_lag_to_current parameter. Earlier timepoints are not indexed.

Returns

A string = The name of the subtrack table.

An exception = An error.

Example: Use the default values

The following statement is run at 2016-02-01 08:00:00 creates the t_vehicle_subtrack subtrack table and starts a Scheduler task:

BEGIN WORK;
Started transaction.

EXECUTE FUNCTION STS_SubtrackCreate('t_vehicle', 'ts_track', 't_vehicle_subtrack');

(expression)  t_vehicle_subtrack

1 row(s) retrieved.

COMMIT WORK;
Data committed.

The subtrack table and the Scheduler task are created with default values. The Scheduler task runs for the first time at 08:00:00 and processes the time series data with timepoints between the origin of the time series and 2016-01-31 08:00:00. The task takes about 30 minutes to index the data. The task runs again at 09:00:00 and indexes data with timepoints between 2016-01-31 08:00:00 and 2016-01-31 09:00:00. Any data with timepoints earlier than 2016-01-31 08:00:00 that was inserted after the first task was run is not indexed.

Example: Set custom spatiotemporal data definitions

The following statement creates the ts_vehicle_subtrack table and sets custom values to configure trajectories, but does not create a Scheduler task:

BEGIN WORK;
EXECUTE FUNCTION STS_SubtrackCreate('t_vehicle', 'ts_track', 
           'ts_vehicle_subtrack', 2,
      '{ "averageMovingGPSRate":10, 
         "minStationaryInterval":300,
         "maxStationaryDistance":1, 
         "minNoDataInterval":900,
         "maxGPSTimeIntervalPerTrajectory :1800, 
         "maxMovingTrajectoryArea":128000,
         "unitOfMeasure":"meter", 
         "SRID":4326, 
         "storage":{
                    "subtrack":"FRAGMENT BY ROUND ROBIN in datadbs_1,datadbs_2 ",
                    "subtrackGeometry":"IN (sbspace1,sbspace2)",  
                    "subtrackPK":"in indexspace1",  
                    "subtrackET":"in indexspace1",  
                    "subtrackRT":"fragment by expression partition part1 ( instance_id < 100000 ) 
                                  in indexdbs, partition part2 ( instance_id < 200000 ) in indexdbs, 
                                  partition part3 ( instance_id < 300000 ) in indexdbs,
                                  partition part4 remainder in indexdbs"
                   },
       "extent":{  
                 "subtrack":[12800,6400],
                 "subtrackPK":[6400,3200],  
                 "subtrackET":[6400,3200],
                 "subtrackRT":[2000,4000] } }'::json::bson);
COMMIT WORK;

Readings for moving objects are taken at an average of every 10 seconds. Objects must not move more that 1 meter in 5 minutes to be considered stationary. If data is missing for an object for 15 minutes, the object has an interrupted signal status. Each trajectory is limited to 30 minutes and a bounding box area of 128000 m². The subtrack tables and their associated indexes have storage clauses and extent sizes.

The subtrack table remains empty until you run the STS_SubtrackBuild function to build trajectories, or run the STS_SubtrackAlterProcessing function to start a Scheduler task.

Example: Set a custom Scheduler task definition

The following statement creates the ts_vehicle_subtrack table and sets custom values for the Scheduler task:

BEGIN WORK;
EXECUTE FUNCTION STS_SubtrackSubtrackCreate('t_vehicle', 'ts_track',
       't_vehicle_subtrack', 1, NULL,  
       '2014-04-01 00:00:00', '0 12:00:00',
       3, '0 02:00:00', '12:00:00', '1 00:00:00');
COMMIT WORK;

The first time stamp is set to the beginning of May 2014. The indexing lag time is set to 12 hours. Three Scheduler tasks are started that run every two hours, first start at 12:00, and process one day of data.