F.34. seg

F.34. seg
Prev	Up	Appendix F. Additional Supplied Modules	Home	Next

F.34.1. Rationale
F.34.2. Syntax
F.34.3. Precision
F.34.4. Usage
F.34.5. Notes
F.34.6. Credits

+ 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. +

+ This module is considered “trusted”, that is, it can be + installed by non-superusers who have CREATE privilege + on the current database. +

F.34.1. Rationale

+ 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. +

+ Check this out: + +

+test=> select '6.25 .. 6.50'::seg as "pH";
+          pH
+------------
+6.25 .. 6.50
+(1 row)
+

F.34.2. Syntax

+ 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.26 gives an overview of allowed + representations; Table F.27 shows some + examples. +

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

Table F.26. 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.27. 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. +
`<5.0`	+ Creates a point at 5.0. `<` is ignored but + is preserved as a comment. +
`>5.0`	+ Creates a point at 5.0. `>` is ignored but + is preserved as a comment. +
`5(+-)0.3`	+ Creates an interval `4.7 .. 5.3`. + Note that the `(+-)` notation isn't preserved. +
`50 ..`	Everything that is greater than or equal to 50
`.. 0`	Everything that is less than or equal to 0
`1.5e-2 .. 2E-2`	Creates an interval `0.015 .. 0.02`
`1 ... 2`	+ The same as `1...2`, or `1 .. 2`, + or `1..2` + (spaces around the range operator are ignored) +

+ Because the ... operator is widely used in data sources, it is allowed + as an alternative spelling of the .. operator. 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. +

F.34.3. Precision

+ 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. +

F.34.4. Usage

+ The seg module includes a GiST index operator class for + seg values. + The operators supported by the GiST operator class are shown in Table F.28. +

Table F.28. Seg GiST Operators

+ Operator + + + Description +
+ `seg` `<<` `seg` + → `boolean` + + + Is the first `seg` entirely to the left of the second? + [a, b] << [c, d] is true if b < c. +
+ `seg` `>>` `seg` + → `boolean` + + + Is the first `seg` entirely to the right of the second? + [a, b] >> [c, d] is true if a > d. +
+ `seg` `&<` `seg` + → `boolean` + + + Does the first `seg` not extend to the right of the + second? + [a, b] &< [c, d] is true if b <= d. +
+ `seg` `&>` `seg` + → `boolean` + + + Does the first `seg` not extend to the left of the + second? + [a, b] &> [c, d] is true if a >= c. +
+ `seg` `=` `seg` + → `boolean` + + + Are the two `seg`s equal? +
+ `seg` `&&` `seg` + → `boolean` + + + Do the two `seg`s overlap? +
+ `seg` `@>` `seg` + → `boolean` + + + Does the first `seg` contain the second? +
+ `seg` `<@` `seg` + → `boolean` + + + Is the first `seg` contained in the second? +

+ Operator +

+ Description +

+ seg << seg + → boolean +

+ Is the first seg entirely to the left of the second? + [a, b] << [c, d] is true if b < c. +

+ seg >> seg + → boolean +

+ Is the first seg entirely to the right of the second? + [a, b] >> [c, d] is true if a > d. +

+ seg &< seg + → boolean +

+ Does the first seg not extend to the right of the + second? + [a, b] &< [c, d] is true if b <= d. +

+ seg &> seg + → boolean +

+ Does the first seg not extend to the left of the + second? + [a, b] &> [c, d] is true if a >= c. +

+ seg = seg + → boolean +

+ Are the two segs equal? +

+ seg && seg + → boolean +

+ Do the two segs overlap? +

+ seg @> seg + → boolean +

+ Does the first seg contain the second? +

+ seg <@ seg + → boolean +

+ Is the first seg contained in the second? +

+ (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!) +

+ In addition to the above operators, the usual comparison + operators shown in Table 9.1 are + available for type seg. 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. +

F.34.5. Notes

+ For examples of usage, see the regression test sql/seg.sql. +

+ 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: + +

+postgres=> select '10(+-)1'::seg as seg;
+      seg
+---------
+9.0 .. 11             -- should be: 9 .. 11
+

+ 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. +

F.34.6. Credits

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

+ My thanks are primarily to Prof. Joe Hellerstein + (https://dsf.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. +

Prev	Up	Next
F.33. postgres_fdw	Home	F.35. sepgsql