- publishing free software manuals
 PostgreSQL Reference Manual - Volume 1 - SQL Language Reference by The PostgreSQL Global Development GroupPaperback (6"x9"), 716 pagesISBN 0954612027RRP £32.00 (\$49.95)Sales of this book support the PostgreSQL project! Get a printed copy>>>

## 7.10 Geometric Functions and Operators

The geometric types `point`, `box`, `lseg`, `line`, `path`, `polygon`, and `circle` have a large set of native support functions and operators, shown in Table 7-28, Table 7-29, and Table 7-30.

Caution: Note that the “same as” operator, `~=`, represents the usual notion of equality for the `point`, `box`, `polygon`, and `circle` types. Some of these types also have an `=` operator, but `=` compares for equal areas only. The other scalar comparison operators (`<=` and so on) likewise compare areas for these types.

Table 7-28: Geometric Operators
 Operator Description `+` Translation e.g. `box '((0,0),(1,1))' + point '(2.0,0)'` `-` Translation e.g. `box '((0,0),(1,1))' - point '(2.0,0)'` `*` Scaling/rotation e.g. `box '((0,0),(1,1))' * point '(2.0,0)'` `/` Scaling/rotation e.g. `box '((0,0),(2,2))' / point '(2.0,0)'` `#` Point or box of intersection e.g. `'((1,-1),(-1,1))' # '((1,1),(-1,-1))'` `#` Number of points in path or polygon e.g. `# '((1,0),(0,1),(-1,0))'` `@-@` Length or circumference e.g. `@-@ path '((0,0),(1,0))'` `@@` Center e.g. `@@ circle '((0,0),10)'` `##` Closest point to first operand on second operand e.g. `point '(0,0)' ## lseg '((2,0),(0,2))'` `<->` Distance between e.g. `circle '((0,0),1)' <-> circle '((5,0),1)'` `&&` Overlaps? e.g. `box '((0,0),(1,1))' && box '((0,0),(2,2))'` `<<` Is strictly left of? e.g. `circle '((0,0),1)' << circle '((5,0),1)'` `>>` Is strictly right of? e.g. `circle '((5,0),1)' >> circle '((0,0),1)'` `&<` Does not extend to the right of? e.g. `box '((0,0),(1,1))' &< box '((0,0),(2,2))'` `&>` Does not extend to the left of? e.g. `box '((0,0),(3,3))' &> box '((0,0),(2,2))'` `<<|` Is strictly below? e.g. `box '((0,0),(3,3))' <<| box '((3,4),(5,5))'` `|>>` Is strictly above? e.g. `box '((3,4),(5,5))' |>> box '((0,0),(3,3))'` `&<|` Does not extend above? e.g. `box '((0,0),(1,1))' &<| box '((0,0),(2,2))'` `|&>` Does not extend below? e.g. `box '((0,0),(3,3))' |&> box '((0,0),(2,2))'` `<^` Is below (allows touching)? e.g. `circle '((0,0),1)' <^ circle '((0,5),1)'` `>^` Is above (allows touching)? e.g. `circle '((0,5),1)' >^ circle '((0,0),1)'` `?#` Intersects? e.g. `lseg '((-1,0),(1,0))' ?# box '((-2,-2),(2,2))'` `?-` Is horizontal? e.g. `?- lseg '((-1,0),(1,0))'` `?-` Are horizontally aligned? e.g. `point '(1,0)' ?- point '(0,0)'` `?|` Is vertical? e.g. `?| lseg '((-1,0),(1,0))'` `?|` Are vertically aligned? e.g. `point '(0,1)' ?| point '(0,0)'` `?-|` Is perpendicular? e.g. `lseg '((0,0),(0,1))' ?-| lseg '((0,0),(1,0))'` `?||` Are parallel? e.g. `lseg '((-1,0),(1,0))' ?|| lseg '((-1,2),(1,2))'` `@>` Contains? e.g. `circle '((0,0),2)' @> point '(1,1)'` `<@` Contained in or on? e.g. `point '(1,1)' <@ circle '((0,0),2)'` `~=` Same as? e.g. `polygon '((0,0),(1,1))' ~= polygon '((1,1),(0,0))'`

Note: 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.

