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.
Return a set of bins that cover a value or time span with bins of the same size or duration
bins(numspan,size number,origin number=0) → {(index,span)}bins(datespan,duration interval,origin date='2000-01-03') → {(index,span)}bins(tstzspan,duration interval,origin timestamptz='2000-01-03') → {(index,span)}The result is a set of pairs
(index,span). If the origin is not specified, it is set by default to 0 for value spans and Monday, January 3, 2000 for time spans. The indices start by 1.SELECT bins(floatspan '[-10, -1]', 2.5, -7); -- (1,"[-12, -9.5)") -- (2,"[-9.5, -7)") -- (3,"[-7, -4.5)") -- ... SELECT (bl).index, (bl).span FROM (SELECT bins(intspan '[15, 25]', 2) AS bl) t; -- 1 | [14,16) -- 2 | [16,18) -- 3 | [18,20) -- ... SELECT (bl).index, (bl).span FROM (SELECT bins(datespan '[2001-01-15, 2001-01-25]','2 days') AS bl) t; -- 1 | [2001-01-15, 2001-01-17) -- 2 | [2001-01-17, 2001-01-19) -- 3 | [2001-01-19, 2001-01-21) -- ... SELECT bins(tstzspan '[2001-01-15, 2001-01-25]', '2 days', '2001-01-02'); -- (1,"[2001-01-14, 2001-01-16)") -- (2,"[2001-01-16, 2001-01-18)") -- (3,"[2001-01-18, 2001-01-20)") -- ...
Return the bin that contains a number or a timestamp
getBin(number,size number,origin number=0) → spangetBin(date,duration interval,origin date='2000-01-03') →getBin(timestamptz,duration interval,origin timestamptz='2000-01-03') →timestamptzIf 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-03, 2001-01-10) 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 float,vorigin float=0) → {(index,tile)}timeTiles(tbox,duration interval,torigin timestamptz='2000-01-03') →{(index,tile)}valueTimeTiles(tbox,vsize float,duration interval,vorigin float=0,torigin timestamptz='2000-01-03') → {(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.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 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,xsize float,[ysize float,zsize float,]sorigin geompoint='Point(0 0 0)',borderInc bool=true) → {(index,tile)}timeTiles(stbox,duration interval,torigin timestamptz='2000-01-03',borderInc bool=true) → {(index,tile)}spaceTimeTiles(stbox,xsize float,[ysize float,zsize float,]duration interval,sorigin geompoint='Point(0 0 0)',torigin timestamptz='2000-01-03',borderInc bool=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.In the case of a spatiotemporal grid,
ysizeandzsizeare optional, the size for the missing dimensions is assumed to be equal toxsize. 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]'::stbox, 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]'::stbox, '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]'::stbox, 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(value float,vsize float,vorigin float=0.0,) → tboxgetTboxTimeTile(time timestamptz,duration interval,torigin timestamptz='2000-01-03') → tboxgetValueTimeTile(value float,time timestamptz,size float,duration interval,vorigin float=0.0,torigin timestamptz='2000-01-03') → tboxIf 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); -- TBOX ([14,16)) SELECT getTboxTimeTile('2001-01-15', interval '2 days'); -- TBOX ([2001-01-15,2001-01-17)) SELECT getValueTimeTile(15, '2001-01-15', 2, interval '2 days'); -- TBOX XT([14,16),[2001-01-15,2001-01-17)) SELECT getValueTimeTile(15, '2001-01-15', 2, interval '2 days', 1, '2001-01-02'); -- TBOX 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)') → stboxgetStboxTimeTile(time timestamptz,duration interval,torigin timestamptz='2000-01-03') → stboxgetSpaceTimeTile(point geometry,time timestamptz,xsize float,[ysize float,zsize float,]durationinterval,sorigin geompoint='Point(0 0 0)',torigin timestamptz='2000-01-03') → stboxIf 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, the size for the missing dimensions is assumed to be equal toxsize. 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([2001-01-01,2001-01-03)) SELECT getSpaceTimeTile(geometry 'Point(1 1)', '2001-01-01', 2.0, interval '2 days'); -- STBOX XT(((0,0),(2,2)),[2001-01-01, 2001-01-03)) 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 “Bounding Box Splitting” to split the bounding boxes by specifying the maximum size of the boxes in the various dimensions.
Return an array of spans obtained from the instants or segments of a temporal value with respect to a time grid
timeSpans(temp,duration interval,torigin timestamptz='2000-01-03') → tstzspan[]The choice between instants or segments depends on whether the interpolation is discrete or continuous. If the origin of value or time dimension is not specified, it is set by default to 0 for value spans or to Monday, January 3, 2000 for time spans.
SELECT timeSpans(ttext '{AAA@2000-01-01, BBB@2000-01-02, AAA@2000-01-03, CCC@2000-01-04, AAA@2000-01-05}', '3 days'); -- {"[2000-01-01, 2000-01-02]","[2000-01-03, 2000-01-05]"} SELECT timeSpans(tfloat '[1@2000-01-01, 2@2000-01-02, 1@2000-01-03, 4@2000-01-04, 1@2000-01-05]', '3 days', '2000-01-01'); -- {"[2000-01-01, 2000-01-04)","[2000-01-04, 2000-01-05)"}Return an array of spans obtained from the instants or segments of a temporal number with respect to a value grid
valueSpans(tnumber,vsize number,vorigin number=0) → numspan[]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.
SELECT valueSpans(tint '{1@2000-01-01, 2@2000-01-02, 1@2000-01-03, 4@2000-01-04, 1@2000-01-05}', 3); -- {"[1, 3)","[4, 5)"} SELECT valueSpans(tfloat '[1@2000-01-01, 2@2000-01-02, 1@2000-01-03, 4@2000-01-04, 1@2000-01-05]', 3, 1); -- {"[1, 4)","[4, 4]"}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,size number,vorigin number=0) → tbox[]timeBoxes(tnumber,duration interval,torigin timestamptz='2000-01-03') → tbox[]valueTimeBoxes(tnumber,size number,duration interval,vorigin number=0,torigin timestamptz='2000-01-03') → 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.
SELECT valueBoxes(tint '{1@2000-01-01, 2@2000-01-02, 1@2000-01-03, 4@2000-01-04, 1@2000-01-05}', 3); /* {"TBOXINT XT([1, 3),[2000-01-01, 2000-01-05])", "TBOXINT XT([4, 5),[2000-01-04, 2000-01-04])"} */ SELECT timeBoxes(tint '{1@2000-01-01, 2@2000-01-02, 1@2000-01-03, 4@2000-01-04, 1@2000-01-05}', '3 days'); /* {"TBOXINT XT([1, 3),[2000-01-01, 2000-01-02])", "TBOXINT XT([1, 5),[2000-01-03, 2000-01-05])"} */ SELECT valueTimeBoxes(tint '{1@2000-01-01, 2@2000-01-02, 1@2000-01-03, 4@2000-01-04, 1@2000-01-05}', 3, '3 days'); /* {"TBOXINT XT([1, 3),[2000-01-01, 2000-01-02])", "TBOXINT XT([1, 2),[2000-01-03, 2000-01-05])", "TBOXINT XT([4, 5),[2000-01-04, 2000-01-04])"} */ SELECT valueTimeBoxes(tfloat '[1@2000-01-01, 2@2000-01-02, 1@2000-01-03, 4@2000-01-04, 1@2000-01-05]', 3, '3 days', 1, '2000-01-01'); /* {"TBOXFLOAT XT([1, 4),[2000-01-01, 2000-01-04))", "TBOXFLOAT XT([1, 4),(2000-01-04, 2000-01-05])", "TBOXFLOAT XT([4, 4],[2000-01-04, 2000-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,xsize float,[ysize float,zsize float,]sorigin geompoint='Point(0 0 0)',borderInc bool=true) → stbox[]timeBoxes(tgeompoint,duration interval,torigin timestamptz='2000-01-03',borderInc bool=true) → stbox[]spaceTimeBoxes(tgeompoint,xsize float,[ysize float,zsize float,]duration interval,sorigin geompoint='Point(0 0 0)',torigin timestamptz='2000-01-03',borderInc bool=true) → stbox[]The choice between instants or segments depends on whether the interpolation is discrete or continuous. The arguments
ysizeandzsizeare optional, the size for the missing dimensions is assumed to be equal toxsize. 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.SELECT spaceBoxes(tgeompoint '{Point(1 1)@2000-01-01, Point(2 2)@2000-01-02, Point(1 1)@2000-01-03, Point(4 4)@2000-01-04, Point(1 1)@2000-01-05}', 3); /* {"STBOX XT(((1,1),(2,2)),[2000-01-01, 2000-01-05])", "STBOX XT(((4,4),(4,4)),[2000-01-04, 2000-01-04])"} */ SELECT timeBoxes(tgeompoint '{Point(1 1 1)@2000-01-01, Point(2 2 2)@2000-01-02, Point(1 1 1)@2000-01-03, Point(4 4 4)@2000-01-04, Point(1 1 1)@2000-01-05}', interval '2 days', '2000-01-01'); /* {"STBOX ZT(((1,1,1),(2,2,2)),[2000-01-01, 2000-01-02])", "STBOX ZT(((1,1,1),(4,4,4)),[2000-01-03, 2000-01-04])", "STBOX ZT(((1,1,1),(1,1,1)),[2000-01-05, 2000-01-05])"} */ SELECT spaceTimeBoxes(tgeompoint '{Point(1 1)@2000-01-01, Point(2 2)@2000-01-02, Point(1 1)@2000-01-03, Point(4 4)@2000-01-04, Point(1 1)@2000-01-05}', 3, '3 days'); /* {"STBOX XT(((1,1),(2,2)),[2000-01-01, 2000-01-02])", "STBOX XT(((1,1),(1,1)),[2000-01-03, 2000-01-05])", "STBOX XT(((4,4),(4,4)),[2000-01-04, 2000-01-04])"} */ SELECT spaceTimeBoxes(tgeompoint '[Point(1 1 1)@2000-01-01, Point(2 2 2)@2000-01-02, Point(1 1 1)@2000-01-03, Point(4 4 4)@2000-01-04, Point(1 1 1)@2000-01-05]', 3, interval '3 days', 'Point(1 1 1)', '2000-01-01'); /* {"STBOX ZT(((1,1,1),(4,4,4)),[2000-01-01, 2000-01-04))", "STBOX ZT(((1,1,1),(4,4,4)),(2000-01-04, 2000-01-05])", "STBOX ZT(((4,4,4),(4,4,4)),[2000-01-04, 2000-01-04])"} */
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 bins
valueSplit(tnumber,size number,origin number=0) → {(number,tnumber)}The result is a set of pairs
(number,tnumber). If the origin of values is not specified, it is set by default to 0.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 00:00:00+01)}")Split a temporal numbe a temporal value with respect to time bins
timeSplit(ttype,duration interval,origin timestamptz='2000-01-03') →{(time,temp)}The result is a set of pairs
(time,temp). If the origin of time dimension is not specified, it is set by default to Monday, January 3, 2000.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 00:00:00+01, 1.1875@2001-01-01 01:00:00+01), [5.5@2001-01-02 00:00:00+01, 5.6875@2001-01-02 01:00:00+01)} */ /* 01:00:00 | {[1.1875@2001-01-01 01:00:00+01, 1.375@2001-01-01 02:00:00+01), [5.6875@2001-01-02 01:00:00+01, 5.875@2001-01-02 02:00:00+01)} */ /* 02:00:00 | {[1.375@2001-01-01 02:00:00+01, 1.5625@2001-01-01 03:00:00+01), [5.875@2001-01-02 02:00:00+01, 6.0625@2001-01-02 03:00:00+01)} */ /* 03:00:00 | {[1.5625@2001-01-01 03:00:00+01, 1.75@2001-01-01 04:00:00+01), [6.0625@2001-01-02 03:00:00+01, 6.25@2001-01-02 04:00:00+01)} */ /* ... */ 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)} */ /* ... */Split a temporal number with respect to a value and time grid
valueTimeSplit(tnumber,size number,duration interval,vorigin number=0,torigin timestamptz='2000-01-03') → {(number,time,tnumber}The result is a set of triples
(number,time,tnumber). 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.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,xsize float,[ysize float,zsize float,]origin geompoint='Point(0 0 0)',bitmatrix boolean=true,borderInc bool=true) →{(point,tpoint)}The result is a set of pairs
(point,tpoint). If the origin of the space dimension is not specified, it is set by default to'Point(0 0 0)'. The argumentsysizeandzsizeare optional, the size for the missing dimensions is assumed to be equal toxsize. 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.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)} -- ...Split a temporal point with respect to a spatiotemporal grid
spaceTimeSplit(tgeompoint,xsize float,[ysize float,zsize float,]duration interval,sorigin geompoint='Point(0 0 0)',torigin timestamptz='2000-01-03',bitmatrix boolean=true,borderInc boolean=true) →{(point,time,tpoint)}The result is a set of triples
(point,time,tpoint). 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, the size for the missing dimensions is assumed to be equal toxsize. 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.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)} -- ...