pg_sphere - PolarDB - Alibaba Cloud Documentation Center

The pg_sphere extension provides spherical data types, functions, and operators for native PostgreSQL. This extension is used to solve the problems of how to represent points, lines, and planes on a sphere such as 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 (Compatible with Oracle) clusters that run the following engines:

PolarDB for PostgreSQL (Compatible with Oracle) 2.0 (revision version 2.0.14.1.0 or later)
PolarDB for PostgreSQL (Compatible with Oracle) 1.0 (revision version 1.1.27 or later)

Note

You can execute the following statements to view the revision version of a PolarDB for PostgreSQL (Compatible with Oracle) cluster:

SHOW polar_version;

Usage

Create or delete the pg_sphere extension
- Create the pg_sphere extension.
```
CREATE EXTENSION pg_sphere;
```
- Delete 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. 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 
------
    1
(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.