STS_GetFirstTimeByPoint function

The STS_GetFirstTimeByPoint function returns the first time within a time range when an object is near the specified position.

Syntax

STS_GetFirstTimeByPoint(
                        ts            TimeSeries,
                        begin_time    DATETIME YEAR TO FRACTION(5),
                        end_time      DATETIME YEAR TO FRACTION(5),
                        point         ST_Point)
returns DATETIME YEAR TO FRACTION(5)

STS_GetFirstTimeByPoint(
                        ts            TimeSeries,
                        begin_time    DATETIME YEAR TO FRACTION(5),
                        end_time      DATETIME YEAR TO FRACTION(5),
                        point         ST_Point,
                        radius        FLOAT)
returns DATETIME YEAR TO FRACTION(5)

STS_GetFirstTimeByPoint(
                        ts            TimeSeries,
                        begin_time    DATETIME YEAR TO FRACTION(5),
                        end_time      DATETIME YEAR TO FRACTION(5),
                        point         ST_Point,
                        radius        FLOAT,
                        uom           VARCHAR(128))
returns DATETIME YEAR TO FRACTION(5)
ts
The time series value.
begin_time
The start of the time range. Can be NULL to indicate the first element in the time series.
end_time
The end of the time range. Can be NULL to indicate the last element in the time series.
point
An ST_Point for the position of interest.
radius (Optional)
The distance from the point that defines the border of the region of interest.
uom (Optional)
The unit of measure for the radius parameter. If unitOfMeasure is not specified, then it is meter for a geographic coordinate systems, and, it is the linear unit of measure for projected coordinate systems. If the projected coordinate system does not have a linear unit of measure specified, it is meter. Must be the name of a linear unit of measure from the unit_name column of the st_units_of_measure table.

Usage

Run the STS_GetFirstTimeByPoint function to find when an object first passed within the specified distance of the specified position during the specified time range. If you do not specify a time range, the function returns the first time that an object passed close enough to the position. If the object was too far away from the position during the time range, the STS_GetFirstTimeByPoint function returns NULL.

The following illustration shows the trajectory of a vehicle and the point at which the trajectory is first within the specified distance to the point. The query returns the time stamp that is associated with the point on the trajectory.

Figure 1: A trajectory near a point

The position of the vehicle at the first point in its trajectory when it is less than d meters from the point is at point p1.

Returns

A time stamp = When the object first moved within the specified distance of the position.

NULL = The trajectory of the object during the time range was always farther than the maximum distance from the position.

Example

The following query returns the first time that vehicle 1001 passed by the specified position between the times of 2014-06-07 20:50:00 and 2014-06-07 20:55:00:

SELECT STS_GetFirstTimeByPoint(ts_track, 
        '2014-06-07 20:50:00', '2014-06-07 20:55:00',        
        ST_Point(-79.098993, 43.812542, 4326), 10, 'foot')  
        FROM t_vehicle 
        WHERE modid = 1001; 

(expression) 
    2014-06-07 20:52:42.00000 
1 row(s) retrieved.