使用 Earthdistance 和立方体的地理定位

使用纬度和经度地理位置的数据库中的一个常见要求是查询在某个半径范围内的位置或与另一个位置的距离。有多种方法可以轻松管理此类查询。您可以将 Postgres earthdistance 模块与点或立方体一起使用，也可以使用第三方 PostGIS 扩展。在这一集中，我们将重点关注将 earthdistance 模块与点数据类型一起使用。

earthdistance 模块是官方支持的 Postgres 扩展，必须手动启用。它依赖于 Postgres 多维数据集扩展，因此我将快速安装两者。

create extension if not exists cube;
create extension if not exists earthdistance;

对于此示例，我们将使用地址表，该表具有 float8 或双精度类型的纬度和经度列，以及一些条目

\d addresses

select * from addresses;

我们将看两个示例，了解如何在 earthdistance 模块中使用基于立方体的计算。首先是确定位置之间的距离。第二个是在另一个位置的某个半径范围内寻找位置。

让我们从计算两个位置之间的距离开始。我将使用 "\e" 元命令来使用 vim 作为我的查询编辑器。

为了计算 Hashrocket Jacksonville 办公室和我们表中所有其他地址之间的距离，我们将从地址中进行选择并进行横向连接，以获得 Hashrocket Jacksonville 地址作为与其他位置进行比较的位置。通过该设置，我们可以完成我们的选择语句来确定 Hashrocket Jacksonville 地理位置与我们其他地理位置之间的距离。

为了确定两个纬度/经度坐标之间的距离，我们将使用“earth_distance”函数。此函数期望接收两个三坐标位置以用于比较距离，并返回位置之间的距离（以米为单位）。由于我们只有两个坐标，纬度和经度，我们需要转换我们的数据。我们可以使用辅助方法“ll_to_earth”来做到这一点，首先传递纬度，然后传递经度作为我们的参数。

select name, earth_distance(
  ll_to_earth(a.latitude, a.longitude),
  ll_to_earth(hr_jax.latitude, hr_jax.longitude)
) as distance
from addresses a,
lateral (
  select id, latitude, longitude from addresses where name = 'Hashrocket JAX'
) as hr_jax
where a.id <> hr_jax.id
order by distance;

我们可以从我们的输出中看到，我们现在有了每个地址到 Hashrocket Jacksonville 办公室的距离（以米为单位）。如果我们想以英里为单位，我们可以对 earth_distance 进行快速除法，使用米到英里的转换值，即 1609.344。

select name, earth_distance(
  ll_to_earth(a.latitude, a.longitude),
  ll_to_earth(hr_jax.latitude, hr_jax.longitude)
) / 1609.344 as distance
from addresses a,
lateral (
  select id, latitude, longitude from addresses where name = 'Hashrocket JAX'
) as hr_jax
where a.id <> hr_jax.id
order by distance;

要仅查找杰克逊维尔办公室一定距离内的位置，我们可以对现有查询进行快速修改。由于我们已经知道如何获取两个位置之间的地球距离，我们可以重用相同的逻辑，现在将其作为 where 子句的一部分传递，并在不等式中使用它来检查距离是否小于特定值，例如100 英里，这意味着我们可以从之前的转换中移动小数点。

select name, earth_distance(
  ll_to_earth(a.latitude, a.longitude),
  ll_to_earth(hr_jax.latitude, hr_jax.longitude)
) / 1609.344 as distance
from addresses a,
lateral (
  select id, latitude, longitude from addresses where name = 'Hashrocket JAX'
) as hr_jax
where a.id <> hr_jax.id
and earth_distance(
  ll_to_earth(a.latitude, a.longitude),
  ll_to_earth(hr_jax.latitude, hr_jax.longitude)
) < 160934.4
order by distance;

通过这种更改，我们可以从输出中看到，我们现在只包括距离我们的 Hashrocket Jacksonville 地址不到 100 英里的地址。

重要的是要记住 Postgres earthdistance 模块假设地球是一个完美的球体，这并不完全准确。如果您在处理地理位置时需要极高的准确性，Postgres 建议您考虑使用 PostGIS 扩展。

感谢收看！

设置

create table addresses (
  id serial primary key,
  name varchar,
  street_address varchar,
  city varchar,
  state varchar,
  zip varchar,
  latitude float8,
  longitude float8
);

insert into addresses (name, street_address, city, state, zip, longitude, latitude)
values ('Hashrocket JAX', '320 1st St N', 'Jacksonville Beach', 'FL', '32250', '-81.3927381' ,'30.2918842'),
('Hashrocket Chicago', '661 W Lake St', 'Chicago', 'IL', '60661', '-87.6473133', '41.8853881'),
('Satchel''s Pizza', '1800 NE 23rd Ave', 'Gainesville', 'FL', '32609', '-82.3018702', '29.6739466'),
('V Pizza', '528 1st St N', 'Jacksonville Beach', 'FL', '32250', '-81.3905175', '30.2938423'),
('Artichoke Pizza', '321 E 14th St', 'New York', 'NY', '10003', '-73.9860525', '40.7321652'),
('Giordano''s', '130 E Randolph St', 'Chicago', 'IL', '60601', '-87.6252984', '41.8850284');