Spatiotemporal Operators

﻿
﻿ 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] )
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'));
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 in the Spatial Operators section. 
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 is able to represent intervals of time spent intersecting a stationary LINESTRING.
Syntax
SQL
ST_LINEGETALLTIMESATPOINT(line, timestamp_arr, point)
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));
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. Behavior is undefined if the timestamp array is not monotonically increasing. 
Syntax
SQL
ST_LINEGETPOINTATTIME(line, timestamp_arr, time_for_point)
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'));
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)
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 return 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));
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 that is paired with a two-dimensional TIMESTAMP array.   
Syntax
SQL
ST_INTERSECTION(line, timestamp_arr, intersection_point)
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))'));
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 ])
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'));
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 in the Spatial Operators section. 
Related Links
﻿Geospatial Data Types﻿﻿
﻿Spatiotemporal Measurement﻿﻿

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.

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.

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.

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 return the earliest time.

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.

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.

Updated 16 May 2024

Did this page help you?

Spatiotemporal Measurement

Machine Learning Model Functions