September 26, 2024: PostgreSQL 17 Released!
Supported Versions: Current (17) / 16 / 15 / 14 / 13 / 12
Development Versions: devel
Unsupported versions: 11 / 10 / 9.6 / 9.5 / 9.4 / 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 other supported versions listed above instead.

6.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 6-20, Table 6-21, and Table 6-22.

Table 6-20. Geometric Operators

Operator Description Usage
+ 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)'
# Intersection '((1,-1),(-1,1))' # '((1,1),(-1,-1))'
# Number of points in path or polygon # '((1,0),(0,1),(-1,0))'
## Point of closest proximity point '(0,0)' ## lseg '((2,0),(0,2))'
&& Overlaps? box '((0,0),(1,1))' && box '((0,0),(2,2))'
&< Overlaps to left? box '((0,0),(1,1))' &< box '((0,0),(2,2))'
&> Overlaps to right? box '((0,0),(3,3))' &> box '((0,0),(2,2))'
<-> Distance between circle '((0,0),1)' <-> circle '((5,0),1)'
<< Left of? circle '((0,0),1)' << circle '((5,0),1)'
<^ Is below? circle '((0,0),1)' <^ circle '((0,5),1)'
>> Is right of? circle '((5,0),1)' >> circle '((0,0),1)'
>^ Is above? circle '((0,5),1)' >^ circle '((0,0),1)'
?# Intersects or overlaps lseg '((-1,0),(1,0))' ?# box '((-2,-2),(2,2))'
?- Is horizontal? point '(1,0)' ?- point '(0,0)'
?-| Is perpendicular? lseg '((0,0),(0,1))' ?-| lseg '((0,0),(1,0))'
@-@ Length or circumference @-@ path '((0,0),(1,0))'
?| Is vertical? point '(0,1)' ?| point '(0,0)'
?|| Is parallel? lseg '((-1,0),(1,0))' ?|| lseg '((-1,2),(1,2))'
@ Contained or on point '(1,1)' @ circle '((0,0),2)'
@@ Center of @@ circle '((0,0),10)'
~= Same as polygon '((0,0),(1,1))' ~= polygon '((1,1),(0,0))'

Table 6-21. Geometric Functions

Function Returns Description Example
area(object) double precision area of item area(box '((0,0),(1,1))')
box(box, box) box intersection box box(box '((0,0),(1,1))',box '((0.5,0.5),(2,2))')
center(object) point center of item 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 of item 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 path 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 width(box '((0,0),(1,1))')

Table 6-22. Geometric Type Conversion Functions

Function Returns 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 to circle circle(box '((0,0),(1,1))')
circle(point, double precision) circle point to circle circle(point '(0,0)', 2.0)
lseg(box) lseg box diagonal to lseg lseg(box '((-1,0),(1,0))')
lseg(point, point) lseg points to lseg 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 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 point(polygon '((0,0),(1,1),(2,0))')
polygon(box) polygon 4-point polygon polygon(box '((0,0),(1,1))')
polygon(circle) polygon 12-point polygon polygon(circle '((0,0),2.0)')
polygon(npts, circle) polygon npts 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 subscripts 0, 1. For example, if t.p is a point column then SELECT p[0] FROM t retrieves the X coordinate; UPDATE t SET p[1] = ... changes the Y coordinate. In the same way, a box or an lseg may be treated as an array of two points.