- publishing free software manuals
PostgreSQL Reference Manual - Volume 1 - SQL Language Reference
by The PostgreSQL Global Development Group
Paperback (6"x9"), 716 pages
ISBN 0954612027
RRP £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 paths side by side on a piece of graph paper.

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