pg_sphere - PolarDB - Alibaba Cloud Documentation Center

The pg_sphere extension provides spherical data types, compute functions, and operators for the native PostgreSQL. It is mainly used to solve the problems of how to represent points, lines, and planes on a sphere (for example, the Earth), and how to calculate the distance or area between points, lines, and planes.

Prerequisites

The pg_sphere extension is supported on the PolarDB for PostgreSQL clusters that run the following engines:

PostgreSQL 14 (revision version 14.5.1.0 or later)
PostgreSQL 11 (revision version 1.1.27 or later)

Note You can execute the following statements to view the revision version of a PolarDB for PostgreSQL cluster:

PostgreSQL 14
```
select version();
```
PostgreSQL 11
```
show polar_version;
```

Usage

Enable or disable the pg_sphere extension
- Enable the pg_sphere extension.
```
CREATE EXTENSION pg_sphere;
```
- Disable the pg_sphere extension.
```
DROP EXTENSION pg_sphere;
```
Data types
The pg_sphere extension supports the following data types:
- POINT
- EULER TRANSFORMATION
- CIRCLE
- LINE
- ELLIPSES
- PATH
- POLYGON
Points are used in the following examples.
Points are used in the following examples. Three ways can be used to express points on a sphere:
- Specify the position with longitude and latitude. Unit: radians.
```
SELECT spoint '(0.1,-0.2)';
```
  Sample result:
```
    spoint
--------------
 (0.1 , -0.2)
(1 row)
```
- Specify the position with longitude and latitude. Unit: degrees.
```
SELECT spoint '( 10.1d, -90d)';
```
  Sample result:
```
                 spoint
----------------------------------------
 (0.176278254451427 , -1.5707963267949)
(1 row)
```
- Specify the longitude and latitude in degrees minutes seconds (DMS).
```
SELECT spoint '( 10d 12m 11.3s, -13d 14m)';
```
  Sample result:
```
                 spoint
----------------------------------------
 (0.176278254451427 , -1.5707963267949)
(1 row)
```
  Note If a circular is divided into 360 equal parts, the central angle corresponding to one part is 1 degree. If the arc length corresponding to 1 degree is divided into 60 equal parts, the central angle corresponding to one part is 1 minute. If the arc length corresponding to 1 minute is divided into 60 equal parts, the central angle corresponding to one part is 1 second.
Constructors
Constructors can use other data types to construct spherical data types. Constructors support the following data types: POINT, EULER TRANSFORMATION, CIRCLE, LINE, ELLIPSES, PATH, and POLYGON.
Points are used in the following example. Construct a spherical point by using the longitude and latitude. The spherical position with longitude of 270 and latitude of -30 is obtained.
```
SELECT spoint (270.0 * pi() / 180.0, -30.0 * pi() / 180.0) AS spoint;
```
Sample result:
```
                 spoint
-----------------------------------------
 (4.71238898038469 , -0.523598775598299)
(1 row)
```
Operators
The pg_sphere extension supports the following operators:
- Casting
- Equality
- Contain and overlap
- Crossing of lines
- Distance
- Length and circumference
- Center
Distance is used to calculate the distance between two circles in the following example.
```
SELECT 180 * ( scircle '<(0d,20d),2d>' <-> scircle '<(0d,40d),2d>' ) / pi() AS dist;
```
Sample result:
```
 dist
------
   16
(1 row)
```
Functions
The pg_sphere extension supports the following computing functions:
- Area functions
- Path functions
- Distance functions
The area calculation function and point calculation function are used in the following examples.
- Calculate the area of a sphere in pi.
```
SELECT area( scircle '<(0d,90d),60d>' ) / pi() AS area;
```
  Sample result:
```
        area
--------------------
 0.9999999999999997
(1 row)
```
- Obtain the longitude and latitude of a spherical point in degrees.
  Note The long(spoint) function in the pg_sphere extension is changed to long_sphere(spoint).
```
SELECT long_sphere ( spoint '(10d,20d)' ) * 180.0 / pi() AS longitude;
```
  Sample result:
```
longitude
------------
10
(1 row)
```
```
SELECT lat ( spoint '(10d,20d)' ) * 180.0 / pi() AS latitude;
```
  Sample result:
```
latitude
----------
20
(1 row)
```

References

For more information about pg_sphere, see Documentation.