MySQL中给定半径内的查询点

发布于 2024-08-24 16:00:13 字数 469 浏览 10 评论 0原文

我创建了以下 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 万个点，因此这种需求需要尽可能高效。

原文

I have created the following MySQL table to store latitude/longitude coordinates along with a name for each point:

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;

I am trying to query:

all points within an n mile radius of a given point;
the distance of each returned point from the given point

All of the examples I have found refer to using a minimum bounding rectangle (MBR) rather than a radius. The table contains approximately 1 million points, so this need needs to be as efficient as possible.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

念三年u 2024-08-31 16:00:13

对于 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)，那么这是正确的顺序，并且也是大多数函数（如点）接受参数

注 2 的顺序：您实际上无法利用空间如果要使用圆圈，则使用索引；另请注意，点字段可以设置为接受 null，但如果它可以为 null，则空间索引无法对其进行索引（索引中的所有字段都必须为非 null）。

注释 3：st_buffer 被认为（根据文档）不适合此用例

注释 4：上面的函数（特别是 st_distance_sphere）被记录为快速但不一定超级准确；如果您的数据对此非常敏感，请为搜索添加一些回旋空间并对结果集进行一些微调

For MySQL 5.7+

Given we have the following simple table,

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

create spatial index example_lnglat 
    on example (lnglat);

With the following simple data,

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));

You would get the points within a given range of another point (note: we have to search inside a polygon) with the following combination of st functions:

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);

You should see something like this as a result:

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

For reference on distance, if we remove the constraint the result for the test point looks like this:

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

Note 1: the field is called lnglat since that's the correct order if you think of points as (x, y) and is also the order most functions (like point) accept the parameter

Note 2: you can't actually take advantage of spatial indexes if you were to use circles; also note that the point field can be set to accept null but spatial indexes can't index it if it's nullable (all fields in the index are required to be non-null).

Note 3: st_buffer is considered (by the documentation) to be bad for this use case

Note 4: the functions above (in particular st_distance_sphere) are documented as fast but not necessarily super accurate; if your data is super sensitive to that add a bit of wiggle room to the search and do some fine tuning to the result set

回复收藏 0 原文

王权女流氓 2024-08-31 16:00:13

半径不能有效地转位。您应该使用边界矩形来快速获取您可能正在寻找的点，然后过滤半径之外的点。

回复收藏 0 原文

蒲公英的约定 2024-08-31 16:00:13

我对半径为圆内的一个点执行了此操作

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

I did that for one point inside the circle with radius

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

回复收藏 0 原文

~没有更多了~

关于作者

憧憬巴黎街头的黎明

暂无简介

0 文章

0 评论

24 人气

关注发私信

友情链接

文江博客

MySQL中给定半径内的查询点

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（3）

关于作者

相关话题

热门标签

推荐作者

小瓶盖

wxsp_Ukbq8xGR

1638627670

仅一夜美梦

夜访吸血鬼

近卫軍团

友情链接

MySQL中给定半径内的查询点

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（3）

关于作者

相关话题

热门标签

推荐作者

小瓶盖

wxsp_Ukbq8xGR

1638627670

仅一夜美梦

夜访吸血鬼

近卫軍团

友情链接

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。