Postgresql 中文操作指南
F.15. earthdistance — calculate great-circle distances #
earthdistance 模块提供了两种不同的方法来计算地球表面的大圆距离。首先描述的方法依赖于 cube 模块。第二种方法基于内置的 point 数据类型，使用经度和纬度作为坐标。
The earthdistance module provides two different approaches to calculating great circle distances on the surface of the Earth. The one described first depends on the cube module. The second one is based on the built-in point data type, using longitude and latitude for the coordinates.
在此模块中，假定地球是完美的球形。（如果对您来说这种假设太不准确，您可能想要查看 PostGIS项目。）
In this module, the Earth is assumed to be perfectly spherical. (If that’s too inaccurate for you, you might want to look at the PostGIS project.)
cube 模块必须在 earthdistance 安装之前安装（尽管你可以使用 CASCADE 选项 CREATE EXTENSION 在一个命令中安装两者）。
The cube module must be installed before earthdistance can be installed (although you can use the CASCADE option of CREATE EXTENSION to install both in one command).
Caution
强烈建议在同一个模式中安装 earthdistance 和 cube，且该模式应是尚未并且不会向任何不值得信赖的用户授予 CREATE 权限的模式。如果 earthdistance 的模式包含不可信用户定义的对象，则在安装时会存在安全隐患。此外，在安装后使用 earthdistance 函数时，整个搜索路径应只包含受信任的模式。
It is strongly recommended that earthdistance and cube be installed in the same schema, and that that schema be one for which CREATE privilege has not been and will not be granted to any untrusted users. Otherwise there are installation-time security hazards if earthdistance's schema contains objects defined by a hostile user. Furthermore, when using earthdistance's functions after installation, the entire search path should contain only trusted schemas.
F.15.1. Cube-Based Earth Distances #
数据存储在作为点的立方体中（两个角相同），使用从地球中心到 x、y 和 z 距离的 3 个坐标。提供了 domain earth 的 [role="bare"]glossary.html#GLOSSARY-DOMAIN cube 类型 cube ，其中包括约束检查，这些检查可确保值满足这些限制并且与地球的实际表面大致接近。
Data is stored in cubes that are points (both corners are the same) using 3 coordinates representing the x, y, and z distance from the center of the Earth. A [role="bare"]glossary.html#GLOSSARY-DOMAINdomain earth over type cube is provided, which includes constraint checks that the value meets these restrictions and is reasonably close to the actual surface of the Earth.
地球半径是从 earth() 函数获取的。其以米为单位。但是，通过更改此函数，你可以更改模块，以便使用其他单位，或使用你觉得更合适的不同半径值。
The radius of the Earth is obtained from the earth() function. It is given in meters. But by changing this one function you can change the module to use some other units, or to use a different value of the radius that you feel is more appropriate.
此包也适用于天文数据库。天文学家可能希望更改 earth() 以返回 180/pi() 半径，以便距离以度为单位。
This package has applications to astronomical databases as well. Astronomers will probably want to change earth() to return a radius of 180/pi() so that distances are in degrees.
提供函数来支持纬度和经度（以度为单位）输入，支持纬度和经度输出，计算两个点之间的最大圆周距离，并轻松指定可用于索引搜索的边界框。
Functions are provided to support input in latitude and longitude (in degrees), to support output of latitude and longitude, to calculate the great circle distance between two points and to easily specify a bounding box usable for index searches.
提供的函数所示于 Table F.5。
The provided functions are shown in Table F.5.
Table F.5. Cube-Based Earthdistance Functions
Function Description |
earth () → float8 Returns the assumed radius of the Earth. |
sec_to_gc ( float8 ) → float8 Converts the normal straight line (secant) distance between two points on the surface of the Earth to the great circle distance between them. |
gc_to_sec ( float8 ) → float8 Converts the great circle distance between two points on the surface of the Earth to the normal straight line (secant) distance between them. |
ll_to_earth ( float8, float8 ) → earth Returns the location of a point on the surface of the Earth given its latitude (argument 1) and longitude (argument 2) in degrees. |
latitude ( earth ) → float8 Returns the latitude in degrees of a point on the surface of the Earth. |
longitude ( earth ) → float8 Returns the longitude in degrees of a point on the surface of the Earth. |
earth_distance ( earth, earth ) → float8 Returns the great circle distance between two points on the surface of the Earth. |
earth_box ( earth, float8 ) → cube Returns a box suitable for an indexed search using the cube @> operator for points within a given great circle distance of a location. Some points in this box are further than the specified great circle distance from the location, so a second check using earth_distance should be included in the query. |
F.15.2. Point-Based Earth Distances #
模块的第二部分依靠将地球位置表示为 point 类型的值，其中第一个分量代表经度，以度为单位，第二个分量代表纬度，以度为单位。采用点为 (经度、纬度)，而不是相反，这是因为经度更接近于 x 轴直观概念，纬度更接近于 y 轴。
The second part of the module relies on representing Earth locations as values of type point, in which the first component is taken to represent longitude in degrees, and the second component is taken to represent latitude in degrees. Points are taken as (longitude, latitude) and not vice versa because longitude is closer to the intuitive idea of x-axis and latitude to y-axis.
提供了一个单一运算符，如 Table F.6中所示。
A single operator is provided, shown in Table F.6.
Table F.6. Point-Based Earthdistance Operators
Operator Description |
point <@> point → float8 Computes the distance in statute miles between two points on the Earth’s surface. |
请注意，与模块基于 cube 的部分不同，此处单位是固定的：更改 earth() 函数不会影响此操作符的结果。
Note that unlike the cube-based part of the module, units are hardwired here: changing the earth() function will not affect the results of this operator.
经度/纬度表示法的一个缺点是，您需要注意极点附近以及经度 +/- 180 度附近的边缘条件。基于 cube 的表示法避免了这些不连续性。
One disadvantage of the longitude/latitude representation is that you need to be careful about the edge conditions near the poles and near +/- 180 degrees of longitude. The cube-based representation avoids these discontinuities.