Spatiotemporal Operators - Ocient Documentation

spatiotemporal operators perform calculations on geospatial data using an array of TIMESTAMP arguments.

ST_LONGESTLINE

With the specified two LINESTRING-TIMESTAMP ARRAY pairs, this function returns a two-point LINESTRING that represents the maximum distance between points at a concurrent time. If the two LINESTRING values do not overlap temporally, this function returns NULL. Syntax

SQL

ST_LONGESTLINE(linestring1, ts_arr1, linestring2, ts_arr2 [, use_spheroid] )

Argument	Data Type	Description
`linestring1`	`LINESTRING`	A geospatial object to be evaluated for its maximum cotemporal distance from `linestring2`.
`ts_arr1`	`TIMESTAMP ARRAY`	A timestamp array that is paired with `linestring1`. Behavior is undefined if this timestamp array is not monotonically increasing.
`linestring2`	`LINESTRING`	A geospatial object to be evaluated for its maximum cotemporal distance from `linestring1`.
`ts_arr2`	`TIMESTAMP ARRAY`	A timestamp array that is paired with `linestring2`. Behavior is undefined if this timestamp array is not monotonically increasing.
`use_spheroid`	`BOOLEAN`	Optional. If you set this argument to `TRUE`, this function uses a spheroid model instead of a spherical model. Defaults to `FALSE`.

Example

SQL

SELECT ST_LONGESTLINE(
        ST_MAKELINE(ST_POINT(0, 0), ST_POINT(0, 12)),
        TIMESTAMP [](
            '2020-07-04 07:00:00.000000000',
            '2020-07-04 11:00:00.000000000'),
        ST_MAKELINE(ST_POINT(0, 0), ST_POINT(8, 0)),
        TIMESTAMP [](
            '2020-07-04 08:00:00.000000000',
            '2020-07-04 10:00:00.000000000'));

Output: LINESTRING(0 9, 8 0)

To use ST_LONGESTLINE as a spatial function to calculate the longest line between two points, see the ST_LONGESTLINE function.

ST_LINEGETALLTIMESATPOINT

Returns a timestamp array of all times when the specified LINESTRING value intersects the specified POINT value. If the line never intersects the point, then the function returns an empty timestamp array. If the specified inputs are NULL, the function returns NULL. This function cannot represent intervals of time where the line was stationary at a point, therefore, use this function only for mobile paths. If the path is stationary, use the ST_INTERSECTION function as it can represent intervals of time spent intersecting a stationary LINESTRING. Syntax

SQL

ST_LINEGETALLTIMESATPOINT(line, timestamp_arr, point)

Argument	Data Type	Description
`line`	`LINESTRING`	A geospatial `LINESTRING` to be evaluated for all time values in the `timestamp_arr` when it intersects the specified `point` value. If this value is empty, the function returns an empty timestamp array. This value must be the same size as the `timestamp_arr`.
`timestamp_arr`	`TIMESTAMP ARRAY`	A `TIMESTAMP ARRAY` that is paired with the `line` argument. `timestamp_arr` must be the same size as the `line` value. Behavior is undefined if the timestamp array is not monotonically increasing.
`point`	`POINT`	A geospatial point to be evaluated for the time values when the point intersects the `line` value.

Example

SQL

SELECT ST_LINEGETALLTIMESATPOINT(
        ST_MAKELINE(ST_POINT(0, 0), ST_POINT(4, 0), ST_POINT(0, 0)),
        TIMESTAMP [](
            '2020-07-04 07:00:00.000000000',
            '2020-07-04 08:00:00.000000000',
            '2020-07-04 09:00:00.000000000'),
        ST_POINT(0, 0));

Output: TIMESTAMP[]('2020-07-04 07:00:00.000000000', '2020-07-04 09:00:00.000000000')

ST_LINEGETPOINTATTIME

Returns a POINT within the bounds of the specified LINESTRING that corresponds to the interpolated point at the specified TIMESTAMP value. The specified TIMESTAMP value does not need to be an actual value explicitly included in the array. The function interpolates the corresponding point, which means that the function approximates the point coordinates from the surrounding values. The LINESTRING and TIMESTAMP array must be the same size. If the timestamp array is not monotonically increasing, the behavior is undefined. Syntax

SQL

ST_LINEGETPOINTATTIME(line, timestamp_arr, time_for_point)

Argument	Data Type	Description
`line`	`LINESTRING`	A geospatial `LINESTRING` to be evaluated for an interpolated point, based on the `time_for_point` value. This value must be the same size as the `timestamp_arr`.
`timestamp_arr`	`TIMESTAMP ARRAY`	A `TIMESTAMP ARRAY` that is paired with the `line` argument. `timestamp_arr` must be the same size as the `line` value. Behavior is undefined if the timestamp array is not monotonically increasing.
`time_for_point`	`TIMESTAMP`	A time value to approximate a `POINT` value on the `line` argument, based on interpolating the values in `timestamp_arr`. If this time value precedes the first value in `timestamp_arr`, the function returns the first `POINT` on `line`. Similarly, if this time value comes after the last value in `timestamp_arr`, the function returns the last `POINT` on `line`.

Example

SQL

SELECT ST_LINEGETPOINTATTIME(
    ST_MAKELINE(ST_POINT(0, 0), ST_POINT(4, 0)),
    TIMESTAMP[]('2020-07-04 07:00:00.000000000', '2020-07-04 08:00:00.000000000'),
    TIMESTAMP('2020-07-04 07:30:00.000000000'));

Output: POINT(2, 0)

ST_LINEGETTIMEATPOINT

