地理位置MySQL查询 [英] Geolocation MySQL Query

问题描述

我运行了一个基于地理位置的社交网络。成员可以看到其他成员基于他们有多接近。

现在我的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。 $字段。 从成员WHERE（user_lat间。$ lat_min。和。$ lat_max。和user_long间。$ long_min。和。$ long_max。）或（gc_lat间）。$ lat_min。AND。 $ lat_max。AND gc_long BETWEEN。$ long_min。AND。$ long_max。）; 
  

user_lat 和 user_long 是基于地理位置的坐标，如果它们在浏览器中打开的话。 gc_lat 和 gc_long 以及基于IP地址的坐标。这些行都在数据库中编入索引。我将所有成员都拉到10度以内。

问题是这个查询大约需要2秒钟才能完成，我们希望网站能够扩展。 / p>

ATTEMPT 2：我曾尝试为每个成员分配象限，例如36x-99...我将经纬度四舍五入到最接近的3的倍数以标记象限，然后我只在成员所在象限的12度范围内拉出象限。

  $ members_query =SELECT。 $字段。 FROM成员在象限IN（'36x-99'，'33x-99'，'30x-99'，...）; 
  

这让我在查询速度方面没有明显的不同。

任何人有任何想法我应该做什么？我需要找到解决方案这将使网站的规模更好。

解决方案

问题是你在数据库中存储数据的方式不适合您正在执行的任务的类型，在几何数据点中使用 Point 值是实际的方法。 4年以上，为此目的，但有问题的发现。但似乎覆盖了它。

编辑好的，找到了我的旧代码，但它指的是旧客户端数据显然不能共享，但在数据库中使用坐标加速的关键是使用 PO INT 数据库中存储的数据类型为 GEOMETRY 。 官方MySQL网站上的更多详细信息 。由于我需要重新审视这种类型的代码的原因以及概念，这里有一段很快的MySQL脚本，我创建了一个带有示例数据的示例表来表达基本概念。一旦你明白发生了什么，它会打开很多很酷的选项。

还发现。

然后找到 MySQL 5.6中对空间数据的另一个重要评估。很多有关索引和信息的详细信息性能。特别是关于MySQL空间索引性能：
$ b

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

另一方面：

InnoDB引擎不支持空间索引，因此这些查询将会很慢。

下面是我的基本MySQL测试脚本来帮助说明这个概念：

$ $ $ $ $ $ $ $ $创建数据库`spatial_test` * /
CREATE DATABASE`spatial_test` CHARACTER SET utf8 COLLATE utf8_general_ci;
$ b $ *在`spatial_test`中创建位置表* /
CREATE TABLE`spatial_test`.`locations`（
`id` int（11）NOT NULL AUTO_INCREMENT ，
`坐标点NOT NULL，
UNIQUE KEY`id`（`id`），
SPATIAL KEY` idx_coordinates`（`coordinates`）
）ENGINE = MyISAM DEFAULT CHARSET = utf8 AUTO_INCREMENT = 1;

/ *在其中插入一些测试数据。 * /
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）'））;

/ *提取'纬度'和' 'longitude'* /
SELECT x（`spatial_test`.`locations`.`coordinates`）AS latitude，y（`spatial_test`.`locations`.`coordinates`）AS longitude FROM`spatial_test`.locations `;

/ *另一个示例SELECT查询使用另一组坐标计算基于GLength的数据库中所有项目的距离。 * /
SELECT GLength（LineStringFromWKB（LineString（GeomFromText（astext（PointFromWKB（`spatial_test`.`locations`.`coordinates`））），GeomFromText（astext（PointFromWKB（POINT（40.782710，-73.965310）））） ）））AS距离
FROM`spatial_test`.`locations`
;

/ *另一个示例SELECT查询通过使用地球的半径来选择项目。 'HAVING距离< 100'等于根据您设置查询的距离少于100英里或千米。 * /
/ *地球直径以公里为单位：6371 * /
/ *地球直径（英里）：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（弧度（40.782710））* sin（radians（x（`spatial_test`.`locations`.`coordinates`）））））AS距离
FROM`spatial_test`.`locations`
HAVING distance< 100
ORDER BY id
;

I run a geolocation-based social network. Members can see other members based on how close they are.

Right now my MySQL query looks like:

$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 and user_long are coordinates based on geolocation if they have it turned on in their browser. gc_lat and gc_long and coordinates based on their IP address. These rows are all indexed in the database. I am pulling all members within 10 degrees.

The problem is this query takes about 2 seconds to perform for our 250,000+ members, and we want the site to scale.

ATTEMPT 2: I have tried assigning quadrants to each member e.g. "36x-99" ... I am rounding off the latitude and longitude to the nearest multiple of 3 to label the quadrant, and then I only pull quadrants within 12 degrees of the quadrant the member is in.

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

This gave me no noticeable difference in query speed.

Anyone have any ideas of what I should do? I need to find a solution that will allow the site to scale better.

解决方案

The problem is the way you are storing data in the database is not suited for the type of task you are performing. Using Point values in Geometry data points is the way to go. Actually coded something 4+ years back for this purpose, but having issues finding it. But this post seems to cover it well.

EDIT Okay, found my old code, but it’s referring to old client data that I obviously cannot share. But the key to speed with coordinates in databases is using POINT data stored in the database table with the type of GEOMETRY. More details here on the official MySQL site. Since I have needed a reason to revisit this type of code—and the concepts—for a while here is a quick MySQL script I whipped up to create a sample table with sample data to convey the basic concepts. Once you understand what is happening, it opens up lots of cool options.

Also found this great/simple explanation of the concept as well.

And found another great assessment of spatial data in MySQL 5.6. Lots of great info on indexes & performance. Specifically regarding MySQL spatial index performance:

MyISAM tables support Spatial indexes, so the above queries will use those indexes.

And on the other side of that:

The InnoDB engine does not support spatial indexes, so those queries will be slow.

And here is my basic MySQL testing scripts to help illustrate the concept:

/* 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
;

这篇关于地理位置MySQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！