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.35, Table 9.36, and Table 9.37.
Table 9.35. Geometric Operators
Operator Description Example(s) |
---|
Adds the coordinates of the second
|
Concatenates two open paths (returns NULL if either path is closed).
|
Subtracts the coordinates of the second
|
Multiplies each point of the first argument by the second
|
Divides each point of the first argument by the second
|
Computes the total length.
Available for
|
Computes the center point.
Available for
|
Returns the number of points.
Available for
|
Computes the point of intersection, or NULL if there is none.
Available for
|
Computes the intersection of two boxes, or NULL if there is none.
|
Computes the closest point to the first object on the second object.
Available for these pairs of types:
(
|
Computes the distance between the objects.
Available for all seven geometric types, for all combinations
of
|
Does first object contain second?
Available for these pairs of types:
(
|
Is first object contained in or on second?
Available for these pairs of types:
(
|
Do these objects overlap? (One point in common makes this true.)
Available for
|
Is first object strictly left of second?
Available for
|
Is first object strictly right of second?
Available for
|
Does first object not extend to the right of second?
Available for
|
Does first object not extend to the left of second?
Available for
|
Is first object strictly below second?
Available for
|
Is first object strictly above second?
Available for
|
Does first object not extend above second?
Available for
|
Does first object not extend below second?
Available for
|
Is first object below second (allows edges to touch)?
|
Is first object above second (allows edges to touch)?
|
Do these objects intersect?
Available for these pairs of types:
(
|
Is line horizontal?
|
Are points horizontally aligned (that is, have same y coordinate)?
|
Is line vertical?
|
Are points vertically aligned (that is, have same x coordinate)?
|
Are lines perpendicular?
|
Are lines parallel?
|
Are these objects the same?
Available for
|
[a] “Rotating” a box with these operators only moves its corner points: the box is still considered to have sides parallel to the axes. Hence the box's size is not preserved, as a true rotation would do. |
Note that the “same as” operator, ~=
,
represents the usual notion of equality for the point
,
box
, polygon
, and circle
types.
Some of the geometric types also have an =
operator, but
=
compares for equal areas only.
The other scalar comparison operators (<=
and so
on), where available for these types, likewise compare areas.
Before PostgreSQL 14, the point
is strictly below/above comparison operators point
<<|
point
and point
|>>
point
were respectively
called <^
and >^
. These
names are still available, but are deprecated and will eventually be
removed.
Table 9.36. Geometric Functions
Table 9.37. Geometric Type Conversion Functions
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.