Returns the interpolated time of the specified POINT on the specified LINESTRING that is paired with a TIMESTAMP ARRAY. The LINESTRING and TIMESTAMP ARRAY must be the same size. Syntax

SQL

ST_LINEGETTIMEATPOINT(line, timestamp_arr, point_for_time)

Argument	Data Type	Description
`line`	`LINESTRING`	A geospatial line to be evaluated for an interpolated time value based on the `point_for_time` value. This value must be the same size as the `timestamp_arr`.
`timestamp_arr`	`TIMESTAMP ARRAY`	A timestamp array that is paired with the `line` argument. `timestamp_arr` must be the same size as the `line` value. Behavior is undefined if the timestamp array is not monotonically increasing.
`point_for_time`	`POINT`	A `POINT` value to approximate a `TIMESTAMP` value on the `line` argument, based on interpolating the values in `timestamp_arr`. If `line` intersects this `POINT` value multiple times, then the function returns the earliest time.

Example

SQL

SELECT ST_LINEGETTIMEATPOINT(
    ST_MAKELINE(ST_POINT(0, 0), ST_POINT(4, 0)),
    TIMESTAMP[]('2020-07-04 07:00:00.000000000', '2020-07-04 08:00:00.000000000'),
    ST_POINT(2, 0));

Output: TIMESTAMP('2020-07-04 07:30:00.000000000')

ST_INTERSECTION

Returns a tuple that represents the intersection of a spatiotemporal LINESTRING with a static geography. If the specified static geography is a POINT, the returned value is a tuple that contains a paired POINT array and TIMESTAMP array. If the specified static geography is a LINESTRING or POLYGON, then the returned value is a LINESTRING array paired with a two-dimensional TIMESTAMP array. Syntax

SQL

ST_INTERSECTION(line, timestamp_arr, intersection_point)

Argument	Data Type	Description
`line`	`LINESTRING`	A geospatial line to be evaluated for all intersections with the `intersection_point`. If this argument is empty, the function returns NULL.
`timestamp_arr`	`TIMESTAMP ARRAY`	A `TIMESTAMP ARRAY` that is paired with the `line` argument. `timestamp_arr` must be the same size as the `line` value. Behavior is undefined if the timestamp array is not monotonically increasing.
`intersection_point`	`POINT, LINESTRING`, or `POLYGON`	A geographic object to evaluate for all intersections with the `line` argument. If this value is empty, the function returns an empty tuple. If the value is NULL, the function returns NULL.

Example

SQL

SELECT ST_INTERSECTION(
    ST_LINESTRING('LINESTRING(1 1,2 2,3 3)'),
    TIMESTAMP[]('2020-07-04 07:00:00.000000000', '2020-07-04 11:00:00.000000000',
         '2020-07-04 12:00:00.000000000'),
    ST_POLYGON('POLYGON((2 2,3 2,3 3,3 2,2 2))'));

Output:

TUPLE(LINESTRING[](ST_LINESTRING('LINESTRING(2 2)'), ST_LINESTRING('LINESTRING(3 3)')), ARRAY[ARRAY[TIMESTAMP '2020-07-04 11:00:00.000000000'], ARRAY[TIMESTAMP '2020-07-04 12:00:00.000000000']])

ST_SHORTESTLINE

When you specify two LINESTRING-TIMESTAMP ARRAY pairs, this function returns a LINESTRING with two points that represents the minimum distance between points at a concurrent time. If the two linestrings do not overlap temporally, this function returns NULL. Syntax

SQL

ST_SHORTESTLINE(linestring1, ts_arr1, linestring2, ts_arr2 [, use_spheroid ])

Argument	Data Type	Description
`linestring1`	`LINESTRING`	A geospatial object to be evaluated for its minimum cotemporal distance from `linestring2`.
`ts_arr1`	`TIMESTAMP ARRAY`	A timestamp array that is paired with `linestring1`.
`linestring2`	`LINESTRING`	A geospatial object to be evaluated for its minimum cotemporal distance from `linestring1`.
`ts_arr2`	`TIMESTAMP ARRAY`	A timestamp array that is paired with `linestring2`.
`use_spheroid`	`BOOLEAN`	Optional. If you set this argument to `TRUE`, this function uses a spheroid model instead of a spherical model. Defaults to `FALSE`.

Example

SQL

SELECT ST_SHORTESTLINE(
    ST_MAKELINE(ST_POINT(0, 0), ST_POINT(4, 0)),
    TIMESTAMP[]('2020-07-04 07:00:00.000000000', '2020-07-04 08:00:00.000000000'),
    ST_MAKELINE(ST_POINT(3, 0), ST_POINT(4, 8)),
    TIMESTAMP[]('2020-07-04 07:00:00.000000000', '2020-07-04 07:30:00.000000000'));

Output: LINESTRING(0.09295649430902601 0, 3.0461764508256914 0.37184471667525754)

To use ST_SHORTESTLINE as a spatial function to calculate the shortest line between two points, see the ST_SHORTESTLINE function.

Geospatial Data Types Spatiotemporal Measurement

​ST_LONGESTLINE

​ST_LINEGETALLTIMESATPOINT

​ST_LINEGETPOINTATTIME

​ST_LINEGETTIMEATPOINT

​ST_INTERSECTION

​ST_SHORTESTLINE

​Related Links

ST_LONGESTLINE

ST_LINEGETALLTIMESATPOINT

ST_LINEGETPOINTATTIME

ST_LINEGETTIMEATPOINT

ST_INTERSECTION

ST_SHORTESTLINE

Related Links