<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>F.9. cube</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="citext.html" title="F.8. citext" /><link rel="next" href="dblink.html" title="F.10. dblink" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">F.9. cube</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="citext.html" title="F.8. citext">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules">Up</a></td><th width="60%" align="center">Appendix F. Additional Supplied Modules</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 11.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="dblink.html" title="F.10. dblink">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="CUBE"><div class="titlepage"><div><div><h2 class="title" style="clear: both">F.9. cube</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="cube.html#id-1.11.7.18.4">F.9.1. Syntax</a></span></dt><dt><span class="sect2"><a href="cube.html#id-1.11.7.18.5">F.9.2. Precision</a></span></dt><dt><span class="sect2"><a href="cube.html#id-1.11.7.18.6">F.9.3. Usage</a></span></dt><dt><span class="sect2"><a href="cube.html#id-1.11.7.18.7">F.9.4. Defaults</a></span></dt><dt><span class="sect2"><a href="cube.html#id-1.11.7.18.8">F.9.5. Notes</a></span></dt><dt><span class="sect2"><a href="cube.html#id-1.11.7.18.9">F.9.6. Credits</a></span></dt></dl></div><a id="id-1.11.7.18.2" class="indexterm"></a><p>
This module implements a data type <code class="type">cube</code> for
representing multidimensional cubes.
</p><div class="sect2" id="id-1.11.7.18.4"><div class="titlepage"><div><div><h3 class="title">F.9.1. Syntax</h3></div></div></div><p>
<a class="xref" href="cube.html#CUBE-REPR-TABLE" title="Table F.2. Cube External Representations">Table F.2</a> shows the valid external
representations for the <code class="type">cube</code>
type. <em class="replaceable"><code>x</code></em>, <em class="replaceable"><code>y</code></em>, etc. denote
floating-point numbers.
</p><div class="table" id="CUBE-REPR-TABLE"><p class="title"><strong>Table F.2. Cube External Representations</strong></p><div class="table-contents"><table class="table" summary="Cube External Representations" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>External Syntax</th><th>Meaning</th></tr></thead><tbody><tr><td><code class="literal"><em class="replaceable"><code>x</code></em></code></td><td>A one-dimensional point
(or, zero-length one-dimensional interval)
</td></tr><tr><td><code class="literal">(<em class="replaceable"><code>x</code></em>)</code></td><td>Same as above</td></tr><tr><td><code class="literal"><em class="replaceable"><code>x1</code></em>,<em class="replaceable"><code>x2</code></em>,...,<em class="replaceable"><code>xn</code></em></code></td><td>A point in n-dimensional space, represented internally as a
zero-volume cube
</td></tr><tr><td><code class="literal">(<em class="replaceable"><code>x1</code></em>,<em class="replaceable"><code>x2</code></em>,...,<em class="replaceable"><code>xn</code></em>)</code></td><td>Same as above</td></tr><tr><td><code class="literal">(<em class="replaceable"><code>x</code></em>),(<em class="replaceable"><code>y</code></em>)</code></td><td>A one-dimensional interval starting at <em class="replaceable"><code>x</code></em> and ending at <em class="replaceable"><code>y</code></em> or vice versa; the
order does not matter
</td></tr><tr><td><code class="literal">[(<em class="replaceable"><code>x</code></em>),(<em class="replaceable"><code>y</code></em>)]</code></td><td>Same as above</td></tr><tr><td><code class="literal">(<em class="replaceable"><code>x1</code></em>,...,<em class="replaceable"><code>xn</code></em>),(<em class="replaceable"><code>y1</code></em>,...,<em class="replaceable"><code>yn</code></em>)</code></td><td>An n-dimensional cube represented by a pair of its diagonally
opposite corners
</td></tr><tr><td><code class="literal">[(<em class="replaceable"><code>x1</code></em>,...,<em class="replaceable"><code>xn</code></em>),(<em class="replaceable"><code>y1</code></em>,...,<em class="replaceable"><code>yn</code></em>)]</code></td><td>Same as above</td></tr></tbody></table></div></div><br class="table-break" /><p>
It does not matter which order the opposite corners of a cube are
entered in. The <code class="type">cube</code> functions
automatically swap values if needed to create a uniform
<span class="quote">“<span class="quote">lower left — upper right</span>”</span> internal representation.
When the corners coincide, <code class="type">cube</code> stores only one corner
along with an <span class="quote">“<span class="quote">is point</span>”</span> flag to avoid wasting space.
</p><p>
White space is ignored on input, so
<code class="literal">[(<em class="replaceable"><code>x</code></em>),(<em class="replaceable"><code>y</code></em>)]</code> is the same as
<code class="literal">[ ( <em class="replaceable"><code>x</code></em> ), ( <em class="replaceable"><code>y</code></em> ) ]</code>.
</p></div><div class="sect2" id="id-1.11.7.18.5"><div class="titlepage"><div><div><h3 class="title">F.9.2. Precision</h3></div></div></div><p>
Values are stored internally as 64-bit floating point numbers. This means
that numbers with more than about 16 significant digits will be truncated.
</p></div><div class="sect2" id="id-1.11.7.18.6"><div class="titlepage"><div><div><h3 class="title">F.9.3. Usage</h3></div></div></div><p>
<a class="xref" href="cube.html#CUBE-OPERATORS-TABLE" title="Table F.3. Cube Operators">Table F.3</a> shows the operators provided for
type <code class="type">cube</code>.
</p><div class="table" id="CUBE-OPERATORS-TABLE"><p class="title"><strong>Table F.3. Cube Operators</strong></p><div class="table-contents"><table class="table" summary="Cube Operators" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>Operator</th><th>Result</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal">a = b</code></td><td><code class="type">boolean</code></td><td>The cubes a and b are identical.</td></tr><tr><td><code class="literal">a && b</code></td><td><code class="type">boolean</code></td><td>The cubes a and b overlap.</td></tr><tr><td><code class="literal">a @> b</code></td><td><code class="type">boolean</code></td><td>The cube a contains the cube b.</td></tr><tr><td><code class="literal">a <@ b</code></td><td><code class="type">boolean</code></td><td>The cube a is contained in the cube b.</td></tr><tr><td><code class="literal">a < b</code></td><td><code class="type">boolean</code></td><td>The cube a is less than the cube b.</td></tr><tr><td><code class="literal">a <= b</code></td><td><code class="type">boolean</code></td><td>The cube a is less than or equal to the cube b.</td></tr><tr><td><code class="literal">a > b</code></td><td><code class="type">boolean</code></td><td>The cube a is greater than the cube b.</td></tr><tr><td><code class="literal">a >= b</code></td><td><code class="type">boolean</code></td><td>The cube a is greater than or equal to the cube b.</td></tr><tr><td><code class="literal">a <> b</code></td><td><code class="type">boolean</code></td><td>The cube a is not equal to the cube b.</td></tr><tr><td><code class="literal">a -> n</code></td><td><code class="type">float8</code></td><td>Get <em class="replaceable"><code>n</code></em>-th coordinate of cube (counting from 1).</td></tr><tr><td><code class="literal">a ~> n</code></td><td><code class="type">float8</code></td><td>
Get <em class="replaceable"><code>n</code></em>-th coordinate of cube in following way:
n = 2 * k - 1 means lower bound of <em class="replaceable"><code>k</code></em>-th
dimension, n = 2 * k means upper bound of
<em class="replaceable"><code>k</code></em>-th dimension. Negative
<em class="replaceable"><code>n</code></em> denotes the inverse value of the corresponding
positive coordinate. This operator is designed for KNN-GiST support.
</td></tr><tr><td><code class="literal">a <-> b</code></td><td><code class="type">float8</code></td><td>Euclidean distance between a and b.</td></tr><tr><td><code class="literal">a <#> b</code></td><td><code class="type">float8</code></td><td>Taxicab (L-1 metric) distance between a and b.</td></tr><tr><td><code class="literal">a <=> b</code></td><td><code class="type">float8</code></td><td>Chebyshev (L-inf metric) distance between a and b.</td></tr></tbody></table></div></div><br class="table-break" /><p>
(Before PostgreSQL 8.2, the containment operators <code class="literal">@></code> and <code class="literal"><@</code> were
respectively called <code class="literal">@</code> and <code class="literal">~</code>. These names are still available, but are
deprecated and will eventually be retired. Notice that the old names
are reversed from the convention formerly followed by the core geometric
data types!)
</p><p>
The scalar ordering operators (<code class="literal"><</code>, <code class="literal">>=</code>, etc)
do not make a lot of sense for any practical purpose but sorting. These
operators first compare the first coordinates, and if those are equal,
compare the second coordinates, etc. They exist mainly to support the
b-tree index operator class for <code class="type">cube</code>, which can be useful for
example if you would like a UNIQUE constraint on a <code class="type">cube</code> column.
</p><p>
The <code class="filename">cube</code> module also provides a GiST index operator class for
<code class="type">cube</code> values.
A <code class="type">cube</code> GiST index can be used to search for values using the
<code class="literal">=</code>, <code class="literal">&&</code>, <code class="literal">@></code>, and
<code class="literal"><@</code> operators in <code class="literal">WHERE</code> clauses.
</p><p>
In addition, a <code class="type">cube</code> GiST index can be used to find nearest
neighbors using the metric operators
<code class="literal"><-></code>, <code class="literal"><#></code>, and
<code class="literal"><=></code> in <code class="literal">ORDER BY</code> clauses.
For example, the nearest neighbor of the 3-D point (0.5, 0.5, 0.5)
could be found efficiently with:
</p><pre class="programlisting">
SELECT c FROM test ORDER BY c <-> cube(array[0.5,0.5,0.5]) LIMIT 1;
</pre><p>
</p><p>
The <code class="literal">~></code> operator can also be used in this way to
efficiently retrieve the first few values sorted by a selected coordinate.
For example, to get the first few cubes ordered by the first coordinate
(lower left corner) ascending one could use the following query:
</p><pre class="programlisting">
SELECT c FROM test ORDER BY c ~> 1 LIMIT 5;
</pre><p>
And to get 2-D cubes ordered by the first coordinate of the upper right
corner descending:
</p><pre class="programlisting">
SELECT c FROM test ORDER BY c ~> 3 DESC LIMIT 5;
</pre><p>
</p><p>
<a class="xref" href="cube.html#CUBE-FUNCTIONS-TABLE" title="Table F.4. Cube Functions">Table F.4</a> shows the available functions.
</p><div class="table" id="CUBE-FUNCTIONS-TABLE"><p class="title"><strong>Table F.4. Cube Functions</strong></p><div class="table-contents"><table class="table" summary="Cube Functions" border="1"><colgroup><col /><col /><col /><col /></colgroup><thead><tr><th>Function</th><th>Result</th><th>Description</th><th>Example</th></tr></thead><tbody><tr><td><code class="literal">cube(float8)</code></td><td><code class="type">cube</code></td><td>Makes a one dimensional cube with both coordinates the same.
</td><td>
<code class="literal">cube(1) == '(1)'</code>
</td></tr><tr><td><code class="literal">cube(float8, float8)</code></td><td><code class="type">cube</code></td><td>Makes a one dimensional cube.
</td><td>
<code class="literal">cube(1,2) == '(1),(2)'</code>
</td></tr><tr><td><code class="literal">cube(float8[])</code></td><td><code class="type">cube</code></td><td>Makes a zero-volume cube using the coordinates
defined by the array.
</td><td>
<code class="literal">cube(ARRAY[1,2]) == '(1,2)'</code>
</td></tr><tr><td><code class="literal">cube(float8[], float8[])</code></td><td><code class="type">cube</code></td><td>Makes a cube with upper right and lower left
coordinates as defined by the two arrays, which must be of the
same length.
</td><td>
<code class="literal">cube(ARRAY[1,2], ARRAY[3,4]) == '(1,2),(3,4)'
</code>
</td></tr><tr><td><code class="literal">cube(cube, float8)</code></td><td><code class="type">cube</code></td><td>Makes a new cube by adding a dimension on to an existing cube,
with the same values for both endpoints of the new coordinate. This
is useful for building cubes piece by piece from calculated values.
</td><td>
<code class="literal">cube('(1,2),(3,4)'::cube, 5) == '(1,2,5),(3,4,5)'</code>
</td></tr><tr><td><code class="literal">cube(cube, float8, float8)</code></td><td><code class="type">cube</code></td><td>Makes a new cube by adding a dimension on to an existing
cube. This is useful for building cubes piece by piece from calculated
values.
</td><td>
<code class="literal">cube('(1,2),(3,4)'::cube, 5, 6) == '(1,2,5),(3,4,6)'</code>
</td></tr><tr><td><code class="literal">cube_dim(cube)</code></td><td><code class="type">integer</code></td><td>Returns the number of dimensions of the cube.
</td><td>
<code class="literal">cube_dim('(1,2),(3,4)') == '2'</code>
</td></tr><tr><td><code class="literal">cube_ll_coord(cube, integer)</code></td><td><code class="type">float8</code></td><td>Returns the <em class="replaceable"><code>n</code></em>-th coordinate value for the lower
left corner of the cube.
</td><td>
<code class="literal">cube_ll_coord('(1,2),(3,4)', 2) == '2'</code>
</td></tr><tr><td><code class="literal">cube_ur_coord(cube, integer)</code></td><td><code class="type">float8</code></td><td>Returns the <em class="replaceable"><code>n</code></em>-th coordinate value for the
upper right corner of the cube.
</td><td>
<code class="literal">cube_ur_coord('(1,2),(3,4)', 2) == '4'</code>
</td></tr><tr><td><code class="literal">cube_is_point(cube)</code></td><td><code class="type">boolean</code></td><td>Returns true if the cube is a point, that is,
the two defining corners are the same.</td><td>
</td></tr><tr><td><code class="literal">cube_distance(cube, cube)</code></td><td><code class="type">float8</code></td><td>Returns the distance between two cubes. If both
cubes are points, this is the normal distance function.
</td><td>
</td></tr><tr><td><code class="literal">cube_subset(cube, integer[])</code></td><td><code class="type">cube</code></td><td>Makes a new cube from an existing cube, using a list of
dimension indexes from an array. Can be used to extract the endpoints
of a single dimension, or to drop dimensions, or to reorder them as
desired.
</td><td>
<code class="literal">cube_subset(cube('(1,3,5),(6,7,8)'), ARRAY[2]) == '(3),(7)'</code>
<code class="literal">cube_subset(cube('(1,3,5),(6,7,8)'), ARRAY[3,2,1,1]) ==
'(5,3,1,1),(8,7,6,6)'</code>
</td></tr><tr><td><code class="literal">cube_union(cube, cube)</code></td><td><code class="type">cube</code></td><td>Produces the union of two cubes.
</td><td>
</td></tr><tr><td><code class="literal">cube_inter(cube, cube)</code></td><td><code class="type">cube</code></td><td>Produces the intersection of two cubes.
</td><td>
</td></tr><tr><td><code class="literal">cube_enlarge(c cube, r double, n integer)</code></td><td><code class="type">cube</code></td><td>Increases the size of the cube by the specified
radius <em class="replaceable"><code>r</code></em> in at least <em class="replaceable"><code>n</code></em> dimensions.
If the radius is negative the cube is shrunk instead.
All defined dimensions are changed by the radius <em class="replaceable"><code>r</code></em>.
Lower-left coordinates are decreased by <em class="replaceable"><code>r</code></em> and
upper-right coordinates are increased by <em class="replaceable"><code>r</code></em>. If a
lower-left coordinate is increased to more than the corresponding
upper-right coordinate (this can only happen when <em class="replaceable"><code>r</code></em>
< 0) than both coordinates are set to their average.
If <em class="replaceable"><code>n</code></em> is greater than the number of defined dimensions
and the cube is being enlarged (<em class="replaceable"><code>r</code></em> > 0), then extra
dimensions are added to make <em class="replaceable"><code>n</code></em> altogether;
0 is used as the initial value for the extra coordinates.
This function is useful for creating bounding boxes around a point for
searching for nearby points.
</td><td>
<code class="literal">cube_enlarge('(1,2),(3,4)', 0.5, 3) ==
'(0.5,1.5,-0.5),(3.5,4.5,0.5)'</code>
</td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="id-1.11.7.18.7"><div class="titlepage"><div><div><h3 class="title">F.9.4. Defaults</h3></div></div></div><p>
I believe this union:
</p><pre class="programlisting">
select cube_union('(0,5,2),(2,3,1)', '0');
cube_union
-------------------
(0, 0, 0),(2, 5, 2)
(1 row)
</pre><p>
does not contradict common sense, neither does the intersection
</p><pre class="programlisting">
select cube_inter('(0,-1),(1,1)', '(-2),(2)');
cube_inter
-------------
(0, 0),(1, 0)
(1 row)
</pre><p>
In all binary operations on differently-dimensioned cubes, I assume the
lower-dimensional one to be a Cartesian projection, i. e., having zeroes
in place of coordinates omitted in the string representation. The above
examples are equivalent to:
</p><pre class="programlisting">
cube_union('(0,5,2),(2,3,1)','(0,0,0),(0,0,0)');
cube_inter('(0,-1),(1,1)','(-2,0),(2,0)');
</pre><p>
The following containment predicate uses the point syntax,
while in fact the second argument is internally represented by a box.
This syntax makes it unnecessary to define a separate point type
and functions for (box,point) predicates.
</p><pre class="programlisting">
select cube_contains('(0,0),(1,1)', '0.5,0.5');
cube_contains
--------------
t
(1 row)
</pre></div><div class="sect2" id="id-1.11.7.18.8"><div class="titlepage"><div><div><h3 class="title">F.9.5. Notes</h3></div></div></div><p>
For examples of usage, see the regression test <code class="filename">sql/cube.sql</code>.
</p><p>
To make it harder for people to break things, there
is a limit of 100 on the number of dimensions of cubes. This is set
in <code class="filename">cubedata.h</code> if you need something bigger.
</p></div><div class="sect2" id="id-1.11.7.18.9"><div class="titlepage"><div><div><h3 class="title">F.9.6. Credits</h3></div></div></div><p>
Original author: Gene Selkov, Jr. <code class="email"><<a class="email" href="mailto:selkovjr@mcs.anl.gov">selkovjr@mcs.anl.gov</a>></code>,
Mathematics and Computer Science Division, Argonne National Laboratory.
</p><p>
My thanks are primarily to Prof. Joe Hellerstein
(<a class="ulink" href="http://db.cs.berkeley.edu/jmh/" target="_top">http://db.cs.berkeley.edu/jmh/</a>) for elucidating the
gist of the GiST (<a class="ulink" href="http://gist.cs.berkeley.edu/" target="_top">http://gist.cs.berkeley.edu/</a>), and
to his former student Andy Dong for his example written for Illustra.
I am also grateful to all Postgres developers, present and past, for
enabling myself to create my own world and live undisturbed in it. And I
would like to acknowledge my gratitude to Argonne Lab and to the
U.S. Department of Energy for the years of faithful support of my database
research.
</p><p>
Minor updates to this package were made by Bruno Wolff III
<code class="email"><<a class="email" href="mailto:bruno@wolff.to">bruno@wolff.to</a>></code> in August/September of 2002. These include
changing the precision from single precision to double precision and adding
some new functions.
</p><p>
Additional updates were made by Joshua Reich <code class="email"><<a class="email" href="mailto:josh@root.net">josh@root.net</a>></code> in
July 2006. These include <code class="literal">cube(float8[], float8[])</code> and
cleaning up the code to use the V1 call protocol instead of the deprecated
V0 protocol.
</p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="citext.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="contrib.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="dblink.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.8. citext </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> F.10. dblink</td></tr></table></div></body></html>
