1 <!-- doc/src/sgml/seg.sgml -->
3 <sect1 id="seg" xreflabel="seg">
11 This module implements a data type <type>seg</> for
12 representing line segments, or floating point intervals.
13 <type>seg</> can represent uncertainty in the interval endpoints,
14 making it especially useful for representing laboratory measurements.
18 <title>Rationale</title>
21 The geometry of measurements is usually more complex than that of a
22 point in a numeric continuum. A measurement is usually a segment of
23 that continuum with somewhat fuzzy limits. The measurements come out
24 as intervals because of uncertainty and randomness, as well as because
25 the value being measured may naturally be an interval indicating some
26 condition, such as the temperature range of stability of a protein.
30 Using just common sense, it appears more convenient to store such data
31 as intervals, rather than pairs of numbers. In practice, it even turns
32 out more efficient in most applications.
36 Further along the line of common sense, the fuzziness of the limits
37 suggests that the use of traditional numeric data types leads to a
38 certain loss of information. Consider this: your instrument reads
39 6.50, and you input this reading into the database. What do you get
40 when you fetch it? Watch:
43 test=> select 6.50 :: float8 as "pH";
50 In the world of measurements, 6.50 is not the same as 6.5. It may
51 sometimes be critically different. The experimenters usually write
52 down (and publish) the digits they trust. 6.50 is actually a fuzzy
53 interval contained within a bigger and even fuzzier interval, 6.5,
54 with their center points being (probably) the only common feature they
55 share. We definitely do not want such different data items to appear the
60 Conclusion? It is nice to have a special data type that can record the
61 limits of an interval with arbitrarily variable precision. Variable in
62 the sense that each data element records its own precision.
69 test=> select '6.25 .. 6.50'::seg as "pH";
82 The external representation of an interval is formed using one or two
83 floating-point numbers joined by the range operator (<literal>..</literal>
84 or <literal>...</literal>). Alternatively, it can be specified as a
85 center point plus or minus a deviation.
86 Optional certainty indicators (<literal><</literal>,
87 <literal>></literal> or <literal>~</literal>) can be stored as well.
88 (Certainty indicators are ignored by all the built-in operators, however.)
89 <xref linkend="seg-repr-table"> gives an overview of allowed
90 representations; <xref linkend="seg-input-examples"> shows some
95 In <xref linkend="seg-repr-table">, <replaceable>x</>, <replaceable>y</>, and
96 <replaceable>delta</> denote
97 floating-point numbers. <replaceable>x</> and <replaceable>y</>, but
98 not <replaceable>delta</>, can be preceded by a certainty indicator.
101 <table id="seg-repr-table">
102 <title><type>seg</> External Representations</title>
106 <entry><literal><replaceable>x</></literal></entry>
107 <entry>Single value (zero-length interval)
111 <entry><literal><replaceable>x</> .. <replaceable>y</></literal></entry>
112 <entry>Interval from <replaceable>x</> to <replaceable>y</>
116 <entry><literal><replaceable>x</> (+-) <replaceable>delta</></literal></entry>
117 <entry>Interval from <replaceable>x</> - <replaceable>delta</> to
118 <replaceable>x</> + <replaceable>delta</>
122 <entry><literal><replaceable>x</> ..</literal></entry>
123 <entry>Open interval with lower bound <replaceable>x</>
127 <entry><literal>.. <replaceable>x</></literal></entry>
128 <entry>Open interval with upper bound <replaceable>x</>
135 <table id="seg-input-examples">
136 <title>Examples of Valid <type>seg</> Input</title>
140 <entry><literal>5.0</literal></entry>
142 Creates a zero-length segment (a point, if you will)
146 <entry><literal>~5.0</literal></entry>
148 Creates a zero-length segment and records
149 <literal>~</> in the data. <literal>~</literal> is ignored
150 by <type>seg</> operations, but
151 is preserved as a comment.
155 <entry><literal><5.0</literal></entry>
157 Creates a point at 5.0. <literal><</literal> is ignored but
158 is preserved as a comment.
162 <entry><literal>>5.0</literal></entry>
164 Creates a point at 5.0. <literal>></literal> is ignored but
165 is preserved as a comment.
169 <entry><literal>5(+-)0.3</literal></entry>
171 Creates an interval <literal>4.7 .. 5.3</literal>.
172 Note that the <literal>(+-)</> notation isn't preserved.
176 <entry><literal>50 .. </literal></entry>
177 <entry>Everything that is greater than or equal to 50</entry>
180 <entry><literal>.. 0</literal></entry>
181 <entry>Everything that is less than or equal to 0</entry>
184 <entry><literal>1.5e-2 .. 2E-2 </literal></entry>
185 <entry>Creates an interval <literal>0.015 .. 0.02</literal></entry>
188 <entry><literal>1 ... 2</literal></entry>
190 The same as <literal>1...2</literal>, or <literal>1 .. 2</literal>,
191 or <literal>1..2</literal>
192 (spaces around the range operator are ignored)
200 Because <literal>...</> is widely used in data sources, it is allowed
201 as an alternative spelling of <literal>..</>. Unfortunately, this
202 creates a parsing ambiguity: it is not clear whether the upper bound
203 in <literal>0...23</> is meant to be <literal>23</> or <literal>0.23</>.
204 This is resolved by requiring at least one digit before the decimal
205 point in all numbers in <type>seg</> input.
209 As a sanity check, <type>seg</> rejects intervals with the lower bound
210 greater than the upper, for example <literal>5 .. 2</>.
216 <title>Precision</title>
219 <type>seg</> values are stored internally as pairs of 32-bit floating point
220 numbers. This means that numbers with more than 7 significant digits
225 Numbers with 7 or fewer significant digits retain their
226 original precision. That is, if your query returns 0.00, you will be
227 sure that the trailing zeroes are not the artifacts of formatting: they
228 reflect the precision of the original data. The number of leading
229 zeroes does not affect precision: the value 0.0067 is considered to
230 have just 2 significant digits.
238 The <filename>seg</> module includes a GiST index operator class for
240 The operators supported by the GiST operator class are shown in <xref linkend="seg-gist-operators">.
243 <table id="seg-gist-operators">
244 <title>Seg GiST Operators</title>
248 <entry>Operator</entry>
249 <entry>Description</entry>
255 <entry><literal>[a, b] << [c, d]</literal></entry>
256 <entry>[a, b] is entirely to the left of [c, d]. That is, [a,
257 b] << [c, d] is true if b < c and false otherwise.</entry>
261 <entry><literal>[a, b] >> [c, d]</literal></entry>
262 <entry>[a, b] is entirely to the right of [c, d]. That is, [a,
263 b] >> [c, d] is true if a > d and false otherwise.</entry>
267 <entry><literal>[a, b] &< [c, d]</literal></entry>
268 <entry>Overlaps or is left of — This might be better read
269 as <quote>does not extend to right of</quote>. It is true when
274 <entry><literal>[a, b] &> [c, d]</literal></entry>
275 <entry>Overlaps or is right of — This might be better read
276 as <quote>does not extend to left of</quote>. It is true when
281 <entry><literal>[a, b] = [c, d]</literal></entry>
282 <entry>Same as — The segments [a, b] and [c, d] are
283 identical, that is, a = c and b = d.</entry>
287 <entry><literal>[a, b] && [c, d]</literal></entry>
288 <entry>The segments [a, b] and [c, d] overlap.</entry>
292 <entry><literal>[a, b] @> [c, d]</literal></entry>
293 <entry>The segment [a, b] contains the segment [c, d], that is,
294 a <= c and b >= d.</entry>
298 <entry><literal>[a, b] <@ [c, d]</literal></entry>
299 <entry>The segment [a, b] is contained in [c, d], that is, a
300 >= c and b <= d.</entry>
307 (Before PostgreSQL 8.2, the containment operators <literal>@></> and <literal><@</> were
308 respectively called <literal>@</> and <literal>~</>. These names are still available, but are
309 deprecated and will eventually be retired. Notice that the old names
310 are reversed from the convention formerly followed by the core geometric
315 The standard B-tree operators are also provided, for example
321 <entry>Operator</entry>
322 <entry>Description</entry>
328 <entry><literal>[a, b] < [c, d]</literal></entry>
329 <entry>Less than</entry>
333 <entry><literal>[a, b] > [c, d]</literal></entry>
334 <entry>Greater than</entry>
340 These operators do not make a lot of sense for any practical
341 purpose but sorting. These operators first compare (a) to (c),
342 and if these are equal, compare (b) to (d). That results in
343 reasonably good sorting in most cases, which is useful if
344 you want to use ORDER BY with this type.
352 For examples of usage, see the regression test <filename>sql/seg.sql</>.
356 The mechanism that converts <literal>(+-)</> to regular ranges
357 isn't completely accurate in determining the number of significant digits
358 for the boundaries. For example, it adds an extra digit to the lower
359 boundary if the resulting interval includes a power of ten:
362 postgres=> select '10(+-)1'::seg as seg;
365 9.0 .. 11 -- should be: 9 .. 11
370 The performance of an R-tree index can largely depend on the initial
371 order of input values. It may be very helpful to sort the input table
372 on the <type>seg</> column; see the script <filename>sort-segments.pl</>
378 <title>Credits</title>
381 Original author: Gene Selkov, Jr. <email>selkovjr@mcs.anl.gov</email>,
382 Mathematics and Computer Science Division, Argonne National Laboratory.
386 My thanks are primarily to Prof. Joe Hellerstein
387 (<ulink url="http://db.cs.berkeley.edu/jmh/"></ulink>) for elucidating the
388 gist of the GiST (<ulink url="http://gist.cs.berkeley.edu/"></ulink>). I am
389 also grateful to all Postgres developers, present and past, for enabling
390 myself to create my own world and live undisturbed in it. And I would like
391 to acknowledge my gratitude to Argonne Lab and to the U.S. Department of
392 Energy for the years of faithful support of my database research.