5.12. Spatial Functions and Operators
Prev Chapter 5. Manipulating Temporal Types Next

5.12. Spatial Functions and Operators

In the following, we specify with the symbol that the function supports 3D points and with the symbol that the function is available for geographies.

5.12.1. Input/Output Functions

  • Get the Well-Known Text (WKT) representation

    asText({tpoint,tpoint[],geo[]}): {text,text[]}

    SELECT asText(tgeompoint 'SRID=4326;[Point(0 0 0)@2012-01-01, Point(1 1 1)@2012-01-02)');
-- "[POINT Z (0 0 0)@2012-01-01 00:00:00+00, POINT Z (1 1 1)@2012-01-02 00:00:00+00)"
SELECT asText(ARRAY[geometry 'Point(0 0)', 'Point(1 1)']);
-- "{"POINT(0 0)","POINT(1 1)"}"

  • Get the Extended Well-Known Text (EWKT) representation

    asEWKT({tpoint,tpoint[],geo[]}): {text,text[]}

    SELECT asEWKT(tgeompoint 'SRID=4326;[Point(0 0 0)@2012-01-01, Point(1 1 1)@2012-01-02)');
-- "SRID=4326;[POINT Z (0 0 0)@2012-01-01 00:00:00+00,
  POINT Z (1 1 1)@2012-01-02 00:00:00+00)"
SELECT asEWKT(ARRAY[geometry 'SRID=5676;Point(0 0)', 'SRID=5676;Point(1 1)']);
-- "{"SRID=5676;POINT(0 0)","SRID=5676;POINT(1 1)"}"

  • Get the Moving Features JSON representation

    asMFJSON(tpoint,maxdecdigits integer=15,options integer=0): bytea

    The last options argument could be used to add BBOX and/or CRS in MFJSON output:

    • 0: means no option (default value)

    • 1: MFJSON BBOX

    • 2: MFJSON Short CRS (e.g EPSG:4326)

    • 4: MFJSON Long CRS (e.g urn:ogc:def:crs:EPSG::4326)

    SELECT asMFJSON(tgeompoint 'Point(1 2)@2019-01-01 18:00:00.15+02');
-- "{"type":"MovingPoint","coordinates":[1,2],"datetimes":"2019-01-01T17:00:00.15+01",
  "interpolations":["Discrete"]}"
SELECT asMFJSON(tgeompoint 'SRID=4326;
  Point(50.813810 4.384260)@2019-01-01 18:00:00.15+02', 2, 3);
-- "{"type":"MovingPoint","crs":{"type":"name","properties":{"name":"EPSG:4326"}},
  "stBoundedBy":{"bbox":[50.81,4.38,50.81,4.38],
  "period":{"begin":"2019-01-01 17:00:00.15+01","end":"2019-01-01 17:00:00.15+01"}},
  "coordinates":[50.81,4.38],"datetimes":"2019-01-01T17:00:00.15+01",
  "interpolations":["Discrete"]}"

  • Get the Well-Known Binary (WKB) representation

    asBinary(tpoint): bytea

    asBinary(tpoint,endian text): bytea

    The result is encoded using either the little-endian (NDR) or the big-endian (XDR) encoding. If no encoding is specified, then the encoding of the machine is used.

    SELECT asBinary(tgeompoint 'Point(1 2 3)@2012-01-01');
-- "\x0191000000000000f03f0000000000000040000000000000084000fce0136a580100"

  • Get the Extended Well-Known Binary (EWKB) representation

    asEWKB(tpoint): bytea

    asEWKB(tpoint,endian text): bytea

    The result is encoded using either the little-endian (NDR) or the big-endian (XDR) encoding. If no encoding is specified, then the encoding of the machine is used.

    SELECT asEWKB(tgeogpoint 'SRID=7844;Point(1 2 3)@2012-01-01');
-- "\x01f1a41e0000000000000000f03f0000000000000040000000000000084000fce0136a580100"

  • Get the Hexadecimal Extended Well-Known Binary (EWKB) representation as text

    asHexEWKB(tpoint): text

    asHexEWKB(tpoint,endian text): text

    The result is encoded using either the little-endian (NDR) or the big-endian (XDR) encoding. If no encoding is specified, then NDR is used.

    SELECT asHexEWKB(tgeompoint 'SRID=3812;Point(1 2 3)@2012-01-01');
-- "01D1E40E0000000000000000F03F0000000000000040000000000000084000FCE0136A580100"

  • Input a temporal geometry point from a Well-Known Text (WKT) representation

    tgeompointFromText(text): tgeompoint

    SELECT asEWKT(tgeompointFromText(text '[POINT(1 2)@2000-01-01, POINT(3 4)@2000-01-02]'));
-- "[POINT(1 2)@2000-01-01, POINT(3 4)@2000-01-02]"

  • Input a temporal geography point from a Well-Known Text (WKT) representation

    tgeogpointFromText(text): tgeogpoint

    SELECT asEWKT(tgeogpointFromText(text '[POINT(1 2)@2000-01-01, POINT(3 4)@2000-01-02]'));
