9.11. Geometric Functions and Operators
The geometric typespoint,box,lseg,line,path,polygon, andcircle have a large set of native support functions and operators, shown inTable 9.31,Table 9.32, andTable 9.33.
Caution
Note that the“same as” operator,~=, represents the usual notion of equality for thepoint,box,polygon, andcircle types. Some of these types also have an= operator, but= compares for equalareas only. The other scalar comparison operators (<= and so on) likewise compare areas for these types.
Table 9.31. 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))' |
Note
BeforePostgreSQL 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 |
|---|---|---|---|
| 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.33. Geometric Type Conversion Functions
It is possible to access the two component numbers of apoint as though the point were an array with indexes 0 and 1. For example, ift.p is apoint column thenSELECT p[0] FROM t retrieves the X coordinate andUPDATE t SET p[1] = ... changes the Y coordinate. In the same way, a value of typebox orlseg can be treated as an array of twopoint values.
Thearea function works for the typesbox,circle, andpath. Thearea function only works on thepath data type if the points in thepath are non-intersecting. For example, thepath'((0,0),(0,1),(2,1),(2,2),(1,2),(1,0),(0,0))'::PATH will 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))'::PATH will work. If the concept of an intersecting versus non-intersectingpath is confusing, draw both of the abovepaths side by side on a piece of graph paper.