All Products
Search

This Product

    PolarDB:pg_sphere (spherical data calculations)

    Document Center

    PolarDB:pg_sphere (spherical data calculations)

    Last Updated:Mar 30, 2026

    pg_sphere is an open-source PostgreSQL extension that adds spherical data types, operators, and functions to PolarDB for PostgreSQL. Use it to store and query objects on a sphere—such as geographic coordinates, star positions, and polygons on Earth—and to calculate distances and areas between them.

    Prerequisites

    Before you begin, ensure that you have:

    • A PolarDB for PostgreSQL cluster running one of the following versions:

      • PostgreSQL 16 (minor engine version 2.0.16.9.6.0 or later)

      • PostgreSQL 14 (minor engine version 2.0.14.5.1.0 or later)

      • PostgreSQL 11 (minor engine version 2.0.11.9.27.0 or later)

    To check your minor engine version, run SHOW polardb_version; or check it in the console. If your version does not meet the requirements, upgrade the minor engine version.

    Install or uninstall the extension

    Install the extension:

    CREATE EXTENSION pg_sphere;

    Uninstall the extension:

    DROP EXTENSION pg_sphere;

    Data types

    pg_sphere provides the following spherical object types:

    Type Description
    Point A position on a sphere defined by longitude and latitude. Use for geographic locations, star positions, or any point on a spherical surface.
    Euler transformation A rotation in 3D space defined by three Euler angles. Use to transform or rotate spherical coordinates.
    Circle A spherical cap defined by a center point and a radius.
    Line A great circle arc on the sphere.
    Ellipses An elliptical region on the sphere.
    Path An ordered sequence of points forming an open arc on the sphere.
    Polygon A closed region bounded by great circle arcs. Use for geographic boundaries such as country outlines or coverage areas.

    Represent a point

    Specify a point (spoint) in one of three formats:

    Radians — specify longitude and latitude in radians:

    SELECT spoint '(0.1,-0.2)';

    Output:

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

    Degrees — append d to each value:

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

    Output:

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

    Degrees, minutes, and seconds (DMS) — use d, m, and s suffixes:

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

    Output:

    spoint
------------------------------------------
 (0.178078367649387 , -0.230965237680583)
(1 row)
    A circle is divided into 360 degrees. One degree is divided into 60 minutes. One minute is divided into 60 seconds.

    Constructors

    Constructors create spherical objects from scalar values. The following example constructs a point with longitude 270 degrees and latitude -30 degrees by converting from degrees to radians:

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

    Output:

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

    Operators

    pg_sphere supports the following operators:

    Category Operators Description
    Casting :: Project a spherical object to a compatible type
    Equality =, <> Test whether two objects are equal or not equal
    Contain and overlap <@, @>, !<@, !@>, &&, !&& Test containment and overlap between objects
    Crossing of lines # Test whether two lines cross
    Distance <-> Calculate the angular distance between two objects
    Length and circumference Calculate the length of a path or circumference of a circle
    Center @@ Return the center point of an object

    The following example uses the distance operator (<->) to calculate the angular distance between two circles centered at 20 degrees and 40 degrees latitude, each with a radius of 2 degrees. The result is returned in degrees:

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

    Output:

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

    Functions

    pg_sphere provides three categories of calculation functions:

    • Area functions — calculate the area of a spherical object in square radians. Supported types: scircle, spolygon, sbox.

    • Path functions — calculate properties of spherical paths.

    • Distance functions — calculate the angular distance between spherical objects.

    Area of a circle

    Calculate the area of a spherical circle with its center at the north pole (90 degrees) and a radius of 60 degrees, expressed as a multiple of pi:

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

    Output:

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

    Longitude and latitude of a point

    In the pg_sphere extension for PolarDB, the long(spoint) function has been renamed to long_sphere(spoint). Update any existing queries that use long(spoint).

    Get the longitude of a point in degrees:

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

    Output:

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

    Get the latitude of a point in degrees:

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

    Output:

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

    References

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