This module implements a data type seg
for representing line segments, or floating point intervals.
seg can represent uncertainty in the
interval endpoints, making it especially useful for representing
laboratory measurements.

The geometry of measurements is usually more complex than
that of a point in a numeric continuum. A measurement is
usually a segment of that continuum with somewhat fuzzy limits.
The measurements come out as intervals because of uncertainty
and randomness, as well as because the value being measured may
naturally be an interval indicating some condition, such as the
temperature range of stability of a protein.

Using just common sense, it appears more convenient to store
such data as intervals, rather than pairs of numbers. In
practice, it even turns out more efficient in most
applications.

Further along the line of common sense, the fuzziness of the
limits suggests that the use of traditional numeric data types
leads to a certain loss of information. Consider this: your
instrument reads 6.50, and you input this reading into the
database. What do you get when you fetch it? Watch:

test=> select 6.50 :: float8 as "pH";
pH
---
6.5
(1 row)

In the world of measurements, 6.50 is not the same as 6.5.
It may sometimes be critically different. The experimenters
usually write down (and publish) the digits they trust. 6.50 is
actually a fuzzy interval contained within a bigger and even
fuzzier interval, 6.5, with their center points being
(probably) the only common feature they share. We definitely do
not want such different data items to appear the same.

Conclusion? It is nice to have a special data type that can
record the limits of an interval with arbitrarily variable
precision. Variable in the sense that each data element records
its own precision.

The external representation of an interval is formed using
one or two floating-point numbers joined by the range operator
(.. or ...).
Alternatively, it can be specified as a center point plus or
minus a deviation. Optional certainty indicators (<, > or
~) can be stored as well. (Certainty
indicators are ignored by all the built-in operators, however.)
Table F-24 gives an
overview of allowed representations; Table F-25 shows some
examples.

In Table F-24,
x, y, and delta denote floating-point numbers.
x and y, but not delta, can be preceded by a certainty
indicator.

Table F-24. seg External
Representations

x

Single value (zero-length interval)

x .. y

Interval from x to
y

x (+-) delta

Interval from x -
delta to x + delta

x ..

Open interval with lower bound x

.. x

Open interval with upper bound x

Table F-25. Examples of Valid seg Input

5.0

Creates a zero-length segment (a point, if you
will)

~5.0

Creates a zero-length segment and records
~ in the data. ~ is ignored by seg operations, but is preserved as a
comment.

The same as 1...2, or
1 .. 2, or 1..2 (spaces around the range operator
are ignored)

Because ... is widely used in data
sources, it is allowed as an alternative spelling of ... Unfortunately, this creates a parsing
ambiguity: it is not clear whether the upper bound in
0...23 is meant to be 23 or 0.23. This is
resolved by requiring at least one digit before the decimal
point in all numbers in seg input.

As a sanity check, seg rejects
intervals with the lower bound greater than the upper, for
example 5 .. 2.

seg values are stored internally as
pairs of 32-bit floating point numbers. This means that numbers
with more than 7 significant digits will be truncated.

Numbers with 7 or fewer significant digits retain their
original precision. That is, if your query returns 0.00, you
will be sure that the trailing zeroes are not the artifacts of
formatting: they reflect the precision of the original data.
The number of leading zeroes does not affect precision: the
value 0.0067 is considered to have just 2 significant
digits.

The segment [a, b] is contained in [c, d], that is,
a >= c and b <= d.

(Before PostgreSQL 8.2, the containment operators @> and <@ were
respectively called @ and ~. 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!)

The standard B-tree operators are also provided, for
example

Operator

Description

[a, b] < [c, d]

Less than

[a, b] > [c, d]

Greater than

These operators do not make a lot of sense for any
practical purpose but sorting. These operators first compare
(a) to (c), and if these are equal, compare (b) to (d). That
results in reasonably good sorting in most cases, which is
useful if you want to use ORDER BY with this type.

The mechanism that converts (+-) to
regular ranges isn't completely accurate in determining the
number of significant digits for the boundaries. For example,
it adds an extra digit to the lower boundary if the resulting
interval includes a power of ten:

The performance of an R-tree index can largely depend on the
initial order of input values. It may be very helpful to sort
the input table on the seg column; see
the script sort-segments.pl for an
example.

My thanks are primarily to Prof. Joe Hellerstein (http://db.cs.berkeley.edu/jmh/) for elucidating the
gist of the GiST (http://gist.cs.berkeley.edu/). 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.