-- "SRID=4326;[POINT(1 2)@2000-01-01, POINT(3 4)@2000-01-02]"

  • Input a temporal geometry point from an Extended Well-Known Text (EWKT) representation

    tgeompointFromEWKT(text): tgeompoint

    SELECT asEWKT(tgeompointFromEWKT(text 'SRID=3812;[POINT(1 2)@2000-01-01,
  POINT(3 4)@2000-01-02]'));
-- "SRID=3812;[POINT(1 2)@2000-01-01 00:00:00+01, POINT(3 4)@2000-01-02 00:00:00+01]"

  • Input a temporal geography point from an Extended Well-Known Text (EWKT) representation

    tgeogpointFromEWKT(text): tgeogpoint

    SELECT asEWKT(tgeogpointFromEWKT(text 'SRID=7844;[POINT(1 2)@2000-01-01,
  POINT(3 4)@2000-01-02]'));
-- "SRID=7844;[POINT(1 2)@2000-01-01, POINT(3 4)@2000-01-02]"

  • Input a temporal geometry point from a Moving Features JSON representation

    tgeompointFromMFJSON(text): tgeompoint

    SELECT asEWKT(tgeompointFromMFJSON(text '{"type":"MovingPoint","crs":{"type":"name",
  "properties":{"name":"EPSG:4326"}},"coordinates":[50.81,4.38],
  "datetimes":"2019-01-01T17:00:00.15+01","interpolations":["Discrete"]}'));
-- "SRID=4326;POINT(50.81 4.38)@2019-01-01 17:00:00.15+01"

  • Input a temporal geography point from a Moving Features JSON representation

    tgeogpointFromMFJSON(text): tgeogpoint

    SELECT asEWKT(tgeogpointFromMFJSON(text '{"type":"MovingPoint","crs":{"type":"name",
  "properties":{"name":"EPSG:4326"}},"coordinates":[50.81,4.38],
  "datetimes":"2019-01-01T17:00:00.15+01","interpolations":["Discrete"]}'));
-- "SRID=4326;POINT(50.81 4.38)@2019-01-01 17:00:00.15+01"

  • Input a temporal geometry point from a Well-Known Binary (WKB) representation

    tgeompointFromBinary(bytea): tgeompoint

    SELECT asEWKT(tgeompointFromBinary(
  '\x0181000000000000f03f0000000000000040005c6c29ffffffff'));
-- "POINT(1 2)@2000-01-01"

  • Input a temporal geography point from a Well-Known Binary (WKB) representation

    tgeogpointFromBinary(bytea): tgeogpoint

    SELECT asEWKT(tgeompointFromBinary(
  '\x01b1000000000000f03f000000000000f03f000000000000f03f005c6c29ffffffff'));
-- "SRID=4326;POINT Z (1 1 1)@2000-01-01"

  • Input a temporal geometry point from an Extended Well-Known Binary (EWKB) representation

    tgeompointFromEWKB(bytea): tgeompoint

    SELECT asEWKT(tgeompointFromEWKB(
  '\x01c1e40e0000000000000000f03f0000000000000040005c6c29ffffffff'));
-- "SRID=3812;POINT(1 2)@2000-01-01"

  • Input a temporal geography point from an Extended Well-Known Binary (EWKB) representation

    tgeogpointFromEWKB(bytea): tgeogpoint

    SELECT asEWKT(tgeogpointFromEWKB(
  '\x01f1a41e0000000000000000f03f000000000000f03f000000000000f03f005c6c29ffffffff'));
-- "SRID=7844;POINT Z (1 1 1)@2000-01-01"

  • Input a temporal geometry point from an Hexadecimal Extended Well-Known Binary (HexEWKB) representation

    tgeompointFromHexEWKB(text): tgeompoint

    SELECT asEWKT(tgeompointFromHexEWKB(
  '01C1E40E0000000000000000F03F0000000000000040005C6C29FFFFFFFF'));
-- "SRID=3812;POINT(1 2)@2000-01-01"

  • Input a temporal geography point from an Hexadecimal Extended Well-Known Binary (HexEWKB) representation

    tgeogpointFromHexEWKB(text): tgeogpoint

    SELECT asEWKT(tgeogpointFromHexEWKB(
  '01F1A41E0000000000000000F03F000000000000F03F000000000000F03F005C6C29FFFFFFFF'));
-- "SRID=7844;POINT Z (1 1 1)@2000-01-01"

5.12.2. Spatial Reference System Functions

  • Get the spatial reference identifier

    SRID(tpoint): integer

    SELECT SRID(tgeompoint 'Point(0 0)@2012-01-01');
-- 0

  • Set the spatial reference identifier

    setSRID(tpoint): tpoint

    SELECT asEWKT(setSRID(tgeompoint '[Point(0 0)@2012-01-01, Point(1 1)@2012-01-02)', 4326));
-- "SRID=4326;[POINT(0 0)@2012-01-01 00:00:00+00, POINT(1 1)@2012-01-02 00:00:00+00)"

  • Transform to a different spatial reference

    transform(tpoint,integer): tpoint

    SELECT asEWKT(transform(tgeompoint 'SRID=4326;Point(4.35 50.85)@2012-01-01', 3812));
-- "SRID=3812;POINT(648679.018035303 671067.055638114)@2012-01-01 00:00:00+00"

5.12.3. Accessor Functions

  • Get the X coordinate values as a temporal float

    getX(tpoint): tfloat

    SELECT getX(tgeompoint '{Point(1 2)@2000-01-01, Point(3 4)@2000-01-02,
  Point(5 6)@2000-01-03}');
-- "{1@2000-01-01, 3@2000-01-02, 5@2000-01-03}"
SELECT getX(tgeogpoint 'Interp=Stepwise;[Point(1 2 3)@2000-01-01, Point(4 5 6)@2000-01-02,
  Point(7 8 9)@2000-01-03]');
-- "Interp=Stepwise;[1@2000-01-01, 4@2000-01-02, 7@2000-01-03]"

  • Get the Y coordinate values as a temporal float

    getY(tpoint): tfloat

    SELECT getY(tgeompoint '{Point(1 2)@2000-01-01, Point(3 4)@2000-01-02,
  Point(5 6)@2000-01-03}');
-- "{2@2000-01-01, 4@2000-01-02, 6@2000-01-03}"
SELECT getY(tgeogpoint 'Interp=Stepwise;[Point(1 2 3)@2000-01-01, Point(4 5 6)@2000-01-02,
  Point(7 8 9)@2000-01-03]');
-- "Interp=Stepwise;[2@2000-01-01, 5@2000-01-02, 8@2000-01-03]"

  • Get the Z coordinate values as a temporal float

    getZ(tpoint): tfloat

    SELECT getZ(tgeompoint '{Point(1 2)@2000-01-01, Point(3 4)@2000-01-02,
  Point(5 6)@2000-01-03}');
-- The temporal point do not have Z dimension
SELECT getZ(tgeogpoint 'Interp=Stepwise;[Point(1 2 3)@2000-01-01, Point(4 5 6)@2000-01-02,
  Point(7 8 9)@2000-01-03]');
-- "Interp=Stepwise;[3@2000-01-01, 6@2000-01-02, 9@2000-01-03]"

  • Returns true if the temporal point does not spatially self-intersect

    isSimple(tpoint): boolean

    Notice that a temporal sequence set point is simple if every composing sequence is simple.

    SELECT isSimple(tgeompoint '[Point(0 0)@2000-01-01, Point(1 1)@2000-01-02,
  Point(0 0)@2000-01-03]');
-- false
SELECT isSimple(tgeompoint '[Point(0 0 0)@2000-01-01, Point(1 1 1)@2000-01-02,
  Point(2 0 2)@2000-01-03, Point(0 0 0)@2000-01-04]');
-- true
SELECT isSimple(tgeompoint '{[Point(0 0 0)@2000-01-01, Point(1 1 1)@2000-01-02],
  [Point(1 1 1)@2000-01-03, Point(0 0 0)@2000-01-04]}');
-- true

  • Get the length traversed by the temporal point

    length(tpoint): float

    SELECT length(tgeompoint '[Point(0 0 0)@2000-01-01, Point(1 1 1)@2000-01-02]');
-- 1.73205080756888
SELECT length(tgeompoint '[Point(0 0 0)@2000-01-01, Point(1 1 1)@2000-01-02,
  Point(0 0 0)@2000-01-03]');
-- 3.46410161513775
SELECT length(tgeompoint 'Interp=Stepwise;[Point(0 0 0)@2000-01-01,
  Point(1 1 1)@2000-01-02, Point(0 0 0)@2000-01-03]');
-- 0

  • Get the cumulative length traversed by the temporal point

    cumulativeLength(tpoint): tfloat_seq

    SELECT round(cumulativeLength(tgeompoint '{[Point(0 0)@2000-01-01, Point(1 1)@2000-01-02,
  Point(1 0)@2000-01-03], [Point(1 0)@2000-01-04, Point(0 0)@2000-01-05]}'), 6);
-- {[0@2000-01-01, 1.414214@2000-01-02, 2.414214@2000-01-03],
  [2.414214@2000-01-04, 3.414214@2000-01-05]}
SELECT cumulativeLength(tgeompoint 'Interp=Stepwise;[Point(0 0 0)@2000-01-01,
  Point(1 1 1)@2000-01-02, Point(0 0 0)@2000-01-03]');
-- Interp=Stepwise;[0@2000-01-01, 0@2000-01-03]

  • Get the speed of the temporal point in units per second

    speed(tpoint): tfloat_seqset

    The temporal point must have linear interpolation

    SELECT speed(tgeompoint '{[Point(0 0)@2000-01-01, Point(1 1)@2000-01-02,
  Point(1 0)@2000-01-03], [Point(1 0)@2000-01-04, Point(0 0)@2000-01-05]}') * 3600 * 24;
-- "Interp=Stepwise;{[1.4142135623731@2000-01-01, 1@2000-01-02, 1@2000-01-03],
  [1@2000-01-04, 1@2000-01-05]}"
