Use cube

Eugenia Kuzina

Collapse content Expand content

Contents

Overview
Operators of the cube data type
- Examples
Functions of the cube data type

Overview

The cube extension implements the cube data type to represent multidimensional arrays. This type allows you to efficiently store and index multidimensional arrays, as well as perform operations on them. In this article, the term cube refers to a multidimensional array.

NOTE

The CUBE operator, which is used when creating grouping sets, is not related to the cube extension and can be used without installing it.

The package required for the installation of the cube extension is shipped with ADP. To use cube, execute the CREATE EXTENSION command:

CREATE EXTENSION cube;

NOTE

If the cube extension is created in the template1 database used as the default template, all subsequently created databases will have this extension installed.

ADP uses the 1.5 cube version. To check it, execute the following query:

SELECT extversion FROM pg_extension
    WHERE extname = 'cube';

 extversion
------------
1.5

The cube values are stored internally as 64-bit floating point numbers. It means that numbers with more than 16 significant digits will be truncated.

The table below displays cube external representations.

Syntax Description

One-dimensional point (zero-length one-dimensional interval)

(x)

x1,x2,…,xn

Point in n-dimensional space represented internally as a zero-volume cube

(x1,x2,…,xn)

(x),(y)

One-dimensional interval starting at x and ending at y or vice versa; the order does not matter

[(x),(y)]

(x1,…,xn),(y1,…,yn)

N-dimensional cube represented by a pair of its diagonally opposite corners

[(x1,…,xn),(y1,…,yn)]

It does not matter in what order the opposite corners of a cube are specified. Functions that accept the cube type swap values to produce the internal representation "bottom left — top right". When these corner points coincide, cube stores only one corner with the "is point" flag.

Operators of the cube data type

The cube data type has special operators listed in the table below.

Operator Description

cube && cube → boolean

Determines if the cubes overlap each other

cube @> cube → boolean

Determines if the first cube contains the second one

cube <@ cube → boolean

Determines if the second cube contains the first one

cube -> integer → float8

Extracts the n-th coordinate of the cube (numbering starts at 1)

cube ~> integer → float8

Extracts the n-th coordinate of the cube, counting in the following way: means lower bound of k-th dimension, means upper bound of k-th dimension. Negative n denotes the inverse value 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 taxicab (L-1 metric) distance between two cubes

cube <=> cube → float8

Computes the Chebyshev (L-inf metric) distance between two cubes

In addition to the above operators, the standard comparison operators are available for the cube type. These operators compare the first coordinates, and if those are equal, compare the second coordinates, etc. They are intended primarily to support the B-tree index operator class for the cube type.

Examples

Equality / inequality (= / !=)

SELECT '38, 102.30'::cube = '38, 102.30'::cube AS result;

 result
--------
 t

SELECT '(2,0),(3,1)'::cube = '(2,0,0,0,0),(3,1,0,0,0)'::cube AS result;

 result
--------
 f

SELECT '38, 102.30'::cube != '38, 102.31'::cube AS result;

 result
--------
 t

Lower than / greater than (</>)

SELECT '(2,0),(3,1)'::cube < '(2,0,0,0,0),(3,1,0,0,0)'::cube AS result;

 result
--------
 t

SELECT '(2,0,0,0,0),(3,1,0,0,1)'::cube > '(2,0),(3,2)'::cube AS result;

 result
--------
 f

Overlap (&&)

SELECT '[(-1,-1,-1),(1,1,1)]'::cube && '0'::cube AS result;

 result
--------
 t

SELECT '[(-1,-1,-1),(1,1,1)]'::cube && '[(1,1,1),(2,2,2)]'::cube AS result;

 result
--------
 t

Contained in (<@)

SELECT '(1,0,0),(0,0,1)'::cube <@ '(-1,-1,-1),(1,1,1)'::cube AS result;

 result
--------
 t

