Multidimensional tiling is the mechanism used to partition the domain of temporal values in bins or tiles of varying number of dimensions. In the case of a single dimension, the domain can be partitioned by value or by time using bins of the same size or duration, respectively. For temporal numbers, the domain can be partitioned in two-dimensional tiles of the same size for the value dimension and the same duration for the time dimension. For temporal points, the domain can be partitioned in space in two- or three-dimensional tiles, depending on the number of dimensions of the spatial coordinates. Finally, for temporal points, the domain can be partitioned in space and time using three- or four-dimensional tiles.
Multidimensional tiling can be used for various purposes. It can be used for computing multidimensional histograms, where the temporal values are aggregated according to the underlying partition of the domain. On the other hand, multidimensional tiling can also be used for indexing purposes, where the bounding box of a temporal value can be fragmented into multiple boxes in order to improve the efficiency of the index. Finally, multidimensional tiling can be used for fragmenting temporal values according to a multidimensional grid defined over the underlying domain. This enables the distribution of a dataset across a cluster of servers, where each server contains a partition of the dataset. The advantage of this partition mechanism is that it preserves proximitity in value/space and time, unlike the traditional hash-based partition mechanisms.
Figure 9.4, “Multidimensional tiling for temporal floats.” illustrates multidimensional tiling for temporal floats. The two-dimensional domain is split into tiles having the same size for the value dimension and the same duration for the time dimension. Suppose that this tiling scheme is used for distribute a dataset across a cluster of six servers, as suggested by the gray pattern in the figure. In this case, the values are fragmented so each server will receive the data of contiguous tiles. This implies in particular that four nodes will receive one fragment of the temporal float depicted in the figure. One advantage of this distribution of data based on multidimensional tiling is that it reduces the data that needs to be exchanged between nodes when processing queries, a process typically referred to as reshuffling.
Many of the functions in this section are set-returning functions (also known as a table functions) since they typically return more than one value. In this case, the functions are marked with the 
symbol.
Every bin, tile and box contains its lower bound and excludes its upper one, so a position on the boundary of two of them belongs to the one starting there. The extent of a value or of a box contains its upper bound, so when that bound lies on a boundary of the grid, the bin or tile starting there holds nothing of the extent but its upper border. The optional argument borderInc, true by default, states whether that bin or tile is part of the grid. When it is true, the border has its own bin, tile, box or fragment, so the fragments of a split cover the whole value; when it is false, the border is left out, as for an extent read without its upper bound. A value whose extent excludes its upper bound has no border to leave out and answers alike. The grids of H3 and S2 cells, which have no axis to align with the extent, assign a position on a cell boundary to one of the cells meeting there, and the cell a trajectory enters at its last instant is the one cell holding only the upper border of its extent: borderInc states whether that cell is part of the result.
Return an array of bins that cover a value or time span with bins of the same size or duration
bins(numspans,vsize number,vorigin number=0,borderInc boolean=true) → span[] bins(datespans,duration interval,torigin date='2000-01-01', borderInc boolean=true) → span[] bins(tstzspans,duration interval,torigin timestamptz='2000-01-03', borderInc boolean=true) → span[]
If the origin is not specified, it is set by default to 0 for value spans, to Saturday, January 1, 2000 for date spans and to Monday, January 3, 2000 for timestamp spans. The optional argument
borderIncstates whether the upper border of the span is included and thus, the bin starting at that border and holding only it is generated.SELECT bins(floatspan '[-10, -1]', 2.5, -7); -- {"[-12, -9.5)","[-9.5, -7)","[-7, -4.5)","[-4.5, -2)","[-2, 0.5)"} SELECT bins(intspan '[15, 25]', 2); -- {"[14, 16)","[16, 18)","[18, 20)","[20, 22)","[22, 24)","[24, 26)"} SELECT index, span FROM unnest(bins(datespan '[2001-01-15, 2001-01-25]', '2 days')) WITH ORDINALITY t(span, index); -- 1 | [2001-01-15, 2001-01-17) -- 2 | [2001-01-17, 2001-01-19) -- 3 | [2001-01-19, 2001-01-21) -- ... SELECT index, span FROM unnest(bins(tstzspanset '{[2001-01-15, 2001-01-17],[2001-01-22, 2001-01-25]}', '2 days', '2001-01-02')) WITH ORDINALITY t(span, index); -- 1 | [2001-01-15, 2001-01-16) -- 2 | [2001-01-16, 2001-01-17] -- 3 | [2001-01-22, 2001-01-24) -- 4 | [2001-01-24, 2001-01-25]Return the bin that contains a number or a timestamp
getBin(number,vsize number,vorigin number=0) → span getBin(date,duration interval,torigin date='2000-01-03') → datespan getBin(timestamptz,duration interval,torigin timestamptz='2000-01-03') → tstzspan
If the origin is not specified, it is set by default to 0 for value bins and to Monday, January 3, 2000 for time bins.
SELECT getBin(2, 2); -- [2, 4) SELECT getBin(2, 2.5, 1.5); -- [1.5, 4) SELECT getBin('2001-01-04', interval '1 week'); -- [2001-01-01 08:00:00, 2001-01-08 08:00:00) SELECT getBin('2001-01-04', interval '1 week', '2001-01-07'); -- [2000-12-31, 2001-01-07)
Return the set of tiles that covers a temporal box with tiles of the same size and/or duration
valueTiles(tbox,vsize number,vorigin number=0,borderInc boolean=true) → {(index,tile)} timeTiles(tbox,duration interval,torigin timestamptz='2000-01-03', borderInc boolean=true) → {(index,tile)} valueTimeTiles(tbox,vsize number,duration interval,vorigin number=0, torigin timestamptz='2000-01-03',borderInc boolean=true) → {(index,tile)}The result is a set of pairs
(index,tile). If the origin of the value and/or time dimension is not specified, it is set by default to 0 and to Monday, January 3, 2000, respectively. The value size and origin have the base type of the values of the box, and a box of floats also takes integer ones. The optional argumentborderIncstates whether the upper border of the box is included and thus, extra tiles containing the border are generated.SELECT (gr).index, (gr).tile FROM (SELECT valueTiles(tfloat '[15@2001-01-15, 25@2001-01-25]'::tbox, 2.0) AS gr) t; -- 1 | TBOXFLOAT X([14, 16)) -- 2 | TBOXFLOAT X([16, 18)) -- 3 | TBOXFLOAT X([18, 20)) -- ... SELECT (gr).index, (gr).tile FROM (SELECT timeTiles(tfloat '[15@2001-01-15, 25@2001-01-25]'::tbox, '2 days') AS gr) t; -- 1 | TBOX T([2001-01-15, 2001-01-17)) -- 2 | TBOX T([2001-01-17, 2001-01-19)) -- 3 | TBOX T([2001-01-19, 2001-01-21)) -- ... SELECT (gr).index, (gr).tile FROM (SELECT valueTimeTiles(tfloat '[15@2001-01-15, 25@2001-01-25]'::tbox, 2.0, '2 days') AS gr) t; -- 1 | TBOX XT([14,16),[2001-01-15,2001-01-17)) -- 2 | TBOX XT([16,18),[2001-01-15,2001-01-17)) -- 3 | TBOX XT([18,20),[2001-01-15,2001-01-17)) -- ... SELECT valueTiles(tbox 'TBOXINT XT([15, 25],[2001-01-15, 2001-01-25])', 2, 15); -- (1,"TBOXINT X([15, 17))") -- (2,"TBOXINT X([17, 19))") -- (3,"TBOXINT X([19, 21))") -- ... SELECT valueTimeTiles(tfloat '[15@2001-01-15,25@2001-01-25]'::tbox, 2.0, '2 days', 11.5); -- (1,"TBOX XT([13.5,15.5),[2001-01-15,2001-01-17))") -- (2,"TBOX XT([15.5,17.5),[2001-01-15,2001-01-17))") -- (3,"TBOX XT([17.5,19.5),[2001-01-15,2001-01-17))") -- ...
Return the set of tiles that covers a spatiotemporal box with tiles of the same size and/or duration
spaceTiles({stbox,tgeompoint},xsize float,[ysize float,zsize float,] sorigin geompoint='Point(0 0 0)',borderInc boolean=true) → {(index,tile)} timeTiles({stbox,tgeompoint},duration interval,torigin timestamptz='2000-01-03', borderInc boolean=true) → {(index,tile)} spaceTimeTiles({stbox,tgeompoint},xsize float,[ysize float,zsize float,]duration interval, sorigin geompoint='Point(0 0 0)',torigin timestamptz='2000-01-03', borderInc boolean=true) → {(index,tile)}The result is a set of pairs
(index,tile). If the origin of the space and/or time dimension is not specified, it is set by default to'Point(0 0 0)'and to Monday, January 3, 2000, respectively. The optional argumentborderIncstates whether the upper border of the extent is included and thus, extra tiles containing the border are generated. When the first argument is atgeompoint, the bounding spatiotemporal box is derived implicitly viastbox(temp). In the case of a spatiotemporal grid,ysizeandzsizeare optional. A size left out, or given as 0, takes the value ofxsize, andzsizeis read only for a value with Z dimension. Every size is a finite number that is not negative, otherwise an error is raised. The SRID of the tile coordinates is determined by the input box and the sizes are given in the units of the SRID. If the origin for the spatial coordinates is given, which must be a point, its dimensionality and SRID should be equal to the one of box, otherwise an error is raised.SELECT spaceTiles(tgeompoint '[Point(3 3)@2001-01-15, Point(15 15)@2001-01-25]', 2.0); -- (1,"STBOX X((2,2),(4,4))") -- (2,"STBOX X((4,2),(6,4))") -- (3,"STBOX X((6,2),(8,4))") -- ... SELECT timeTiles(tgeompoint '[Point(3 3)@2001-01-15, Point(15 15)@2001-01-25]', '2 days'); -- (1,"STBOX T([2001-01-15, 2001-01-17))") -- (2,"STBOX T([2001-01-17, 2001-01-19))") -- (3,"STBOX T([2001-01-19, 2001-01-21))") -- ... SELECT spaceTiles(tgeompoint 'SRID=3812; [Point(3 3)@2001-01-15, Point(15 15)@2001-01-25]'::stbox, 2.0, geometry 'Point(3 3)'); -- (1,"SRID=3812;STBOX X((3,3),(5,5))") -- (2,"SRID=3812;STBOX X((5,3),(7,5))") -- (3,"SRID=3812;STBOX X((7,3),(9,5))") -- ... SELECT spaceTiles(tgeompoint '[Point(3 3 3)@2001-01-15, Point(15 15 15)@2001-01-25]'::stbox, 2.0, geometry 'Point(3 3 3)'); -- (1,"STBOX Z((3,3,3),(5,5,5))") -- (2,"STBOX Z((5,3,3),(7,5,5))") -- (3,"STBOX Z((7,3,3),(9,5,5))") -- ... SELECT spaceTimeTiles(tgeompoint '[Point(3 3)@2001-01-15, Point(15 15)@2001-01-25]', 2.0, interval '2 days'); -- (1,"STBOX XT(((2,2),(4,4)),[2001-01-15,2001-01-17))") -- (2,"STBOX XT(((4,2),(6,4)),[2001-01-15,2001-01-17))") -- (3,"STBOX XT(((6,2),(8,4)),[2001-01-15,2001-01-17))") -- ... SELECT spaceTimeTiles(tgeompoint '[Point(3 3 3)@2001-01-15, Point(15 15 15)@2001-01-25]'::stbox, 2.0, interval '2 days', 'Point(3 3 3)', '2001-01-15'); -- (1,"STBOX ZT(((3,3,3),(5,5,5)),[2001-01-15,2001-01-17))") -- (2,"STBOX ZT(((5,3,3),(7,5,5))),[2001-01-15,2001-01-17)") -- (3,"STBOX ZT(((7,3,3),(9,5,5))),[2001-01-15,2001-01-17)") -- ...
Return the temporal tile that covers a value and/or a timestamp
getValueTile(v float,vsize float,vorigin float=0.0) → tbox getTboxTimeTile(time timestamptz,duration interval, torigin timestamptz='2000-01-03') → tbox getValueTimeTile(v float,t timestamptz,vsize float,duration interval, vorigin float=0.0,torigin timestamptz='2000-01-03') → tbox
If the origin of the value and/or time dimension is not specified, it is set by default to 0 and to Monday, January 3, 2000, respectively.
SELECT getValueTile(15, 2); -- TBOXFLOAT X([14, 16)) SELECT getTboxTimeTile('2001-01-15', interval '2 days'); -- TBOX T([2001-01-13 08:00:00, 2001-01-15 08:00:00)) SELECT getValueTimeTile(15, '2001-01-15', 2, interval '2 days'); -- TBOXFLOAT XT([14, 16),[2001-01-13 08:00:00, 2001-01-15 08:00:00)) SELECT getValueTimeTile(15, '2001-01-15', 2, interval '2 days', 1, '2001-01-02'); -- TBOXFLOAT XT([15, 17),[2001-01-14, 2001-01-16))Return the spatiotemporal tile that covers a point and/or a timestamp
getSpaceTile(point geometry,xsize float,[ysize float,zsize float], sorigin geompoint='Point(0 0 0)') → stbox getStboxTimeTile(time timestamptz,duration interval, torigin timestamptz='2000-01-03') → stbox getSpaceTimeTile(point geometry,time timestamptz,xsize float, [ysize float,zsize float,]duration interval,sorigin geompoint='Point(0 0 0)', torigin timestamptz='2000-01-03') → stbox
If the origin of the space and/or time dimension is not specified, it is set by default to
Point(0 0 0)and to Monday, January 3, 2000, respectively. In the case of a spatiotemporal grid,ysizeandzsizeare optional. A size left out, or given as 0, takes the value ofxsize, andzsizeis read only for a value with Z dimension. Every size is a finite number that is not negative, otherwise an error is raised. The SRID of the tile coordinates is determined by the input point and the sizes are given in the units of the SRID. If the origin for the spatial coordinates is given, which must be a point, its dimensionality and SRID should be equal to the one of box, otherwise an error is raised.SELECT getSpaceTile(geometry 'Point(1 1 1)', 2.0); -- STBOX Z((0,0,0),(2,2,2)) SELECT getStboxTimeTile(timestamptz '2001-01-01', interval '2 days'); -- STBOX T([2000-12-30 08:00:00, 2001-01-01 08:00:00)) SELECT getSpaceTimeTile(geometry 'Point(1 1)', '2001-01-01', 2.0, interval '2 days'); -- STBOX XT(((0,0),(2,2)),[2000-12-30 08:00:00, 2001-01-01 08:00:00)) SELECT getSpaceTimeTile(geometry 'Point(1 1)', '2001-01-01', 2.0, interval '2 days', 'Point(1 1)', '2001-01-02'); -- STBOX XT(((1,1),(3,3)),[2000-12-31, 2001-01-02))
These functions fragment the bounding box of a temporal value with respect to a multidimensional grid. They provide an alternative to the functions in the section called “Splitting Operations” to split the bounding boxes by specifying the maximum size of the boxes in the various dimensions.
Return an array of value or time spans obtained from the instants or segments of a temporal number with respect to a value or time grid
valueBins(tnumber,vsize number,vorigin number=0,borderInc boolean=true) → numspan[] timeBins(ttype,duration interval,torigin timestamptz='2000-01-03', borderInc boolean=true) → tstzspan[]
The choice between instants or segments depends on whether the interpolation is discrete or continuous. If the origin of values or time is not specified, it is set by default to 0 for value spans or to Monday, January 3, 2000 for time spans. The optional argument
borderIncstates whether the upper border of the extent is included and thus, the span starting at that border and holding only it is generated.SELECT valueBins(tint '{1@2001-01-01, 2@2001-01-02, 1@2001-01-03, 4@2001-01-04, 1@2001-01-05}', 3); -- {"[0, 3)","[3, 6)"} SELECT valueBins(tfloat '[1@2001-01-01, 2@2001-01-02, 1@2001-01-03, 4@2001-01-04, 1@2001-01-05]', 3, 1); -- {"[1, 4)","[4, 7)"}SELECT timeBins(ttext '{AAA@2001-01-01, BBB@2001-01-02, AAA@2001-01-03, CCC@2001-01-04, AAA@2001-01-05}', '3 days'); /* {"[1999-12-31 08:00:00, 2001-01-03 08:00:00)","[2001-01-03 08:00:00, 2001-01-06 08:00:00)"} */ SELECT timeBins(tfloat '[1@2001-01-01, 2@2001-01-02, 1@2001-01-03, 4@2001-01-04, 1@2001-01-05]', '3 days', '2001-01-01'); -- {"[2001-01-01, 2001-01-04)","[2001-01-04, 2001-01-07)"}Return an array of temporal boxes obtained from the instants or segments of a temporal number with respect to a value and/or time grid
valueBoxes(tnumber,vsize number,vorigin number=0,borderInc boolean=true) → tbox[] timeBoxes(tnumber,duration interval,torigin timestamptz='2000-01-03', borderInc boolean=true) → tbox[] valueTimeBoxes(tnumber,vsize number,duration interval,vorigin number=0, torigin timestamptz='2000-01-03',borderInc boolean=true) → tbox[]
The choice between instants or segments depends on whether the interpolation is discrete or continuous. If the origin of value and/or time dimension is not specified, it is set by default to 0 and to Monday, January 3, 2000, respectively. The optional argument
borderIncstates whether the upper border of the extent is included and thus, the boxes starting at that border and holding only it are generated.SELECT valueBoxes(tint '{1@2001-01-01, 2@2001-01-02, 1@2001-01-03, 4@2001-01-04, 1@2001-01-05}', 3); /* {"TBOXINT XT([1, 3),[2001-01-01, 2001-01-05])","TBOXINT XT([4, 5),[2001-01-04, 2001-01-04])"} */ SELECT timeBoxes(tint '{1@2001-01-01, 2@2001-01-02, 1@2001-01-03, 4@2001-01-04, 1@2001-01-05}', '3 days'); /* {"TBOXINT XT([1, 3),[2001-01-01, 2001-01-03])","TBOXINT XT([1, 5),[2001-01-04, 2001-01-05])"} */ SELECT valueTimeBoxes(tint '{1@2001-01-01, 2@2001-01-02, 1@2001-01-03, 4@2001-01-04, 1@2001-01-05}', 3, '3 days'); /* {"TBOXINT XT([1, 3),[2001-01-01, 2001-01-03])","TBOXINT XT([1, 2),[2001-01-05, 2001-01-05])","TBOXINT XT([4, 5),[2001-01-04, 2001-01-04])"} */ SELECT valueTimeBoxes(tfloat '[1@2001-01-01, 2@2001-01-02, 1@2001-01-03, 4@2001-01-04, 1@2001-01-05]', 3, '3 days', 1, '2001-01-01'); /* {"TBOXFLOAT XT([1, 4),[2001-01-01, 2001-01-04))","TBOXFLOAT XT([1, 4),(2001-01-04, 2001-01-05])","TBOXFLOAT XT([4, 4],[2001-01-04, 2001-01-04])"} */Return an array of spatiotemporal boxes obtained from the instants or segments of a temporal point with respect to a space and/or time grid
spaceBoxes({tgeompoint,tgeometry,tpose,tpcpoint,trgeometry},xsize float, [ysize float,zsize float,] sorigin geompoint='Point(0 0 0)',borderInc boolean=true) → stbox[] timeBoxes({tgeompoint,tgeometry,tpose,tpcpoint,trgeometry},duration interval, torigin timestamptz='2000-01-03', borderInc boolean=true) → stbox[] spaceTimeBoxes({tgeompoint,tgeometry,tpose,tpcpoint,trgeometry},xsize float, [ysize float,zsize float,]duration interval, sorigin geompoint='Point(0 0 0)',torigin timestamptz='2000-01-03', borderInc boolean=true) → stbox[]The choice between instants or segments depends on whether the interpolation is discrete or continuous. The arguments
ysizeandzsizeare optional. A size left out, or given as 0, takes the value ofxsize, andzsizeis read only for a value with Z dimension. Every size is a finite number that is not negative, otherwise an error is raised. The SRID of the tile coordinates is determined by the temporal point and the sizes are given in the units of the SRID. If the origin for the spatial coordinates is given, which must be a point, its dimensionality and SRID should be equal to the one of temporal point, otherwise an error is raised. If the origin of space and/or time dimension is not specified, it is set by default to'Point(0 0 0)'and to Monday, January 3, 2000, respectively. The optional argumentborderIncstates whether the upper border of the extent is included and thus, extra tiles containing the border are generated. A temporal pose and a temporal point cloud point answer these at the temporal geometry point their position resolves to, so a value carrying linear interpolation is tiled along its trajectory rather than at its instants alone.SELECT spaceBoxes(tgeompoint '{Point(1 1)@2001-01-01, Point(2 2)@2001-01-02, Point(1 1)@2001-01-03, Point(4 4)@2001-01-04, Point(1 1)@2001-01-05}', 3); /* {"STBOX XT(((1,1),(2,2)),[2001-01-01, 2001-01-05])","STBOX XT(((4,4),(4,4)),[2001-01-04, 2001-01-04])"} */ SELECT timeBoxes(tgeompoint '{Point(1 1 1)@2001-01-01, Point(2 2 2)@2001-01-02, Point(1 1 1)@2001-01-03, Point(4 4 4)@2001-01-04, Point(1 1 1)@2001-01-05}', interval '2 days', '2001-01-01'); /* {"STBOX ZT(((1,1,1),(2,2,2)),[2001-01-01, 2001-01-02])","STBOX ZT(((1,1,1),(4,4,4)),[2001-01-03, 2001-01-04])","STBOX ZT(((1,1,1),(1,1,1)),[2001-01-05, 2001-01-05])"} */ SELECT spaceTimeBoxes(tgeompoint '{Point(1 1)@2001-01-01, Point(2 2)@2001-01-02, Point(1 1)@2001-01-03, Point(4 4)@2001-01-04, Point(1 1)@2001-01-05}', 3, '3 days'); /* {"STBOX XT(((1,1),(2,2)),[2001-01-01, 2001-01-03])","STBOX XT(((1,1),(1,1)),[2001-01-05, 2001-01-05])","STBOX XT(((4,4),(4,4)),[2001-01-04, 2001-01-04])"} */ SELECT spaceTimeBoxes(tgeompoint '[Point(1 1 1)@2001-01-01, Point(2 2 2)@2001-01-02, Point(1 1 1)@2001-01-03, Point(4 4 4)@2001-01-04, Point(1 1 1)@2001-01-05]', 3, interval '3 days', 'Point(1 1 1)', '2001-01-01'); /* {"STBOX ZT(((1,1,1),(4,4,4)),[2001-01-01, 2001-01-04))","STBOX ZT(((1,1,1),(4,4,4)),(2001-01-04, 2001-01-05])","STBOX ZT(((4,4,4),(4,4,4)),[2001-01-04, 2001-01-04])"} */ SELECT spaceBoxes(tpose '[Pose(Point(1 1),0.1)@2001-01-01, Pose(Point(3 3),0.1)@2001-01-03]', 2.0); /* {"STBOX XT(((1,1),(2,2)),[2001-01-01, 2001-01-02))","STBOX XT(((2,2),(3,3)),[2001-01-02, 2001-01-03])"} */
These functions split a temporal value with respect to a sequence of bins (see the section called “Bin Operations”) or tiles (see the section called “Tile Operations”).
Split a temporal number with respect to value and/or time bins
valueSplit(tnumber,vsize number,vorigin number=0, borderInc boolean=true) → {(number,tnumber)} timeSplit(ttype,duration interval,torigin timestamptz='2000-01-03', borderInc boolean=true) → {(time,temp)} valueTimeSplit(tnumber,vsize number,duration interval,vorigin number=0, torigin timestamptz='2000-01-03',borderInc boolean=true) → {(number,time,tnumber)}The result is a set of pairs or a set of triples. If the origin of the value and/or the time dimension is not specified, they are set by default to 0 and to Monday, January 3, 2000, respectively. The optional argument
borderIncstates whether the upper border of the extent is included and thus, the fragments starting at that border and holding only it are generated.SELECT (sp).number, (sp).tnumber FROM (SELECT valueSplit(tint '[1@2001-01-01, 2@2001-01-02, 5@2001-01-05, 10@2001-01-10]', 2) AS sp) t; -- 0 | {[1@2001-01-01, 1@2001-01-02)} -- 2 | {[2@2001-01-02, 2@2001-01-05)} -- 4 | {[5@2001-01-05, 5@2001-01-10)} -- 10 | {[10@2001-01-10]} SELECT valueSplit(tfloat '[1@2001-01-01, 10@2001-01-10)', 2.0, 1.0); -- (1,"{[1@2001-01-01, 3@2001-01-03)}") -- (3,"{[3@2001-01-03, 5@2001-01-05)}") -- (5,"{[5@2001-01-05, 7@2001-01-07)}") -- (7,"{[7@2001-01-07, 9@2001-01-09)}") -- (9,"{[9@2001-01-09, 10@2001-01-10)}")SELECT (ts).time, (ts).temp FROM (SELECT timeSplit(tfloat '[1@2001-02-01, 10@2001-02-10)', '2 days') AS ts) t; -- 2001-01-31 | [1@2001-02-01, 2@2001-02-02) -- 2001-02-02 | [2@2001-02-02, 4@2001-02-04) -- 2001-02-04 | [4@2001-02-04, 6@2001-02-06) -- ... SELECT (ts).time, astext((ts).temp) AS temp FROM (SELECT timeSplit(tgeompoint '[Point(1 1)@2001-02-01, Point(10 10)@2001-02-10]', '2 days', '2001-02-01') AS ts) AS t; -- 2001-02-01 | [POINT(1 1)@2001-02-01, POINT(3 3)@2001-02-03) -- 2001-02-03 | [POINT(3 3)@2001-02-03, POINT(5 5)@2001-02-05) -- 2001-02-05 | [POINT(5 5)@2001-02-05, POINT(7 7)@2001-02-07) -- ...
Notice that we can split a temporal value in cyclic (instead of linear) time bins. The following two examples show how to split a temporal value by hour and by day of the week.
SELECT (ts).time::time AS hour, merge((ts).temp) AS temp FROM (SELECT timeSplit(tfloat '[1@2001-01-01, 10@2001-01-03]', '1 hour') AS ts) t GROUP BY hour ORDER BY hour; /* 00:00:00 | {[1@2001-01-01, 1.1875@2001-01-01 01:00:00), [5.5@2001-01-02, 5.6875@2001-01-02 01:00:00)} */ /* 01:00:00 | {[1.1875@2001-01-01 01:00:00, 1.375@2001-01-01 02:00:00), [5.6875@2001-01-02 01:00:00, 5.875@2001-01-02 02:00:00)} */ /* 02:00:00 | {[1.375@2001-01-01 02:00:00, 1.5625@2001-01-01 03:00:00), [5.875@2001-01-02 02:00:00, 6.0625@2001-01-02 03:00:00)} */ /* 03:00:00 | {[1.5625@2001-01-01 03:00:00, 1.75@2001-01-01 04:00:00), [6.0625@2001-01-02 03:00:00, 6.25@2001-01-02 04:00:00)} */ -- ... SELECT EXTRACT(DOW FROM (ts).time) AS dow_no, TO_CHAR((ts).time, 'Dy') AS dow, asText(round(merge((ts).temp), 2)) AS temp FROM (SELECT timeSplit(tgeompoint '[Point(1 1)@2001-01-01, Point(10 10)@2001-01-14)', '1 hour') AS ts) t GROUP BY dow, dow_no ORDER BY dow_no; /* 0 | Sun | {[POINT(1 1)@2001-01-01, POINT(1.69 1.69)@2001-01-02), [POINT(5.85 5.85)@2001-01-08, POINT(6.54 6.54)@2001-01-09)} */ /* 1 | Mon | {[POINT(1.69 1.69)@2001-01-02, POINT(2.38 2.38)@2001-01-03), [POINT(6.54 6.54)@2001-01-09, POINT(7.23 7.23)@2001-01-10)} */ /* 2 | Tue | {[POINT(2.38 2.38)@2001-01-03, POINT(3.08 3.08)@2001-01-04), [POINT(7.23 7.23)@2001-01-10, POINT(7.92 7.92)@2001-01-11)} */ -- ...SELECT (sp).number, (sp).time, (sp).tnumber FROM (SELECT valueTimeSplit(tint '[1@2001-02-01, 2@2001-02-02, 5@2001-02-05, 10@2001-02-10]', 5, '5 days') AS sp) t; -- 0 | 2001-02-01 | {[1@2001-02-01, 2@2001-02-02, 2@2001-02-05)} -- 5 | 2001-02-01 | {[5@2001-02-05, 5@2001-02-06)} -- 5 | 2001-02-06 | {[5@2001-02-06, 5@2001-02-10)} -- 10 | 2001-02-06 | {[10@2001-02-10]} SELECT (sp).number, (sp).time, (sp).tnumber FROM (SELECT valueTimeSplit(tfloat '[1@2001-02-01, 10@2001-02-10)', 5.0, '5 days', 1.0, '2001-02-01') AS sp) t; -- 1 | 2001-01-01 | [1@2001-01-01, 6@2001-01-06) -- 6 | 2001-01-06 | [6@2001-01-06, 10@2001-01-10)Split a temporal point with respect to a spatial grid
spaceSplit({tgeompoint,tgeogpoint,tgeometry,tpose,tpcpoint},xsize float, [ysize float,zsize float,] origin point='Point(0 0 0)',bitmatrix boolean=true, borderInc boolean=true) → {(point,tpoint)} timeSplit(tpoint,duration interval,torigin timestamptz='2000-01-03', borderInc boolean=true) → {(time,tpoint)} spaceTimeSplit({tgeompoint,tgeogpoint,tgeometry,tpose,tpcpoint},xsize float, [ysize float,zsize float,] duration interval,sorigin point='Point(0 0 0)', torigin timestamptz='2000-01-03',bitmatrix boolean=true, borderInc boolean=true) → {(point,time,tpoint)}A temporal pose and a temporal point cloud point answer these at the temporal geometry point their position resolves to, so a value carrying linear interpolation is tiled along its trajectory rather than at its instants alone. The second column of a split carries the type of the first argument, so splitting a temporal pose returns temporal poses. The result is a set of pairs or a set of triples. If the origin of the space and/or time dimension is not specified, it is set by default to
'Point(0 0 0)'and to Monday, January 3, 2000, respectively. The argumentsysizeandzsizeare optional. A size left out, or given as 0, takes the value ofxsize, andzsizeis read only for a value with Z dimension. Every size is a finite number that is not negative, otherwise an error is raised. If the argumentbitmatrixis not specified, then the computation will use a bit matrix to speed up the process. The optional argumentborderIncstates whether the upper border of the extent is included and thus extra tiles containing the border are generated.A geodetic temporal point travels the great circle between two positions, so it is split at the tiles that circle crosses rather than at those of the straight line in longitude and latitude: a trip whose positions both lie at latitude 61 reaches latitude 62.485889 and enters the row of tiles north of the one that line stays in. The grid laid on a geodetic value takes a geography origin and its tiles are geodetic, so a tile and the value restricted to it state coordinates of one kind. The bit matrix is filled from the straight line between two positions, which a great circle leaves, so a geodetic value is split without it and every tile of its bounding box is read instead.
SELECT ST_AsText((sp).point) AS point, astext((sp).tpoint) AS tpoint FROM (SELECT spaceSplit(tgeompoint '[Point(1 1)@2001-03-01, Point(10 10)@2001-03-10]', 2.0) AS sp) t; -- POINT(0 0) | {[POINT(1 1)@2001-03-01, POINT(2 2)@2001-03-02)} -- POINT(2 2) | {[POINT(2 2)@2001-03-02, POINT(4 4)@2001-03-04)} -- POINT(4 4) | {[POINT(4 4)@2001-03-04, POINT(6 6)@2001-03-06)} -- ... SELECT ST_AsText((sp).point) AS point, astext((sp).tpoint) AS tpoint FROM (SELECT spaceSplit(tgeompoint '[Point(1 1 1)@2001-03-01, Point(10 10 10)@2001-03-10]', 2.0, geometry 'Point(1 1 1)') AS sp) t; -- POINT Z(1 1 1) | {[POINT Z (1 1 1)@2001-03-01, POINT Z (3 3 3)@2001-03-03)} -- POINT Z(3 3 3) | {[POINT Z (3 3 3)@2001-03-03, POINT Z (5 5 5)@2001-03-05)} -- POINT Z(5 5 5) | {[POINT Z (5 5 5)@2001-03-05, POINT Z (7 7 7)@2001-03-07)} -- ... SELECT ST_AsText((sp).point) AS point, asText((sp).tpose) AS tpose FROM (SELECT spaceSplit(tpose '[Pose(Point(1 1),0.1)@2001-01-01, Pose(Point(3 3),0.1)@2001-01-03]', 2.0) AS sp) t; -- POINT(0 0) | {[Pose(POINT(1 1),0.1)@2001-01-01, Pose(POINT(2 2),0.1)@2001-01-02)} -- POINT(2 2) | {[Pose(POINT(2 2),0.1)@2001-01-02, Pose(POINT(3 3),0.1)@2001-01-03]} SELECT ST_AsText((sp).point) AS point, asText((sp).tpoint, 6) AS tpoint FROM (SELECT spaceSplit(tgeogpoint '[Point(10 61)@2001-03-01, Point(50 61)@2001-03-03]', 20.0) AS sp) t; -- POINT(0 60) | {[POINT(10 61)@2001-03-01, POINT(20 62.124941)@2001-03-01 12:17:24.687202)} -- POINT(20 60) | {[POINT(20 62.124941)@2001-03-01 12:17:24.687202, -- POINT(40 62.124941)@2001-03-02 11:42:35.312797)} -- POINT(40 60) | {[POINT(40 62.124941)@2001-03-02 11:42:35.312797, POINT(50 61)@2001-03-03]}SELECT (sp).time, astext((sp).temp) AS tpoint FROM (SELECT timeSplit(tgeompoint '[Point(1 1)@2001-02-01, Point(10 10)@2001-02-10]', interval '2 days') AS sp) t; -- 2001-01-31 | [POINT(1 1)@2001-02-01, POINT(2 2)@2001-02-02) -- 2001-02-02 | [POINT(2 2)@2001-02-02, POINT(4 4)@2001-02-04) -- 2001-02-04 | [POINT(4 4)@2001-02-04, POINT(6 6)@2001-02-06) -- ...
SELECT ST_AsText((sp).point) AS point, (sp).time, astext((sp).tpoint) AS tpoint FROM (SELECT spaceTimeSplit(tgeompoint '[Point(1 1)@2001-02-01, Point(10 10)@2001-02-10]', 2.0, interval '2 days') AS sp) t; -- POINT(0 0) | 2001-01-31 | {[POINT(1 1)@2001-02-01, POINT(2 2)@2001-02-02)} -- POINT(2 2) | 2001-01-31 | {[POINT(2 2)@2001-02-02]} -- POINT(2 2) | 2001-02-02 | {[POINT(2 2)@2001-02-02, POINT(4 4)@2001-02-04)} -- ... SELECT ST_AsText((sp).point) AS point, (sp).time, astext((sp).tpoint) AS tpoint FROM (SELECT spaceTimeSplit(tgeompoint '[Point(1 1 1)@2001-02-01, Point(10 10 10)@2001-02-10]', 2.0, interval '2 days', 'Point(1 1 1)', '2001-03-01') AS sp) t; -- POINT Z(1 1 1) | 2001-02-01 | {[POINT Z(1 1 1)@2001-02-01, POINT Z(3 3 3)@2001-02-03)} -- POINT Z(3 3 3) | 2001-02-01 | {[POINT Z(3 3 3)@2001-02-03]} -- POINT Z(3 3 3) | 2001-02-03 | {[POINT Z(3 3 3)@2001-02-03, POINT Z (5 5 5)@2001-02-05)} -- ...