| PostgreSQL 7.4.16 Documentation | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 9. Functions and Operators | Fast Forward | 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-28, Table 9-29, and Table 9-30.
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))' |
| &< | Overlaps or is left of? | box '((0,0),(1,1))' &< box '((0,0),(2,2))' |
| &> | Overlaps or is right of? | box '((0,0),(3,3))' &> box '((0,0),(2,2))' |
| << | Is left of? | circle '((0,0),1)' << circle '((5,0),1)' |
| >> | Is right of? | circle '((5,0),1)' >> circle '((0,0),1)' |
| <^ | Is below? | circle '((0,0),1)' <^ circle '((0,5),1)' |
| >^ | Is above? | 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))') |
box_intersect(box, box) | box | intersection box | box_intersect(box '((0,0),(1,1))',box '((0.5,0.5),(2,2))') |
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) | integer | number of points | npoints(path '[(0,0),(1,1),(2,0)]') |
npoints(polygon) | integer | number of points | npoints(polygon '((1,1),(0,0))') |
pclose(path) | path | convert path to closed | popen(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 | point and radius to circle | circle(point '(0,0)', 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(circle) | point | center of circle | point(circle '((0,0),2.0)') |
point(lseg, lseg) | point | intersection | point(lseg '((-1,0),(1,0))', lseg '((-2,-2),(2,2))') |
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.
No comments could be found for this page.
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.
In order to submit a comment, you must have a community account.
* denotes required field