When creating indexes for temporal types, what is stored in the index is not the actual value but instead, a bounding box that represents the value. In this case, the index will provide a list of candidate values that may satisfy the query predicate, and a second step is needed to filter out candidate values by computing the query predicate on the actual values.
However, when the bounding boxes have a large empty space not covered by the actual values, the index will generate many candidate values that do not satisfy the query predicate, which reduces the efficiency of the index. In these situations, it may be better to represent a value not with a single bounding box, but instead with multiple bounding boxes. This increases considerably the efficiency of the index, provided that the index is able to manage multiple bounding boxes per value. The following functions are used for generating multiple bounding boxes for a single temporal value.
Return an array of N time spans obtained by merging the instants or segments of a temporal value
splitNSpans(temp, integer) → tstzspan[]The choice between instants or segments depends on whether the interpolation is discrete or continuous. The last argument specifies the number of output spans. If the number of instants or segments is less than or equal to the given number, the resulting array will have one span per instant or segment of the temporal value. Otherwise, the given number of spans will be obtained by merging consecutive instants or segments.
SELECT splitNSpans(ttext '{A@2000-01-01, B@2000-01-02, A@2000-01-03, B@2000-01-04, A@2000-01-05}', 1); -- {"[2000-01-01, 2000-01-05]"} SELECT splitNSpans(tfloat '{1@2000-01-01, 2@2000-01-02, 1@2000-01-03, 2@2000-01-04, 1@2000-01-05}', 2); -- {"[2000-01-01, 2000-01-03]","[2000-01-04, 2000-01-05]"} SELECT splitNSpans(tgeompoint '[Point(1 1)@2000-01-01, Point(2 2)@2000-01-02, Point(1 1)@2000-01-03, Point(2 2)@2000-01-04, Point(1 1)@2000-01-05]', 2); -- {"[2000-01-01, 2000-01-03]","[2000-01-03, 2000-01-05]"} SELECT splitNSpans(tgeogpoint '{[Point(1 1 1)@2000-01-01, Point(2 2 2)@2000-01-02], [Point(1 1 1)@2000-01-03, Point(2 2 2)@2000-01-04], [Point(1 1 1)@2000-01-05]}', 2); -- {"[2000-01-01, 2000-01-04])","[2000-01-05, 2000-01-05])"} SELECT splitNSpans(tint '{1@2000-01-01, 2@2000-01-02, 1@2000-01-03, 2@2000-01-04, 2@2000-01-05}', 6); /* {"[2000-01-01, 2000-01-01]","[2000-01-02, 2000-01-02]","[2000-01-03, 2000-01-03]", "[2000-01-04, 2000-01-04]","[2000-01-05, 2000-01-05]"} */ SELECT splitNSpans(ttext '[A@2000-01-01, B@2000-01-02, A@2000-01-03, B@2000-01-04, A@2000-01-05]', 6); /* {"[2000-01-01, 2000-01-02]","[2000-01-02, 2000-01-03]", "[2000-01-03, 2000-01-04]","[2000-01-04, 2000-01-05]"} */Return an array of time spans obtained by merging N consecutive instants or segments of a temporal value
splitEachNSpans(temp, integer) → tstzspan[]The choice between instants or segments depends on whether the interpolation is discrete or continuous. The last argument specifies the number of input instants or segments that are merged to produce an output span. If the number of input elements is less than or equal to the given number, the resulting array will have a single span per sequence. Otherwise, the given number of consecutive instants or segments will be merged into each output span. Notice that, contrary to the
splitNSpansfunction, the number of spans in the result depends on the number of input instants or segments.SELECT splitEachNSpans(ttext '{A@2000-01-01, B@2000-01-02, A@2000-01-03, B@2000-01-04, A@2000-01-05}', 1); /* {"[2000-01-01, 2000-01-01]","[2000-01-02, 2000-01-02]","[2000-01-03, 2000-01-03]", "[2000-01-04, 2000-01-04]","[2000-01-05, 2000-01-05]} */ SELECT splitEachNSpans(tfloat '[1@2000-01-01, 2@2000-01-02, 1@2000-01-03, 2@2000-01-04, 1@2000-01-05]', 2); -- {"[2000-01-01, 2000-01-03]","[2000-01-03, 2000-01-05]"} SELECT splitEachNSpans(tgeompoint '{[Point(1 1 1)@2000-01-01, Point(2 2 2)@2000-01-02], [Point(1 1 1)@2000-01-03, Point(2 2 2)@2000-01-04], [Point(1 1 1)@2000-01-05]}', 2); -- {"[2000-01-01, 2000-01-02]","[2000-01-03, 2000-01-04]","[2000-01-05, 2000-01-05]"} SELECT splitEachNSpans(tgeogpoint '[Point(1 1 1)@2000-01-01, Point(2 2 2)@2000-01-02, Point(1 1 1)@2000-01-03, Point(2 2 2)@2000-01-04, Point(1 1 1)@2000-01-05]', 6); -- {"[2000-01-01, 2000-01-05])"} SELECT splitEachNSpans(tgeogpoint '{[Point(1 1 1)@2000-01-01, Point(2 2 2)@2000-01-02], [Point(1 1 1)@2000-01-03, Point(2 2 2)@2000-01-04], [Point(1 1 1)@2000-01-05]}', 6); -- {"[2000-01-01, 2000-01-02]","[2000-01-03, 2000-01-04]","[2000-01-05, 2000-01-05]"}Return an array of N temporal boxes obtained by merging the instants or segments of a temporal number
splitNTboxes(tnumber, integer) → tbox[]The choice between instants or segments depends on whether the interpolation is discrete or continuous. The last argument specifies the number of output boxes. If the number of input instants or segments is less than or equal to the given number, the resulting array will have one box per instant or segment of the temporal number. Otherwise, the specified number of boxes will be obtained by merging consecutive instants or segments.
SELECT splitNTboxes(tint '{1@2000-01-01, 2@2000-01-02, 1@2000-01-03, 4@2000-01-04, 1@2000-01-05}', 1); -- {"TBOXINT XT([1, 5),[2000-01-01, 2000-01-05])"} SELECT splitNTboxes(tfloat '{[1@2000-01-01, 2@2000-01-02], [1@2000-01-03, 4@2000-01-04], [1@2000-01-05]}', 2); /* {"TBOXFLOAT XT([1, 4],[2000-01-01, 2000-01-04])", "TBOXFLOAT XT([1, 1],[2000-01-05, 2000-01-05])"} */ SELECT splitNTboxes(tfloat '[1@2000-01-01, 2@2000-01-02, 1@2000-01-03, 4@2000-01-04, 1@2000-01-05]', 3); /* {"TBOXFLOAT XT([1, 2],[2000-01-01, 2000-01-03])", "TBOXFLOAT XT([1, 4],[2000-01-03, 2000-01-04])", "TBOXFLOAT XT([1, 4],[2000-01-04, 2000-01-05])"} */ SELECT splitNTboxes(tint '{1@2000-01-01, 2@2000-01-02, 1@2000-01-03, 4@2000-01-04, 1@2000-01-05}', 6); /* {"TBOXINT XT([1, 2),[2000-01-01, 2000-01-01])", "TBOXINT XT([2, 3),[2000-01-02, 2000-01-02])", "TBOXINT XT([1, 2),[2000-01-03, 2000-01-03])", "TBOXINT XT([4, 5),[2000-01-04, 2000-01-04])", "TBOXINT XT([1, 2),[2000-01-05, 2000-01-05])"} */ SELECT splitNTboxes(tint '[1@2000-01-01, 2@2000-01-02, 1@2000-01-03, 4@2000-01-04, 1@2000-01-05]', 6); /* {"TBOXINT XT([1, 3),[2000-01-01, 2000-01-02])", "TBOXINT XT([1, 3),[2000-01-02, 2000-01-03])", "TBOXINT XT([1, 5),[2000-01-03, 2000-01-04])", "TBOXINT XT([1, 5),[2000-01-04, 2000-01-05])"} */Return an array of temporal boxes obtained by merging N consecutive instants or segments of a temporal number
splitEachNTboxes(tnumber, integer) → tbox[]The choice between instants or segments depends on whether the interpolation is discrete or continuous. The last argument specifies the number of input instants or segments that are merged to produce an output box. If the number of input instants or segments is less than or equal to the given number, the resulting array will have a single box per sequence. Otherwise, the specified number of consecutive instants or segments will be merged into each output box. Notice that, contrary to the
splitNTboxesfunction, the number of boxes in the result depends on the number of input instants or segments.SELECT splitEachNTboxes(tint '{1@2000-01-01, 2@2000-01-02, 1@2000-01-03, 4@2000-01-04, 1@2000-01-05}', 1); /* {"TBOXINT XT([1, 2),[2000-01-01, 2000-01-01])", "TBOXINT XT([2, 3),[2000-01-02, 2000-01-02])", "TBOXINT XT([1, 2),[2000-01-03, 2000-01-03])", "TBOXINT XT([4, 5),[2000-01-04, 2000-01-04])"} */ SELECT splitEachNTboxes(tint '[1@2000-01-01, 2@2000-01-02, 1@2000-01-03, 4@2000-01-04, 1@2000-01-05]', 1); /* {"TBOXINT XT([1, 3),[2000-01-01, 2000-01-02])", "TBOXINT XT([1, 3),[2000-01-02, 2000-01-03])", "TBOXINT XT([1, 5),[2000-01-03, 2000-01-04])", "TBOXINT XT([1, 5),[2000-01-04, 2000-01-05])"} */ SELECT splitEachNTboxes(tfloat '[1@2000-01-01, 2@2000-01-02, 1@2000-01-03, 4@2000-01-04, 1@2000-01-05]', 3); /* {"TBOXFLOAT XT([1, 4],[2000-01-01, 2000-01-04])", "TBOXFLOAT XT([1, 4],[2000-01-04, 2000-01-05])"} */ SELECT splitEachNTboxes(tfloat '{[1@2000-01-01, 2@2000-01-02], [1@2000-01-03, 4@2000-01-04], [1@2000-01-05]}', 6); /* {"TBOXFLOAT XT([1, 2],[2000-01-01, 2000-01-02])", "TBOXFLOAT XT([1, 4],[2000-01-03, 2000-01-04])", "TBOXFLOAT XT([1, 1],[2000-01-05, 2000-01-05])"} */Return either an array of N spatial boxes obtained by merging the segments of a (multi)line or an array of N spatiotemporal boxes obtained by merging the instants or segments of a temporal point
splitNStboxes(lines, integer) → stbox[]splitNStboxes(tpoint, integer) → stbox[]For temporal points, the choice between instants or segments depends on whether the interpolation is discrete or continuous. The last argument specifies the number of output boxes. If the number of instants or segments is less than or equal to the given number, the resulting array will have either one box per segment of the (multi)line or one box per instant or segment of the temporal point. Otherwise, the specified number of boxes will be obtained by merging consecutive instants or segments.
SELECT splitNStboxes(geometry 'Linestring(1 1,2 2,3 1,4 2,5 1)', 1); -- {"STBOX X((1,1),(5,2))"} SELECT splitNStboxes(geometry 'Linestring(1 1,2 2,3 1,4 2,5 1)', 2); -- {"STBOX X((1,1),(3,2))","STBOX X((3,1),(5,2))"} SELECT splitNStboxes(geography 'Linestring(1 1 1,2 2 1,3 1 1,4 2 1,5 1 1)', 6); /* {"SRID=4326;GEODSTBOX Z((1,1,1),(2,2,1))", "SRID=4326;GEODSTBOX Z((2,1,1),(3,2,1))", "SRID=4326;GEODSTBOX Z((3,1,1),(4,2,1))", "SRID=4326;GEODSTBOX Z((4,1,1),(5,2,1))"} */ SELECT splitNStboxes(geometry 'MultiLinestring((1 1,2 2),(3 1,4 2),(5 1,6 2))', 2); -- {"STBOX X((1,1),(4,2))","STBOX X((5,1),(6,2))"}SELECT splitNStboxes(tgeompoint '{Point(1 1)@2000-01-01, Point(2 2)@2000-01-02, Point(3 1)@2000-01-03, Point(4 2)@2000-01-04, Point(5 1)@2000-01-05}', 1); -- {"STBOX XT(((1,1),(5,2)),[2000-01-01, 2000-01-05])"} SELECT splitNStboxes(tgeompoint '[Point(1 1)@2000-01-01, Point(2 2)@2000-01-02, Point(3 1)@2000-01-03, Point(4 2)@2000-01-04, Point(5 1)@2000-01-05]'); /* {"STBOX XT(((1,1),(2,2)),[2000-01-01, 2000-01-02])", "STBOX XT(((2,1),(3,2)),[2000-01-02, 2000-01-03])", "STBOX XT(((3,1),(4,2)),[2000-01-03, 2000-01-04])", "STBOX XT(((4,1),(5,2)),[2000-01-04, 2000-01-05])"} */ SELECT splitNStboxes(tgeompoint '{[Point(1 1)@2000-01-01, Point(2 2)@2000-01-02], [Point(3 1)@2000-01-03, Point(4 2)@2000-01-04], [Point(5 1)@2000-01-05]}', 2); /* {"STBOX XT(((1,1),(4,2)),[2000-01-01, 2000-01-04])", "STBOX XT(((5,1),(5,1)),[2000-01-05, 2000-01-05])"} */ SELECT splitNStboxes(tgeogpoint '{Point(1 1 1)@2000-01-01, Point(2 2 1)@2000-01-02, Point(3 1 1)@2000-01-03, Point(4 2 1)@2000-01-04, Point(5 1 1)@2000-01-05}', 6); /* {"SRID=4326;GEODSTBOX ZT(((1,1,1),(1,1,1)),[2000-01-01, 2000-01-01])", "SRID=4326;GEODSTBOX ZT(((2,2,1),(2,2,1)),[2000-01-02, 2000-01-02])", "SRID=4326;GEODSTBOX ZT(((3,1,1),(3,1,1)),[2000-01-03, 2000-01-03])", "SRID=4326;GEODSTBOX ZT(((4,2,1),(4,2,1)),[2000-01-04, 2000-01-04])", "SRID=4326;GEODSTBOX ZT(((5,1,1),(5,1,1)),[2000-01-05, 2000-01-05])"} */ SELECT splitNStboxes(tgeompoint '[Point(1 1)@2000-01-01, Point(2 2)@2000-01-02, Point(3 1)@2000-01-03, Point(4 2)@2000-01-04, Point(5 1)@2000-01-05]', 6); /* {"STBOX XT(((1,1),(2,2)),[2000-01-01, 2000-01-02])", "STBOX XT(((2,1),(3,2)),[2000-01-02, 2000-01-03])", "STBOX XT(((3,1),(4,2)),[2000-01-03, 2000-01-04])", "STBOX XT(((4,1),(5,2)),[2000-01-04, 2000-01-05])"} */Return either an array of spatial boxes obtained by merging N consecutive segments of a (multi)line or an array of spatiotemporal boxes obtained by merging N consecutive instants or segments of a temporal point
splitEachNStboxes(lines, integer) → stbox[]splitEachNStboxes(tpoint, integer) → stbox[]For temporal points, the choice between instants or segments depends on whether the interpolation is discrete or continuous. The last argument specifies the number of input instants or segments that are merged to produce an output box. If the number of instants or segments is less than or equal to the given number, the resulting array will have a single box per sequence. Otherwise, the specified number of consecutive instants or segments will be merged into each output box. Notice that, contrary to the
splitNStboxesfunction, the number of boxes in the result depends on the number of input instants or segments.SELECT splitEachNStboxes(geometry 'Linestring(1 1,2 2,3 1,4 2,5 1)', 1); -- {"STBOX X((1,1),(5,2))"} SELECT splitEachNStboxes(geometry 'Linestring(1 1,2 2,3 1,4 2,5 1)', 2); -- {"STBOX X((1,1),(3,2))","STBOX X((3,1),(5,2))"} SELECT splitEachNStboxes(geography 'Linestring(1 1 1,2 2 1,3 1 1,4 2 1,5 1 1)', 6); /* {"SRID=4326;GEODSTBOX Z((1,1,1),(2,2,1))", "SRID=4326;GEODSTBOX Z((2,1,1),(3,2,1))", "SRID=4326;GEODSTBOX Z((3,1,1),(4,2,1))", "SRID=4326;GEODSTBOX Z((4,1,1),(5,2,1))"} */ SELECT splitEachNStboxes(geometry 'MultiLinestring((1 1,2 2),(3 1,4 2),(5 1,6 2))', 2); -- {"STBOX X((1,1),(4,2))","STBOX X((5,1),(6,2))"}SELECT splitEachNStboxes(tgeompoint '{Point(1 1)@2000-01-01, Point(2 2)@2000-01-02, Point(3 1)@2000-01-03, Point(4 2)@2000-01-04, Point(5 1)@2000-01-05}', 1); /* {"STBOX XT(((1,1),(1,1)),[2000-01-01, 2000-01-01])", "STBOX XT(((2,2),(2,2)),[2000-01-02, 2000-01-02])", "STBOX XT(((3,1),(3,1)),[2000-01-03, 2000-01-03])", "STBOX XT(((4,2),(4,2)),[2000-01-04, 2000-01-04])", "STBOX XT(((5,1),(5,1)),[2000-01-05, 2000-01-05])"} */ SELECT splitEachNStboxes(tgeompoint '[Point(1 1)@2000-01-01, Point(2 2)@2000-01-02, Point(3 1)@2000-01-03, Point(4 2)@2000-01-04, Point(5 1)@2000-01-05]', 1); /* {"STBOX XT(((1,1),(2,2)),[2000-01-01, 2000-01-02])", "STBOX XT(((2,1),(3,2)),[2000-01-02, 2000-01-03])", "STBOX XT(((3,1),(4,2)),[2000-01-03, 2000-01-04])", "STBOX XT(((4,1),(5,2)),[2000-01-04, 2000-01-05])"} */ SELECT splitEachNStboxes(tgeogpoint '{[Point(1 1)@2000-01-01, Point(2 2)@2000-01-02], [Point(3 1)@2000-01-03, Point(4 2)@2000-01-04], [Point(5 1)@2000-01-05]}', 2); /* {"SRID=4326;GEODSTBOX XT(((1,1),(2,2)),[2000-01-01, 2000-01-02])", "SRID=4326;GEODSTBOX XT(((3,1),(4,2)),[2000-01-03, 2000-01-04])", "SRID=4326;GEODSTBOX XT(((5,1),(5,1)),[2000-01-05, 2000-01-05])"} */