pg_sphere-PolarDB(PolarDB)-阿里云帮助中心

pg_sphere is an open-source PostgreSQL extension that adds support for spherical data types, functions, and operators. This extension lets you represent objects like points, lines, and polygons on a sphere, such as the Earth, and calculate the distances and areas between them.

Prerequisites

This extension supports the following PolarDB for PostgreSQL versions:

PostgreSQL 14 (kernel minor version 14.5.1.0 and later)
PostgreSQL 11 (kernel minor version 1.1.27 and later)

Note You can run the following statements to view the kernel minor version of PolarDB for PostgreSQL:

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

Usage

Enable or disable the extension
- Enable the extension.
```
CREATE EXTENSION pg_sphere;
```
- Disable the extension.
```
DROP EXTENSION pg_sphere;
```
Data Type
The pg_sphere extension supports the following object types:
- point
- euler transformation
- circle
- line
- ellipse
- path
- polygon
This section uses the point data type as an example.
For example, you can define a point on a sphere in three ways:
- Specify the position using longitude and latitude in radians.
```
SELECT spoint '(0.1,-0.2)';
```
  The output is as follows:
```
    spoint
--------------
 (0.1 , -0.2)
(1 row)
```
- Specify the position using longitude and latitude in degrees.
```
SELECT spoint '( 10.1d, -90d)';
```
  The output is as follows:
```
                 spoint
----------------------------------------
 (0.176278254451427 , -1.5707963267949)
(1 row)
```
- Specify the longitude and latitude using the Degrees Minutes Seconds (DMS) format.
```
SELECT spoint '( 10d 12m 11.3s, -13d 14m)';
```
  The output is as follows:
```
                 spoint
----------------------------------------
 (0.176278254451427 , -1.5707963267949)
(1 row)
```
  Note A circle contains 360 degrees. Each degree contains 60 arc minutes, and each arc minute contains 60 arc seconds.
Constructors
The extension provides constructors for point, euler transformation, circle, line, ellipse, path, and polygon.
The following example shows how to create a point. It constructs a spherical point at longitude 270 degrees and latitude -30 degrees:
```
SELECT spoint (270.0 * pi() / 180.0, -30.0 * pi() / 180.0) AS spoint;
```
The output is as follows:
```
                 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
The following example shows how to use 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 as follows:
```
 dist
------
   16
(1 row)
```
Functions
The pg_sphere extension provides the following categories of functions:
- Area functions
- Path functions
- Distance functions
The following examples show how to use an area function and a point function.
- Calculate the area of a spherical object in units of pi.
```
SELECT area( scircle '<(0d,90d),60d>' ) / pi() AS area;
```
  The output is as follows:
```
        area
--------------------
 0.9999999999999997
(1 row)
```
- Get the longitude and latitude of a spherical point in degrees.
  Note In the pg_sphere extension, the long(spoint) function is renamed to the long_sphere(spoint) function.
```
SELECT long_sphere ( spoint '(10d,20d)' ) * 180.0 / pi() AS longitude;
```
  The output is as follows:
```
longitude
------------
10
(1 row)
```
```
SELECT lat ( spoint '(10d,20d)' ) * 180.0 / pi() AS latitude;
```
  The output is as follows:
```
latitude
----------
20
(1 row)
```

References

For more information about pg_sphere, see the pg_sphere documentation.