Table 7-29: Geometric Functions
 Function Return Type Description `area(object)` `double precision` area e.g. `area(box '((0,0),(1,1))')` `center(object)` `point` center e.g. `center(box '((0,0),(1,2))')` `diameter(circle)` `double precision` diameter of circle e.g. `diameter(circle '((0,0),2.0)')` `height(box)` `double precision` vertical size of box e.g. `height(box '((0,0),(1,1))')` `isclosed(path)` `boolean` a closed path? e.g. `isclosed(path '((0,0),(1,1),(2,0))')` `isopen(path)` `boolean` an open path? e.g. `isopen(path '[(0,0),(1,1),(2,0)]')` `length(object)` `double precision` length e.g. `length(path '((-1,0),(1,0))')` `npoints(path)` `int` number of points e.g. `npoints(path '[(0,0),(1,1),(2,0)]')` `npoints(polygon)` `int` number of points e.g. `npoints(polygon '((1,1),(0,0))')` `pclose(path)` `path` convert path to closed e.g. `pclose(path '[(0,0),(1,1),(2,0)]')` `popen(path)` `path` convert path to open e.g. `popen(path '((0,0),(1,1),(2,0))')` `radius(circle)` `double precision` radius of circle e.g. `radius(circle '((0,0),2.0)')` `width(box)` `double precision` horizontal size of box e.g. `width(box '((0,0),(1,1))')`
Table 7-30: Geometric Type Conversion Functions
 Function Return Type Description `box(circle)` `box` circle to box e.g. `box(circle '((0,0),2.0)')` `box(point, point)` `box` points to box e.g. `box(point '(0,0)', point '(1,1)')` `box(polygon)` `box` polygon to box e.g. `box(polygon '((0,0),(1,1),(2,0))')` `circle(box)` `circle` box to circle e.g. `circle(box '((0,0),(1,1))')` `circle(point, double precision)` `circle` center and radius to circle e.g. `circle(point '(0,0)', 2.0)` `circle(polygon)` `circle` polygon to circle e.g. `circle(polygon '((0,0),(1,1),(2,0))')` `lseg(box)` `lseg` box diagonal to line segment e.g. `lseg(box '((-1,0),(1,0))')` `lseg(point, point)` `lseg` points to line segment e.g. `lseg(point '(-1,0)', point '(1,0)')` `path(polygon)` `point` polygon to path e.g. `path(polygon '((0,0),(1,1),(2,0))')` ```point(double precision, double precision)``` `point` construct point e.g. `point(23.4, -44.5)` `point(box)` `point` center of box e.g. `point(box '((-1,0),(1,0))')` `point(circle)` `point` center of circle e.g. `point(circle '((0,0),2.0)')` `point(lseg)` `point` center of line segment e.g. `point(lseg '((-1,0),(1,0))')` `point(polygon)` `point` center of polygon e.g. `point(polygon '((0,0),(1,1),(2,0))')` `polygon(box)` `polygon` box to 4-point polygon e.g. `polygon(box '((0,0),(1,1))')` `polygon(circle)` `polygon` circle to 12-point polygon e.g. `polygon(circle '((0,0),2.0)')` `polygon(npts, circle)` `polygon` circle to npts-point polygon e.g. `polygon(12, circle '((0,0),2.0)')` `polygon(path)` `polygon` path to polygon e.g. `polygon(path '((0,0),(1,1),(2,0))')`

It is possible to access the two component numbers of a `point` as though it were an array with indices 0 and 1. For example, if `t.p` is a `point` column then `SELECT p[0] FROM t` retrieves the X coordinate and `UPDATE t SET p[1] = ...` changes the Y coordinate. In the same way, a value of type `box` or `lseg` may be treated as an array of two `point` values.

The `area` function works for the types `box`, `circle`, and `path`. The `area` function only works on the `path` data type if the points in the `path` are non-intersecting. For example, the `path` `'((0,0),(0,1),(2,1),(2,2),(1,2),(1,0),(0,0))'::PATH` won't work, however, the following visually identical `path` `'((0,0),(0,1),(1,1),(1,2),(2,2),(2,1),(1,1),(1,0),(0,0))'::PATH` will work. If the concept of an intersecting versus non-intersecting `path` is confusing, draw both of the above `path`s side by side on a piece of graph paper.

 ISBN 0954612027 PostgreSQL Reference Manual - Volume 1 - SQL Language Reference See the print edition