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

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

我创建了以下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;

I am trying to query:

我想问一下:

all points within an n mile radius of a given point;
在给定点半径n英里内的所有点;
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.

我发现的所有示例都使用最小边界矩形(MBR)而不是半径。该表包含大约100万点，因此需要尽可能地提高效率。

4 个解决方案

#1

Thank you both for your answers.

谢谢你们的回答。

I eventually found the solution at http://www.movable-type.co.uk/scripts/latlong-db.html.

最终，我在http://www.movabletype.co.uk/scripts/latdb.html找到了解决方案。

#2

For MySQL 5.7+

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:

你会得到一个给定范围内的点(注:我们必须在一个多边形中搜索)和下列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);

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

注意1:该字段被称为lnglat，因为如果您将点看作(x, y)，那么它就是正确的顺序，而且它也是大多数函数(如点)接受参数的顺序

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

注2:如果你使用圆圈，你实际上不能利用空间索引;还要注意，可以将点字段设置为接受null，但如果它是空的，空间索引就不能索引它(索引中的所有字段都必须是非空的)。

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

注意3:st_buffer(文档)被认为对这个用例是不好的

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

注意4:上面的函数(特别是st_distance_sphere)被记录为速度快，但不一定超级准确;如果您的数据对这一点非常敏感，那么在搜索中添加一些回旋余地，并对结果集进行一些微调

#3

Radius is not efficiently indexable. You should use the bounding rectangle to quickly get the points you are probably looking for, and then filter points outside of the radius.

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

#4

I did that for one point inside the circle with radius

我在半径为1的圆内做了这个

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

details and one more sample query here http://dexxtr.com/post/83498801191/how-to-determine-point-inside-circle-using-mysql, hope this helps

在http://dexxtr.com/post/83498801191/how-to- resolve -point-inside- circle-use -mysql中还有一个示例查询，希望这能有所帮助

#1