9.11. Geometric Functions and Operators

Note: The following description applies both to Postgres-XC and PostgreSQL if not described explicitly.

The geometric types point, box, lseg, line, path, polygon, and circle have a large set of native support functions and operators, shown in Table 9-30, Table 9-31, and Table 9-32.

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 9-30. Geometric Operators

OperatorDescriptionExample
+ Translationbox '((0,0),(1,1))' + point '(2.0,0)'
- Translationbox '((0,0),(1,1))' - point '(2.0,0)'
* Scaling/rotationbox '((0,0),(1,1))' * point '(2.0,0)'
/ Scaling/rotationbox '((0,0),(2,2))' / point '(2.0,0)'
# Point or box of intersection'((1,-1),(-1,1))' # '((1,1),(-1,-1))'
# Number of points in path or polygon# '((1,0),(0,1),(-1,0))'
@-@ Length or circumference@-@ path '((0,0),(1,0))'
@@ Center@@ circle '((0,0),10)'
## Closest point to first operand on second operandpoint '(0,0)' ## lseg '((2,0),(0,2))'
<-> Distance betweencircle '((0,0),1)' <-> circle '((5,0),1)'
&& Overlaps? (One point in common makes this true.)box '((0,0),(1,1))' && box '((0,0),(2,2))'
<< Is strictly left of?circle '((0,0),1)' << circle '((5,0),1)'
>> Is strictly right of?circle '((5,0),1)' >> circle '((0,0),1)'
&< Does not extend to the right of?box '((0,0),(1,1))' &< box '((0,0),(2,2))'
&> Does not extend to the left of?box '((0,0),(3,3))' &> box '((0,0),(2,2))'
<<| Is strictly below?box '((0,0),(3,3))' <<| box '((3,4),(5,5))'
|>> Is strictly above?box '((3,4),(5,5))' |>> box '((0,0),(3,3))'
&<| Does not extend above?box '((0,0),(1,1))' &<| box '((0,0),(2,2))'
|&> Does not extend below?box '((0,0),(3,3))' |&> box '((0,0),(2,2))'
<^ Is below (allows touching)?circle '((0,0),1)' <^ circle '((0,5),1)'
>^ Is above (allows touching)?circle '((0,5),1)' >^ circle '((0,0),1)'
?# Intersects?lseg '((-1,0),(1,0))' ?# box '((-2,-2),(2,2))'
?- Is horizontal??- lseg '((-1,0),(1,0))'
?- Are horizontally aligned?point '(1,0)' ?- point '(0,0)'
?| Is vertical??| lseg '((-1,0),(1,0))'
?| Are vertically aligned?point '(0,1)' ?| point '(0,0)'
?-| Is perpendicular?lseg '((0,0),(0,1))' ?-| lseg '((0,0),(1,0))'
?|| Are parallel?lseg '((-1,0),(1,0))' ?|| lseg '((-1,2),(1,2))'
@> Contains?circle '((0,0),2)' @> point '(1,1)'
<@ Contained in or on?point '(1,1)' <@ circle '((0,0),2)'
~= Same as?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 removed.

Table 9-31. Geometric Functions

FunctionReturn TypeDescriptionExample
area(object)double precisionareaarea(box '((0,0),(1,1))')
center(object)pointcentercenter(box '((0,0),(1,2))')
diameter(circle)double precisiondiameter of circlediameter(circle '((0,0),2.0)')
height(box)double precisionvertical size of boxheight(box '((0,0),(1,1))')
isclosed(path)booleana closed path?isclosed(path '((0,0),(1,1),(2,0))')
isopen(path)booleanan open path?isopen(path '[(0,0),(1,1),(2,0)]')
length(object)double precisionlengthlength(path '((-1,0),(1,0))')
npoints(path)intnumber of pointsnpoints(path '[(0,0),(1,1),(2,0)]')
npoints(polygon)intnumber of pointsnpoints(polygon '((1,1),(0,0))')
pclose(path)pathconvert path to closedpclose(path '[(0,0),(1,1),(2,0)]')
popen(path)pathconvert path to openpopen(path '((0,0),(1,1),(2,0))')
radius(circle)double precisionradius of circleradius(circle '((0,0),2.0)')
width(box)double precisionhorizontal size of boxwidth(box '((0,0),(1,1))')

Table 9-32. Geometric Type Conversion Functions

FunctionReturn TypeDescriptionExample
box(circle) boxcircle to boxbox(circle '((0,0),2.0)')
box(point, point)boxpoints to boxbox(point '(0,0)', point '(1,1)')
box(polygon)boxpolygon to boxbox(polygon '((0,0),(1,1),(2,0))')
circle(box) circlebox to circlecircle(box '((0,0),(1,1))')
circle(point, double precision)circlecenter and radius to circlecircle(point '(0,0)', 2.0)
circle(polygon)circlepolygon to circlecircle(polygon '((0,0),(1,1),(2,0))')
lseg(box) lsegbox diagonal to line segmentlseg(box '((-1,0),(1,0))')
lseg(point, point)lsegpoints to line segmentlseg(point '(-1,0)', point '(1,0)')
path(polygon) pointpolygon to pathpath(polygon '((0,0),(1,1),(2,0))')
point(double precision, double precision) pointconstruct pointpoint(23.4, -44.5)
point(box)pointcenter of boxpoint(box '((-1,0),(1,0))')
point(circle)pointcenter of circlepoint(circle '((0,0),2.0)')
point(lseg)pointcenter of line segmentpoint(lseg '((-1,0),(1,0))')
point(polygon)pointcenter of polygonpoint(polygon '((0,0),(1,1),(2,0))')
polygon(box) polygonbox to 4-point polygonpolygon(box '((0,0),(1,1))')
polygon(circle)polygoncircle to 12-point polygonpolygon(circle '((0,0),2.0)')
polygon(npts, circle)polygoncircle to npts-point polygonpolygon(12, circle '((0,0),2.0)')
polygon(path)polygonpath to polygonpolygon(path '((0,0),(1,1),(2,0))')

It is possible to access the two component numbers of a point as though the point were an array with indexes 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 can 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 will not 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.