地理位置MySQL查询

Question

地理位置MySQL查询

3

我是一名基于地理位置的社交网络翻译。会员可以根据他们之间的距离看到其他会员。

现在我的MySQL查询看起来像：

$lat_min = $geo['user_lat'] - 5;
$lat_max = $geo['user_lat'] + 5;
$long_min = $geo['user_long'] - 5;
$long_max = $geo['user_long'] + 5;    

$members_query = "SELECT " . $fields . " FROM members WHERE (user_lat BETWEEN " . $lat_min . " AND " . $lat_max . " AND user_long BETWEEN " . $long_min . " AND " . $long_max . ") OR (gc_lat BETWEEN " . $lat_min . " AND " . $lat_max . " AND gc_long BETWEEN " . $long_min . " AND " . $long_max . ")";

user_lat 和 user_long 是基于浏览器中的地理位置信息确定的坐标。而gc_lat 和 gc_long 是基于用户IP地址确定的坐标。这些行都被索引到了数据库中。我正在检索所有在10度范围内的成员。

问题在于，对于我们的250,000多名成员，这个查询需要约2秒钟的时间，并且我们希望网站能够扩展。 尝试2： 我尝试为每个成员分配象限，例如“36x-99”... 我将纬度和经度四舍五入到最接近3的倍数来标记象限，然后仅检索与成员所在象限相差12度以内的象限。

$members_query = "SELECT " . $fields . " FROM members WHERE quadrant IN ('36x-99', '33x-99', '30x-99', ...);

这对查询速度没有显著的影响。

有人有什么想法吗？我需要找到一个能够使网站更好扩展的解决方案。

- Ben Buras

可能是快速查找两个经纬度点之间距离的最快方法的重复问题。 - Giacomo1968

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Giacomo1968 · Accepted Answer

问题在于你存储在数据库中的数据方式不适合你正在执行的任务类型。使用Geometry数据点中的Point值是正确的方法。实际上，我4年前编写了一些与此相关的代码，但我现在找不到它。但这个帖子似乎涵盖得很全面。（链接）编辑好的，我找到了我的旧代码，但它正在引用我显然无法分享的旧客户端数据。但在数据库表中使用存储为GEOMETRY类型的POINT数据是加快坐标处理速度的关键。在MySQL官方网站（链接）上有更多详细信息。因为我需要一个理由重新访问这种代码和概念已经有一段时间了，所以我想出了一个快速创建一个含有样本数据的表的MySQL脚本来传达基本概念。一旦你理解了发生了什么，就会开启大量很酷的选择。

还找到了这个关于概念的好/简单解释。

在MySQL 5.6中，另外发现了对空间数据进行评估的另一个很好的文章。有关索引和性能的许多重要信息。特别是关于MySQL空间索引性能：

MyISAM表支持空间索引，因此上述查询将使用这些索引。

在另一方面：

InnoDB引擎不支持空间索引，因此这些查询将变得缓慢。

以下是我用来帮助说明这一概念的基本MySQL测试脚本：

/* Create the database `spatial_test` */
CREATE DATABASE `spatial_test` CHARACTER SET utf8 COLLATE utf8_general_ci;

/* Create the table `locations` in `spatial_test` */
CREATE TABLE `spatial_test`.`locations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `coordinates` point NOT NULL,
  UNIQUE KEY `id` (`id`),
  SPATIAL KEY `idx_coordinates` (`coordinates`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

/* Insert some test data into it. */
INSERT INTO `spatial_test`.`locations` (`id`, `coordinates`) VALUES (NULL, GeomFromText('POINT(27.174961 78.041822)'));
INSERT INTO `spatial_test`.`locations` (`id`, `coordinates`) VALUES (NULL, GeomFromText('POINT(27.985818 86.923596)'));
INSERT INTO `spatial_test`.`locations` (`id`, `coordinates`) VALUES (NULL, GeomFromText('POINT(44.427963 -110.588455)'));
INSERT INTO `spatial_test`.`locations` (`id`, `coordinates`) VALUES (NULL, GeomFromText('POINT(19.896766 -155.582782)'));
INSERT INTO `spatial_test`.`locations` (`id`, `coordinates`) VALUES (NULL, GeomFromText('POINT(40.748328 -73.985560)'));
INSERT INTO `spatial_test`.`locations` (`id`, `coordinates`) VALUES (NULL, GeomFromText('POINT(40.782710 -73.965310)'));

/* A sample SELECT query that extracts the 'latitude' & 'longitude' */
SELECT x(`spatial_test`.`locations`.`coordinates`) AS latitude, y(`spatial_test`.`locations`.`coordinates`) AS longitude FROM `spatial_test`.`locations`;

/* Another sample SELECT query calculates distance of all items in database based on GLength using another set of coordinates. */
SELECT GLength(LineStringFromWKB(LineString(GeomFromText(astext(PointFromWKB(`spatial_test`.`locations`.`coordinates`))), GeomFromText(astext(PointFromWKB(POINT(40.782710,-73.965310))))))) AS distance
FROM `spatial_test`.`locations`
;

/* Yet another sample SELECT query that selects items by using the Earth’s radius. The 'HAVING distance < 100' equates to a distance of less than 100 miles or kilometers based on what you set the query for. */
/* Earth’s diameter in kilometers: 6371 */
/* Earth’s diameter in miles: 3959 */
SELECT id, (3959 * acos(cos(radians(40.782710)) * cos(radians(x(`spatial_test`.`locations`.`coordinates`))) * cos(radians(y(`spatial_test`.`locations`.`coordinates`)) - radians(-73.965310)) + sin(radians(40.782710)) * sin(radians(x(`spatial_test`.`locations`.`coordinates`))))) AS distance 
FROM `spatial_test`.`locations`
HAVING distance < 100
ORDER BY id
;