STS_Speed function
The STS_Speed function returns the average speed for a specified trajectory.
Syntax
STS_Speed(
begin_time DATETIME YEAR TO FRACTION(5),
end_time DATETIME YEAR TO FRACTION(5),
geometry ST_Geometry,
uom VARCHAR(128) DEFAULT 'kph')
returns FLOAT- begin_time
- The time when the object was at the beginning of the line. Can be NULL to indicate the first element in the time series.
- end_time
- The time when the object was at the end of the line. Can be NULL to indicate the last element in the time series.
- geometry
- The geometry that represents the region of interest. Can be an ST_Point, ST_MultiPoint, ST_LineString, ST_MultiLineString, ST_Polygon, or ST_MultiPolygon. Must use the SRID 4326.
- uom
- The unit of measure for the speed. Default is kph. The string is ISO Latin1
(8859-1) and case does not matter.
You can specify the uom as linear_uom / time_uom, where linear_uom is any linear unit of measure that is defined for spatial data types, and time_uom can be any of the following strings: day, hour (or h, or hr), minute (or m or min) or second (or s or sec). Other supported abbreviations include: km/h, kmph (kilometer/hour), mph (mile_us/hour), mps (mile_us/second), fps (foot_us/second), m/s (meter/second), and kn (nautical_mile/hour).
Usage
Run the STS_Speed function when you want to find the average speed of an object for a specific trajectory or a time range.
Returns
A floating number = The speed, in the specified unit of measure.
An exception = An error.
Example
The following query returns the average speed of the vehicles while they were moving:
SELECT round(STS_Speed(begin_time, end_time, geometry, 'kph'), 2)
FROM t_vehicle_subtrack
WHERE state = 0;
(expression)
18.25
8.16