PolarDB:pg_sphere - PolarDB

pg_sphere is a PostgreSQL extension that adds spherical data types, operators, and functions to PolarDB for PostgreSQL (Compatible with Oracle). Use it to store and query positions on a sphere — such as geographic coordinates on Earth or star positions in the sky — and to calculate distances and areas between spherical objects.

Prerequisites

pg_sphere is supported on the following engine versions:

Engine	Minimum revision version
PolarDB for PostgreSQL (Compatible with Oracle) 2.0	2.0.14.1.0
PolarDB for PostgreSQL (Compatible with Oracle) 1.0	1.1.27

To check the revision version of your cluster, run:

SHOW polar_version;

Install and remove the extension

To install pg_sphere:

CREATE EXTENSION pg_sphere;

To remove pg_sphere:

DROP EXTENSION pg_sphere;

Data types

pg_sphere provides the following spherical data types:

Data type	Use cases
`POINT` (spoint)	Geographic sites on Earth, star positions on the sky sphere, positions on a planet
`CIRCLE` (scircle)	Search areas around a location, round celestial clusters, positions with an undirected error radius
`LINE`	Great circle segments and arcs on a sphere
`ELLIPSES`	Elliptical regions on a sphere, such as nebulae or observation error ellipses
`PATH`	Sequences of connected arcs on a sphere
`POLYGON`	Arbitrary polygonal regions on a sphere
`EULER TRANSFORMATION`	Rotations and coordinate system transformations on a sphere

Specify a spherical point

Three formats are accepted for spoint literals.

Longitude and latitude in radians:

SELECT spoint '(0.1,-0.2)';

spoint
--------------
 (0.1 , -0.2)
(1 row)

Longitude and latitude in degrees (append `d`):

SELECT spoint '( 10.1d, -90d)';

spoint
----------------------------------------
 (0.176278254451427 , -1.5707963267949)
(1 row)

Degrees, minutes, and seconds (DMS):

SELECT spoint '( 10d 12m 11.3s, -13d 14m)';

spoint
----------------------------------------
 (0.176278254451427 , -1.5707963267949)
(1 row)

In DMS notation, 1 degree is 1/360 of a full circle, 1 minute is 1/60 of a degree, and 1 second is 1/60 of a minute.

Constructors

Constructors build spherical data types from raw numeric values. They accept all seven data types: POINT, EULER TRANSFORMATION, CIRCLE, LINE, ELLIPSES, PATH, and POLYGON.

The following example constructs a spherical point at longitude 270°, latitude −30° by converting degrees to radians:

SELECT spoint (270.0 * pi() / 180.0, -30.0 * pi() / 180.0) AS spoint;

spoint
-----------------------------------------
 (4.71238898038469 , -0.523598775598299)
(1 row)

Operators

pg_sphere supports the following operator categories:

Category	Description
Casting	Convert between spherical and other data types
Equality	Test whether two spherical objects are equal
Contain and overlap	Test spatial containment and overlap relationships
Crossing of lines	Test whether two spherical lines cross
Distance (`<->`)	Calculate the angular distance between two spherical objects
Length and circumference	Measure arc length or circumference
Center	Return the center point of a spherical object

Distance example

The <-> operator returns the angular distance in radians. The following example calculates the distance between two circles centered at (0°, 20°) and (0°, 40°), each with a radius of 2°, and converts the result to degrees:

SELECT 180 * ( scircle '<(0d,20d),2d>' <-> scircle '<(0d,40d),2d>' ) / pi() AS dist;

dist
------
   16
(1 row)

Functions

pg_sphere provides three categories of functions:

Category	Description
Area functions	Calculate the area of a spherical object
Path functions	Operate on spherical paths
Distance functions	Calculate distances between spherical objects

Area function

The following example calculates the area of a circle centered at the north pole (90°) with a radius of 60° and expresses the result as a multiple of π:

SELECT area( scircle '<(0d,90d),60d>' ) / pi() AS area;

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

Point coordinate functions

The long(spoint) function has been renamed to long_sphere(spoint) in this implementation.

SELECT long_sphere ( spoint '(10d,20d)' ) * 180.0 / pi() AS longitude;

longitude
----------
       10
(1 row)

SELECT lat ( spoint '(10d,20d)' ) * 180.0 / pi() AS latitude;

latitude
----------
      20
(1 row)

References

For the full list of pg_sphere data types, operators, and functions, see the pg_sphere documentation.