9.11. 地理資訊函式及運算子
The geometric typespoint,box,lseg,line,path,polygon, andcirclehave a large set of native support functions and operators, shown inTable 9.33,Table 9.34, andTable 9.35.
Note that the“same as”operator,~=, represents the usual notion of equality for thepoint,box,polygon, andcircletypes. 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 |
| | | | |
| Translation |
| | | | |
| Scaling/rotation |
| | | | |
| Scaling/rotation |
| | | | |
| Point or box of intersection |
| | | | |
| Number of points in path or polygon |
| | | | |
Length or circumference |
| | | | | |
| Center |
| | | | |
| Closest point to first operand on second operand |
| | | | |
| Distance between |
| | | | |
| Overlaps? (One point in common makes this true.) |
| | | | |
| Is strictly left of? |
| | | | |
| Is strictly right of? |
| | | | |
| Does not extend to the right of? |
| | | | |
| Does not extend to the left of? |
| | | | |
`<< | ` | 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)? |
| | | | |
| Is above (allows touching)? |
| | | | |
| Intersects? |
| | | | |
| Is horizontal? |
| | | | |
| Are horizontally aligned? |
| | | | |
`? | ` | 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? |
| | | | |
| Contained in or on? |
| | | | |
| Same as? |
| | | | |
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 |
|
|
| center |
|
|
| diameter of circle |
|
|
| vertical size of box |
|
|
| a closed path? |
|
|
| an open path? |
|
|
| length |
|
|
| number of points |
|
|
| number of points |
|
|
| convert path to closed |
|
|
| convert path to open |
|
|
| radius of circle |
|
|
| horizontal size of box |
|
Table 9.35. Geometric Type Conversion Functions
Function | Return Type | Description | Example |
|
| circle to box |
|
|
| point to empty box |
|
|
| points to box |
|
|
| polygon to box |
|
|
| boxes to bounding box |
|
|
| box to circle |
|
|
| center and radius to circle |
|
|
| polygon to circle |
|
|
| points to line |
|
|
| box diagonal to line segment |
|
|
| points to line segment |
|
|
| polygon to path |
|
|
| construct point |
|
|
| center of box |
|
|
| center of circle |
|
|
| center of line segment |
|
|
| center of polygon |
|
|
| box to 4-point polygon |
|
|
| circle to 12-point polygon |
|
|
| circle to |
|
|
| path to polygon |
|
It is possible to access the two component numbers of apointas though the point were an array with indexes 0 and 1. For example, ift.pis apointcolumn thenSELECT p[0] FROM tretrieves the X coordinate andUPDATE t SET p[1] = ...changes the Y coordinate. In the same way, a value of typeboxorlsegcan be treated as an array of twopointvalues.
Theareafunction works for the typesbox,circle, andpath. Theareafunction only works on thepathdata type if the points in thepathare non-intersecting. For example, thepath'((0,0),(0,1),(2,1),(2,2),(1,2),(1,0),(0,0))'::PATHwill 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))'::PATHwill work. If the concept of an intersecting versus non-intersectingpathis confusing, draw both of the abovepaths side by side on a piece of graph paper.