SELECT speed(tgeompoint 'Interp=Stepwise;[Point(0 0)@2000-01-01, Point(1 1)@2000-01-02,
  Point(1 0)@2000-01-03]');
-- ERROR:  The temporal value must have linear interpolation

  • Get the time-weighted centroid

    twCentroid(tgeompoint): point

    SELECT ST_AsText(twCentroid(tgeompoint '{[Point(0 0 0)@2012-01-01,
  Point(0 1 1)@2012-01-02, Point(0 1 1)@2012-01-03, Point(0 0 0)@2012-01-04)}'));
-- "POINT Z (0 0.666666666666667 0.666666666666667)"

  • Get the temporal azimuth

    azimuth(tpoint): tfloat

    SELECT degrees(azimuth(tgeompoint '[Point(0 0 0)@2012-01-01, Point(1 1 1)@2012-01-02,
  Point(1 1 1)@2012-01-03, Point(0 0 0)@2012-01-04)'));
-- "Interp=Stepwise;{[45@2012-01-01, 45@2012-01-02], [225@2012-01-03, 225@2012-01-04)}"

  • Get the temporal bearing

    bearing({tpoint, point}, {tpoint, point}): tfloat

    SELECT degrees(bearing(tgeompoint '[Point(1 1)@2012-01-01, Point(3 3)@2012-01-03]',
  geometry 'Point(2 2)'));
-- [45@2012-01-01, 0@2012-01-02, 225@2012-01-03]
SELECT round(degrees(bearing(tgeompoint '[Point(0 0)@2012-01-01, Point(2 0)@2012-01-03]',
  tgeompoint '[Point(2 1)@2012-01-01, Point(0 1)@2012-01-03]')), 3);
--  [63.435@2012-01-01, 0@2012-01-02, 296.565@2012-01-03]
SELECT round(degrees(bearing(tgeompoint '[Point(2 1)@2012-01-01, Point(0 1)@2012-01-03]',
  tgeompoint '[Point(0 0)@2012-01-01, Point(2 0)@2012-01-03]')), 3);
-- [243.435@2012-01-01, 116.565@2012-01-03]

    Please notice that this function currently does not accept two temporal geographic points.

5.12.4. Manipulation Functions

  • Round the coordinate values to a number of decimal places

    setPrecision(tpoint,integer): tpoint

    SELECT asText(setPrecision(tgeompoint '{Point(1.12345 1.12345 1.12345)@2000-01-01,
  Point(2 2 2)@2000-01-02, Point(1.12345 1.12345 1.12345)@2000-01-03}', 2));
-- "{POINT Z (1.12 1.12 1.12)@2000-01-01, POINT Z (2 2 2)@2000-01-02,
  POINT Z (1.12 1.12 1.12)@2000-01-03}"
SELECT asText(setPrecision(tgeogpoint 'Point(1.12345 1.12345)@2000-01-01', 2));
-- "POINT(1.12 1.12)@2000-01-01"

  • Returns an array of fragments of the temporal point which are simple

    makeSimple(tpoint): tgeompoint[]

    SELECT asText(makeSimple(tgeompoint '[Point(0 0)@2000-01-01, Point(1 1)@2000-01-02,
  Point(0 0)@2000-01-03]'));
-- {"[POINT(0 0)@2000-01-01, POINT(1 1)@2000-01-02)",
   "[POINT(1 1)@2000-01-02, POINT(0 0)@2000-01-03]"}
SELECT asText(makeSimple(tgeompoint '[Point(0 0 0)@2000-01-01, Point(1 1 1)@2000-01-02,
  Point(2 0 2)@2000-01-03, Point(0 0 0)@2000-01-04]'));
-- {"[POINT Z (0 0 0)@2000-01-01, POINT Z (1 1 1)@2000-01-02, POINT Z (2 0 2)@2000-01-03,
   POINT Z (0 0 0)@2000-01-04]"}
SELECT asText(makeSimple(tgeompoint '[Point(0 0)@2000-01-01, Point(1 1)@2000-01-02,
  Point(0 1)@2000-01-03, Point(1 0)@2000-01-04]'));
-- {"[POINT(0 0)@2000-01-01, POINT(1 1)@2000-01-02, POINT(0 1)@2000-01-03)",
   "[POINT(0 1)@2000-01-03, POINT(1 0)@2000-01-04]"}
SELECT asText(makeSimple(tgeompoint '{[Point(0 0 0)@2000-01-01, Point(1 1 1)@2000-01-02],
  [Point(1 1 1)@2000-01-03, Point(0 0 0)@2000-01-04]}'));
-- {"{[POINT Z (0 0 0)@2000-01-01, POINT Z (1 1 1)@2000-01-02],
   [POINT Z (1 1 1)@2000-01-03, POINT Z (0 0 0)@2000-01-04]}"}

  • Simplify a temporal point using a generalization of the Douglas-Peucker algorithm

    simplify(tpoint,distance float): tpoint

    simplify(tpoint,distance float,speed float): tpoint

    The first version remove points that are less than the distance passed as second argument, which is specified in the units of the coordinate system. The second version remove points that are less than the distance passed as second argument provided that the speed difference between the point and the corresponding point in the simplified version is less than the speed passed as third argument, which is specified in units per second. Notice that simplification applies only to temporal sequences or sequence sets with linear interpolation. In all other cases, a copy of the given temporal point is returned.

    -- Only distance specified
SELECT ST_AsText(trajectory(simplify(tgeompoint '[Point(0 4)@2000-01-01,
  Point(1 1)@2000-01-02, Point(2 3)@2000-01-03, Point(3 1)@2000-01-04,
  Point(4 3)@2000-01-05, Point(5 0)@2000-01-06, Point(6 4)@2000-01-07]', 1.5)));
-- "LINESTRING(0 4,1 1,4 3,5 0,6 4)"
SELECT ST_AsText(trajectory(simplify(tgeompoint '[Point(0 4)@2000-01-01,
  Point(1 1)@2000-01-02, Point(2 3)@2000-01-03, Point(3 1)@2000-01-04,
  Point(4 3)@2000-01-05, Point(5 0)@2000-01-06, Point(6 4)@2000-01-07]', 2)));
-- "LINESTRING(0 4,5 0,6 4)"
SELECT ST_AsText(trajectory(simplify(tgeompoint '[Point(0 4)@2000-01-01,
  Point(1 1)@2000-01-02, Point(2 3)@2000-01-03, Point(3 1)@2000-01-04,
  Point(4 3)@2000-01-05, Point(5 0)@2000-01-06, Point(6 4)@2000-01-07]', 4)));
-- "LINESTRING(0 4,6 4)"

-- Only speed difference specified
SELECT round(speed(tgeompoint '[Point(0 4)@2000-01-01, Point(1 1)@2000-01-02,
  Point(2 3)@2000-01-03, Point(3 1)@2000-01-04, Point(4 3)@2000-01-05,
  Point(5 0)@2000-01-06, Point(6 4)@2000-01-07]') * 1e5, 2);
-- "Interp=Stepwise;[3.66@2000-01-01, 2.59@2000-01-02, 3.66@2000-01-05,
  4.77@2000-01-06, 4.77@2000-01-07]"

-- Both distance and delta speed specified
SELECT ST_AsText(trajectory(simplify(tgeompoint '[Point(0 4)@2000-01-01,
  Point(1 1)@2000-01-02, Point(2 3)@2000-01-03, Point(3 1)@2000-01-04,
  Point(4 3)@2000-01-05, Point(5 0)@2000-01-06, Point(6 4)@2000-01-07]', 4, 1 / 1e5)));
-- "LINESTRING(0 4,1 1,2 3,3 1,4 3,5 0,6 4)"
SELECT ST_AsText(trajectory(simplify(tgeompoint '[Point(0 4)@2000-01-01,
  Point(1 1)@2000-01-02, Point(2 3)@2000-01-03, Point(3 1)@2000-01-04,
  Point(4 3)@2000-01-05, Point(5 0)@2000-01-06, Point(6 4)@2000-01-07]', 4, 2 / 1e5)));
-- "LINESTRING(0 4,1 1,5 0,6 4)"
SELECT ST_AsText(trajectory(simplify(tgeompoint '[Point(0 4)@2000-01-01,
  Point(1 1)@2000-01-02, Point(2 3)@2000-01-03, Point(3 1)@2000-01-04,
  Point(4 3)@2000-01-05, Point(5 0)@2000-01-06, Point(6 4)@2000-01-07]', 4, 3 / 1e5)));
-- "LINESTRING(0 4,6 4)"

    A typical use for the simplify function is to reduce the size of a dataset, in particular for visualization purposes.

  • Construct a geometry/geography with M measure from a temporal point and a temporal float

    geoMeasure(tpoint,tfloat,segmentize=false): geo

    The last segmentize argument states whether the resulting value is a either Linestring M or a MultiLinestring M where each component is a segment of two points.

    SELECT st_astext(geoMeasure(tgeompoint '{Point(1 1 1)@2000-01-01,
  Point(2 2 2)@2000-01-02}', '{5@2000-01-01, 5@2000-01-02}'));
-- "MULTIPOINT ZM (1 1 1 5,2 2 2 5)"
SELECT st_astext(geoMeasure(tgeogpoint '{[Point(1 1)@2000-01-01, Point(2 2)@2000-01-02],
  [Point(1 1)@2000-01-03, Point(1 1)@2000-01-04]}',
  '{[5@2000-01-01, 5@2000-01-02],[7@2000-01-03, 7@2000-01-04]}'));
-- "GEOMETRYCOLLECTION M (LINESTRING M (1 1 5,2 2 5),POINT M (1 1 7))"
SELECT st_astext(geoMeasure(tgeompoint '[Point(1 1)@2000-01-01,
  Point(2 2)@2000-01-02, Point(1 1)@2000-01-03]',
  '[5@2000-01-01, 7@2000-01-02, 5@2000-01-03]', true));
-- "MULTILINESTRING M ((1 1 5,2 2 5),(2 2 7,1 1 7))"

    A typical visualization for mobility data is to show on a map the trajectory of the moving object using different colors according to the speed. Figure 5.1 shows the result of the query below using a color ramp in QGIS.

    WITH Temp(t) AS (
  SELECT tgeompoint '[Point(0 0)@2012-01-01, Point(1 1)@2012-01-05,
    Point(2 0)@2012-01-08, Point(3 1)@2012-01-10, Point(4 0)@2012-01-11]'
)
SELECT ST_AsText(geoMeasure(t, round(speed(t) * 3600 * 24, 2), true))
FROM Temp;
-- "MULTILINESTRING M ((0 0 0.35,1 1 0.35),(1 1 0.47,2 0 0.47),(2 0 0.71,3 1 0.71),
  (3 1 1.41,4 0 1.41))"

    The following expression is used in QGIS to achieve this. The scale_linear function transforms the M value of each composing segment to the range [0, 1]. This value is then passed to the ramp_color function.

    ramp_color(
  'RdYlBu',
  scale_linear(
    m(start_point(geometry_n($geometry,@geometry_part_num))),
    0, 2, 0, 1)
)
    Visualizing the speed of a moving object using a color ramp in QGIS.

    Figure 5.1. Visualizing the speed of a moving object using a color ramp in QGIS.


  • Transform a temporal geometric point into the coordinate space of a Mapbox Vector Tile. The result is a couple composed of a geometry value and an array of associated timestamp values encoded as Unix epoch

    asMVTGeom(tpoint,bounds,extent=4096,buffer=256,clip=TRUE): geom_times

    The parameters are as follows:

    • tpoint is the temporal point to transform

    • bounds is an stbox defining the geometric bounds of the tile contents without buffer

    • extent is the tile extent in tile coordinate space

    • buffer is the buffer distance in tile coordinate space

    • clip is a Boolean that determines if the resulting geometries and timestamps should be clipped or not

    SELECT ST_AsText((mvt).geom), (mvt).times
FROM (SELECT asMVTGeom(tgeompoint '[Point(0 0)@2000-01-01, Point(100 100)@2000-01-02]',
  stbox 'STBOX((40,40),(60,60))') AS mvt ) AS t;
-- LINESTRING(-256 4352,4352 -256) | {946714680,946734120}
SELECT ST_AsText((mvt).geom), (mvt).times
FROM (SELECT asMVTGeom(tgeompoint '[Point(0 0)@2000-01-01, Point(100 100)@2000-01-02]',
  stbox 'STBOX((40,40),(60,60))', clip:=false) AS mvt ) AS t;
-- LINESTRING(-8192 12288,12288 -8192) | {946681200,946767600}

5.12.5. Distance Functions and Operators

  • Get the smallest distance ever

    {geo,tpoint} |=| {geo,tpoint}: float

    SELECT tgeompoint '[Point(0 0)@2012-01-02, Point(1 1)@2012-01-04, Point(0 0)@2012-01-06)'
  |=| geometry 'Linestring(2 2,2 1,3 1)';
-- 1
SELECT tgeompoint '[Point(0 0)@2012-01-01, Point(1 1)@2012-01-03, Point(0 0)@2012-01-05)'
  |=| tgeompoint '[Point(2 0)@2012-01-02, Point(1 1)@2012-01-04, Point(2 2)@2012-01-06)';
-- 0.5
SELECT tgeompoint '[Point(0 0 0)@2012-01-01, Point(1 1 1)@2012-01-03,
  Point(0 0 0)@2012-01-05)' |=| tgeompoint '[Point(2 0 0)@2012-01-02,
  Point(1 1 1)@2012-01-04, Point(2 2 2)@2012-01-06)';
-- 0.5
SELECT tgeompoint 'Interp=Stepwise;(Point(1 1)@2000-01-01, Point(3 1)@2000-01-03]' |=|
  geometry 'Linestring(1 3,2 2,3 3)';
-- 1.4142135623731

    The operator |=| can be used for doing nearest neighbor searches using a GiST index (see Section 5.17). This operator corresponds to the PostGIS function ST_DistanceCPA, altough the latter requires both arguments to be a trajectory.

    SELECT ST_DistanceCPA(
  tgeompoint '[Point(0 0 0)@2012-01-01, Point(1 1 1)@2012-01-03,
    Point(0 0 0)@2012-01-05)'::geometry,
  tgeompoint '[Point(2 0 0)@2012-01-02, Point(1 1 1)@2012-01-04,
    Point(2 2 2)@2012-01-06)'::geometry);
-- 0.5

  • Get the instant of the first temporal point at which the two arguments are at the nearest distance

    nearestApproachInstant({geo,tpoint},{geo,tpoint}): tpoint

    The function will only return the first instant that it finds if there are more than one. The resulting instant may be at an exclusive bound.

    SELECT asText(NearestApproachInstant(tgeompoint '(Point(1 1)@2000-01-01,
  Point(3 1)@2000-01-03]', geometry 'Linestring(1 3,2 2,3 3)'));
-- "POINT(2 1)@2000-01-02"
SELECT asText(NearestApproachInstant(tgeompoint 'Interp=Stepwise;(Point(1 1)@2000-01-01,
  Point(3 1)@2000-01-03]', geometry 'Linestring(1 3,2 2,3 3)'));
-- "POINT(1 1)@2000-01-01"
SELECT asText(NearestApproachInstant(tgeompoint '(Point(1 1)@2000-01-01,
  Point(2 2)@2000-01-03]', tgeompoint '(Point(1 1)@2000-01-01, Point(4 1)@2000-01-03]'));
-- "POINT(1 1)@2000-01-01"
SELECT asText(nearestApproachInstant(tgeompoint '[Point(0 0 0)@2012-01-01,
  Point(1 1 1)@2012-01-03, Point(0 0 0)@2012-01-05)', tgeompoint
  '[Point(2 0 0)@2012-01-02, Point(1 1 1)@2012-01-04, Point(2 2 2)@2012-01-06)'));
-- "POINT Z (0.75 0.75 0.75)@2012-01-03 12:00:00+00"

    Function nearestApproachInstant generalizes the PostGIS function ST_ClosestPointOfApproach. First, the latter function requires both arguments to be trajectories. Second, function nearestApproachInstant returns both the point and the timestamp of the nearest point of approach while the PostGIS function only provides the timestamp as shown next.

    SELECT to_timestamp(ST_ClosestPointOfApproach(
  tgeompoint '[Point(0 0 0)@2012-01-01, Point(1 1 1)@2012-01-03,
    Point(0 0 0)@2012-01-05)'::geometry,
  tgeompoint '[Point(2 0 0)@2012-01-02, Point(1 1 1)@2012-01-04,
    Point(2 2 2)@2012-01-06)'::geometry));
-- "2012-01-03 12:00:00+00"

  • Get the line connecting the nearest approach point

    shortestLine({geo,tpoint},{geo,tpoint}): geo

    The function will only return the first line that it finds if there are more than one.

    SELECT ST_AsText(shortestLine(tgeompoint '(Point(1 1)@2000-01-01,
  Point(3 1)@2000-01-03]', geometry 'Linestring(1 3,2 2,3 3)'));
-- "LINESTRING(2 1,2 2)"
SELECT ST_AsText(shortestLine(tgeompoint 'Interp=Stepwise;(Point(1 1)@2000-01-01,
  Point(3 1)@2000-01-03]', geometry 'Linestring(1 3,2 2,3 3)'));
-- "LINESTRING(1 1,2 2)"
SELECT ST_AsText(shortestLine(
  tgeompoint '[Point(0 0 0)@2012-01-01, Point(1 1 1)@2012-01-03,
    Point(0 0 0)@2012-01-05)',
  tgeompoint '[Point(2 0 0)@2012-01-02, Point(1 1 1)@2012-01-04,
    Point(2 2 2)@2012-01-06)'));
-- "LINESTRING Z (0.75 0.75 0.75,1.25 0.75 0.75)"

    Function shortestLine can be used to obtain the result provided by the PostGIS function ST_CPAWithin when both arguments are trajectories as shown next.

    SELECT ST_Length(shortestLine(
  tgeompoint '[Point(0 0 0)@2012-01-01, Point(1 1 1)@2012-01-03,
    Point(0 0 0)@2012-01-05)',
  tgeompoint '[Point(2 0 0)@2012-01-02, Point(1 1 1)@2012-01-04,
    Point(2 2 2)@2012-01-06)')) <= 0.5;
-- true
SELECT ST_CPAWithin(
  tgeompoint '[Point(0 0 0)@2012-01-01, Point(1 1 1)@2012-01-03,
    Point(0 0 0)@2012-01-05)'::geometry,
  tgeompoint '[Point(2 0 0)@2012-01-02, Point(1 1 1)@2012-01-04,
    Point(2 2 2)@2012-01-06)'::geometry, 0.5);
-- true

The temporal distance operator, denoted <->, computes the distance at each instant of the intersection of the temporal extents of their arguments and results in a temporal float. Computing temporal distance is useful in many mobility applications. For example, a moving cluster (also known as convoy or flock) is defined as a set of objects that move close to each other for a long time interval. This requires to compute temporal distance between two moving objects.

The temporal distance operator accepts a geometry/geography restricted to a point or a temporal point as arguments. Notice that the temporal types only consider linear interpolation between values, while the distance is a root of a quadratic function. Therefore, the temporal distance operator gives a linear approximation of the actual distance value for temporal sequence points. In this case, the arguments are synchronized in the time dimension, and for each of the composing line segments of the arguments, the spatial distance between the start point, the end point, and the nearest point of approach is computed, as shown in the examples below.

  • Get the temporal distance

    {point,tpoint} <-> {point,tpoint}: tfloat

    SELECT tgeompoint '[Point(0 0)@2012-01-01, Point(1 1)@2012-01-03)' <->
  geometry 'Point(0 1)';
-- "[1@2012-01-01, 0.707106781186548@2012-01-02, 1@2012-01-03)"
SELECT tgeompoint '[Point(0 0)@2012-01-01, Point(1 1)@2012-01-03)' <->
  tgeompoint '[Point(0 1)@2012-01-01, Point(1 0)@2012-01-03)';
-- "[1@2012-01-01, 0@2012-01-02, 1@2012-01-03)"
SELECT tgeompoint '[Point(0 1)@2012-01-01, Point(0 0)@2012-01-03)' <->
  tgeompoint '[Point(0 0)@2012-01-01, Point(1 0)@2012-01-03)';
-- "[1@2012-01-01, 0.707106781186548@2012-01-02, 1@2012-01-03)"
SELECT tgeompoint '[Point(0 0)@2012-01-01, Point(1 1)@2012-01-02)' <->
  tgeompoint '[Point(0 1)@2012-01-01, Point(1 2)@2012-01-02)';
-- "[1@2012-01-01,1@2012-01-02)"

5.12.6. Spatial Relationships

The topological relationships such as ST_Intersects and the distance relationships such as ST_DWithin can be generalized for temporal points. The arguments of these generalized functions are either a temporal point and a base type (that is, a geometry or a geography) or two temporal points. Furthermore, both arguments must be of the same base type, that is, these functions do not allow to mix a temporal geometry point (or a geometry) and a temporal geography point (or a geography).

There are two versions of the spatial relationships:

  • The possible relationships apply the traditional topological or distance relationship to the union of all values taken by the temporal point (which is a geometry or geography) and returns a boolean. Examples are the intersects and dwithin functions.

  • The temporal relationships are defined with the temporal semantics, that is, the traditional topological or distance relationship is computed at each instant and results in a tbool. Examples are the tintersects and tdwithin functions.

The semantics of the possible relationships varies depending on the relationship and the type of the arguments. For example, the following query 

SELECT intersects(geometry 'Polygon((0 0,0 1,1 1,1 0,0 0))',
  tgeompoint '[Point(0 1)@2012-01-01, Point(1 1)@2012-01-03)');

tests whether the temporal point ever intersected the geometry, since the query is conceptually equivalent to the following one 

SELECT ST_Intersects(geometry 'Polygon((0 0,0 1,1 1,1 0,0 0))',
  geometry 'Linestring(0 1,1 1)');

where the second geometry is obtained by applying the trajectory function to the temporal point. On the other hand, the query 

SELECT contains(geometry 'Polygon((0 0,0 1,1 1,1 0,0 0))',
  tgeompoint '[Point(0 1)@2012-01-01, Point(1 1)@2012-01-03)');

tests whether the geometry always contains the temporal point. Finally, the following query 

SELECT intersects(tgeompoint '[Point(0 1)@2012-01-01, Point(1 0)@2012-01-03)',
  tgeompoint '[Point(0 0)@2012-01-01, Point(1 1)@2012-01-03)');

tests whether the temporal points may intersect in space independently of time, since the query above is conceptually equivalent to the following one 

SELECT ST_Intersects('Linestring(0 1,1 0)', 'Linestring(0 0,1 1)');

The possible relationships are typically used in combination with a spatiotemporal index when computing the temporal relationships. For example, the following query 

SELECT T.TripId, R.RegionId, tintersects(T.Trip, R.Geom)
FROM Trips T, Regions R
WHERE intersects(T.Trip, R.Geom)

which verifies whether a trip T (which is a temporal point) intersects a region R (which is a geometry), will benefit from a spatiotemporal index on the column T.Trip since the intersects function will automatically perform the bounding box comparison T.Trip && R.Geom. This is further explained later in this document.

Not all spatial relationships available in PostGIS have a meaningful generalization for temporal points. A generalized version of the following relationships are defined for temporal geometric points: intersects, disjoint, dwithin, contains, and touches, while for temporal geographic points only the three first ones are defined. Furthermore, not all combinations of parameters are meaningful for a given generalized function. For example, while tcontains(geometry, tpoint) is meaningful, tcontains(tpoint, geometry) is meaningful only when the geometry is a single point, and tcontains(tpoint, tpoint) is equivalent to tintersects(tpoint, geometry). For this reason, only the first combination of parameters is defined for contains and tcontains.

Finally, it is worth noting that the temporal relationships allow to mix 2D/3D geometries but in that case, the computation is only performed on 2D.

5.12.7. Possible Spatial Relationships

  • May contain

    contains({geo,tgeompoint},{geo,tgeompoint}): boolean

    SELECT contains(geometry 'Polygon((0 0,0 1,1 1,1 0,0 0))',
  tgeompoint '[Point(0 0)@2012-01-01, Point(1 1)@2012-01-03)');
-- true

  • May be disjoint

    disjoint({geo,tgeompoint},{geo,tgeompoint}): boolean

    SELECT disjoint(geometry 'Polygon((0 0,0 1,1 1,1 0,0 0))',
  tgeompoint '[Point(0 0)@2012-01-01, Point(1 1)@2012-01-03)');
-- false

  • May be at distance within

    dwithin({geo,tpoint},{geo,tpoint},float): boolean

    SELECT dwithin(geometry 'Polygon((0 0 0,0 1 1,1 1 1,1 0 0,0 0 0))',
  tgeompoint 'Point(0 2 1)@2000-01-01,Point(2 2 1)@2000-01-02', 1)
-- true
SELECT dwithin(geometry 'Polygon((0 0 0,0 1 1,1 1 1,1 0 0,0 0 0))',
  tgeompoint 'Point(0 2 2)@2000-01-01,Point(2 2 2)@2000-01-02', 1)
-- false

  • May intersect

    intersects({geo,tpoint},{geo,tpoint}): boolean

    SELECT intersects(geometry 'Polygon((0 0 0,0 1 0,1 1 0,1 0 0,0 0 0))',
  tgeompoint '[Point(0 0 1)@2012-01-01, Point(1 1 1)@2012-01-03)');
-- false
SELECT intersects(geometry 'Polygon((0 0 0,0 1 1,1 1 1,1 0 0,0 0 0))',
  tgeompoint '[Point(0 0 1)@2012-01-01, Point(1 1 1)@2012-01-03)');
-- true

  • May touch

    touches({geo,tgeompoint},{geo,tgeompoint}): boolean

    SELECT touches(geometry 'Polygon((0 0,0 1,1 1,1 0,0 0))',
  tgeompoint '[Point(0 0)@2012-01-01, Point(0 1)@2012-01-03)');
-- true

5.12.8. Temporal Spatial Relationships

  • Temporal contains

    tcontains({geo,tgeompoint},{geo,tgeompoint}): tbool

    SELECT tcontains(geometry 'Polygon((1 1,1 2,2 2,2 1,1 1))',
  tgeompoint '[Point(0 0)@2012-01-01, Point(3 3)@2012-01-04)');
-- "{[f@2012-01-01, f@2012-01-02], (t@2012-01-02, f@2012-01-03, f@2012-01-04)}"

  • Temporal disjoint

    tdisjoint({geo,tgeompoint},{geo,tgeompoint}): tbool

    SELECT tdisjoint(geometry 'Polygon((1 1,1 2,2 2,2 1,1 1))',
  tgeompoint '[Point(0 0)@2012-01-01, Point(3 3)@2012-01-04)');
-- "{[t@2012-01-01, f@2012-01-02, f@2012-01-03], (t@2012-01-03, t@2012-01-04]}"
SELECT tdisjoint(tgeompoint '[Point(0 3)@2012-01-01, Point(3 0)@2012-01-05)',
  tgeompoint '[Point(0 0)@2012-01-01, Point(3 3)@2012-01-05)');
-- "{[t@2012-01-01, f@2012-01-03], (t@2012-01-03, t@2012-01-05)}"

  • Temporal distance within

    tdwithin({geo,tpoint},{geo,tpoint},float): tbool

    SELECT tdwithin(geometry 'Polygon((1 1,1 2,2 2,2 1,1 1))',
  tgeompoint '[Point(0 0)@2012-01-01, Point(3 0)@2012-01-04)', 1);
-- "{[f@2012-01-01, t@2012-01-02, t@2012-01-03], (f@2012-01-03, f@2012-01-04)}"
SELECT tdwithin(tgeompoint '[Point(1 0)@2000-01-01, Point(1 4)@2000-01-05]',
  tgeompoint 'Interp=Stepwise;[Point(1 2)@2000-01-01, Point(1 3)@2000-01-05]', 1);
-- "{[f@2000-01-01, t@2000-01-02, t@2000-01-04], (f@2000-01-04, t@2000-01-05]}"

  • Temporal intersects

    tintersects({geo,tpoint},{geo,tpoint}): tbool

    SELECT tintersects(geometry 'MultiPoint(1 1,2 2)',
  tgeompoint '[Point(0 0)@2012-01-01, Point(3 3)@2012-01-04)');
-- "{[f@2012-01-01, t@2012-01-02], (f@2012-01-02, t@2012-01-03],
  (f@2012-01-03, f@2012-01-04]}"
SELECT tintersects(tgeompoint '[Point(0 3)@2012-01-01, Point(3 0)@2012-01-05)',
  tgeompoint '[Point(0 0)@2012-01-01, Point(3 3)@2012-01-05)');
-- "{[f@2012-01-01, t@2012-01-03], (f@2012-01-03, f@2012-01-05)}"

  • Temporal touches

    ttouches({geo,tgeompoint},{geo,tgeompoint}): tbool

    SELECT ttouches(geometry 'Polygon((1 0,1 2,2 2,2 0,1 0))',
  tgeompoint '[Point(0 0)@2012-01-01, Point(3 0)@2012-01-04)');
-- "{[f@2012-01-01, t@2012-01-02, t@2012-01-03], (f@2012-01-03, f@2012-01-04]}"
Prev Up Next
5.11. Text Functions and Operators Home 5.13. Similarity Functions