cube

mdx: format: md

This module implements a data type cube for representing multidimensional cubes.

This module is considered "trusted", meaning it can be installed by non-superusers who have CREATE privilege on the current database.

1. Syntax

Table C.2 shows the valid external representations of the cube type. x, y, etc. represent floating-point numbers.

Table C.2 Cube External Representations

External Syntax	Meaning
x	A one-dimensional point (or a one-dimensional interval of zero length)
(x)	Same as above
x1,x2,...,xn	A point in n-dimensional space, internally represented as a zero-volume cube
(x1,x2,...,xn)	Same as above
(x),(y)	A one-dimensional interval starting at x and ending at y, or vice versa. Order does not matter
[(x),(y)]	Same as above
(x1,...,xn),(y1,...,yn)	An n-dimensional cube represented by its diagonally opposite corner pair
[(x1,...,xn),(y1,...,yn)]	Same as above

The order of the diagonally opposite entries in a cube does not matter. cube functions automatically swap values if needed to create a uniform "lower-left to upper-right" internal representation. When the corners coincide, cube stores only one corner and an "is point" flag, thus avoiding wasted space.

White space in the input is ignored, so [(x),(y)] is the same as [ ( x ), ( y ) ].

2. Precision

Values are stored internally as 64-bit floating-point numbers. This means that numbers with more than 16 significant digits will be truncated.

3. Usage

Table C.3 shows the specialized operators provided for the cube type.

Table C.3. Cube Operators

Operator/Description
cube && cube → boolean Do the cubes overlap?
cube @> cube → boolean Does the first cube contain the second?
cube <@ cube → boolean Is the first cube contained in the second?
cube -> integer → float8 Extracts the n-th coordinate of the cube (counting from 1).
cube ~> integer → float8 Extracts the n-th coordinate of the cube, counted as follows: n = 2 * k - 1 means the lower bound of the k-th dimension, n = 2 * k means the upper bound of the k-th dimension. A negative n indicates the reciprocal of the corresponding positive coordinate. This operator is designed for KNN-GiST support.
cube <-> cube → float8 Computes the Euclidean distance between two cubes.
cube <#> cube → float8 Computes the straight-line (L-1 metric) distance between two cubes.
cube <=> cube → float8 Computes the Chebyshev (L-inf metric) distance between two cubes.

These operators compare the first coordinate, then the second if they are equal, and so on. They primarily exist to support the b-tree index operator class for cube, which is useful for supporting UNIQUE constraints on cube columns. Otherwise, this ordering has little practical use.

The cube module also provides a GiST index operator class for cube values. cube GiST indexes can be used to search values in WHERE clauses using the =, &&, @>, and <@ operators.

Additionally, cube GiST indexes can be used in ORDER BY clauses for nearest-neighbor searches using the distance operators <->, <#>, and <=>. For example, the nearest neighbor of the 3-D point (0.5, 0.5, 0.5) can be found quickly with the following query:

SELECT c FROM test ORDER BY c <-> cube(array[0.5,0.5,0.5]) LIMIT 1;

The ~> operator can also be used in this way to efficiently retrieve the first few values sorted by a selected coordinate. For example, the following query returns the first few cubes sorted in ascending order by the first coordinate (lower-left corner):

SELECT c FROM test ORDER BY c ~> 1 LIMIT 5;

And to get the 2-D cubes sorted in descending order by the first coordinate of the upper-right corner:

SELECT c FROM test ORDER BY c ~> 3 DESC LIMIT 5;

Table C.4 shows the available functions.

Table C.4. Cube Functions

