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

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

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