doc/src/sgml/cube.sgml

   1 <!-- $PostgreSQL$ -->
   2
   3 <sect1 id="cube">
   4  <title>cube</title>
   5
   6  <indexterm zone="cube">
   7   <primary>cube</primary>
   8  </indexterm>
   9
  10  <para>
  11   This module implements a data type <type>cube</> for
  12   representing multi-dimensional cubes.
  13  </para>
  14
  15  <sect2>
  16   <title>Syntax</title>
  17
  18   <para>
  19    <xref linkend="cube-repr-table"> shows the valid external
  20    representations for the <type>cube</>
  21    type.  <replaceable>x</>, <replaceable>y</>, etc. denote
  22    floating-point numbers.
  23   </para>
  24
  25   <table id="cube-repr-table">
  26    <title>Cube external representations</title>
  27    <tgroup cols="2">
  28     <tbody>
  29      <row>
  30       <entry><literal><replaceable>x</></literal></entry>
  31       <entry>A one-dimensional point
  32        (or, zero-length one-dimensional interval)
  33       </entry>
  34      </row>
  35      <row>
  36       <entry><literal>(<replaceable>x</>)</literal></entry>
  37       <entry>Same as above</entry>
  38      </row>
  39      <row>
  40       <entry><literal><replaceable>x1</>,<replaceable>x2</>,...,<replaceable>xn</></literal></entry>
  41       <entry>A point in n-dimensional space, represented internally as a
  42       zero-volume cube
  43       </entry>
  44      </row>
  45      <row>
  46       <entry><literal>(<replaceable>x1</>,<replaceable>x2</>,...,<replaceable>xn</>)</literal></entry>
  47       <entry>Same as above</entry>
  48      </row>
  49      <row>
  50       <entry><literal>(<replaceable>x</>),(<replaceable>y</>)</literal></entry>
  51       <entry>A one-dimensional interval starting at <replaceable>x</> and ending at <replaceable>y</> or vice versa; the
  52        order does not matter
  53       </entry>
  54      </row>
  55      <row>
  56       <entry><literal>[(<replaceable>x</>),(<replaceable>y</>)]</literal></entry>
  57       <entry>Same as above</entry>
  58      </row>
  59      <row>
  60       <entry><literal>(<replaceable>x1</>,...,<replaceable>xn</>),(<replaceable>y1</>,...,<replaceable>yn</>)</literal></entry>
  61       <entry>An n-dimensional cube represented by a pair of its diagonally
  62        opposite corners
  63       </entry>
  64      </row>
  65      <row>
  66       <entry><literal>[(<replaceable>x1</>,...,<replaceable>xn</>),(<replaceable>y1</>,...,<replaceable>yn</>)]</literal></entry>
  67       <entry>Same as above</entry>
  68      </row>
  69     </tbody>
  70    </tgroup>
  71   </table>
  72
  73   <para>
  74    It does not matter which order the opposite corners of a cube are
  75    entered in.  The <type>cube</> functions
  76    automatically swap values if needed to create a uniform
  77    <quote>lower left &mdash; upper right</> internal representation.
  78   </para>
  79
  80   <para>
  81    White space is ignored, so <literal>[(<replaceable>x</>),(<replaceable>y</>)]</literal> is the same as
  82    <literal>[ ( <replaceable>x</> ), ( <replaceable>y</> ) ]</literal>.
  83   </para>
  84  </sect2>
  85
  86  <sect2>
  87   <title>Precision</title>
  88
  89   <para>
  90    Values are stored internally as 64-bit floating point numbers. This means
  91    that numbers with more than about 16 significant digits will be truncated.
  92   </para>
  93  </sect2>
  94
  95  <sect2>
  96   <title>Usage</title>
  97
  98   <para>
  99    The <filename>cube</> module includes a GiST index operator class for
 100    <type>cube</> values.
 101    The operators supported by the GiST opclass include:
 102   </para>
 103
 104   <itemizedlist>
 105    <listitem>
 106     <programlisting>
 107 a = b                 Same as
 108     </programlisting>
 109     <para>
 110      The cubes a and b are identical.
 111     </para>
 112    </listitem>
 113    <listitem>
 114     <programlisting>
 115 a &amp;&amp; b                Overlaps
 116     </programlisting>
 117     <para>
 118      The cubes a and b overlap.
 119     </para>
 120    </listitem>
 121    <listitem>
 122     <programlisting>
 123 a @&gt; b                Contains
 124     </programlisting>
 125     <para>
 126      The cube a contains the cube b.
 127     </para>
 128    </listitem>
 129    <listitem>
 130   <programlisting>
 131 a &lt;@ b                Contained in
 132   </programlisting>
 133     <para>
 134      The cube a is contained in the cube b.
 135     </para>
 136    </listitem>
 137   </itemizedlist>
 138
 139   <para>
 140    (Before PostgreSQL 8.2, the containment operators @&gt; and &lt;@ were
 141    respectively called @ and ~.  These names are still available, but are
 142    deprecated and will eventually be retired.  Notice that the old names
 143    are reversed from the convention formerly followed by the core geometric
 144    datatypes!)
 145   </para>
 146
 147   <para>
 148    The standard B-tree operators are also provided, for example
 149
 150   <programlisting>
 151 [a, b] &lt; [c, d]                Less than
 152 [a, b] &gt; [c, d]                Greater than
 153   </programlisting>
 154
 155    These operators do not make a lot of sense for any practical
 156    purpose but sorting. These operators first compare (a) to (c),
 157    and if these are equal, compare (b) to (d). That results in
 158    reasonably good sorting in most cases, which is useful if
 159    you want to use ORDER BY with this type.
 160   </para>
 161
 162   <para>
 163    <xref linkend="cube-functions-table"> shows the available functions.
 164   </para>
 165
 166   <table id="cube-functions-table">
 167    <title>Cube functions</title>
 168    <tgroup cols="2">
 169     <tbody>
 170      <row>
 171       <entry><literal>cube(float8) returns cube</literal></entry>
 172       <entry>Makes a one dimensional cube with both coordinates the same.
 173        <literal>cube(1) == '(1)'</literal>
 174       </entry>
 175      </row>
 176
 177      <row>
 178       <entry><literal>cube(float8, float8) returns cube</literal></entry>
 179       <entry>Makes a one dimensional cube.
 180        <literal>cube(1,2) == '(1),(2)'</literal>
 181       </entry>
 182      </row>
 183
 184      <row>
 185       <entry><literal>cube(float8[]) returns cube</literal></entry>
 186       <entry>Makes a zero-volume cube using the coordinates
 187        defined by the array.
 188        <literal>cube(ARRAY[1,2]) == '(1,2)'</literal>
 189       </entry>
 190      </row>
 191
 192      <row>
 193       <entry><literal>cube(float8[], float8[]) returns cube</literal></entry>
 194       <entry>Makes a cube with upper right and lower left
 195        coordinates as defined by the two arrays, which must be of the
 196        same length.
 197        <literal>cube('{1,2}'::float[], '{3,4}'::float[]) == '(1,2),(3,4)'
 198        </literal>
 199       </entry>
 200      </row>
 201
 202      <row>
 203       <entry><literal>cube(cube, float8) returns cube</literal></entry>
 204       <entry>Makes a new cube by adding a dimension on to an
 205        existing cube with the same values for both parts of the new coordinate.
 206        This is useful for building cubes piece by piece from calculated values.
 207        <literal>cube('(1)',2) == '(1,2),(1,2)'</literal>
 208       </entry>
 209      </row>
 210
 211      <row>
 212       <entry><literal>cube(cube, float8, float8) returns cube</literal></entry>
 213       <entry>Makes a new cube by adding a dimension on to an
 214        existing cube. This is useful for building cubes piece by piece from
 215        calculated values. <literal>cube('(1,2)',3,4) == '(1,3),(2,4)'</literal>
 216       </entry>
 217      </row>
 218
 219      <row>
 220       <entry><literal>cube_dim(cube) returns int</literal></entry>
 221       <entry>Returns the number of dimensions of the cube
 222       </entry>
 223      </row>
 224
 225      <row>
 226       <entry><literal>cube_ll_coord(cube, int) returns double </literal></entry>
 227       <entry>Returns the n'th coordinate value for the lower left
 228        corner of a cube
 229       </entry>
 230      </row>
 231
 232     <row>
 233       <entry><literal>cube_ur_coord(cube, int) returns double
 234       </literal></entry>
 235       <entry>Returns the n'th coordinate value for the
 236        upper right corner of a cube
 237       </entry>
 238      </row>
 239
 240      <row>
 241       <entry><literal>cube_is_point(cube) returns bool</literal></entry>
 242       <entry>Returns true if a cube is a point, that is,
 243        the two defining corners are the same.</entry>
 244      </row>
 245
 246      <row>
 247       <entry><literal>cube_distance(cube, cube) returns double</literal></entry>
 248       <entry>Returns the distance between two cubes. If both
 249        cubes are points, this is the normal distance function.
 250       </entry>
 251      </row>
 252
 253      <row>
 254       <entry><literal>cube_subset(cube, int[]) returns cube
 255       </literal></entry>
 256       <entry>Makes a new cube from an existing cube, using a list of
 257        dimension indexes from an array. Can be used to find both the LL and UR
 258        coordinates of a single dimension, e.g.
 259        <literal>cube_subset(cube('(1,3,5),(6,7,8)'), ARRAY[2]) = '(3),(7)'</>.
 260        Or can be used to drop dimensions, or reorder them as desired, e.g.
 261        <literal>cube_subset(cube('(1,3,5),(6,7,8)'), ARRAY[3,2,1,1]) = '(5, 3,
 262        1, 1),(8, 7, 6, 6)'</>.
 263       </entry>
 264      </row>
 265
 266      <row>
 267       <entry><literal>cube_union(cube, cube) returns cube</literal></entry>
 268       <entry>Produces the union of two cubes
 269       </entry>
 270      </row>
 271
 272      <row>
 273       <entry><literal>cube_inter(cube, cube) returns cube</literal></entry>
 274       <entry>Produces the intersection of two cubes
 275       </entry>
 276      </row>
 277
 278      <row>
 279       <entry><literal>cube_enlarge(cube c, double r, int n) returns cube</literal></entry>
 280       <entry>Increases the size of a cube by a specified radius in at least
 281        n dimensions. If the radius is negative the cube is shrunk instead. This
 282        is useful for creating bounding boxes around a point for searching for
 283        nearby points. All defined dimensions are changed by the radius r.
 284        LL coordinates are decreased by r and UR coordinates are increased by r.
 285        If a LL coordinate is increased to larger than the corresponding UR
 286        coordinate (this can only happen when r &lt; 0) than both coordinates
 287        are set to their average.  If n is greater than the number of defined
 288        dimensions and the cube is being increased (r &gt;= 0) then 0 is used
 289        as the base for the extra coordinates.
 290       </entry>
 291      </row>
 292     </tbody>
 293    </tgroup>
 294   </table>
 295  </sect2>
 296
 297  <sect2>
 298   <title>Defaults</title>
 299
 300   <para>
 301    I believe this union:
 302   </para>
 303 <programlisting>
 304 select cube_union('(0,5,2),(2,3,1)', '0');
 305 cube_union
 306 -------------------
 307 (0, 0, 0),(2, 5, 2)
 308 (1 row)
 309 </programlisting>
 310
 311    <para>
 312     does not contradict common sense, neither does the intersection
 313    </para>
 314
 315 <programlisting>
 316 select cube_inter('(0,-1),(1,1)', '(-2),(2)');
 317 cube_inter
 318 -------------
 319 (0, 0),(1, 0)
 320 (1 row)
 321 </programlisting>
 322
 323    <para>
 324     In all binary operations on differently-dimensioned cubes, I assume the
 325     lower-dimensional one to be a cartesian projection, i. e., having zeroes
 326     in place of coordinates omitted in the string representation. The above
 327     examples are equivalent to:
 328    </para>
 329
 330 <programlisting>
 331 cube_union('(0,5,2),(2,3,1)','(0,0,0),(0,0,0)');
 332 cube_inter('(0,-1),(1,1)','(-2,0),(2,0)');
 333 </programlisting>
 334
 335    <para>
 336     The following containment predicate uses the point syntax,
 337     while in fact the second argument is internally represented by a box.
 338     This syntax makes it unnecessary to define a separate point type
 339     and functions for (box,point) predicates.
 340    </para>
 341
 342 <programlisting>
 343 select cube_contains('(0,0),(1,1)', '0.5,0.5');
 344 cube_contains
 345 --------------
 346 t
 347 (1 row)
 348 </programlisting>
 349  </sect2>
 350
 351  <sect2>
 352   <title>Notes</title>
 353
 354   <para>
 355    For examples of usage, see the regression test <filename>sql/cube.sql</>.
 356   </para>
 357
 358   <para>
 359    To make it harder for people to break things, there
 360    is a limit of 100 on the number of dimensions of cubes. This is set
 361    in <filename>cubedata.h</> if you need something bigger.
 362   </para>
 363  </sect2>
 364
 365  <sect2>
 366   <title>Credits</title>
 367
 368   <para>
 369    Original author: Gene Selkov, Jr. <email>selkovjr@mcs.anl.gov</email>,
 370    Mathematics and Computer Science Division, Argonne National Laboratory.
 371   </para>
 372
 373   <para>
 374    My thanks are primarily to Prof. Joe Hellerstein
 375    (<ulink url="http://db.cs.berkeley.edu/~jmh/"></ulink>) for elucidating the
 376    gist of the GiST (<ulink url="http://gist.cs.berkeley.edu/"></ulink>), and
 377    to his former student, Andy Dong (<ulink
 378    url="http://best.me.berkeley.edu/~adong/"></ulink>), for his example
 379    written for Illustra,
 380    <ulink url="http://garcia.me.berkeley.edu/~adong/rtree"></ulink>.
 381    I am also grateful to all Postgres developers, present and past, for
 382    enabling myself to create my own world and live undisturbed in it. And I
 383    would like to acknowledge my gratitude to Argonne Lab and to the
 384    U.S. Department of Energy for the years of faithful support of my database
 385    research.
 386   </para>
 387
 388   <para>
 389    Minor updates to this package were made by Bruno Wolff III
 390    <email>bruno@wolff.to</email> in August/September of 2002. These include
 391    changing the precision from single precision to double precision and adding
 392    some new functions.
 393   </para>
 394
 395   <para>
 396    Additional updates were made by Joshua Reich <email>josh@root.net</email> in
 397    July 2006. These include <literal>cube(float8[], float8[])</literal> and
 398    cleaning up the code to use the V1 call protocol instead of the deprecated
 399    V0 protocol.
 400   </para>
 401  </sect2>
 402
 403 </sect1>