PostgreSQL 9.4.4 Documentation | |||
---|---|---|---|

Prev | Up | Chapter 9. Functions and Operators | Next |

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-31, Table
9-32, and Table
9-33.

Caution |

Note that the "same as"
operator, |

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 | '((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? (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: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-32. Geometric Functions

Function | Return Type | Description | Example |
---|---|---|---|

`area(` |
double precision |
area | area(box
'((0,0),(1,1))') |

`center(` |
point |
center | center(box
'((0,0),(1,2))') |

`diameter(` |
double precision |
diameter of circle | diameter(circle
'((0,0),2.0)') |

`height(` |
double precision |
vertical size of box | height(box
'((0,0),(1,1))') |

`isclosed(` |
boolean |
a closed path? | isclosed(path
'((0,0),(1,1),(2,0))') |

`isopen(` |
boolean |
an open path? | isopen(path
'[(0,0),(1,1),(2,0)]') |

`length(` |
double precision |
length | length(path
'((-1,0),(1,0))') |

`npoints(` |
int |
number of points | npoints(path
'[(0,0),(1,1),(2,0)]') |

`npoints(` |
int |
number of points | npoints(polygon
'((1,1),(0,0))') |

`pclose(` |
path |
convert path to closed | pclose(path
'[(0,0),(1,1),(2,0)]') |

`popen(` |
path |
convert path to open | popen(path
'((0,0),(1,1),(2,0))') |

`radius(` |
double precision |
radius of circle | radius(circle
'((0,0),2.0)') |

`width(` |
double precision |
horizontal size of box | width(box
'((0,0),(1,1))') |

Table 9-33. Geometric Type Conversion Functions

Function | Return Type | Description | Example |
---|---|---|---|

`box(` |
box |
circle to box | box(circle
'((0,0),2.0)') |

`box(` |
box |
points to box | box(point '(0,0)', point
'(1,1)') |

`box(` |
box |
polygon to box | box(polygon
'((0,0),(1,1),(2,0))') |

`circle(` |
circle |
box to circle | circle(box
'((0,0),(1,1))') |

`circle(` |
circle |
center and radius to circle | circle(point '(0,0)',
2.0) |

`circle(` |
circle |
polygon to circle | circle(polygon
'((0,0),(1,1),(2,0))') |

`line(` |
line |
points to line | line(point '(-1,0)', point
'(1,0)') |

`lseg(` |
lseg |
box diagonal to line segment | lseg(box
'((-1,0),(1,0))') |

`lseg(` |
lseg |
points to line segment | lseg(point '(-1,0)', point
'(1,0)') |

`path(` |
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(` |
point |
center of box | point(box
'((-1,0),(1,0))') |

`point(` |
point |
center of circle | point(circle
'((0,0),2.0)') |

`point(` |
point |
center of line segment | point(lseg
'((-1,0),(1,0))') |

`point(` |
point |
center of polygon | point(polygon
'((0,0),(1,1),(2,0))') |

`polygon(` |
polygon |
box to 4-point polygon | polygon(box
'((0,0),(1,1))') |

`polygon(` |
polygon |
circle to 12-point polygon | polygon(circle
'((0,0),2.0)') |

`polygon(` |
polygon |
circle to npts-point
polygon |
polygon(12, circle
'((0,0),2.0)') |

`polygon(` |
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 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.

Please use this form to add your own comments regarding your experience with
particular features of PostgreSQL, clarifications of the documentation, or
hints for other users. Please note, this is **not** a support
forum, and your IP address will be logged. If you have a question or need help,
please see the faq, try a
mailing list, or join
us on IRC.
Note that submissions containing URLs or other keywords commonly found in
'spam' comments may be silently discarded. Please contact the
webmaster if you think this
is happening to you in error.

Proceed to the comment form.