Spatial Functions

Spatial Functions
Prev	Chapter 5. Manipulating Temporal Types	Next

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.

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 int4 DEFAULT 15, options int4 DEFAULT 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, 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 'SRID=4326;Point(1 2 3)@2012-01-01');
-- "\001\001\000\000\000\000\000\000\360?\000\000\000\000\000\000\000@\000\000\000\000\000\000\010@\000\374\340\023jX\001\000"
				
```
Get the Extended Well-Known Binary (EWKB) representation

asEWKB(tpoint): bytea

asEWKB(tpoint, 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(tgeompoint 'SRID=4326;Point(1 2 3)@2012-01-01');
-- "\0011\346\020\000\000\000\000\000\000\000\000\360?\000\000\000\000\000\000\000@\000\000\000\000\000\000\010@\000\374\340\023jX\001\000"
				
```
Get the Hexadecimal Extended Well-Known Binary (EWKB) representation as text

asHexEWKB(tpoint): text

asHexEWKB(tpoint, 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=4326;Point(1 2 3)@2012-01-01');
-- "0131E6100000000000000000F03F0000000000000040000000000000084000FCE0136A580100"
				
```

Input a temporal point from a Moving Features JSON representation

fromMFJSON(text): tpoint

SELECT asEWKT(fromMFJSON(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 point from an Extended Well-Known Binary (EWKB) representation

fromEWKB(bytea): tpoint

SELECT asEWKT(fromEWKB(bytea
'\0011\346\020\000\000\000\000\000\000\000\000\360?\000\000\000\000\000\000\000@\000\000\000\000\000\000\010@\000\374\340\023jX\001\000'));
-- "SRID=4326;POINT Z (1 2 3)@2012-01-01 00:00:00+01"

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"

Round the coordinate values to a number of decimal places

setPrecision(tpoint, int): 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"

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): bool

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

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]}"}

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): tfloatseq

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 00:00:00+01, 0@2000-01-03 00:00:00+01]

Get the speed of the temporal point in units per second

speed(tpoint): tfloats

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 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 smallest distance ever

nearestApproachDistance({geo, tpoint}, {geo, tpoint}): float


SELECT NearestApproachDistance(tgeompoint '(Point(1 1)@2000-01-01,
  Point(3 1)@2000-01-03]', geometry 'Linestring(1 3,2 2,3 3)');
-- 1
SELECT NearestApproachDistance(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
SELECT nearestApproachDistance(
  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"

Function nearestApproachDistance has an associated operator |=| that can be used for doing nearest neightbor searches using a GiST index (see the section called “Comparison Operators”). This function corresponds to the function ST_DistanceCPA provided by PostGIS, 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 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

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

simplify(tpoint, float): tpoint

simplify(tpoint, float, 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)"

-- Speed of the temporal point
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, “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)@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)
)

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

Prev	Up	Next
Accessor Functions	Home	Restriction Functions