what is pg_sphere, how to use pg_sphere - PolarDB - Alibaba Cloud Documentation Center

pg_sphere is an open-source PostgreSQL extension that supports spherical data types, functions, and operators. It lets you represent objects on a sphere, such as the points, lines, and polygons of the Earth. It also supports calculating the distance or area between these objects.

Prerequisites

The following versions of PolarDB for PostgreSQL are supported:

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)

Note

You can check the minor engine version in the console or by running the SHOW polardb_version; statement. If your minor engine version does not meet the requirements, you need to upgrade the minor engine version.

Procedure

Install or uninstall the extension

Install the extension.
```
CREATE EXTENSION pg_sphere;
```
Uninstall the extension.
```
DROP EXTENSION pg_sphere;
```

Data types

The pg_sphere extension supports the following object types:

Point
Euler transformation
Circle
Line
Ellipses
Path
Polygon

The following example shows how to create a point object. You can represent a point on a sphere in one of the following three ways:

Specify the location by longitude and latitude in radians.

SELECT spoint '(0.1,-0.2)';

The output is:

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

Specify the location by longitude and latitude in degrees.

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

The output is:

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

Specify the longitude and latitude using degrees, minutes, and seconds (DMS).
```
SELECT spoint '( 10d 12m 11.3s, -13d 14m)';
```
The output is:
```
                  spoint                  
------------------------------------------
 (0.178078367649387 , -0.230965237680583)
(1 row)
```
Note
A circle is divided into 360 degrees. One degree is divided into 60 minutes. One minute is divided into 60 seconds.

Constructors

Constructors are used to create spherical data types, such as Point, Euler transformation, Circle, Line, Ellipses, Path, and Polygon, from other data types.

The following example constructs a spherical point from a longitude and latitude. The resulting point has a longitude of 270 degrees and a latitude of -30 degrees.

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

The output is:

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

Operators

The pg_sphere extension supports the following operators:

Casting refers to projection.
Equality
Contain and overlap
Crossing of lines
Distance
Length and circumference
Center

The following example uses the distance operator to calculate the distance between two circles.

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

The output is:

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

Functions

The pg_sphere extension supports the following calculation functions:

Area calculation functions
Path calculation functions
Distance calculation functions

The following examples show how to use the area calculation and point calculation functions.

Calculate the area of a spherical circle in units of pi.

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

The output is:

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

Retrieve the longitude and latitude of a spherical point in degrees.

Note

In the pg_sphere extension, the long(spoint) function has been changed to long_sphere(spoint).

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

The output is:

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

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

The output is:

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

References

For more information, see the official pg_sphere documentation.