数据库：查询地理位置数据的最佳性能方法？

Question

我有一个MySQL数据库。我将住宅存储在数据库中，对该数据库执行了仅1个查询，但我需要此查询能够超快速地执行，即返回所有在一个正方形地理纬度和经度框内的住宅。

SELECT * FROM homes 
WHERE geolat BETWEEN ??? AND ???
AND geolng BETWEEN ??? AND ???

我应该如何存储我的地理数据，以便可以在最短时间内执行此查询并显示所有位于地理位置框内的住宅?

基本上：

• 我是否正在使用最佳SQL语句以最快速度执行此查询？
• 是否存在任何其他方法，甚至不使用数据库，让我以最快的方式查询位于框定地理位置范围内的住宅结果？

如果有帮助的话，我在下面包含了我的数据库表模式：

CREATE TABLE IF NOT EXISTS `homes` (
  `home_id` int(10) unsigned NOT NULL auto_increment,
  `address` varchar(128) collate utf8_unicode_ci NOT NULL,
  `city` varchar(64) collate utf8_unicode_ci NOT NULL,
  `state` varchar(2) collate utf8_unicode_ci NOT NULL,
  `zip` mediumint(8) unsigned NOT NULL,
  `price` mediumint(8) unsigned NOT NULL,
  `sqft` smallint(5) unsigned NOT NULL,
  `year_built` smallint(5) unsigned NOT NULL,
  `geolat` decimal(10,6) default NULL,
  `geolng` decimal(10,6) default NULL,
  PRIMARY KEY  (`home_id`),
  KEY `geolat` (`geolat`),
  KEY `geolng` (`geolng`),
) ENGINE=InnoDB  ;

更新

我知道空间数据会考虑到地球的曲率，但我最关心的是返回最快的地理数据。除非这些空间数据库包可以更快地返回数据，请不要推荐空间扩展。谢谢。

更新2

请注意，下面没有人真正回答了我的问题。我非常期待任何帮助。提前感谢。

- HankW

---

我还建议阅读有关MySQL空间功能的内容：http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html - OMG Ponies

你说的“数据不好”是什么意思？我的应用程序通常只查看3英里乘以3英里范围内的数据。因此，地球的曲率并没有太大影响。 - HankW

所有的空间查询并不一定更快。我正在使用InnoDB。根据文档，“在MySQL 5.0.16之前，InnoDB表不支持空间数据类型。从5.0.16开始，InnoDB支持空间数据类型，但不支持对其进行索引。” http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html 为什么你还在推荐SPATIAL呢？你提供给我阅读的文档明确指出，在InnoDB数据库中，如果没有索引，查询会变慢。再次强调，我的问题是如何执行最快的地理数据查询？ - HankW

我不想听起来很无礼，但当你声称我没有阅读你链接的文档，并且当我阅读它时，它明确地说明了你所声称的相反情况，这让我感到沮丧。这让我想知道，你自己是否在阅读你所链接的文档？ - HankW

除非感兴趣的区域不超过大约6度经度宽并且最好只在赤道的一侧，否则UTM会很笨拙。如果该区域比此更宽，则需要指定一个区域，跨越区域边界的坐标将是不连续的。在赤道上，y坐标从北方接近零，但从南方接近10000000。对于纬度和经度都很广泛的区域，最简单的坐标系统是纬度和经度。您只需接受球面坐标带来的问题即可。 - Mark Thornton

Answer 1

这里有一篇关于MySQL地理定位性能的好文章（链接）。

编辑 我相当确定这是使用固定半径。此外，我并不100％确定计算距离的算法是否最先进（即它会“穿过”地球）。

重要的是该算法便宜实用，可以让你快速得到一个合理的距离搜索行数的范围。

---

该算法通过在源点周围的正方形中选择候选对象进行预筛选，然后以英里为单位计算距离。

您可以预先计算这个值，或按照源建议使用存储过程。

# Pseudo code
# user_lon and user_lat are the source longitude and latitude
# radius is the radius where you want to search
lon_distance = radius / abs(cos(radians(user_lat))*69);
min_lon = user_lon - lon_distance;
max_lon = user_lon + lon_distance;
min_lat = user_lat - (radius / 69);
max_lat = user_lat + (radius / 69);

SELECT dest.*,
  3956 * 2 * ASIN(
    SQRT(
      POWER(
        SIN(
          (user_lat - dest.lat) * pi() / 180 / 2
        ), 2
      ) + COS(
        user_lat * pi() / 180
      ) * COS(
        dest.lat * pi() / 180
      ) * POWER(
        SIN(
          (user_lon - dest.lon) * pi() / 180 / 2
        ), 2
      )
    )
  ) as distance
FROM dest
WHERE 
  dest.lon between min_lon and max_lon AND
  dest.lat between min_lat and max_lat
HAVING distance < radius
ORDER BY distance
LIMIT 10

- Igor Zevaka

---

看起来在第14页上使用存储过程是有前途的，但我不确定它是否假定了一个固定的半径。你知道半径是固定的还是可变的吗？我想能够传入盒子角（半径）。 - HankW

我需要能够将盒装半径作为参数传递。您认为我可以像这样使用链接文档吗？ - HankW

Answer 2

我曾经遇到过同样的问题，写了一个由三部分组成的博客文章。这比地理索引更快。

介绍，基准测试，SQL

- Evert

---

