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, “Geometric Operators”, Table 9.29, “Geometric Functions”, and Table 9.30, “Geometric Type Conversion Functions”.
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))' |
@-@ |
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))' |
<< |
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))' |
Before PostgreSQL 8.2, the containment
operators @>
and <@
were respectively
called ~
and @
. These names are still
available, but are deprecated and will eventually be retired.
Table 9.29. Geometric Functions
Function | Return Type | Description | Example |
---|---|---|---|
|
double precision |
area | area(box '((0,0),(1,1))') |
|
point |
center | center(box '((0,0),(1,2))') |
|
double precision |
diameter of circle | diameter(circle '((0,0),2.0)') |
|
double precision |
vertical size of box | height(box '((0,0),(1,1))') |
|
boolean |
a closed path? | isclosed(path '((0,0),(1,1),(2,0))') |
|
boolean |
an open path? | isopen(path '[(0,0),(1,1),(2,0)]') |
|
double precision |
length | length(path '((-1,0),(1,0))') |
|
int |
number of points | npoints(path '[(0,0),(1,1),(2,0)]') |
|
int |
number of points | npoints(polygon '((1,1),(0,0))') |
|
path |
convert path to closed | pclose(path '[(0,0),(1,1),(2,0)]') |
|
path |
convert path to open | popen(path '((0,0),(1,1),(2,0))') |
|
double precision |
radius of circle | radius(circle '((0,0),2.0)') |
|
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 to box | box(circle '((0,0),2.0)') |
|
box |
points to box | box(point '(0,0)', point '(1,1)') |
|
box |
polygon to box | box(polygon '((0,0),(1,1),(2,0))') |
|
circle |
box to circle | circle(box '((0,0),(1,1))') |
|
circle |
center and radius to circle | circle(point '(0,0)', 2.0) |
|
circle |
polygon to circle | circle(polygon '((0,0),(1,1),(2,0))') |
|
lseg |
box diagonal to line segment | lseg(box '((-1,0),(1,0))') |
|
lseg |
points to line segment | lseg(point '(-1,0)', point '(1,0)') |
|
point |
polygon to path | path(polygon '((0,0),(1,1),(2,0))') |
|
point |
construct point | point(23.4, -44.5) |
|
point |
center of box | point(box '((-1,0),(1,0))') |
|
point |
center of circle | point(circle '((0,0),2.0)') |
|
point |
center of line segment | point(lseg '((-1,0),(1,0))') |
|
point |
center of polygon | point(polygon '((0,0),(1,1),(2,0))') |
|
polygon |
box to 4-point polygon | polygon(box '((0,0),(1,1))') |
|
polygon |
circle to 12-point polygon | polygon(circle '((0,0),2.0)') |
|
polygon |
circle to npts -point polygon |
polygon(12, circle '((0,0),2.0)') |
|
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 path
s side by side on a piece of graph paper.