数据库：查询地理位置数据的最佳性能方式？ [英] Database: Best performance way to query geo location data?

问题描述

我有一个MySQL数据库。我将数据库中的家庭存储在数据库中，并且只对数据库执行一次查询， 但我需要此查询才能执行超快 box geo latitude&经度。

I have a MySQL database. I store homes in the database and perform literally just 1 query against the database, but I need this query to be performed super fast, and that's to return all homes within a square box geo latitude & longitude.

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

如何最好的方式来存储我的地理数据，以便我可以执行这个查询

How is the best way for me to store my geo data so that I can perform this query of displaying all home within the geolocation box the quickest?

基本上：

• 我是使用最好的SQL语句最快地执行此查询？


• 有任何其他方法存在，甚至不使用数据库，我可以查询最快的方式地理位置边界？

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

In case it helps, I've include my database table schema below:

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  ;

UPDATE

我明白空间会在地球的曲率因素，但我最感兴趣的返回地理数据的FASTEST。除非这些空间数据库包以某种方式更快地返回数据，否则请不要推荐空间扩展。感谢

I understand spatial will factor in the curvature of the earth but I'm most interested in returning geo data the FASTEST. Unless these spatial database packages somehow return data faster, please don't recommend spatial extensions. Thanks

UPDATE 2

请注意，以下没有人真正回答题。我真的期待我可能收到的任何帮助。提前感谢。

Please note, no one below has truly answered the question. I'm really looking forward to any assistance I might receive. Thanks in advance.

推荐答案

有关MySQL地理位置效果的一篇好文章此处。

There is a good paper on MySQL geolocation performance here.

相当肯定这是使用固定半径。另外，我不是100％肯定计算距离的算法是最先进的（即它会钻通过地球）。

EDIT Pretty sure this is using fixed radius. Also I am not 100% certain the algorithm for calculating distance is the most advanced (i.e. it'll "drill" through Earth).

重要的是，算法便宜给你一个球公园限制行数做适当的距离搜索。

What's significant is that the algorithm is cheap to give you a ball park limit on the number of rows to do proper distance search.

这篇关于数据库：查询地理位置数据的最佳性能方式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！