<?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>9.11. Geometric Functions and Operators</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="functions-enum.html" title="9.10. Enum Support Functions" /><link rel="next" href="functions-net.html" title="9.12. Network Address Functions and Operators" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">9.11. Geometric Functions and Operators</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="functions-enum.html" title="9.10. Enum Support Functions">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><th width="60%" align="center">Chapter 9. Functions and Operators</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 11.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="functions-net.html" title="9.12. Network Address Functions and Operators">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="FUNCTIONS-GEOMETRY"><div class="titlepage"><div><div><h2 class="title" style="clear: both">9.11. Geometric Functions and Operators</h2></div></div></div><p>
The geometric types <code class="type">point</code>, <code class="type">box</code>,
<code class="type">lseg</code>, <code class="type">line</code>, <code class="type">path</code>,
<code class="type">polygon</code>, and <code class="type">circle</code> have a large set of
native support functions and operators, shown in <a class="xref" href="functions-geometry.html#FUNCTIONS-GEOMETRY-OP-TABLE" title="Table 9.33. Geometric Operators">Table 9.33</a>, <a class="xref" href="functions-geometry.html#FUNCTIONS-GEOMETRY-FUNC-TABLE" title="Table 9.34. Geometric Functions">Table 9.34</a>, and <a class="xref" href="functions-geometry.html#FUNCTIONS-GEOMETRY-CONV-TABLE" title="Table 9.35. Geometric Type Conversion Functions">Table 9.35</a>.
</p><div class="caution"><h3 class="title">Caution</h3><p>
Note that the <span class="quote">“<span class="quote">same as</span>”</span> operator, <code class="literal">~=</code>, represents
the usual notion of equality for the <code class="type">point</code>,
<code class="type">box</code>, <code class="type">polygon</code>, and <code class="type">circle</code> types.
Some of these types also have an <code class="literal">=</code> operator, but
<code class="literal">=</code> compares
for equal <span class="emphasis"><em>areas</em></span> only. The other scalar comparison operators
(<code class="literal"><=</code> and so on) likewise compare areas for these types.
</p></div><div class="table" id="FUNCTIONS-GEOMETRY-OP-TABLE"><p class="title"><strong>Table 9.33. Geometric Operators</strong></p><div class="table-contents"><table class="table" summary="Geometric Operators" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>Operator</th><th>Description</th><th>Example</th></tr></thead><tbody><tr><td> <code class="literal">+</code> </td><td>Translation</td><td><code class="literal">box '((0,0),(1,1))' + point '(2.0,0)'</code></td></tr><tr><td> <code class="literal">-</code> </td><td>Translation</td><td><code class="literal">box '((0,0),(1,1))' - point '(2.0,0)'</code></td></tr><tr><td> <code class="literal">*</code> </td><td>Scaling/rotation</td><td><code class="literal">box '((0,0),(1,1))' * point '(2.0,0)'</code></td></tr><tr><td> <code class="literal">/</code> </td><td>Scaling/rotation</td><td><code class="literal">box '((0,0),(2,2))' / point '(2.0,0)'</code></td></tr><tr><td> <code class="literal">#</code> </td><td>Point or box of intersection</td><td><code class="literal">box '((1,-1),(-1,1))' # box '((1,1),(-2,-2))'</code></td></tr><tr><td> <code class="literal">#</code> </td><td>Number of points in path or polygon</td><td><code class="literal"># path '((1,0),(0,1),(-1,0))'</code></td></tr><tr><td> <code class="literal">@-@</code> </td><td>Length or circumference</td><td><code class="literal">@-@ path '((0,0),(1,0))'</code></td></tr><tr><td> <code class="literal">@@</code> </td><td>Center</td><td><code class="literal">@@ circle '((0,0),10)'</code></td></tr><tr><td> <code class="literal">##</code> </td><td>Closest point to first operand on second operand</td><td><code class="literal">point '(0,0)' ## lseg '((2,0),(0,2))'</code></td></tr><tr><td> <code class="literal"><-></code> </td><td>Distance between</td><td><code class="literal">circle '((0,0),1)' <-> circle '((5,0),1)'</code></td></tr><tr><td> <code class="literal">&&</code> </td><td>Overlaps? (One point in common makes this true.)</td><td><code class="literal">box '((0,0),(1,1))' && box '((0,0),(2,2))'</code></td></tr><tr><td> <code class="literal"><<</code> </td><td>Is strictly left of?</td><td><code class="literal">circle '((0,0),1)' << circle '((5,0),1)'</code></td></tr><tr><td> <code class="literal">>></code> </td><td>Is strictly right of?</td><td><code class="literal">circle '((5,0),1)' >> circle '((0,0),1)'</code></td></tr><tr><td> <code class="literal">&<</code> </td><td>Does not extend to the right of?</td><td><code class="literal">box '((0,0),(1,1))' &< box '((0,0),(2,2))'</code></td></tr><tr><td> <code class="literal">&></code> </td><td>Does not extend to the left of?</td><td><code class="literal">box '((0,0),(3,3))' &> box '((0,0),(2,2))'</code></td></tr><tr><td> <code class="literal"><<|</code> </td><td>Is strictly below?</td><td><code class="literal">box '((0,0),(3,3))' <<| box '((3,4),(5,5))'</code></td></tr><tr><td> <code class="literal">|>></code> </td><td>Is strictly above?</td><td><code class="literal">box '((3,4),(5,5))' |>> box '((0,0),(3,3))'</code></td></tr><tr><td> <code class="literal">&<|</code> </td><td>Does not extend above?</td><td><code class="literal">box '((0,0),(1,1))' &<| box '((0,0),(2,2))'</code></td></tr><tr><td> <code class="literal">|&></code> </td><td>Does not extend below?</td><td><code class="literal">box '((0,0),(3,3))' |&> box '((0,0),(2,2))'</code></td></tr><tr><td> <code class="literal"><^</code> </td><td>Is below (allows touching)?</td><td><code class="literal">circle '((0,0),1)' <^ circle '((0,5),1)'</code></td></tr><tr><td> <code class="literal">>^</code> </td><td>Is above (allows touching)?</td><td><code class="literal">circle '((0,5),1)' >^ circle '((0,0),1)'</code></td></tr><tr><td> <code class="literal">?#</code> </td><td>Intersects?</td><td><code class="literal">lseg '((-1,0),(1,0))' ?# box '((-2,-2),(2,2))'</code></td></tr><tr><td> <code class="literal">?-</code> </td><td>Is horizontal?</td><td><code class="literal">?- lseg '((-1,0),(1,0))'</code></td></tr><tr><td> <code class="literal">?-</code> </td><td>Are horizontally aligned?</td><td><code class="literal">point '(1,0)' ?- point '(0,0)'</code></td></tr><tr><td> <code class="literal">?|</code> </td><td>Is vertical?</td><td><code class="literal">?| lseg '((-1,0),(1,0))'</code></td></tr><tr><td> <code class="literal">?|</code> </td><td>Are vertically aligned?</td><td><code class="literal">point '(0,1)' ?| point '(0,0)'</code></td></tr><tr><td> <code class="literal">?-|</code> </td><td>Is perpendicular?</td><td><code class="literal">lseg '((0,0),(0,1))' ?-| lseg '((0,0),(1,0))'</code></td></tr><tr><td> <code class="literal">?||</code> </td><td>Are parallel?</td><td><code class="literal">lseg '((-1,0),(1,0))' ?|| lseg '((-1,2),(1,2))'</code></td></tr><tr><td> <code class="literal">@></code> </td><td>Contains?</td><td><code class="literal">circle '((0,0),2)' @> point '(1,1)'</code></td></tr><tr><td> <code class="literal"><@</code> </td><td>Contained in or on?</td><td><code class="literal">point '(1,1)' <@ circle '((0,0),2)'</code></td></tr><tr><td> <code class="literal">~=</code> </td><td>Same as?</td><td><code class="literal">polygon '((0,0),(1,1))' ~= polygon '((1,1),(0,0))'</code></td></tr></tbody></table></div></div><br class="table-break" /><div class="note"><h3 class="title">Note</h3><p>
Before <span class="productname">PostgreSQL</span> 8.2, the containment
operators <code class="literal">@></code> and <code class="literal"><@</code> were respectively
called <code class="literal">~</code> and <code class="literal">@</code>. These names are still
available, but are deprecated and will eventually be removed.
</p></div><a id="id-1.5.8.16.6" class="indexterm"></a><a id="id-1.5.8.16.7" class="indexterm"></a><a id="id-1.5.8.16.8" class="indexterm"></a><a id="id-1.5.8.16.9" class="indexterm"></a><a id="id-1.5.8.16.10" class="indexterm"></a><a id="id-1.5.8.16.11" class="indexterm"></a><a id="id-1.5.8.16.12" class="indexterm"></a><a id="id-1.5.8.16.13" class="indexterm"></a><a id="id-1.5.8.16.14" class="indexterm"></a><a id="id-1.5.8.16.15" class="indexterm"></a><a id="id-1.5.8.16.16" class="indexterm"></a><a id="id-1.5.8.16.17" class="indexterm"></a><div class="table" id="FUNCTIONS-GEOMETRY-FUNC-TABLE"><p class="title"><strong>Table 9.34. Geometric Functions</strong></p><div class="table-contents"><table class="table" summary="Geometric Functions" border="1"><colgroup><col /><col /><col /><col /></colgroup><thead><tr><th>Function</th><th>Return Type</th><th>Description</th><th>Example</th></tr></thead><tbody><tr><td><code class="literal"><code class="function">area(<em class="replaceable"><code>object</code></em>)</code></code></td><td><code class="type">double precision</code></td><td>area</td><td><code class="literal">area(box '((0,0),(1,1))')</code></td></tr><tr><td><code class="literal"><code class="function">center(<em class="replaceable"><code>object</code></em>)</code></code></td><td><code class="type">point</code></td><td>center</td><td><code class="literal">center(box '((0,0),(1,2))')</code></td></tr><tr><td><code class="literal"><code class="function">diameter(<code class="type">circle</code>)</code></code></td><td><code class="type">double precision</code></td><td>diameter of circle</td><td><code class="literal">diameter(circle '((0,0),2.0)')</code></td></tr><tr><td><code class="literal"><code class="function">height(<code class="type">box</code>)</code></code></td><td><code class="type">double precision</code></td><td>vertical size of box</td><td><code class="literal">height(box '((0,0),(1,1))')</code></td></tr><tr><td><code class="literal"><code class="function">isclosed(<code class="type">path</code>)</code></code></td><td><code class="type">boolean</code></td><td>a closed path?</td><td><code class="literal">isclosed(path '((0,0),(1,1),(2,0))')</code></td></tr><tr><td><code class="literal"><code class="function">isopen(<code class="type">path</code>)</code></code></td><td><code class="type">boolean</code></td><td>an open path?</td><td><code class="literal">isopen(path '[(0,0),(1,1),(2,0)]')</code></td></tr><tr><td><code class="literal"><code class="function">length(<em class="replaceable"><code>object</code></em>)</code></code></td><td><code class="type">double precision</code></td><td>length</td><td><code class="literal">length(path '((-1,0),(1,0))')</code></td></tr><tr><td><code class="literal"><code class="function">npoints(<code class="type">path</code>)</code></code></td><td><code class="type">int</code></td><td>number of points</td><td><code class="literal">npoints(path '[(0,0),(1,1),(2,0)]')</code></td></tr><tr><td><code class="literal"><code class="function">npoints(<code class="type">polygon</code>)</code></code></td><td><code class="type">int</code></td><td>number of points</td><td><code class="literal">npoints(polygon '((1,1),(0,0))')</code></td></tr><tr><td><code class="literal"><code class="function">pclose(<code class="type">path</code>)</code></code></td><td><code class="type">path</code></td><td>convert path to closed</td><td><code class="literal">pclose(path '[(0,0),(1,1),(2,0)]')</code></td></tr><tr><td><code class="literal"><code class="function">popen(<code class="type">path</code>)</code></code></td><td><code class="type">path</code></td><td>convert path to open</td><td><code class="literal">popen(path '((0,0),(1,1),(2,0))')</code></td></tr><tr><td><code class="literal"><code class="function">radius(<code class="type">circle</code>)</code></code></td><td><code class="type">double precision</code></td><td>radius of circle</td><td><code class="literal">radius(circle '((0,0),2.0)')</code></td></tr><tr><td><code class="literal"><code class="function">width(<code class="type">box</code>)</code></code></td><td><code class="type">double precision</code></td><td>horizontal size of box</td><td><code class="literal">width(box '((0,0),(1,1))')</code></td></tr></tbody></table></div></div><br class="table-break" /><div class="table" id="FUNCTIONS-GEOMETRY-CONV-TABLE"><p class="title"><strong>Table 9.35. Geometric Type Conversion Functions</strong></p><div class="table-contents"><table class="table" summary="Geometric Type Conversion Functions" border="1"><colgroup><col /><col /><col /><col /></colgroup><thead><tr><th>Function</th><th>Return Type</th><th>Description</th><th>Example</th></tr></thead><tbody><tr><td>
<a id="id-1.5.8.16.19.2.2.1.1.1" class="indexterm"></a>
<code class="literal"><code class="function">box(<code class="type">circle</code>)</code></code>
</td><td><code class="type">box</code></td><td>circle to box</td><td><code class="literal">box(circle '((0,0),2.0)')</code></td></tr><tr><td><code class="literal"><code class="function">box(<code class="type">point</code>)</code></code></td><td><code class="type">box</code></td><td>point to empty box</td><td><code class="literal">box(point '(0,0)')</code></td></tr><tr><td><code class="literal"><code class="function">box(<code class="type">point</code>, <code class="type">point</code>)</code></code></td><td><code class="type">box</code></td><td>points to box</td><td><code class="literal">box(point '(0,0)', point '(1,1)')</code></td></tr><tr><td><code class="literal"><code class="function">box(<code class="type">polygon</code>)</code></code></td><td><code class="type">box</code></td><td>polygon to box</td><td><code class="literal">box(polygon '((0,0),(1,1),(2,0))')</code></td></tr><tr><td><code class="literal"><code class="function">bound_box(<code class="type">box</code>, <code class="type">box</code>)</code></code></td><td><code class="type">box</code></td><td>boxes to bounding box</td><td><code class="literal">bound_box(box '((0,0),(1,1))', box '((3,3),(4,4))')</code></td></tr><tr><td>
<a id="id-1.5.8.16.19.2.2.6.1.1" class="indexterm"></a>
<code class="literal"><code class="function">circle(<code class="type">box</code>)</code></code>
</td><td><code class="type">circle</code></td><td>box to circle</td><td><code class="literal">circle(box '((0,0),(1,1))')</code></td></tr><tr><td><code class="literal"><code class="function">circle(<code class="type">point</code>, <code class="type">double precision</code>)</code></code></td><td><code class="type">circle</code></td><td>center and radius to circle</td><td><code class="literal">circle(point '(0,0)', 2.0)</code></td></tr><tr><td><code class="literal"><code class="function">circle(<code class="type">polygon</code>)</code></code></td><td><code class="type">circle</code></td><td>polygon to circle</td><td><code class="literal">circle(polygon '((0,0),(1,1),(2,0))')</code></td></tr><tr><td><code class="literal"><code class="function">line(<code class="type">point</code>, <code class="type">point</code>)</code></code></td><td><code class="type">line</code></td><td>points to line</td><td><code class="literal">line(point '(-1,0)', point '(1,0)')</code></td></tr><tr><td>
<a id="id-1.5.8.16.19.2.2.10.1.1" class="indexterm"></a>
<code class="literal"><code class="function">lseg(<code class="type">box</code>)</code></code>
</td><td><code class="type">lseg</code></td><td>box diagonal to line segment</td><td><code class="literal">lseg(box '((-1,0),(1,0))')</code></td></tr><tr><td><code class="literal"><code class="function">lseg(<code class="type">point</code>, <code class="type">point</code>)</code></code></td><td><code class="type">lseg</code></td><td>points to line segment</td><td><code class="literal">lseg(point '(-1,0)', point '(1,0)')</code></td></tr><tr><td>
<a id="id-1.5.8.16.19.2.2.12.1.1" class="indexterm"></a>
<code class="literal"><code class="function">path(<code class="type">polygon</code>)</code></code>
</td><td><code class="type">path</code></td><td>polygon to path</td><td><code class="literal">path(polygon '((0,0),(1,1),(2,0))')</code></td></tr><tr><td>
<a id="id-1.5.8.16.19.2.2.13.1.1" class="indexterm"></a>
<code class="literal"><code class="function">point</code>(<code class="type">double
precision</code>, <code class="type">double precision</code>)</code>
</td><td><code class="type">point</code></td><td>construct point</td><td><code class="literal">point(23.4, -44.5)</code></td></tr><tr><td><code class="literal"><code class="function">point(<code class="type">box</code>)</code></code></td><td><code class="type">point</code></td><td>center of box</td><td><code class="literal">point(box '((-1,0),(1,0))')</code></td></tr><tr><td><code class="literal"><code class="function">point(<code class="type">circle</code>)</code></code></td><td><code class="type">point</code></td><td>center of circle</td><td><code class="literal">point(circle '((0,0),2.0)')</code></td></tr><tr><td><code class="literal"><code class="function">point(<code class="type">lseg</code>)</code></code></td><td><code class="type">point</code></td><td>center of line segment</td><td><code class="literal">point(lseg '((-1,0),(1,0))')</code></td></tr><tr><td><code class="literal"><code class="function">point(<code class="type">polygon</code>)</code></code></td><td><code class="type">point</code></td><td>center of polygon</td><td><code class="literal">point(polygon '((0,0),(1,1),(2,0))')</code></td></tr><tr><td>
<a id="id-1.5.8.16.19.2.2.18.1.1" class="indexterm"></a>
<code class="literal"><code class="function">polygon(<code class="type">box</code>)</code></code>
</td><td><code class="type">polygon</code></td><td>box to 4-point polygon</td><td><code class="literal">polygon(box '((0,0),(1,1))')</code></td></tr><tr><td><code class="literal"><code class="function">polygon(<code class="type">circle</code>)</code></code></td><td><code class="type">polygon</code></td><td>circle to 12-point polygon</td><td><code class="literal">polygon(circle '((0,0),2.0)')</code></td></tr><tr><td><code class="literal"><code class="function">polygon(<em class="replaceable"><code>npts</code></em>, <code class="type">circle</code>)</code></code></td><td><code class="type">polygon</code></td><td>circle to <em class="replaceable"><code>npts</code></em>-point polygon</td><td><code class="literal">polygon(12, circle '((0,0),2.0)')</code></td></tr><tr><td><code class="literal"><code class="function">polygon(<code class="type">path</code>)</code></code></td><td><code class="type">polygon</code></td><td>path to polygon</td><td><code class="literal">polygon(path '((0,0),(1,1),(2,0))')</code></td></tr></tbody></table></div></div><br class="table-break" /><p>
It is possible to access the two component numbers of a <code class="type">point</code>
as though the point were an array with indexes 0 and 1. For example, if
<code class="literal">t.p</code> is a <code class="type">point</code> column then
<code class="literal">SELECT p[0] FROM t</code> retrieves the X coordinate and
<code class="literal">UPDATE t SET p[1] = ...</code> changes the Y coordinate.
In the same way, a value of type <code class="type">box</code> or <code class="type">lseg</code> can be treated
as an array of two <code class="type">point</code> values.
</p><p>
The <code class="function">area</code> function works for the types
<code class="type">box</code>, <code class="type">circle</code>, and <code class="type">path</code>.
The <code class="function">area</code> function only works on the
<code class="type">path</code> data type if the points in the
<code class="type">path</code> are non-intersecting. For example, the
<code class="type">path</code>
<code class="literal">'((0,0),(0,1),(2,1),(2,2),(1,2),(1,0),(0,0))'::PATH</code>
will not work; however, the following visually identical
<code class="type">path</code>
<code class="literal">'((0,0),(0,1),(1,1),(1,2),(2,2),(2,1),(1,1),(1,0),(0,0))'::PATH</code>
will work. If the concept of an intersecting versus
non-intersecting <code class="type">path</code> is confusing, draw both of the
above <code class="type">path</code>s side by side on a piece of graph paper.
</p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="functions-enum.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="functions.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="functions-net.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">9.10. Enum Support Functions </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> 9.12. Network Address Functions and Operators</td></tr></table></div></body></html>
