PostgreSQL 经纬度查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10034636/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL latitude longitude query
提问by Idan
i have latitudeand longitudecolumns in locationtable in PostgreSQL database,
and I am trying to execute distance query with a PostgreSQL function.
我有latitude和longitude列location在PostgreSQL数据库表中，我试图用PostgreSQL的函数来执行远程查询。
I read this chapter of the manual:
我阅读了手册的这一章：
https://www.postgresql.org/docs/current/static/earthdistance.html
https://www.postgresql.org/docs/current/static/earthdistance.html
but I think I'm missing something there.
但我想我在那里遗漏了一些东西。
How should I do that? Are there more examples available
我该怎么做？是否有更多示例可用
采纳答案by strkol
This module is optional and is not installed in the default PostgreSQL instalatlion. You must install it from the contrib directory.
此模块是可选的，未安装在默认的 PostgreSQL 安装中。您必须从 contrib 目录安装它。
You can use the following function to calculate the approximate distance between coordinates (in miles):
您可以使用以下函数来计算坐标之间的近似距离（以英里为单位）：
CREATE OR REPLACE FUNCTION distance(lat1 FLOAT, lon1 FLOAT, lat2 FLOAT, lon2 FLOAT) RETURNS FLOAT AS $$
DECLARE
x float = 69.1 * (lat2 - lat1);
y float = 69.1 * (lon2 - lon1) * cos(lat1 / 57.3);
BEGIN
RETURN sqrt(x * x + y * y);
END
$$ LANGUAGE plpgsql;
回答by Steve Tauber
Here's another example using the point operator:
这是使用点运算符的另一个示例：
Initial setup(only need to run once):
初始设置（只需要运行一次）：
create extension cube;
create extension earthdistance;
And then the query:
然后查询：
select (point(-0.1277,51.5073) <@> point(-74.006,40.7144)) as distance;
distance
------------------
3461.10547602474
(1 row)
Note that pointsare created with LONGITUDE FIRST. Per the documentation:
请注意，这points是使用LONGITUDE FIRST创建的。根据文档：
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.
点被视为（经度，纬度），反之亦然，因为经度更接近于 x 轴和纬度到 y 轴的直观想法。
Which is terrible design... but that's the way it is.
这是可怕的设计......但它就是这样。
Your output will be in miles.
您的输出将以英里为单位。
Gives the distance in statute miles between two points on the Earth's surface.
给出地球表面两点之间的法定英里距离。
回答by Mike Sherrill 'Cat Recall'
Assuming you've installed the earthdistance module correctly, this will give you the distance in miles between two cities. This method uses the simpler point-based earth distances. Note that the arguments to point() are first longitude, then latitude.
假设您已经正确安装了 earthdistance 模块，这将为您提供两个城市之间的距离（以英里为单位）。此方法使用更简单的基于点的地球距离。请注意，point() 的参数首先是经度，然后是纬度。
create table lat_lon (
city varchar(50) primary key,
lat float8 not null,
lon float8 not null
);
insert into lat_lon values
('London, GB', 51.67234320, 0.14787970),
('New York, NY', 40.91524130, -73.7002720);
select
(
(select point(lon,lat) from lat_lon where city = 'London, GB') <@>
(select point(lon,lat) from lat_lon where city = 'New York, NY')
) as distance_miles
distance_miles
--
3447.58672105301
回答by Thijs
A more accurate version of @strkol's answer, using the Haversine formula
使用Haversine公式的@strkol答案的更准确版本
CREATE OR REPLACE FUNCTION distance(
lat1 double precision,
lon1 double precision,
lat2 double precision,
lon2 double precision)
RETURNS double precision AS
$BODY$
DECLARE
R integer = 6371e3; -- Meters
rad double precision = 0.01745329252;
φ1 double precision = lat1 * rad;
φ2 double precision = lat2 * rad;
Δφ double precision = (lat2-lat1) * rad;
Δλ double precision = (lon2-lon1) * rad;
a double precision = sin(Δφ/2) * sin(Δφ/2) + cos(φ1) * cos(φ2) * sin(Δλ/2) * sin(Δλ/2);
c double precision = 2 * atan2(sqrt(a), sqrt(1-a));
BEGIN
RETURN R * c;
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
Input is in degrees (e.g. 52.34273489, 6.23847) and output is in meters.
输入以度为单位（例如 52.34273489、6.23847），输出以米为单位。