SELECT '(-2),(1)'::cube <@ '(-1),(1)'::cube AS result;

 result
--------
 f

Contain (@>)

SELECT '(1,0,0),(0,0,1)'::cube @> '(-1,-1,-1),(1,1,1)'::cube AS result;

 result
--------
 f

SELECT '(-1,-1,-1,-1),(1,1,1,1)'::cube @> '(1,0,0),(0,0,1)'::cube AS result;

 result
--------
 t

Access coordinates (->)

SELECT cube(array[10,20,30], array[40,50,60])-> 5 AS result;

 result
--------
     50

Two examples below return the same result because the function cube(array[10,20,30]) creates a cube where the coordinates of both corners match.

SELECT cube(array[10,20,30])-> 3 AS result;

 result
--------
     30

SELECT cube(array[10,20,30])-> 6 AS result;

 result
--------
     30

Access "normalized" coordinates (~>)

SELECT cube(array[40,50,60], array[10,20,30])~> 1 AS result;

result
----------
       10

SELECT cube(array[40,50,60], array[10,20,30])~> 2 AS result;

result
----------
       40

SELECT cube(array[40,50,60], array[10,20,30])~> 3 AS result;

result
----------
       20

SELECT cube(array[40,50,60], array[10,20,30])~> 4 AS result;

result
----------
       50

SELECT cube(array[40,50,60], array[10,20,30])~> (-4) AS result;

result
----------
       -50

Euclidean distance (<->)

SELECT '(1,1)'::cube <-> '(4,5)'::cube AS result;

result
----------
        5

Taxicab distance (<#>)

SELECT '(1,1)'::cube <#> '(4,5)'::cube AS result;

result
----------
        7

Chebyshev distance (<=>)

SELECT '(1,1)'::cube <=> '(4,5)'::cube AS result;

result
----------
        4

Functions of the cube data type

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 the coordinates defined by the array

cube(ARRAY[1,2,3]) → (1, 2, 3)

cube(float8[], float8[]) → cube

Creates a cube with upper right and lower left coordinates defined by two arrays, which must be of the same 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, with the same values for both endpoints of a new coordinate. This is useful for building cubes step by step from calculated 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

cube('(1,2),(3,4)'::cube, 5, 6) → (1, 2, 5),(3, 4, 6)

cube_dim(cube) → integer

Returns the number of cube dimensions

cube_dim('(1,2),(3,4)') → 2

cube_ll_coord(cube, n integer) → float8

Returns the n-th coordinate value for the lower left corner of the cube

cube_ll_coord('(1,2),(3,4)', 2) → 2

cube_ur_coord(cube n integer) → float8

Returns the n-th coordinate value for the upper right corner of the cube

cube_ur_coord('(1,2),(3,4)', 2) → 4

cube_is_point(cube) → boolean

Returns true if the cube is a point, that is, both corners have the same coordinates

cube_is_point(cube(1,1)) → t

cube_distance(cube, cube) → float8

Returns the distance between two cubes. If both cubes are points, this is a standard 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 indexes from an array. It can be used to extract endpoints of a single dimension, to drop dimensions, or to reorder them

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

Performs a union of two cubes

cube_union('(1,2)', '(3,4)') → (1, 2),(3, 4)

cube_inter(cube, cube) → cube

Performs an 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 the specified radius r in at least n dimensions. If the radius (r) is negative, the cube shrinks instead. All dimensions are changed by the radius r. Lower-left coordinates are decreased by r and upper-right coordinates are increased by r. If a lower-left coordinate is increased to more than the corresponding upper-right coordinate (this can only happen when r < 0), both coordinates are set to their average values. If n is greater than the number of defined dimensions and the cube is being enlarged (r > 0), extra dimensions are added, so that the total number of dimensions equals n; 0 is used as the initial value for the extra coordinates. This function is useful for creating bounding boxes 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)

Found a mistake? Seleсt text and press Ctrl+Enter to report it