doc/src/sgml/cube.sgml - master - githubmirror/postgres

doc/src/sgml/cube.sgml
639 строк21 KB
David Rowley
Doc: use uppercase keywords in SQLs
06 ноя 2025, 06:03
06 ноя 2025, 06:0349d43fa
<!-- doc/src/sgml/cube.sgml -->

<sect1 id="cube" xreflabel="cube">
 <title>cube &mdash; a multi-dimensional cube data type</title>

 <indexterm zone="cube">
  <primary>cube (extension)</primary>
 </indexterm>

 <para>
  This module implements a data type <type>cube</type> for
  representing multidimensional cubes.
 </para>

 <para>
  This module is considered <quote>trusted</quote>, that is, it can be
  installed by non-superusers who have <literal>CREATE</literal> privilege
  on the current database.
 </para>

 <sect2 id="cube-syntax">
  <title>Syntax</title>

  <para>
   <xref linkend="cube-repr-table"/> shows the valid external
   representations for the <type>cube</type>
   type.  <replaceable>x</replaceable>, <replaceable>y</replaceable>, etc. denote
   floating-point numbers.
  </para>

  <table id="cube-repr-table">
   <title>Cube External Representations</title>
   <tgroup cols="2">
    <thead>
     <row>
      <entry>External Syntax</entry>
      <entry>Meaning</entry>
     </row>
    </thead>

    <tbody>
     <row>
      <entry><literal><replaceable>x</replaceable></literal></entry>
      <entry>A one-dimensional point
       (or, zero-length one-dimensional interval)
      </entry>
     </row>
     <row>
      <entry><literal>(<replaceable>x</replaceable>)</literal></entry>
      <entry>Same as above</entry>
     </row>
     <row>
      <entry><literal><replaceable>x1</replaceable>,<replaceable>x2</replaceable>,...,<replaceable>xn</replaceable></literal></entry>
      <entry>A point in n-dimensional space, represented internally as a
      zero-volume cube
      </entry>
     </row>
     <row>
      <entry><literal>(<replaceable>x1</replaceable>,<replaceable>x2</replaceable>,...,<replaceable>xn</replaceable>)</literal></entry>
      <entry>Same as above</entry>
     </row>
     <row>
      <entry><literal>(<replaceable>x</replaceable>),(<replaceable>y</replaceable>)</literal></entry>
      <entry>A one-dimensional interval starting at <replaceable>x</replaceable> and ending at <replaceable>y</replaceable> or vice versa; the
       order does not matter
      </entry>
     </row>
     <row>
      <entry><literal>[(<replaceable>x</replaceable>),(<replaceable>y</replaceable>)]</literal></entry>
      <entry>Same as above</entry>
     </row>
     <row>
      <entry><literal>(<replaceable>x1</replaceable>,...,<replaceable>xn</replaceable>),(<replaceable>y1</replaceable>,...,<replaceable>yn</replaceable>)</literal></entry>
      <entry>An n-dimensional cube represented by a pair of its diagonally
       opposite corners
      </entry>
     </row>
     <row>
      <entry><literal>[(<replaceable>x1</replaceable>,...,<replaceable>xn</replaceable>),(<replaceable>y1</replaceable>,...,<replaceable>yn</replaceable>)]</literal></entry>
      <entry>Same as above</entry>
     </row>
    </tbody>
   </tgroup>
  </table>

  <para>
   It does not matter which order the opposite corners of a cube are
   entered in.  The <type>cube</type> functions
   automatically swap values if needed to create a uniform
   <quote>lower left &mdash; upper right</quote> internal representation.
   When the corners coincide, <type>cube</type> stores only one corner
   along with an <quote>is point</quote> flag to avoid wasting space.
  </para>

  <para>
   White space is ignored on input, so
   <literal>[(<replaceable>x</replaceable>),(<replaceable>y</replaceable>)]</literal> is the same as
   <literal>[ ( <replaceable>x</replaceable> ), ( <replaceable>y</replaceable> ) ]</literal>.
  </para>
 </sect2>

 <sect2 id="cube-precision">
  <title>Precision</title>

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

 <sect2 id="cube-usage">
  <title>Usage</title>

  <para>
   <xref linkend="cube-operators-table"/> shows the specialized operators
   provided for type <type>cube</type>.
  </para>

  <table id="cube-operators-table">
   <title>Cube Operators</title>
    <tgroup cols="1">
     <thead>
      <row>
       <entry role="func_table_entry"><para role="func_signature">
        Operator
       </para>
       <para>
        Description
       </para></entry>
      </row>
     </thead>

     <tbody>
      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <type>cube</type> <literal>&amp;&amp;</literal> <type>cube</type>
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Do the cubes overlap?
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <type>cube</type> <literal>@&gt;</literal> <type>cube</type>
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Does the first cube contain the second?
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <type>cube</type> <literal>&lt;@</literal> <type>cube</type>
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Is the first cube contained in the second?
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <type>cube</type> <literal>-&gt;</literal> <type>integer</type>
        <returnvalue>float8</returnvalue>
       </para>
       <para>
        Extracts the <parameter>n</parameter>-th coordinate of the cube
        (counting from 1).
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <type>cube</type> <literal>~&gt;</literal> <type>integer</type>
        <returnvalue>float8</returnvalue>
       </para>
       <para>
        Extracts the <parameter>n</parameter>-th coordinate of the cube,
        counting in the following way: <parameter>n</parameter> = 2
        * <parameter>k</parameter> - 1 means lower bound
        of <parameter>k</parameter>-th dimension, <parameter>n</parameter> = 2
        * <parameter>k</parameter> means upper bound of
        <parameter>k</parameter>-th dimension.  Negative
        <parameter>n</parameter> denotes the inverse value of the corresponding
        positive coordinate.  This operator is designed for KNN-GiST support.
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <type>cube</type> <literal>&lt;-&gt;</literal> <type>cube</type>
        <returnvalue>float8</returnvalue>
       </para>
       <para>
        Computes the Euclidean distance between the two cubes.
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <type>cube</type> <literal>&lt;#&gt;</literal> <type>cube</type>
        <returnvalue>float8</returnvalue>
       </para>
       <para>
        Computes the taxicab (L-1 metric) distance between the two cubes.
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <type>cube</type> <literal>&lt;=&gt;</literal> <type>cube</type>
        <returnvalue>float8</returnvalue>
       </para>
       <para>
        Computes the Chebyshev (L-inf metric) distance between the two cubes.
       </para></entry>
      </row>
     </tbody>
   </tgroup>
  </table>

  <para>
   In addition to the above operators, the usual comparison
   operators shown in <xref linkend="functions-comparison-op-table"/> are
   available for type <type>cube</type>.  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 <type>cube</type>, which can be useful for
   example if you would like a UNIQUE constraint on a <type>cube</type> column.
   Otherwise, this ordering is not of much practical use.
  </para>

  <para>
   The <filename>cube</filename> module also provides a GiST index operator class for
   <type>cube</type> values.
   A <type>cube</type> GiST index can be used to search for values using the
   <literal>=</literal>, <literal>&amp;&amp;</literal>, <literal>@&gt;</literal>, and
   <literal>&lt;@</literal> operators in <literal>WHERE</literal> clauses.
  </para>

  <para>
   In addition, a <type>cube</type> GiST index can be used to find nearest
   neighbors using the metric operators
   <literal>&lt;-&gt;</literal>, <literal>&lt;#&gt;</literal>, and
   <literal>&lt;=&gt;</literal> in <literal>ORDER BY</literal> clauses.
   For example, the nearest neighbor of the 3-D point (0.5, 0.5, 0.5)
   could be found efficiently with:
