MobilityDB generalizes Open Geospatial Consortium's Well-Known Text (WKT), Well-Known Binary (WKB), and Moving Features JSON (MF-JSON) input and output format for all temporal types. We start by describing the WKT format.
An instant value is a couple of the form v@t, where v is a value of the base type and t is a timestamptz value. The temporal extent of an instant value is a single timestamp. Examples of instant values are as follows:
SELECT tbool 'true@2001-01-01 08:00:00'; SELECT tint '1@2001-01-01 08:00:00'; SELECT tfloat '1.5@2001-01-01 08:00:00'; SELECT ttext 'AAA@2001-01-01 08:00:00'; SELECT tgeompoint 'Point(0 0)@2017-01-01 08:00:05'; SELECT tgeogpoint 'Point(0 0)@2017-01-01 08:00:05';
A sequence value is a set of values v1@t1,...,vn@tn delimited by lower and upper bounds, which can be inclusive (represented by ‘[’ and ‘]’) or exclusive (represented by ‘(’ and ‘)’). A sequence value composed of a single couple v@t is called an instantaneous sequence. Sequence values have an associated interpolation function which may be discrete, linear, or step. By definition, the lower and upper bounds of an instantaneous sequence or of a sequence value with discrete interpolation are inclusive. The temporal extent of a sequence value with discrete interpolation is a timestamp set. Examples of sequence values with discrete interpolation are as follows.
SELECT tbool '{true@2001-01-01 08:00:00, false@2001-01-03 08:00:00}';
SELECT tint '{1@2001-01-01 08:00:00, 2@2001-01-03 08:00:00}';
SELECT tint '{1@2001-01-01 08:00:00}'; -- Instantaneous sequence
SELECT tfloat '{1.0@2001-01-01 08:00:00, 2.0@2001-01-03 08:00:00}';
SELECT ttext '{AAA@2001-01-01 08:00:00, BBB@2001-01-03 08:00:00}';
SELECT tgeompoint '{Point(0 0)@2017-01-01 08:00:00, Point(0 1)@2017-01-02 08:05:00}';
SELECT tgeogpoint '{Point(0 0)@2017-01-01 08:00:00, Point(0 1)@2017-01-02 08:05:00}';
The temporal extent of a sequence value with linear or step interpolation is a period defined by the first and last instants as well as the lower and upper bounds. Examples of sequence values with linear interpolation are as follows:
SELECT tbool '[true@2001-01-01 08:00:00, true@2001-01-03 08:00:00]'; SELECT tint '[1@2001-01-01 08:00:00, 1@2001-01-03 08:00:00]'; SELECT tfloat '[2.5@2001-01-01 08:00:00, 3@2001-01-03 08:00:00, 1@2001-01-04 08:00:00]'; SELECT tfloat '[1.5@2001-01-01 08:00:00]'; -- Instantaneous sequence SELECT ttext '[BBB@2001-01-01 08:00:00, BBB@2001-01-03 08:00:00]'; SELECT tgeompoint '[Point(0 0)@2017-01-01 08:00:00, Point(0 0)@2017-01-01 08:05:00)'; SELECT tgeogpoint '[Point(0 0)@2017-01-01 08:00:00, Point(0 1)@2017-01-01 08:05:00, Point(0 0)@2017-01-01 08:10:00)';
Sequence values whose base type is continuous may specify that the interpolation is step with the prefix Interp=Step. If this is not specified, it is supposed that the interpolation is linear by default. Example of sequence values with step interpolation are given next:
SELECT tfloat 'Interp=Step;[2.5@2001-01-01 08:00:00, 3@2001-01-01 08:10:00]'; SELECT tgeompoint 'Interp=Step;[Point(0 0)@2017-01-01 08:00:00, Point(1 1)@2017-01-01 08:05:00, Point(1 1)@2017-01-01 08:10:00)'; SELECT tgeompoint 'Interp=Step;[Point(0 0)@2017-01-01 08:00:00, Point(1 1)@2017-01-01 08:05:00, Point(0 0)@2017-01-01 08:10:00)'; ERROR: Invalid end value for temporal sequence with step interpolation SELECT tgeogpoint 'Interp=Step;[Point(0 0)@2017-01-01 08:00:00, Point(1 1)@2017-01-01 08:10:00]';
The last two instants of a sequence value with discrete interpolation and exclusive upper bound must have the same base value, as shown in the second and third examples above.
A sequence set value is a set {v1,...,vn} where every vi is a sequence value. The interpolation of sequence set values can only be linear or step, not discrete. All sequences composing a sequence set value must have the same interpolation. The temporal extent of a sequence set value is a set of periods. Examples of sequence set values with linear interpolation are as follows:
SELECT tbool '{[false@2001-01-01 08:00:00, false@2001-01-03 08:00:00),
[true@2001-01-03 08:00:00], (false@2001-01-04 08:00:00, false@2001-01-06 08:00:00]}';
SELECT tint '{[1@2001-01-01 08:00:00, 1@2001-01-03 08:00:00),
[2@2001-01-04 08:00:00, 3@2001-01-05 08:00:00, 3@2001-01-06 08:00:00]}';
SELECT tfloat '{[1@2001-01-01 08:00:00, 2@2001-01-03 08:00:00, 2@2001-01-04 08:00:00,
3@2001-01-06 08:00:00]}';
SELECT ttext '{[AAA@2001-01-01 08:00:00, BBB@2001-01-03 08:00:00, BBB@2001-01-04 08:00:00),
[CCC@2001-01-05 08:00:00, CCC@2001-01-06 08:00:00]}';
SELECT tgeompoint '{[Point(0 0)@2017-01-01 08:00:00, Point(0 1)@2017-01-01 08:05:00),
[Point(0 1)@2017-01-01 08:10:00, Point(1 1)@2017-01-01 08:15:00)}';
SELECT tgeogpoint '{[Point(0 0)@2017-01-01 08:00:00, Point(0 1)@2017-01-01 08:05:00),
[Point(0 1)@2017-01-01 08:10:00, Point(1 1)@2017-01-01 08:15:00)}';
Sequence set values whose base type is continuous may specify that the interpolation is step with the prefix Interp=Step. If this is not specified, it is supposed that the interpolation is linear by default. Example of sequence set values with step interpolation are given next:
SELECT tfloat 'Interp=Step;{[1@2001-01-01 08:00:00, 2@2001-01-03 08:00:00,
2@2001-01-04 08:00:00, 3@2001-01-06 08:00:00]}';
SELECT tgeompoint 'Interp=Step;{[Point(0 0)@2017-01-01 08:00:00,
Point(0 1)@2017-01-01 08:05:00], [Point(0 1)@2017-01-01 08:10:00,
Point(0 1)@2017-01-01 08:15:00)}';
SELECT tgeogpoint 'Interp=Step;{[Point(0 0)@2017-01-01 08:00:00,
Point(0 1)@2017-01-01 08:05:00], [Point(0 1)@2017-01-01 08:10:00,
Point(0 1)@2017-01-01 08:15:00)}';
For temporal points, it is possible to specify the spatial reference identifier (SRID) using the Extended Well-Known text (EWKT) representation as follows:
SELECT tgeompoint 'SRID=5435;[Point(0 0)@2001-01-01,Point(0 1)@2001-01-02]'
All component geometries of a temporal point will then be of the given SRID. Furthermore, each component geometry can specify its SRID with the EWKT format as in the following example
SELECT tgeompoint '[SRID=5435;Point(0 0)@2001-01-01,SRID=5435;Point(0 1)@2001-01-02]'
An error is raised if the component geometries are not all in the same SRID or if the SRID of a component geometry is different from the one of the temporal point, as shown below.
SELECT tgeompoint '[SRID=5435;Point(0 0)@2001-01-01,SRID=4326;Point(0 1)@2001-01-02]'; -- ERROR: Geometry SRID (4326) does not match temporal type SRID (5435) SELECT tgeompoint 'SRID=5435;[SRID=4326;Point(0 0)@2001-01-01, SRID=4326;Point(0 1)@2001-01-02]'; -- ERROR: Geometry SRID (4326) does not match temporal type SRID (5435)
If not specified, the default SRID for temporal geometric points is 0 (unknown) and for temporal geographic points is 4326 (WGS 84). Temporal points with step interpolation may also specify the SRID, as shown next.
SELECT tgeompoint 'SRID=5435,Interp=Step;[Point(0 0)@2001-01-01, Point(0 1)@2001-01-02]'; SELECT tgeogpoint 'Interp=Step;[SRID=4326;Point(0 0)@2001-01-01, SRID=4326;Point(0 1)@2001-01-02]';
We give below the input and output functions in Well-Known Text (WKT), Well-Known Binary (WKB), and Moving Features JSON (MF-JSON) format for temporal alphanumeric types. The corresponding functions for temporal points are detailed in the section called “Input and Output”. The default output format of all temporal alphanumeric types is the Well-Known Text format. The function asText given next enables to determine the output of temporal float values.
Return the Well-Known Text (WKT) representation
asText(tfloat,maxdecdigits=15) → text
The maxdecdigits argument can be used to set the maximum number of decimal places in the output of floating point values (default 15).
SELECT asText(tfloat '[10.55@2001-01-01, 25.55@2001-01-02]', 0); -- [11@2001-01-01, 26@2001-01-02]
Return the Well-Known Binary (WKB) representation
asBinary(ttype,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(tbool 'true@2001-01-01'); -- \x011a000101009c57d3c11c0000 SELECT asBinary(tint '1@2001-01-01', 'XDR'); -- \x000023010000000100001cc1d3579c00 SELECT asBinary(tfloat '1.5@2001-01-01'); -- \x01210001000000000000f83f009c57d3c11c0000 SELECT asBinary(ttext 'AAA@2001-01-01'); -- \x01290001040000000000000041414100009c57d3c11c0000
Return the Hexadecimal Well-Known Binary (WKB) representation as text
asHexWKB(ttype,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 the encoding of the machine is used.
SELECT asHexWKB(tbool 'true@2001-01-01'); -- 011A000101009C57D3C11C0000 SELECT asHexWKB(tint '1@2001-01-01', 'XDR'); -- 000023010000000100001CC1D3579C00 SELECT asHexWKB(tfloat '1.5@2001-01-01'); -- 01210001000000000000F83F009C57D3C11C0000 SELECT asHexWKB(ttext 'AAA@2001-01-01'); -- 01290001040000000000000041414100009C57D3C11C0000
Return the Moving Features JSON (MF-JSON) representation
asMFJSON(ttype,options=0,flags=0,maxdecdigits=15) → bytea
The options argument can be used to add a bounding box in the MFJSON output:
0: means no option (default value)
1: MFJSON BBOX
The flags argument can be used to customize the JSON output, for example, to produce an easy-to-read (for human readers) JSON output. Refer to the documentation of the json-c library for the possible values. Typical values are as follows:
0: means no option (default value)
1: JSON_C_TO_STRING_SPACED
2: JSON_C_TO_STRING_PRETTY
The maxdecdigits argument can be used to set the maximum number of decimal places in the output of floating point values (default 15).
SELECT asMFJSON(tbool 't@2001-01-01 18:00:00', 1);
/* {"type":"MovingBoolean","period":{"begin":"2001-01-01T18:00:00+01",
"end":"2001-01-01T18:00:00+01","lowerInc":true,"upperInc":true},
"values":[true],"datetimes":["2001-01-01T18:00:00+01"],"interpolation":"None"} */
SELECT asMFJSON(tint '{10@2001-01-01 18:00:00, 25@2001-01-01 18:10:00}', 1);
/* {"type":"MovingInteger","bbox":[10,25],"period":{"begin":"2001-01-01T18:00:00+01",
"end":"2001-01-01T18:10:00+01"},"values":[10,25],"datetimes":["2001-01-01T18:00:00+01",
"2001-01-01T18:10:00+01"],"lowerInc":true,"upperInc":true,
"interpolation":"Discrete"} */
SELECT asMFJSON(tfloat '[10.5@2001-01-01 18:00:00+02, 25.5@2001-01-01 18:10:00+02]');
/* {"type":"MovingFloat","values":[10.5,25.5],"datetimes":["2001-01-01T17:00:00+01",
"2001-01-01T17:10:00+01"],"lowerInc":true,"upperInc":true,"interpolation":"Linear"} */
SELECT asMFJSON(ttext '{[walking@2001-01-01 18:00:00+02,
driving@2001-01-01 18:10:00+02]}');
/* {"type":"MovingText","sequences":[{"values":["walking","driving"],
"datetimes":["2001-01-01T17:00:00+01","2001-01-01T17:10:00+01"],
"lowerInc":true,"upperInc":true}],"interpolation":"Step"} */
Input a temporal value from its Well-Known Binary (WKB) representation
ttypeFromBinary(bytea) → ttype
There is one function per temporal type, the name of the function has as prefix the name of the type, which is tbool or tint in the examples below.
SELECT tboolFromBinary('\x011a000101009c57d3c11c0000');
-- t@2001-01-01
SELECT tintFromBinary('\x000023010000000100001cc1d3579c00');
-- 1@2001-01-01
SELECT tfloatFromBinary('\x01210001000000000000f83f009c57d3c11c0000');
-- 1.5@2001-01-01
SELECT ttextFromBinary('\x01290001040000000000000041414100009c57d3c11c0000');
-- "AAA"@2001-01-01
Input a temporal value from its Hexadecimal Extended Well-Known Binary (HexEWKB) representation
ttypeFromHexWKB(text) → ttype
There is one function per temporal type, the name of the function has as prefix the name of the type, which is tbool or tint in the examples below.
SELECT tboolFromHexWKB('011A000101009C57D3C11C0000');
-- t@2001-01-01
SELECT tintFromHexWKB('000023010000000100001CC1D3579C00');
-- 1@2001-01-01
SELECT tfloatFromHexWKB('01210001000000000000F83F009C57D3C11C0000');
-- 1.5@2001-01-01
SELECT ttextFromHexWKB('01290001040000000000000041414100009C57D3C11C0000');
-- "AAA"@2001-01-01
Input a temporal value from its Moving Features JSON (MF-JSON) representation
ttypeFromMFJSON(bytea) → ttype
There is one function per temporal type, the name of the function has as prefix the name of the type, which is tbool or tint in the examples below.
SELECT tboolFromMFJSON(text
'{"type":"MovingBoolean","period":{"begin":"2001-01-01T18:00:00+01",
"end":"2001-01-01T18:00:00+01","lowerInc":true,"upperInc":true},
"values":[true],"datetimes":["2001-01-01T18:00:00+01"],"interpolation":"None"}');
-- t@2001-01-01 18:00:00
SELECT tintFromMFJSON(text
'{"type":"MovingInteger","bbox":[10,25],"period":{"begin":"2001-01-01T18:00:00+01",
"end":"2001-01-01T18:10:00+01"},"values":[10,25],"datetimes":["2001-01-01T18:00:00+01",
"2001-01-01T18:10:00+01"],"lowerInc":true,"upperInc":true,
"interpolation":"Discrete"}');
-- {10@2001-01-01 18:00:00, 25@2001-01-01 18:10:00}
SELECT tfloatFromMFJSON(text
'{"type":"MovingFloat","values":[10.5,25.5],"datetimes":["2001-01-01T17:00:00+01",
"2001-01-01T17:10:00+01"],"lowerInc":true,"upperInc":true,
"interpolation":"Linear"}');
-- [10.5@2001-01-01 18:00:00, 25.5@2001-01-01 18:10:00]'
SELECT ttextFromMFJSON(text
'{"type":"MovingText","sequences":[{"values":["walking","driving"],
"datetimes":["2001-01-01T17:00:00+01","2001-01-01T17:10:00+01"],
"lowerInc":true,"upperInc":true}],"interpolation":"Step"}');
-- {["walking"@2001-01-01 18:00:00, "driving"@2001-01-01 18:10:00]}');