Chapter 9. Temporal Types: Spatial Operations (Part 2)

Round the coordinate values to a number of decimal places

round(tpoint,integer=0) → tpoint

SELECT asText(round(tgeompoint '{Point(1.12345 1.12345 1.12345)@2001-01-01,
  Point(2 2 2)@2001-01-02, Point(1.12345 1.12345 1.12345)@2001-01-03}', 2));
/* {POINT Z (1.12 1.12 1.12)@2001-01-01, POINT Z (2 2 2)@2001-01-02,
   POINT Z (1.12 1.12 1.12)@2001-01-03} */
SELECT asText(round(tgeogpoint 'Point(1.12345 1.12345)@2001-01-01', 2));
-- POINT(1.12 1.12)@2001-01-01

Return an array of fragments of the temporal point which are simple

makeSimple(tpoint) → tgeompoint[]

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

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)@2001-01-01,
  Point(2 2 2)@2001-01-02}', '{5@2001-01-01, 5@2001-01-02}'));
-- MULTIPOINT ZM (1 1 1 5,2 2 2 5)
SELECT st_astext(geoMeasure(tgeogpoint '{[Point(1 1)@2001-01-01, Point(2 2)@2001-01-02],
  [Point(1 1)@2001-01-03, Point(1 1)@2001-01-04]}',
  '{[5@2001-01-01, 5@2001-01-02],[7@2001-01-03, 7@2001-01-04]}'));
-- GEOMETRYCOLLECTION M (POINT M (1 1 7),LINESTRING M (1 1 5,2 2 5))
SELECT st_astext(geoMeasure(tgeompoint '[Point(1 1)@2001-01-01,
  Point(2 2)@2001-01-02, Point(1 1)@2001-01-03]',
  '[5@2001-01-01, 7@2001-01-02, 5@2001-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 9.1, “Visualizing the speed of a moving object using a color ramp in QGIS.” shows the result of the query below using a color ramp in QGIS.

WITH Temp(t) AS (
  SELECT tgeompoint '[Point(0 0)@2001-01-01, Point(1 1)@2001-01-05,
    Point(2 0)@2001-01-08, Point(3 1)@2001-01-10, Point(4 0)@2001-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)
)

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

Return the 3D affine transform of a temporal point to do things like translate, rotate, scale in one step

affine(tpoint,float a, float b, float c, float d, float e, float f, float g,

float h, float i, float xoff, float yoff, float zoff) → tpoint

affine(tpoint,float a, float b, float d, float e, float xoff, float yoff) → tpoint

-- Rotate a 3D temporal point 180 degrees about the z axis
SELECT asEWKT(affine(temp, cos(pi()), -sin(pi()), 0, sin(pi()), cos(pi()), 0, 0, 0, 1,
  0, 0, 0))
FROM (SELECT tgeompoint '[POINT(1 2 3)@2001-01-01, POINT(1 4 3)@2001-01-02]' AS temp) t;
-- [POINT Z (-1 -2 3)@2001-01-01, POINT Z (-1 -4 3)@2001-01-02]
SELECT asEWKT(rotate(temp, pi()))
FROM (SELECT tgeompoint '[POINT(1 2 3)@2001-01-01, POINT(1 4 3)@2001-01-02]' AS temp) t;
-- [POINT Z (-1 -2 3)@2001-01-01, POINT Z (-1 -4 3)@2001-01-02]
-- Rotate a 3D temporal point 180 degrees in both the x and z axis
SELECT asEWKT(affine(temp, cos(pi()), -sin(pi()), 0, sin(pi()), cos(pi()), -sin(pi()), 
   0, sin(pi()), cos(pi()), 0, 0, 0))
FROM (SELECT tgeompoint '[Point(1 2 3)@2001-01-01, Point(1 4 3)@2001-01-02]' AS temp) t;
-- [POINT Z (-1 -2 -3)@2001-01-01, POINT Z (-1 -4 -3)@2001-01-02]