Function/Description/Example
cube ( float8 ) → cube Creates a one-dimensional cube with both coordinates being the same. cube(1) → (1)
cube ( float8, float8 ) → cube Creates a one-dimensional cube. cube(1,2) → (1),(2)
cube ( float8[] ) → cube Creates a zero-volume cube using coordinates defined by an array. cube(ARRAY[1,2,3]) → (1, 2, 3)
cube ( float8[], float8[] ) → cube Creates a cube using upper-right and lower-left coordinates defined by two arrays, which must be of equal length. cube(ARRAY[1,2], ARRAY[3,4]) → (1, 2),(3, 4)
cube ( cube, float8 ) → cube Creates a new cube by adding a dimension to an existing cube, using the same value for both endpoints of the new coordinate. This can be used to incrementally build cubes from computed values. cube('(1,2),(3,4)'::cube, 5) → (1, 2, 5),(3, 4, 5)
cube ( cube, float8, float8 ) → cube Creates a new cube by adding a dimension to an existing cube. This can be used to incrementally build cubes from computed values. cube('(1,2),(3,4)'::cube, 5, 6) → (1, 2, 5),(3, 4, 6)
cube_dim ( cube ) → integer Returns the number of dimensions of the cube. cube_dim('(1,2),(3,4)') → 2 cube_ll_coord ( cube, integer ) → float8 Returns the n-th coordinate value of the lower-left corner of a cube cube_ll_coord('(1,2),(3,4)', 2) → 2 cube_ur_coord ( cube, integer ) → float8 Returns the n-th coordinate value of the upper-right corner of a cube cube_ur_coord('(1,2),(3,4)', 2) → 4
cube_is_point ( cube ) → boolean Returns true if a cube is a point, that is, the two defining points are the same. cube_is_point(cube(1,1)) → t
cube_distance ( cube, cube ) → float8 Returns the distance between two cubes. If both are points, this is the ordinary distance function. cube_distance('(1,2)', '(3,4)') → 2.8284271247461903
cube_subset ( cube, integer[] ) → cube Creates a new cube from an existing cube using a list of dimension indices from an array. It can be used to extract endpoints of a single dimension, remove dimensions, or reorder them as needed. cube_subset(cube('(1,3,5),(6,7,8)'), ARRAY[2]) → (3),(7) ; cube_subset(cube('(1,3,5),(6,7,8)'), ARRAY[3,2,1,1]) → (5, 3, 1, 1),(8, 7, 6, 6)
cube_union ( cube, cube ) → cube Produces the union of two cubes. cube_union('(1,2)', '(3,4)') → (1, 2),(3, 4) cube_inter ( cube, cube ) → cube Produces the intersection of two cubes. cube_inter('(1,2)', '(3,4)') → (3, 4),(1, 2) cube_enlarge ( c cube, r double, n integer ) → cube Increases the size of the cube by a specified radius r in at least n dimensions. If the radius is negative, the cube shrinks. All defined dimensions are modified by radius r. The lower-left coordinate is decreased by r and the upper-right coordinate is increased by r. If a lower-left coordinate is increased beyond the corresponding upper-right coordinate (which only happens when r < 0), both coordinates are set to their average. If n is greater than the number of defined dimensions and the cube is being enlarged (r > 0), additional dimensions are added to bring the dimension count to n, using 0 as the initial value for the extra coordinates. This function can be used to create a bounding box around a point to search for nearby points. cube_enlarge('(1,2),(3,4)', 0.5, 3) → (0.5, 1.5, -0.5),(3.5, 4.5, 0.5)

4. Defaults

The following union:

test=## select cube_union('(0,5,2),(2,3,1)', '0');

cube_union

---------------------

(0, 0, 0),(2, 5, 2)

(1 row)

does not contradict common sense, nor does the following intersection:

test=## select cube_inter('(0,-1),(1,1)', '(-2),(2)');

cube_inter

---------------

(0, 0),(1, 0)

(1 row)

In all binary operations on cubes of different dimensions, the lower-dimensional cube is assumed to be projected, with zeros substituted for coordinates omitted in the string representation. The above examples are equivalent to:

cube_union('(0,5,2),(2,3,1)','(0,0,0),(0,0,0)');

cube_inter('(0,-1),(1,1)','(-2,0),(2,0)');

The following containment predicate uses point syntax, but the second argument is actually represented internally as a box. This syntax lets us avoid defining a separate point type and functions for predicates.

test=## select cube_contains('(0,0),(1,1)', '0.5,0.5');

cube_contains

---------------

t

(1 row)

5. Notes

To avoid issues, there is a limit of 100 on the number of dimensions for a cube. If you need larger cubes, you can modify this in cubedata.h.