This chapter is from the book
This chapter is from the book
This chapter is from the book
Operators
PostgreSQL supports a pool of operators that have been defined for PostgreSQL's geometric datatypes. Most of these operators and functions are also defined for basic datatypes, but in addition to that, PostgreSQL supports many operators, which are only available for geometric data. In this section, we take a closer look at some of these operators.
One of them is the <-> operator, which can be used to calculate the distance between two points. Here is an example:
SELECT '(1,1)'::point <-> '(2,2)'::point; The result is ?column? ----------------- 1.4142135623731 (1 row)
The same operator can also be used for calculating the distance between two boxes:
tuning=# SELECT '((1,1), (2,2))'::box <-> '((2,2), (3,3))'::box; ?column? ----------------- 1.4142135623731 (1 row)
<< is another important operator. It calculates if one point is left of another point. The following example shows how this operator can be used:
shop=# SELECT '(0,0)'::point << '-1,0'::point; ?column? ---------- f (1 row)
If you want to find the point of closest proximity the ## operator has to be used. Here is an example:
tuning=# SELECT '(99,99)'::point ## '((3,2),(12,4))'::box; ?column? ---------- (12,4) (1 row)
PostgreSQL supports many more functions and operators, but this is far beyond the scope of this document. Check out the PostgreSQL Developer's Handbook and the official documentation for PostgreSQL to get the complete list of all operators defined. Another possibility is to use PostgreSQL's onboard documentation by typing \do in your favorite SQL shell (such as psql).