# 9.11. 地理資訊函式及運算子

The geometric types`point`,`box`,`lseg`,`line`,`path`,`polygon`, and`circle`have a large set of native support functions and operators, shown inTable 9.33,Table 9.34, andTable 9.35.

# Caution

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.33. 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))'` ​ ​ ​ ​ `@[email protected]` Length or circumference `@[email protected] 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

BeforePostgreSQL8.2, the containment operators`@>`and`<@`were respectively called`~`and`@`. These names are still available, but are deprecated and will eventually be removed.

Table 9.34. Geometric Functions

 Function Return Type Description Example `area(object`) `double precision` area `area(box '((0,0),(1,1))')` `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`) `int` number of points `npoints(path '[(0,0),(1,1),(2,0)]')` `npoints(polygon`) `int` number of points `npoints(polygon '((1,1),(0,0))')` `pclose(path`) `path` convert path to closed `pclose(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.35. Geometric Type Conversion Functions

 Function Return Type Description Example `box(circle`) `box` circle to box `box(circle '((0,0),2.0)')` `box(point`) `box` point to empty box `box(point '(0,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))')` `bound_box(box`,`box`) `box` boxes to bounding box `bound_box(box '((0,0),(1,1))', box '((3,3),(4,4))')` `circle(box`) `circle` box to circle `circle(box '((0,0),(1,1))')` `circle(point`,`double precision`) `circle` center and radius to circle `circle(point '(0,0)', 2.0)` `circle(polygon`) `circle` polygon to circle `circle(polygon '((0,0),(1,1),(2,0))')` `line(point`,`point`) `line` points to line `line(point '(-1,0)', point '(1,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`) `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(box`) `point` center of box `point(box '((-1,0),(1,0))')` `point(circle`) `point` center of circle `point(circle '((0,0),2.0)')` `point(lseg`) `point` center of line segment `point(lseg '((-1,0),(1,0))')` `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 the point were an array with indexes 0 and 1. For example, if`t.p`is a`point`column then`SELECT p FROM t`retrieves the X coordinate and`UPDATE t SET p = ...`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.

Edit on GitHub
Contents
Caution
Note