Chapter 8. Temporal Types: Spatial Operations

Return the Well-Known Text (WKT) representation

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

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

Return the Extended Well-Known Text (EWKT) representation

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

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

Return the Moving Features JSON representation

asMFJSON(tpoint,options=0,flags=0,maxdecdigits=15) → bytea

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

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:

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(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"],
   "interpolation":"None"} */
SELECT asMFJSON(tgeompoint 'SRID=4326;
  Point(50.813810 4.384260)@2019-01-01 18:00:00.15+02', 3, 2);
/* {"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":"None"} */

Return the Well-Known Binary (WKB) representation

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)@2001-01-01');
-- \x012e0011000000000000f03f00000000000000400000000000000840009c57d3c11c0000

Return the Extended Well-Known Binary (EWKB) representation

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)@2001-01-01');
-- \x012f0071a41e0000000000000000f03f00000000000000400000000000000840009c57d3c11c0000

Return the Hexadecimal Extended Well-Known Binary (EWKB) representation as 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 the encoding of the machine is used.

SELECT asHexEWKB(tgeompoint 'SRID=3812;Point(1 2 3)@2001-01-01');
-- 012E0051E40E0000000000000000F03F00000000000000400000000000000840009C57D3C11C0000

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

tgeompointFromText(text) → tgeompoint

tgeogpointFromText(text) → tgeogpoint

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

Input a temporal point from its Extended Well-Known Text (EWKT) representation

tgeompointFromEWKT(text) → tgeompoint

tgeogpointFromEWKT(text) → tgeogpoint

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

Input a temporal point from its Moving Features JSON representation

tgeompointFromMFJSON(text) → tgeompoint

tgeogpointFromMFJSON(text) → tgeogpoint

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"],"interpolation":"None"}'));
-- SRID=4326;POINT(50.81 4.38)@2019-01-01 17:00:00.15+01
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"],"interpolation":"None"}'));
-- SRID=4326;POINT(50.81 4.38)@2019-01-01 17:00:00.15+01

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

tgeompointFromBinary(bytea) → tgeompoint

tgeogpointFromBinary(bytea) → tgeogpoint

SELECT asEWKT(tgeompointFromBinary(
  '\x012e0011000000000000f03f00000000000000400000000000000840009c57d3c11c0000'));
-- POINT Z (1 2 3)@2001-01-01

Input a temporal point from its Extended Well-Known Binary (EWKB) representation

tgeompointFromEWKB(bytea) → tgeompoint

tgeogpointFromEWKB(bytea) → tgeogpoint

SELECT asEWKT(tgeogpointFromEWKB(
  '\x012f0071a41e0000000000000000f03f00000000000000400000000000000840009c57d3c11c0000'));
-- SRID=7844;POINT Z (1 1 1)@2001-01-01

Input a temporal point from its Hexadecimal Extended Well-Known Binary (HexEWKB) representation

tgeompointFromHexEWKB(text) → tgeompoint

tgeogpointFromHexEWKB(text) → tgeogpoint

SELECT asEWKT(tgeompointFromHexEWKB(
  '012E0051E40E0000000000000000F03F00000000000000400000000000000840009C57D3C11C0000'));
-- SRID=3812;POINT(1 2 3)@2001-01-01