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-31, Table 9-32, and Table 9-33.
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.
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-32. Geometric Functions
Function
Return Type
Description
Example
area(object)
double precision
area
area(box '((0,0),(1,1))')
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)
int
number of points
npoints(path '[(0,0),(1,1),(2,0)]')
npoints(polygon)
int
number of points
npoints(polygon '((1,1),(0,0))')
pclose(path)
path
convert path to closed
pclose(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-33. 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
center and radius to circle
circle(point '(0,0)', 2.0)
circle(polygon)
circle
polygon to circle
circle(polygon '((0,0),(1,1),(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)
path
polygon to path
path(polygon '((0,0),(1,1),(2,0))')
point(double
precision, double precision)
point
construct point
point(23.4, -44.5)
point(box)
point
center of box
point(box '((-1,0),(1,0))')
point(circle)
point
center of circle
point(circle '((0,0),2.0)')
point(lseg)
point
center of line segment
point(lseg '((-1,0),(1,0))')
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 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.