<programlisting>
SELECT c FROM test ORDER BY c &lt;-&gt; cube(ARRAY[0.5, 0.5, 0.5]) LIMIT 1;
</programlisting>
  </para>

  <para>
   The <literal>~&gt;</literal> 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:
<programlisting>
SELECT c FROM test ORDER BY c ~&gt; 1 LIMIT 5;
</programlisting>
   And to get 2-D cubes ordered by the first coordinate of the upper right
   corner descending:
<programlisting>
SELECT c FROM test ORDER BY c ~&gt; 3 DESC LIMIT 5;
</programlisting>
  </para>

  <para>
   <xref linkend="cube-functions-table"/> shows the available functions.
  </para>

  <table id="cube-functions-table">
   <title>Cube Functions</title>
    <tgroup cols="1">
     <thead>
      <row>
       <entry role="func_table_entry"><para role="func_signature">
        Function
       </para>
       <para>
        Description
       </para>
       <para>
        Example(s)
       </para></entry>
      </row>
     </thead>

     <tbody>
      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <function>cube</function> ( <type>float8</type> )
        <returnvalue>cube</returnvalue>
       </para>
       <para>
        Makes a one dimensional cube with both coordinates the same.
       </para>
       <para>
        <literal>cube(1)</literal>
        <returnvalue>(1)</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <function>cube</function> ( <type>float8</type>, <type>float8</type> )
        <returnvalue>cube</returnvalue>
       </para>
       <para>
        Makes a one dimensional cube.
       </para>
       <para>
        <literal>cube(1, 2)</literal>
        <returnvalue>(1),(2)</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <function>cube</function> ( <type>float8[]</type> )
        <returnvalue>cube</returnvalue>
       </para>
       <para>
        Makes a zero-volume cube using the coordinates defined by the array.
       </para>
       <para>
        <literal>cube(ARRAY[1,2,3])</literal>
        <returnvalue>(1, 2, 3)</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <function>cube</function> ( <type>float8[]</type>, <type>float8[]</type> )
        <returnvalue>cube</returnvalue>
       </para>
       <para>
        Makes a cube with upper right and lower left coordinates as defined by
        the two arrays, which must be of the same length.
       </para>
       <para>
        <literal>cube(ARRAY[1,2], ARRAY[3,4])</literal>
        <returnvalue>(1, 2),(3, 4)</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <function>cube</function> ( <type>cube</type>, <type>float8</type> )
        <returnvalue>cube</returnvalue>
       </para>
       <para>
        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.
       </para>
       <para>
        <literal>cube('(1,2),(3,4)'::cube, 5)</literal>
        <returnvalue>(1, 2, 5),(3, 4, 5)</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <function>cube</function> ( <type>cube</type>, <type>float8</type>, <type>float8</type> )
        <returnvalue>cube</returnvalue>
       </para>
       <para>
        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.
       </para>
       <para>
        <literal>cube('(1,2),(3,4)'::cube, 5, 6)</literal>
        <returnvalue>(1, 2, 5),(3, 4, 6)</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <function>cube_dim</function> ( <type>cube</type> )
        <returnvalue>integer</returnvalue>
       </para>
       <para>
        Returns the number of dimensions of the cube.
       </para>
       <para>
        <literal>cube_dim('(1,2),(3,4)')</literal>
        <returnvalue>2</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <function>cube_ll_coord</function> ( <type>cube</type>, <type>integer</type> )
        <returnvalue>float8</returnvalue>
       </para>
       <para>
        Returns the <parameter>n</parameter>-th coordinate value for the lower
        left corner of the cube.
       </para>
       <para>
        <literal>cube_ll_coord('(1,2),(3,4)', 2)</literal>
        <returnvalue>2</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <function>cube_ur_coord</function> ( <type>cube</type>, <type>integer</type> )
        <returnvalue>float8</returnvalue>
       </para>
       <para>
        Returns the <parameter>n</parameter>-th coordinate value for the
        upper right corner of the cube.
       </para>
       <para>
        <literal>cube_ur_coord('(1,2),(3,4)', 2)</literal>
        <returnvalue>4</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <function>cube_is_point</function> ( <type>cube</type> )
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Returns true if the cube is a point, that is,
        the two defining corners are the same.
       </para>
       <para>
        <literal>cube_is_point(cube(1,1))</literal>
        <returnvalue>t</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <function>cube_distance</function> ( <type>cube</type>, <type>cube</type> )
        <returnvalue>float8</returnvalue>
       </para>
       <para>
        Returns the distance between two cubes. If both
        cubes are points, this is the normal distance function.
       </para>
       <para>
        <literal>cube_distance('(1,2)', '(3,4)')</literal>
        <returnvalue>2.8284271247461903</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <function>cube_subset</function> ( <type>cube</type>, <type>integer[]</type> )
        <returnvalue>cube</returnvalue>
       </para>
       <para>
        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.
       </para>
       <para>
        <literal>cube_subset(cube('(1,3,5),(6,7,8)'), ARRAY[2])</literal>
        <returnvalue>(3),(7)</returnvalue>
       </para>
       <para>
        <literal>cube_subset(cube('(1,3,5),(6,7,8)'), ARRAY[3,2,1,1])</literal>
        <returnvalue>(5, 3, 1, 1),(8, 7, 6, 6)</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <function>cube_union</function> ( <type>cube</type>, <type>cube</type> )
        <returnvalue>cube</returnvalue>
       </para>
       <para>
        Produces the union of two cubes.
       </para>
       <para>
        <literal>cube_union('(1,2)', '(3,4)')</literal>
        <returnvalue>(1, 2),(3, 4)</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <function>cube_inter</function> ( <type>cube</type>, <type>cube</type> )
        <returnvalue>cube</returnvalue>
       </para>
       <para>
        Produces the intersection of two cubes.
       </para>
       <para>
        <literal>cube_inter('(1,2)', '(3,4)')</literal>
        <returnvalue>(3, 4),(1, 2)</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <function>cube_enlarge</function> ( <parameter>c</parameter> <type>cube</type>, <parameter>r</parameter> <type>double</type>, <parameter>n</parameter> <type>integer</type> )
        <returnvalue>cube</returnvalue>
       </para>
       <para>
        Increases the size of the cube by the specified
        radius <parameter>r</parameter> in at least <parameter>n</parameter>
        dimensions.  If the radius is negative the cube is shrunk instead.
        All defined dimensions are changed by the
        radius <parameter>r</parameter>.  Lower-left coordinates are decreased
        by <parameter>r</parameter> and upper-right coordinates are increased
        by <parameter>r</parameter>.  If a lower-left coordinate is increased
        to more than the corresponding upper-right coordinate (this can only
        happen when <parameter>r</parameter> &lt; 0) than both coordinates are
        set to their average.  If <parameter>n</parameter> is greater than the
        number of defined dimensions and the cube is being enlarged
        (<parameter>r</parameter> &gt; 0), then extra dimensions are added to
        make <parameter>n</parameter> 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.
       </para>
       <para>
        <literal>cube_enlarge('(1,2),(3,4)', 0.5, 3)</literal>
        <returnvalue>(0.5, 1.5, -0.5),(3.5, 4.5, 0.5)</returnvalue>
       </para></entry>
      </row>
     </tbody>
    </tgroup>
  </table>
 </sect2>

 <sect2 id="cube-defaults">
  <title>Defaults</title>

  <para>
   This union:
  </para>
