The geometric typespoint,box,lseg,line,path,polygon, andcirclehave a large set of native support functions and operators, shown inTable 9.33,Table 9.34, andTable 9.35.
Note that the“same as”operator,~=, represents the usual notion of equality for thepoint,box,polygon, andcircletypes. 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.33. 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
box '((1,-1),(-1,1))' # box '((1,1),(-2,-2))'
#
Number of points in path or polygon
# path '((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? (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))'
BeforePostgreSQL8.2, the containment operators@>and<@were respectively called~and@. These names are still available, but are deprecated and will eventually be removed.
Table 9.34. 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.35. Geometric Type Conversion Functions
Function
Return Type
Description
Example
box(circle)
box
circle to box
box(circle '((0,0),2.0)')
box(point)
box
point to empty box
box(point '(0,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))')
bound_box(box,box)
box
boxes to bounding box
bound_box(box '((0,0),(1,1))', box '((3,3),(4,4))')
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))')
line(point,point)
line
points to line
line(point '(-1,0)', point '(1,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 tonpts-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 apointas though the point were an array with indexes 0 and 1. For example, ift.pis apointcolumn thenSELECT p[0] FROM tretrieves the X coordinate andUPDATE t SET p[1] = ...changes the Y coordinate. In the same way, a value of typeboxorlsegcan be treated as an array of twopointvalues.
Theareafunction works for the typesbox,circle, andpath. Theareafunction only works on thepathdata type if the points in thepathare non-intersecting. For example, thepath'((0,0),(0,1),(2,1),(2,2),(1,2),(1,0),(0,0))'::PATHwill not work; however, the following visually identicalpath'((0,0),(0,1),(1,1),(1,2),(2,2),(2,1),(1,1),(1,0),(0,0))'::PATHwill work. If the concept of an intersecting versus non-intersectingpathis confusing, draw both of the abovepaths side by side on a piece of graph paper.