在MySQL中查询给定半径内的点

Question

我创建了以下MySQL表来存储每个点的纬度/经度坐标以及名称：

CREATE TABLE `points` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL,
  `location` point NOT NULL,
  PRIMARY KEY (`id`),
  SPATIAL KEY `location` (`location`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

我想查询：

• 距离给定点n英里半径内的所有点；
• 返回的每个点距离给定点的距离。

我找到的所有示例都是使用最小外接矩形（MBR）而不是半径。表格包含大约100万个点，因此需要尽可能高效。

- gjb

---

我最终在 http://www.movable-type.co.uk/scripts/latlong-db.html 找到了解决方案。 - gjb

Answer 1

针对MySQL 5.7+

假设我们有以下简单的表格：

create table example (
  id bigint not null auto_increment primary key,
  lnglat point not null
);

create spatial index example_lnglat 
    on example (lnglat);

使用以下简单数据，

insert into example (lnglat) 
values
(point(-2.990435, 53.409246)),
(point(-2.990037, 53.409471)),
(point(-2.989736, 53.409676)),
(point(-2.989554, 53.409797)),
(point(-2.989350, 53.409906)),
(point(-2.989178, 53.410085)),
(point(-2.988739, 53.410309)),
(point(-2.985874, 53.412656)),
(point(-2.758019, 53.635928));

使用以下st函数的组合，您可以在另一点的给定范围内获取积分（注意：我们必须在多边形内搜索）：

set @px = -2.990497;
set @py = 53.410943;
set @range = 150; -- meters
set @rangeKm = @range / 1000;

set @search_area = st_makeEnvelope (
  point((@px + @rangeKm / 111), (@py + @rangeKm / 111)),
  point((@px - @rangeKm / 111), (@py - @rangeKm / 111))
);

select id, 
       st_x(lnglat) lng, 
       st_y(lnglat) lat,
       st_distance_sphere(point(@px, @py), lnglat) as distance
  from example
 where st_contains(@search_area, lnglat);

你应该会看到类似这样的结果：

3   -2.989736   53.409676   149.64084252776277
4   -2.989554   53.409797   141.93232714661812
5   -2.98935    53.409906   138.11516275402533
6   -2.989178   53.410085   129.40289289527473

距离参考，如果我们删除约束条件，则测试点的结果如下：

1   -2.990435   53.409246   188.7421181457556
2   -2.990037   53.409471   166.49406509160158
3   -2.989736   53.409676   149.64084252776277
4   -2.989554   53.409797   141.93232714661812
5   -2.98935    53.409906   138.11516275402533
6   -2.989178   53.410085   129.40289289527473
7   -2.988739   53.410309   136.1875540498202
8   -2.985874   53.412656   360.78532732013963
9   -2.758019   53.635928   29360.27797292756

---

注意1：该字段称为lnglat，因为如果将点视为（x，y），那么这是正确的顺序，并且大多数函数（如point）都接受该参数。

注意2：如果使用圆形，则实际上无法利用空间索引；此外，请注意，点字段可以设置为可接受null，但如果可空，则空间索引无法对其进行索引（索引中的所有字段均需要为非null）。

注意3：文档认为st_buffer在此用例中不好。

注意4：上述函数（特别是st_distance_sphere）被记录为快速但不一定超级准确。如果您的数据对此非常敏感，请在搜索中添加一些余地并对结果集进行微调。

- srcspider

---

也许我误解了，但是使用st_contains，这仍然会在笛卡尔平面内进行计算吗？而不是所需的球形地球？ - Twig

你好，我正在使用你上面的代码，我有一个问题，@rangeKm / 111是什么意思？111代表什么？ - Ben Osborne

@BenOsborne 每个纬度度数大约有111公里。但那不是很精确的，我不建议这样做。该值从赤道到极地会发生变化。因此，如果您在非常大的半径内搜索，例如2000公里，则结果将会非常不准确。 - Roman

Answer 2

半径并不是有效的可索引项。您应该使用边界矩形快速获取可能正在查找的点，然后过滤掉半径外的点。

- Lukáš Lalinský

---

感谢您的回答。 半径索引限制只是 MySQL 中的问题吗？我在想 PostgreSQL 是否更可行？ 如何从边界矩形中剔除不在半径内的点？ - gjb

不，那是一个普遍的问题。PostgreSQL 使这更容易，因为您可以明确地询问点是否包含在圆内，并且这将尽可能使用索引，但我相信它也会首先仅使用矩形搜索。我看不到任何 MySQL 函数可以做到这一点，但您可以简单地计算中心和点之间的距离。 - Lukáš Lalinský

Answer 3

我在圆内某一点上做了这件事，该圆的半径为

SELECT 
    *
FROM 
    `locator`
WHERE
    SQRT(POW(X(`center`) - 49.843317 , 2) + POW(Y(`center`) - 24.026642, 2)) * 100 < `radius`

- dexxtr