diff options
| author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 12:15:05 +0000 |
|---|---|---|
| committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 12:15:05 +0000 |
| commit | 46651ce6fe013220ed397add242004d764fc0153 (patch) | |
| tree | 6e5299f990f88e60174a1d3ae6e48eedd2688b2b /doc/src/sgml/html/seg.html | |
| parent | Initial commit. (diff) | |
| download | postgresql-14-upstream.tar.xz postgresql-14-upstream.zip | |
Adding upstream version 14.5.upstream/14.5upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'doc/src/sgml/html/seg.html')
| -rw-r--r-- | doc/src/sgml/html/seg.html | 222 |
1 files changed, 222 insertions, 0 deletions
diff --git a/doc/src/sgml/html/seg.html b/doc/src/sgml/html/seg.html new file mode 100644 index 0000000..528697e --- /dev/null +++ b/doc/src/sgml/html/seg.html @@ -0,0 +1,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>
\ No newline at end of file |