PostgreSQL supports six data types that represent two-dimensional geometric objects. The most basic geometric data type is the POINT?as you might expect, a POINT represents a point within a two-dimensional plane.
A POINT is composed of an x-coordinate and a y-coordinate?each coordinate is a DOUBLE PRECISION number.
The LSEG data type represents a two-dimensional line segment. When you create a LSEG value, you specify two points?the starting POINT and the ending POINT.
A BOX value is used to define a rectangle?the two points that define a box specify opposite corners.
A PATH is a collection of an arbitrary number of POINTs that are connected. A PATH can specify either a closed path or an open path. In a closed path, the beginning and ending points are considered to be connected, and in an open path, the first and last points are not connected. PostgreSQL provides two functions to force a PATH to be either open or closed: POPEN() and PCLOSE(). You can also specify whether a PATH is open or closed using special literal syntax (described later).
A POLYGON is similar to a closed PATH. The difference between the two types is in the supporting functions.
A center POINT and a (DOUBLE PRECISION) floating-point radius represent a CIRCLE.
Table 2.18 summarizes the geometric data types.
Type |
Meaning |
Defined By |
---|---|---|
POINT |
2D point on a plane |
x- and y-coordinates |
LSEG |
Line segment |
Two points |
BOX |
Rectangle |
Two points |
PATH |
Open or closed path |
n points |
POLYGON |
Polygon |
n points |
CIRCLE |
Circle |
Center point and radius |
When you enter a value for geometric data type, keep in mind that you are working with a list of two-dimensional points (except in the case of a CIRCLE, where you are working with a POINT and a radius).
A single POINT can be entered in either of the following two forms:
'( x, y )' ' x, y '
The LSEG and BOX types are constructed from a pair of POINTs. You can enter a pair of POINTs in any of the following formats:
'(( x1, y1 ), ( x2, y2 ))' '( x1, y1 ), ( x2, y2 )' 'x1, y1, x2, y2'
The PATH and POLYGON types are constructed from a list of one or more POINTs. Any of the following forms is acceptable for a PATH or POLYGON literal:
'(( x1, y1 ), ..., ( xn, yn ))' '( x1, y1 ), ..., ( xn, yn )' '( x1, y1, ..., xn, yn )' 'x1, y1, ..., xn, yn'
You can also use the syntax '[( x1, y1 ), ..., ( xn, yn )]' to enter a PATH literal: A PATH entered in this form is considered to be an open PATH.
A CIRCLE is described by a central point and a floating point radius. You can enter a CIRCLE in any of the following forms:
'< ( x, y ), r >' '(( x, y ), r )' '( x, y ), r' 'x, y, r'
Notice that the surrounding single quotes are required around all geometric literals?in other words, geometric literals are entered as string literals. If you want to create a geometric value from individual components, you will have to use a geometric conversion function. For example, if you want to create a POINT value from the results of some computation, you would use:
POINT( 4, 3*height )
The POINT( DOUBLE PRECISION x, DOUBLE PRECISION y ) function creates a POINT value from two DOUBLE PRECISION values. There are similar functions that you can use to create any geometric type starting from individual components. Table 2.19 lists the conversion functions for geometric types.
Result Type |
Meaning |
---|---|
POINT |
POINT( DOUBLE PRECISION x, DOUBLE PRECISION y ) |
LSEG |
LSEG( POINT p1, POINT p2 ) |
BOX |
BOX( POINT p1, POINT p2 ) |
PATH |
PATH( POLYGON poly ) |
POLYGON |
POLYGON( PATH path ) POLYGON( BOX b ) yields a 12-point polygon POLYGON( CIRCLE c ) yields a 12-point polygon POLYGON( INTEGER n, CIRCLE c ) yields an n point polygon |
CIRCLE |
CIRCLE( BOX b ) CIRCLE( POINT radius, DOUBLE PRECISION point ) |
Table 2.20 lists the size of each geometric data type.
Type |
Size (in bytes) |
---|---|
POINT |
16 (2 sizeof DOUBLE PRECISION) |
LSEG |
32 (2 sizeof POINT) |
BOX |
32 (2 sizeof POINT) |
PATH |
4+(32number of points)^{[4]} |
POLYGON |
4+(32number of points)^{[4]} |
CIRCLE |
24 (sizeof POINT + sizeof DOUBLE PRECISION) |
^{[4]} The size of a PATH or POLYGON is equal to 4 + ( sizeof LSEG number of segments).
PostgreSQL features a large collection of operators that work with the geometric data types. I've divided the geometric operators into two broad categories (transformation and proximity) to make it a little easier to talk about them.
Using the transformation operators, you can translate, rotate, and scale geometric objects. The + and - operators translate a geometric object to a new location. Consider Figure 2.1, which shows a BOX defined as BOX( POINT( 3,5 ), POINT( 1,2 )).
If you use the + operator to add the POINT( 2,1 ) to this BOX, you end up with the object shown in Figure 2.2.
You can see that the x-coordinate of the POINT is added to each of the x-coordinates in the BOX, and the y-coordinate of the POINT is added to the y-coordinates in the BOX. The - operator works in a similar fashion: the x-coordinate of the POINT is subtracted from the x-coordinates of the BOX, and the y-coordinate of the POINT is subtracted from each y-coordinate in the BOX.
Using the + and - operators, you can move a POINT, BOX, PATH, or CIRCLE to a new location. In each case, the x-coordinate in the second operand (a POINT), is added or subtracted from each x-coordinate in the first operand, and the y-coordinate in the second operand is added or subtracted from each y-coordinate in the first operand.
The multiplication and division operators ( * and / ) are used to scale and rotate. The multiplication and division operators treat the operands as points in the complex plane. Let's look at some examples.
Figure 2.3 shows the result of multiplying BOX(POINT(3,2),POINT(1,1)) by POINT(2,0).
You can see that each coordinate in the original box is multiplied by the x-coordinate of the point, resulting in BOX(POINT(6,4),POINT(2,2)). If you had multiplied the box by POINT(0.5,0), you would have ended up with BOX(POINT(1.5,1),POINT(0.5,0.5)). So the effect of multiplying an object by POINT(x,0) is that each coordinate in the object moves away from the origin by a factor x. If x is negative, the coordinates move to the other side of the origin, as shown in Figure 2.4.
You can see that the x-coordinate controls scaling. The y-coordinate controls rotation. When you multiply any given geometric object by POINT(0,y), each point in the object is rotated around the origin. When y is equal to 1, each point is rotated counterclockwise by 90° about the origin. When y is equal to ?1, each point is rotated ?90° about the origin (or 270°). When you rotate a point without scaling, the length of the line segment drawn between the point and origin remains constant, as shown in Figure 2.5.
You can combine rotation and scaling into the same operation by specifying non-zero values for both the x- and y-coordinates. For more information on using complex numbers to represent geometric points, see http://www.clarku.edu/~djoyce/complex.
Table 2.21 shows the valid combinations for geometric types and geometric operators.
Data Types |
Valid Operators (q) |
---|---|
POINT q POINT |
* + - / |
BOX q POINT |
* + - / |
PATH q POINT |
* + - / |
CIRCLE q POINT |
* + - / |
The proximity operators allow you to determine the spatial relationships between two geometric objects.
First, let's look at the three containment operators. The ~ operator evaluates to TRUE if the left operand contains the right operand. The @ operator evaluates to TRUE if the left operand is contained within the right operand. The ~= returns TRUE if the left operand is the same as the right operand?two geographic objects are considered identical if the points that define the objects are identical (two circles are considered identical if the radii and center points are the same).
The next two operators are used to determine the distance between two geometric objects.
The ## operator returns the closest point between two objects. You can use the ## operator with the following operand types shown in Table 2.22.
Operator |
Description |
---|---|
LSEG_{a} ## BOX_{b} |
Returns the point in BOX_{b} that is closest to LSEG_{a} |
LSEG_{a} ## LSEG_{b} |
Returns the point in LSEG_{b} that is closest to LSEG_{a} |
POINT_{a} ## BOX_{b} |
Returns the point in BOX_{b} that is closest to POINT_{a} |
POINT_{a} ## LSEG_{b} |
Returns the point in LSEG_{b} that is closest to POINT_{a} |
The distance (<->) operator returns (as a DOUBLE PRECISION number) the distance between two geometric objects. You can use the distance operator with the operand types in Table 2.23.
Operator |
Description (or Formula) |
---|---|
BOX_{a} <-> BOX_{b} |
(@@ BOX_{a}) <-> (@@ BOX_{b}) |
CIRCLE_{a} <-> CIRCLE_{b} |
(@@ CIRCLE_{a}) <-> (@@ CIRCLE_{b}) ? (radius_{a} + radius_{b}) |
CIRCLE_{a} <-> POLYGON_{b} |
0 if any point in POLYGON_{b} is inside CIRCLE_{a} otherwise, distance between center of CIRCLE_{a} and closest point in POLYGON_{b} |
LSEG_{a} <-> BOX_{b} |
(LSEG ## BOX) <-> (LSEG ## (LSEG ## BOX)) |
LSEG_{a} <-> LSEG_{b} |
Distance between closest points (0 if LSEG_{a} intersects LSEG_{b}) |
PATH_{a} <-> PATH_{b} |
Distance between closest points |
POINT_{a} <-> BOX_{b} |
POINT_{a} <-> (POINT_{a} ## BOX_{b} ) |
POINT_{a} <-> CIRCLE_{b} |
POINT_{a} <-> ((@@ CIRCLE_{b}) ? CIRCLE_{b} radius) |
POINT_{a} <-> LSEG_{b} |
POINT_{a} <-> (POINT_{a} ## LSEG_{b}) |
POINT_{a} <-> PATH_{b} |
Distance between POINT_{a} and closest points |
POINT_{a} <-> POINT_{b} |
SQRT(( POINT_{a}.x ? POINT_{b}.x )^{2 }+ (POINT_{a}.y ? POINT_{b}.y )^{2 }) |
Next, you can determine the spatial relationships between two objects using the left-of (<<), right-of(>>), below (<^), and above (>^) operators.
There are three overlap operators. && evaluates to TRUE if the left operand overlaps the right operand. The &> operator evaluates to TRUE if the leftmost point in the first operand is left of the rightmost point in the second operand. The &< evaluates to TRUE if the rightmost point in the first operand is right of the leftmost point in the second operand.
The intersection operator (#)returns the intersecting points of two objects. You can find the intersection of two BOXes, or the intersection of two LSEGs. The intersection of two BOXes evaluates to a BOX. The intersection of two LSEGs evaluates to a single POINT.
Finally, the ?# operator evaluates to TRUE if the first operand intersects with or overlaps the second operand.
The final set of geometric operators determines the relationship between a line segment and an axis, or the relationship between two line segments.
The ?- operator evaluates to TRUE if the given line segment is horizontal (that is, parallel to the x-axis). The ?| operator evaluates to TRUE if the given line segment is vertical (that is, parallel to the y-axis). When you use the ?- and ?| operators with a line segment, they function as prefix unary operators. You can also use the ?- and ?| operators as infix binary operators (meaning that the operator appears between two values), in which case they operate as if you specified two points on a line segment.
The ?-| operator evaluates to TRUE if the two operands are perpendicular. The ?|| operator evaluates to TRUE if the two operands are parallel. The perpendicular and parallel operators can be used only with values of type LSEG.
The final geometric operator (@@) returns the center point of an LSEG, PATH, BOX, POLYGON, or CIRCLE.
Data Types |
Valid Operators (q) |
---|---|
POINT q POINT |
<-> << <^ >> >^ ?- ?| @ |
POINT q LSEG |
## <-> @ |
POINT q BOX |
## <-> @ |
POINT q PATH |
<-> @ |
POINT q POLYGON |
@ |
POINT q CIRCLE |
<-> @ |
LSEG q LSEG |
# ## < <-> <= <> = > >= ?# ?-| ?|| |
LSEG q BOX |
## <-> ?# @ |
BOX q POINT |
* + - / |
BOX q BOX |
# && &< &> < <-> << <= <^ = > >= >> >^ ?# @ ~ ~= |
PATH q POINT |
* + - / ~ |
PATH q PATH |
+ < <-> <= = > >= ?# |
POLYGON q POINT |
~ |
POLYGON q POLYGON |
&& &< &> <-> >> << @ ~ ~= |
CIRCLE q POINT |
* + - / ~ |
CIRCLE q POLYGON |
<-> |
CIRCLE q CIRCLE |
&& &< &> > <-> << <= <> <^ = > >= >> >^ @ ~ ~= |
Table 2.25 summarizes the names of the proximity operators for geometric types.
Data Types |
Valid Operators (q) |
---|---|
# |
Intersection or point count(for polygons) |
## |
Point of closest proximity |
<-> |
Distance Between |
<< |
Left of? |
>> |
Right of? |
<^ |
Below? |
>^ |
Above? |
&& |
Overlaps |
&> |
Overlaps to left |
&< |
Overlaps to right |
?# |
Intersects or overlaps |
@ |
Contained in |
~ |
Contains |
~= |
Same as |
?- |
Horizontal |
?| |
Vertical |
?-| |
Perpendicular |
?|| |
Parallel |
@@ |
Center |