Return the temporal point rotated counter-clockwise about the origin point

rotate(tpoint,float radians) → tpoint

rotate(tpoint,float radians,float x0,float y0) → tpoint

rotate(tpoint,float radians,geometry origin) → tpoint

-- Rotate a temporal point 180 degrees
SELECT asEWKT(rotate(tgeompoint '[POINT(50 160)@2001-01-01, POINT(50 50)@2001-01-02, 
  POINT(100 50)@2001-01-03]', pi()), 6);
-- [POINT(-50 -160)@2001-01-01, POINT(-50 -50)@2001-01-02, POINT(-100 -50)@2001-01-03]
-- Rotate 30 degrees counter-clockwise at x=50, y=160
SELECT asEWKT(rotate(tgeompoint '[POINT(50 160)@2001-01-01, POINT(50 50)@2001-01-02, 
  POINT(100 50)@2001-01-03]', pi()/6, 50, 160), 6);
/* [POINT(50 160)@2001-01-01, POINT(105 64.737206)@2001-01-02, 
    POINT(148.30127 89.737206)@2001-01-03] */
-- Rotate 60 degrees clockwise from centroid
SELECT asEWKT(rotate(temp, -pi()/3, ST_Centroid(trajectory(temp))), 6)
FROM (SELECT tgeompoint '[POINT(50 160)@2001-01-01, POINT(50 50)@2001-01-02, 
  POINT(100 50)@2001-01-03]' AS temp) AS t;
/* [POINT(116.422459 130.672073)@2001-01-01, POINT(21.159664 75.672073)@2001-01-02, 
    POINT(46.159664 32.370803)@2001-01-03] */

Return a temporal point scaled by given factors

scale(tpoint,float Xfactor,float Yfactor,float Zfactor) → tpoint

scale(tpoint,float Xfactor,float Yfactor) → tpoint

scale(tpoint,geometry factor) → tpoint

scale(tpoint,geometry factor,geometry origin) → tpoint

SELECT asEWKT(scale(tgeompoint '[Point(1 2 3)@2001-01-01, Point(1 1 1)@2001-01-02]', 
  0.5, 0.75, 0.8));
--  [POINT Z (0.5 1.5 2.4)@2001-01-01, POINT Z (0.5 0.75 0.8)@2001-01-02]
SELECT asEWKT(scale(tgeompoint '[Point(1 2 3)@2001-01-01, Point(1 1 1)@2001-01-02]', 
  0.5, 0.75));
-- [POINT Z (0.5 1.5 3)@2001-01-01, POINT Z (0.5 0.75 1)@2001-01-02]
SELECT asEWKT(scale(tgeompoint '[Point(1 2 3)@2001-01-01, Point(1 1 1)@2001-01-02]', 
  geometry 'POINT(0.5 0.75 0.8)'));
-- [POINT Z (0.5 1.5 2.4)@2001-01-01, POINT Z (0.5 0.75 0.8)@2001-01-02]
SELECT asEWKT(scale(tgeompoint '[Point(1 1)@2001-01-01, Point(2 2)@2001-01-02]', 
  geometry 'POINT(2 2)', geometry 'POINT(1 1)'));
--  Point(1 1,3 3)

Transform a temporal geometric point into the coordinate space of a Mapbox Vector Tile

asMVTGeom(tpoint,bounds,extent=4096,buffer=256,clip=true) → (geom,times)

The result is a couple composed of a geometry value and an array of associated timestamp values encoded as Unix epoch. The parameters are as follows:

SELECT ST_AsText((mvt).geom), (mvt).times
FROM (SELECT asMVTGeom(tgeompoint '[Point(0 0)@2001-01-01, Point(100 100)@2001-01-02]',
  stbox 'STBOX X((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)@2001-01-01, Point(100 100)@2001-01-02]',
  stbox 'STBOX X((40,40),(60,60))', clip:=false) AS mvt ) AS t;
-- LINESTRING(-8192 12288,12288 -8192) | {946681200,946767600}