Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Mail Systems
Eclipse Documentation

How To Guides
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Problem Solutions
Privacy Policy




9.10. 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.


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-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))'
@[email protected] Length or circumference @[email protected] 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))'
<< 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))'

Table 9-29. 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-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 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) point 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 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.

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 won't 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.

  Published courtesy of The PostgreSQL Global Development Group Design by Interspire