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.34, Table 9.35, and Table 9.36.
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.34. 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))' |
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.35. 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.36. Geometric Type Conversion Functions
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 path
s side by side on a piece of graph paper.