w3resource

PostgreSQL: Geometric Functions and Operators

Introduction

There are various geometric functions as well as operators available in PostgreSQL. The geometric types point, box, lseg, line, path, polygon, and circle have a large set of native support functions and operators, shown in the following section:

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 '((1,-1),(-1,1))' # '((1,1),(-1,-1))'
# Number of points in path or polygon # '((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))'

Geometric Functions

area(object)

The area() function is used to create the area of an object.

Return Type: double precision

Example:

Code:

SELECT area(box '((0,0),(2,3))');

Sample Output:

 area
------
    6
(1 row)

Code:

SELECT area(box '((2,3),(-2,-3))');

Sample Output:

 area
------
    24
(1 row)

The following statement calculates the area of a circle.

Code:

SELECT area(circle '((0,0),5)');

Sample Output:

       area
------------------
 78.5398163397448
(1 row)

center(object)

The center() function is used to create the center of an object.

Return Type: point

Example:

Code:

SELECT center(box '((0,0),(3,4))');

Sample Output:

 center
---------
 (1.5,2)
(1 row)

Code:

SELECT center(circle '((2,3),8)');

Sample Output:

 center
--------
 (2,3)
(1 row)

diameter(circle)

The diameter() function is used to create the diameter of a circle.

Return Type: double precision

Example:

Code:

SELECT diameter(circle '((2,3),8)');

Sample Output:

 diameter
----------
       16
(1 row)

height(box)

The height() function is used to create the vertical size of a box.

Return Type: double precision

Example:

Code:

SELECT height(box '((2,3),(5,8))');

Sample Output:

 height
--------
      5
(1 row)

isclosed(path)

The isclosed() function is used to verify whether a particular path is close or not.

Return Type: boolean

Example:

Code:

SELECT isclosed(path '((2,3),(1,1),(2,2))');

Sample Output:

 isclosed
----------
 t
(1 row)

isopen(path)

The isclosed() function is used to verify whether a particular path is open or not.

Return Type: boolean

Example:

Code:

SELECT isopen(path '((2,3),(1,1),(2,2))');

Sample Output:

 isopen
--------
 f
(1 row)

length(object)

The length() function is used to find the length of an object.

Return Type: double precision

Example:

Code:

SELECT length(path '((2,3),(5,5))');

Sample Output:

      length
------------------
 7.21110255092798
(1 row)

npoints(path)

The npoints() function is used to return the number of points of a path.

Return Type: int

Example:

Code:

SELECT npoints(path '[(1,1),(2,2),(3,3)]');

Sample Output:

 npoints
---------
       3
(1 row)	

npoints(polygon)

The npoints() function is used to return the number of points of a polygon given in the argument.

Return Type: int

Example:

Code:

SELECT npoints(polygon '((1,1),(0,0),(2,3),(3,5))');

Sample Output:

 npoints
---------
       4
(1 row)

pclose(path)

The pclose() function is used to convert a path to close.

Return Type: path

Example:

Code:

SELECT pclose(path '((2,3),(1,1),(2,2))');

Sample Output:

       pclose
---------------------
 ((2,3),(1,1),(2,2))
(1 row)

popen(path)

The popen() function is used to convert path to open.

Return Type: path

Example:

Code:

SELECT popen(path '((2,3),(1,1),(2,2))');

Sample Output:

        popen
---------------------
 [(2,3),(1,1),(2,2)]
(1 row)		

radius(circle)

The radius() function is used to get the radius of a circle.

Return Type: double precision

Example:

Code:

SELECT radius(circle '((2,3),8)');

Sample Output:

 radius
--------
      8
(1 row)		

width(box)

The width() function is used to get the horizontal size of box

Return Type: double precision

Example:

Code:

SELECT width(box '((2,3),(5,8))');

Sample Output:

 width
-------
     3
(1 row)	

Geometric Type Conversion Functions

Function Return Type Description Example
box(circle) box circle to box box(circle '((0,0),2.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))')
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))')
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))')

Previous: Data Type Formatting Functions
Next: JSON Functions and Operators