1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>F.36. seg</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="postgres-fdw.html" title="F.35. postgres_fdw" /><link rel="next" href="sepgsql.html" title="F.37. sepgsql" /></head><body id="docContent" class="container-fluid col-10"><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">F.36. seg</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="postgres-fdw.html" title="F.35. postgres_fdw">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules">Up</a></td><th width="60%" align="center">Appendix F. Additional Supplied Modules</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 14.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sepgsql.html" title="F.37. sepgsql">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="SEG"><div class="titlepage"><div><div><h2 class="title" style="clear: both">F.36. seg</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="seg.html#id-1.11.7.45.5">F.36.1. Rationale</a></span></dt><dt><span class="sect2"><a href="seg.html#id-1.11.7.45.6">F.36.2. Syntax</a></span></dt><dt><span class="sect2"><a href="seg.html#id-1.11.7.45.7">F.36.3. Precision</a></span></dt><dt><span class="sect2"><a href="seg.html#id-1.11.7.45.8">F.36.4. Usage</a></span></dt><dt><span class="sect2"><a href="seg.html#id-1.11.7.45.9">F.36.5. Notes</a></span></dt><dt><span class="sect2"><a href="seg.html#id-1.11.7.45.10">F.36.6. Credits</a></span></dt></dl></div><a id="id-1.11.7.45.2" class="indexterm"></a><p>
This module implements a data type <code class="type">seg</code> for
representing line segments, or floating point intervals.
<code class="type">seg</code> can represent uncertainty in the interval endpoints,
making it especially useful for representing laboratory measurements.
</p><p>
This module is considered <span class="quote">“<span class="quote">trusted</span>”</span>, that is, it can be
installed by non-superusers who have <code class="literal">CREATE</code> privilege
on the current database.
</p><div class="sect2" id="id-1.11.7.45.5"><div class="titlepage"><div><div><h3 class="title">F.36.1. Rationale</h3></div></div></div><p>
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.
</p><p>
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.
</p><p>
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:
</p><pre class="screen">
test=> select 6.50 :: float8 as "pH";
pH
---
6.5
(1 row)
</pre><p>
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.
</p><p>
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.
</p><p>
Check this out:
</p><pre class="screen">
test=> select '6.25 .. 6.50'::seg as "pH";
pH
------------
6.25 .. 6.50
(1 row)
</pre><p>
</p></div><div class="sect2" id="id-1.11.7.45.6"><div class="titlepage"><div><div><h3 class="title">F.36.2. Syntax</h3></div></div></div><p>
The external representation of an interval is formed using one or two
floating-point numbers joined by the range operator (<code class="literal">..</code>
or <code class="literal">...</code>). Alternatively, it can be specified as a
center point plus or minus a deviation.
Optional certainty indicators (<code class="literal"><</code>,
<code class="literal">></code> or <code class="literal">~</code>) can be stored as well.
(Certainty indicators are ignored by all the built-in operators, however.)
<a class="xref" href="seg.html#SEG-REPR-TABLE" title="Table F.27. seg External Representations">Table F.27</a> gives an overview of allowed
representations; <a class="xref" href="seg.html#SEG-INPUT-EXAMPLES" title="Table F.28. Examples of Valid seg Input">Table F.28</a> shows some
examples.
</p><p>
In <a class="xref" href="seg.html#SEG-REPR-TABLE" title="Table F.27. seg External Representations">Table F.27</a>, <em class="replaceable"><code>x</code></em>, <em class="replaceable"><code>y</code></em>, and
<em class="replaceable"><code>delta</code></em> denote
floating-point numbers. <em class="replaceable"><code>x</code></em> and <em class="replaceable"><code>y</code></em>, but
not <em class="replaceable"><code>delta</code></em>, can be preceded by a certainty indicator.
</p><div class="table" id="SEG-REPR-TABLE"><p class="title"><strong>Table F.27. <code class="type">seg</code> External Representations</strong></p><div class="table-contents"><table class="table" summary="seg External Representations" border="1"><colgroup><col /><col /></colgroup><tbody><tr><td><code class="literal"><em class="replaceable"><code>x</code></em></code></td><td>Single value (zero-length interval)
</td></tr><tr><td><code class="literal"><em class="replaceable"><code>x</code></em> .. <em class="replaceable"><code>y</code></em></code></td><td>Interval from <em class="replaceable"><code>x</code></em> to <em class="replaceable"><code>y</code></em>
</td></tr><tr><td><code class="literal"><em class="replaceable"><code>x</code></em> (+-) <em class="replaceable"><code>delta</code></em></code></td><td>Interval from <em class="replaceable"><code>x</code></em> - <em class="replaceable"><code>delta</code></em> to
<em class="replaceable"><code>x</code></em> + <em class="replaceable"><code>delta</code></em>
</td></tr><tr><td><code class="literal"><em class="replaceable"><code>x</code></em> ..</code></td><td>Open interval with lower bound <em class="replaceable"><code>x</code></em>
</td></tr><tr><td><code class="literal">.. <em class="replaceable"><code>x</code></em></code></td><td>Open interval with upper bound <em class="replaceable"><code>x</code></em>
</td></tr></tbody></table></div></div><br class="table-break" /><div class="table" id="SEG-INPUT-EXAMPLES"><p class="title"><strong>Table F.28. Examples of Valid <code class="type">seg</code> Input</strong></p><div class="table-contents"><table class="table" summary="Examples of Valid seg Input" border="1"><colgroup><col class="col1" /><col class="col2" /></colgroup><tbody><tr><td><code class="literal">5.0</code></td><td>
Creates a zero-length segment (a point, if you will)
</td></tr><tr><td><code class="literal">~5.0</code></td><td>
Creates a zero-length segment and records
<code class="literal">~</code> in the data. <code class="literal">~</code> is ignored
by <code class="type">seg</code> operations, but
is preserved as a comment.
</td></tr><tr><td><code class="literal"><5.0</code></td><td>
Creates a point at 5.0. <code class="literal"><</code> is ignored but
is preserved as a comment.
</td></tr><tr><td><code class="literal">>5.0</code></td><td>
Creates a point at 5.0. <code class="literal">></code> is ignored but
is preserved as a comment.
</td></tr><tr><td><code class="literal">5(+-)0.3</code></td><td>
Creates an interval <code class="literal">4.7 .. 5.3</code>.
Note that the <code class="literal">(+-)</code> notation isn't preserved.
</td></tr><tr><td><code class="literal">50 .. </code></td><td>Everything that is greater than or equal to 50</td></tr><tr><td><code class="literal">.. 0</code></td><td>Everything that is less than or equal to 0</td></tr><tr><td><code class="literal">1.5e-2 .. 2E-2 </code></td><td>Creates an interval <code class="literal">0.015 .. 0.02</code></td></tr><tr><td><code class="literal">1 ... 2</code></td><td>
The same as <code class="literal">1...2</code>, or <code class="literal">1 .. 2</code>,
or <code class="literal">1..2</code>
(spaces around the range operator are ignored)
</td></tr></tbody></table></div></div><br class="table-break" /><p>
Because the <code class="literal">...</code> operator is widely used in data sources, it is allowed
as an alternative spelling of the <code class="literal">..</code> operator. Unfortunately, this
creates a parsing ambiguity: it is not clear whether the upper bound
in <code class="literal">0...23</code> is meant to be <code class="literal">23</code> or <code class="literal">0.23</code>.
This is resolved by requiring at least one digit before the decimal
point in all numbers in <code class="type">seg</code> input.
</p><p>
As a sanity check, <code class="type">seg</code> rejects intervals with the lower bound
greater than the upper, for example <code class="literal">5 .. 2</code>.
</p></div><div class="sect2" id="id-1.11.7.45.7"><div class="titlepage"><div><div><h3 class="title">F.36.3. Precision</h3></div></div></div><p>
<code class="type">seg</code> 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.
</p><p>
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.
</p></div><div class="sect2" id="id-1.11.7.45.8"><div class="titlepage"><div><div><h3 class="title">F.36.4. Usage</h3></div></div></div><p>
The <code class="filename">seg</code> module includes a GiST index operator class for
<code class="type">seg</code> values.
The operators supported by the GiST operator class are shown in <a class="xref" href="seg.html#SEG-GIST-OPERATORS" title="Table F.29. Seg GiST Operators">Table F.29</a>.
</p><div class="table" id="SEG-GIST-OPERATORS"><p class="title"><strong>Table F.29. Seg GiST Operators</strong></p><div class="table-contents"><table class="table" summary="Seg GiST Operators" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
Operator
</p>
<p>
Description
</p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
<code class="type">seg</code> <code class="literal"><<</code> <code class="type">seg</code>
→ <code class="returnvalue">boolean</code>
</p>
<p>
Is the first <code class="type">seg</code> entirely to the left of the second?
[a, b] << [c, d] is true if b < c.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="type">seg</code> <code class="literal">>></code> <code class="type">seg</code>
→ <code class="returnvalue">boolean</code>
</p>
<p>
Is the first <code class="type">seg</code> entirely to the right of the second?
[a, b] >> [c, d] is true if a > d.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="type">seg</code> <code class="literal">&<</code> <code class="type">seg</code>
→ <code class="returnvalue">boolean</code>
</p>
<p>
Does the first <code class="type">seg</code> not extend to the right of the
second?
[a, b] &< [c, d] is true if b <= d.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="type">seg</code> <code class="literal">&></code> <code class="type">seg</code>
→ <code class="returnvalue">boolean</code>
</p>
<p>
Does the first <code class="type">seg</code> not extend to the left of the
second?
[a, b] &> [c, d] is true if a >= c.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="type">seg</code> <code class="literal">=</code> <code class="type">seg</code>
→ <code class="returnvalue">boolean</code>
</p>
<p>
Are the two <code class="type">seg</code>s equal?
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="type">seg</code> <code class="literal">&&</code> <code class="type">seg</code>
→ <code class="returnvalue">boolean</code>
</p>
<p>
Do the two <code class="type">seg</code>s overlap?
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="type">seg</code> <code class="literal">@></code> <code class="type">seg</code>
→ <code class="returnvalue">boolean</code>
</p>
<p>
Does the first <code class="type">seg</code> contain the second?
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="type">seg</code> <code class="literal"><@</code> <code class="type">seg</code>
→ <code class="returnvalue">boolean</code>
</p>
<p>
Is the first <code class="type">seg</code> contained in the second?
</p></td></tr></tbody></table></div></div><br class="table-break" /><p>
In addition to the above operators, the usual comparison
operators shown in <a class="xref" href="functions-comparison.html#FUNCTIONS-COMPARISON-OP-TABLE" title="Table 9.1. Comparison Operators">Table 9.1</a> are
available for type <code class="type">seg</code>. 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.
</p></div><div class="sect2" id="id-1.11.7.45.9"><div class="titlepage"><div><div><h3 class="title">F.36.5. Notes</h3></div></div></div><p>
For examples of usage, see the regression test <code class="filename">sql/seg.sql</code>.
</p><p>
The mechanism that converts <code class="literal">(+-)</code> 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:
</p><pre class="screen">
postgres=> select '10(+-)1'::seg as seg;
seg
---------
9.0 .. 11 -- should be: 9 .. 11
</pre><p>
</p><p>
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 <code class="type">seg</code> column; see the script <code class="filename">sort-segments.pl</code>
for an example.
</p></div><div class="sect2" id="id-1.11.7.45.10"><div class="titlepage"><div><div><h3 class="title">F.36.6. Credits</h3></div></div></div><p>
Original author: Gene Selkov, Jr. <code class="email"><<a class="email" href="mailto:selkovjr@mcs.anl.gov">selkovjr@mcs.anl.gov</a>></code>,
Mathematics and Computer Science Division, Argonne National Laboratory.
</p><p>
My thanks are primarily to Prof. Joe Hellerstein
(<a class="ulink" href="https://dsf.berkeley.edu/jmh/" target="_top">https://dsf.berkeley.edu/jmh/</a>) for elucidating the
gist of the GiST (<a class="ulink" href="http://gist.cs.berkeley.edu/" target="_top">http://gist.cs.berkeley.edu/</a>). 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.
</p></div></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="postgres-fdw.html" title="F.35. postgres_fdw">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sepgsql.html" title="F.37. sepgsql">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.35. postgres_fdw </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 14.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> F.37. sepgsql</td></tr></table></div></body></html>
|
