12th September 2019: PostgreSQL 12 Beta 4 Released!
Supported Versions: Current (11) / 10 / 9.6 / 9.5 / 9.4
Development Versions: 12 / devel
Unsupported versions: 9.3 / 9.2 / 9.1 / 9.0 / 8.4 / 8.3 / 8.2 / 8.1 / 8.0 / 7.4 / 7.3 / 7.2 / 7.1
This documentation is for an unsupported version of PostgreSQL.
You may want to view the same page for the current version, or one of the supported versions listed above instead.

9.9. 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 9-28, Table 9-29, and Table 9-30.

Table 9-28. Geometric Operators

Operator Description Example
+ Translation box '((0,0),(1,1))' + point '(2.0,0)'
- Translation box '((0,0),(1,1))' - point '(2.0,0)'
* Scaling/rotation box '((0,0),(1,1))' * point '(2.0,0)'
/ Scaling/rotation box '((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 operand point '(0,0)' ## lseg '((2,0),(0,2))'
<-> Distance between circle '((0,0),1)' <-> circle '((5,0),1)'
&& Overlaps? box '((0,0),(1,1))' && box '((0,0),(2,2))'
&< Overlaps or is left of? box '((0,0),(1,1))' &< box '((0,0),(2,2))'
&> Overlaps or is right of? box '((0,0),(3,3))' &> box '((0,0),(2,2))'
<< Is left of? circle '((0,0),1)' << circle '((5,0),1)'
>> Is right of? circle '((5,0),1)' >> circle '((0,0),1)'
<^ Is below? circle '((0,0),1)' <^ circle '((0,5),1)'
>^ Is above? 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))'

Table 9-29. Geometric Functions

Function Return Type Description Example
area(object) double precision area area(box '((0,0),(1,1))')
box_intersect(box, box) box intersection box box_intersect(box '((0,0),(1,1))',box '((0.5,0.5),(2,2))')
center(object) point center center(box '((0,0),(1,2))')
diameter(circle) double precision diameter of circle diameter(circle '((0,0),2.0)')
height(box) double precision vertical size of box height(box '((0,0),(1,1))')
isclosed(path) boolean a closed path? isclosed(path '((0,0),(1,1),(2,0))')
isopen(path) boolean an open path? isopen(path '[(0,0),(1,1),(2,0)]')
length(object) double precision length length(path '((-1,0),(1,0))')
npoints(path) integer number of points npoints(path '[(0,0),(1,1),(2,0)]')
npoints(polygon) integer number of points npoints(polygon '((1,1),(0,0))')
pclose(path) path convert path to closed popen(path '[(0,0),(1,1),(2,0)]')
popen(path) path convert path to open popen(path '((0,0),(1,1),(2,0))')
radius(circle) double precision radius of circle radius(circle '((0,0),2.0)')
width(box) double precision horizontal size of box width(box '((0,0),(1,1))')

Table 9-30. Geometric Type Conversion Functions

Function Return Type Description Example
box(circle) box circle to box box(circle '((0,0),2.0)')
box(point, point) box points to box box(point '(0,0)', point '(1,1)')
box(polygon) box polygon to box box(polygon '((0,0),(1,1),(2,0))')
circle(box) circle box to circle circle(box '((0,0),(1,1))')
circle(point, double precision) circle point and radius to circle circle(point '(0,0)', 2.0)
lseg(box) lseg box diagonal to line segment lseg(box '((-1,0),(1,0))')
lseg(point, point) lseg points to line segment lseg(point '(-1,0)', point '(1,0)')
path(polygon) point polygon to path path(polygon '((0,0),(1,1),(2,0))')
point(circle) point center of circle point(circle '((0,0),2.0)')
point(lseg, lseg) point intersection point(lseg '((-1,0),(1,0))', lseg '((-2,-2),(2,2))')
point(polygon) point center of polygon point(polygon '((0,0),(1,1),(2,0))')
polygon(box) polygon box to 4-point polygon polygon(box '((0,0),(1,1))')
polygon(circle) polygon circle to 12-point polygon polygon(circle '((0,0),2.0)')
polygon(npts, circle) polygon circle to npts-point polygon polygon(12, circle '((0,0),2.0)')
polygon(path) polygon path to polygon 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.