pg_sphere

The pg_sphere extension adds spherical data types, operators, and functions to PostgreSQL. Use it to represent and query geometric objects on a sphere — points, circles, lines, ellipses, paths, and polygons — and to compute distances and areas between them. Common use cases include geographic coordinate systems (sites on Earth) and astronomical positioning (star positions on the celestial sphere).

Enable and disable the extension

-- Enable
CREATE EXTENSION pg_sphere;

-- Disable
DROP EXTENSION pg_sphere;

Data types

pg_sphere supports the following spherical data types:

SQL type	Geometric type	Use cases
`spoint`	Point (position)	Sites on Earth, star positions, spherical positions on planets
`strans`	Euler transformation	Coordinate frame rotations, orbital element transformations
`scircle`	Circle (area around a point)	Proximity search, round clusters, position with undirected error
`sline`	Line	Great-circle arcs between two spherical points
`sellipse`	Ellipse	Elongated clusters, error ellipses
`spath`	Path	Sequences of connected arcs
`spoly`	Polygon	Regions bounded by multiple arcs

Point input formats

spoint accepts three input formats. All examples below represent the same or equivalent positions.

Radians — specify longitude and latitude directly in radians:

SELECT spoint '(0.1,-0.2)';

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

Decimal degrees — append d to each value:

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

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

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

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

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

Note The DMS format subdivides a degree into 60 arc minutes, and each arc minute into 60 arc seconds.

Constructors

Constructors build spherical data types from numeric values. All supported types — spoint, strans, scircle, sline, sellipse, spath, and spoly — have corresponding constructor functions.

The following example constructs a spherical point from longitude and latitude in radians (longitude 270°, latitude −30°):

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

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

Operators

pg_sphere defines operators across the following categories:

Category	Description
Casting	Convert between spherical types
Equality	Test whether two objects are identical
Contain and overlap	Test containment and overlap between objects
Crossing of lines	Test whether two lines cross
Distance	Compute angular distance between two objects (in radians)
Length and circumference	Compute arc length or circumference
Center	Return the center point of an object

Distance example — compute the angular distance (in degrees) between two circles:

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	Compute the area of a spherical object
Path functions	Compute arc lengths and work with paths
Distance functions	Compute angular distances

Key function signatures:

Function	Signature	Description
`long_sphere`	`long_sphere(spoint p) → float8`	Longitude of a point in radians
`lat`	`lat(spoint p) → float8`	Latitude of a point in radians
`area`	`area(scircle c) → float8`	Area of a circle in steradians

Note The long(spoint) function in the pg_sphere extension is changed to long_sphere(spoint).

Get the area of a circle in multiples of π:

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

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

Get the longitude and latitude of a point in degrees:

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 operator and function reference, see the pg_sphere documentation.