<programlisting>
SELECT cube_union('(0,5,2),(2,3,1)', '0');
cube_union
-------------------
(0, 0, 0),(2, 5, 2)
(1 row)
</programlisting>

   <para>
    does not contradict common sense, neither does the intersection:
   </para>

<programlisting>
SELECT cube_inter('(0,-1),(1,1)', '(-2),(2)');
cube_inter
-------------
(0, 0),(1, 0)
(1 row)
</programlisting>

   <para>
    In all binary operations on differently-dimensioned cubes,
    the lower-dimensional one is assumed to be a Cartesian projection, i. e., having zeroes
    in place of coordinates omitted in the string representation. The above
    examples are equivalent to:
   </para>

<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)');
</programlisting>

   <para>
    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.
   </para>

<programlisting>
SELECT cube_contains('(0,0),(1,1)', '0.5,0.5');
cube_contains
--------------
t
(1 row)
</programlisting>
 </sect2>

 <sect2 id="cube-notes">
  <title>Notes</title>

  <para>
   For examples of usage, see the regression test <filename>sql/cube.sql</filename>.
  </para>

  <para>
   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 <filename>cubedata.h</filename> if you need something bigger.
  </para>
 </sect2>

 <sect2 id="cube-credits">
  <title>Credits</title>

  <para>
   Original author: Gene Selkov, Jr. <email>selkovjr@mcs.anl.gov</email>,
   Mathematics and Computer Science Division, Argonne National Laboratory.
  </para>

  <para>
   My thanks are primarily to Prof. Joe Hellerstein
   (<ulink url="https://dsf.berkeley.edu/jmh/"></ulink>) for elucidating the
   gist of the GiST (<ulink url="http://gist.cs.berkeley.edu/"></ulink>), 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.
  </para>

  <para>
   Minor updates to this package were made by Bruno Wolff III
   <email>bruno@wolff.to</email> in August/September of 2002. These include
   changing the precision from single precision to double precision and adding
   some new functions.
  </para>

  <para>
   Additional updates were made by Joshua Reich <email>josh@root.net</email> in
   July 2006. These include <literal>cube(float8[], float8[])</literal> and
   cleaning up the code to use the V1 call protocol instead of the deprecated
   V0 protocol.
  </para>
 </sect2>

</sect1>