Evert，你是如何实现Morton（z-value）的？你的第二篇文章只是简单地介绍了一下，没有说你是如何计算这个值的。 - HankW

第三个确实有。有一个存储过程。 - Evert

这就是为什么 Stack Overflow 的答案应该包含相关的引用...链接可能已经失效。 - Stijn de Witt

批评仍然有一定道理; 你应该引用你的帖子中的几个相关部分。至少提到Morton Number或其他内容，这样如果/当那些链接再次失效时，我们至少可以搜索到信息。 - Stijn de Witt

我不反对，但我也不太在意。这个答案是从'09年的。 - Evert

Answer 3

如果你真的需要追求性能，可以为数据定义边界框，并在插入时将预计算的边界框映射到对象上，以便稍后用于查询。

如果结果集比较小，你仍然可以在应用逻辑中进行精度校正（比在数据库中更容易水平扩展），同时保证提供准确的结果。

可以查看布雷特·斯拉金的geobox.py，其中包含了这种方法的详细文档。

如果你打算在可预见的未来进行更复杂的查询，我仍然建议与MySQL相比，先了解PostgreSQL和PostGIS。

- tosh

---

这正是为什么我们不应该在StackOverflow上使用链接的原因。你的链接已经失效了。 - user1967599

@Sandor 谢谢你让我知道，我已经修改了答案并删除了失效的链接。 - tosh

Answer 4

一个非常好的选择是MongoDB，它具有地理空间索引。

- jalogar

Answer 5

这里有一个技巧，我曾经用过并且取得了一些成功，就是创建舍入区域。也就是说，如果您有一个位于36.12345，-120.54321的位置，并且您想将其与其他在半英里（大约）网格框内的位置分组，则可以称其区域为36.12x-120.54，并且所有具有相同舍入区域的其他位置都将落入同一框中。

显然，这不会给您带来干净的半径，即如果您查看的位置比另一个位置更靠近边缘。然而，有了这种设置，很容易计算出围绕主要位置框的八个框。 例如：

[36.13x-120.55][36.13x-120.54][36.13x-120.53]
[36.12x-120.55][36.12x-120.54][36.12x-120.53]
[36.11x-120.55][36.11x-120.54][36.11x-120.53]

从数据库中提取所有带有匹配的舍入标签的位置，然后进行距离计算以确定要使用哪些位置。

- Ben

Answer 6

如果您坚持使用当前的方法，有一个更改建议您进行， 不要单独索引geolat和geolong，而应该使用组合索引：

KEY `geolat_geolng` (`geolat`, `geolng`),

目前您的查询只能利用两个索引中的一个。

- Ben

Answer 7

您正在使用的索引确实是B树索引，并支持查询中的BETWEEN关键字。这意味着优化器能够使用您的索引来查找在“盒子”内的房屋。但这并不意味着它总是会使用这些索引。如果您指定的范围包含太多的“命中”，则不会使用这些索引。

- Peter Lindqvist

---

那么，使用 min_latitude >= ??? max_latitude <= ??? 代替使用 BETWEEN 是否更好？ - HankW

从手册中：这相当于表达式（min <= expr AND expr <= max） - Peter Lindqvist

如果有太多的“命中”，索引将不会被使用，这是什么意思？我不理解。 - HankW

如果您指定的区域包含太多记录，则索引将不会被使用。 - Peter Lindqvist

Answer 8

自 MySQL 5.7 起，mysql 可以使用像 ST_Distance_Sphere() 和 ST_Contains() 这样的地理索引来提高性能。

- Anak1

Answer 9

你可以考虑创建一个名为“GeoLocations”的单独表，它具有（'geolat'，'geolng'）的主键，并具有一个列，如果该特定地理位置有家，则保存home_id。这样应该可以让优化器搜索一系列地理位置，这些位置将在磁盘上排序以获取home_ids列表。然后，您可以使用'homes'表来执行连接以查找有关这些home_ids的信息。

CREATE TABLE IF NOT EXISTS `GeoLocations` (
`geolat` decimal(10,6) NOT NULL,
`geolng` decimal(10,6) NOT NULL,
`home_id` int(10) NULL
PRIMARY KEY  (`geolat`,`geolng`)
);

SELECT GL.home_id
FROM GeoLocations GL
INNER JOIN Homes H
 ON GL.home_id = H.home_id
WHERE GL.geolat between X and Y
 and GL.geolng between X and Y

- Clayton Stewart

Answer 10

这看起来相当快。我唯一的担心是它会使用一个索引来获取所有距离纬度3英里以内的值，然后过滤那些距离经度3英里以内的值。如果我了解底层系统的工作原理，您只能在每个表上使用一个INDEX，因此lat或long上的索引都是无用的。

如果您有大量数据，将每个1x1英里的正方形赋予唯一的逻辑ID，然后对SELECT进行附加限制(area="23234/34234" OR area="23235/34234" OR ...)，以便选择您所在点周围的所有正方形，然后强制数据库使用该索引而不是lat和long。然后，您只需要过滤更少的平方英里的数据。

- Christopher Gutteridge

---

一张表一个索引？你把它和主键搞混了吗？ - Peter Lindqvist

我的意思是，当您执行SELECT时，它仅在SELECT中使用每个表的一个索引。 - Christopher Gutteridge

啊.. 这是个好点子，但你认为创建一个复合索引会有所不同吗？ - Peter Lindqvist

一个（更复杂的）组合索引就是空间索引所做的，如果数据很多，它会更快。 - Mark Thornton