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