mysql实现地理位置搜索_MySQL

来源：懂视网责编：小采时间：2020-11-09 19:29:28

mysql实现地理位置搜索_MySQL

mysql实现地理位置搜索_MySQL:随着LBS应用的遍地开花,在数据库中实现基于地理位置的搜索显得尤为重要.今天研究了下,顺便做个小结.首先设计好一个简单的数据表,用来存放经纬度信息:CREATE TABLE `index` ( `id` int(11) NOT NULL AUTO_INCREMENT, `lat` double

推荐度：

点击下载本文 文档为doc格式

导读mysql实现地理位置搜索_MySQL:随着LBS应用的遍地开花,在数据库中实现基于地理位置的搜索显得尤为重要.今天研究了下,顺便做个小结.首先设计好一个简单的数据表,用来存放经纬度信息:CREATE TABLE `index` ( `id` int(11) NOT NULL AUTO_INCREMENT, `lat` double

随着LBS应用的遍地开花,在数据库中实现基于地理位置的搜索显得尤为重要.今天研究了下,顺便做个小结.

首先设计好一个简单的数据表,用来存放经纬度信息:

CREATE TABLE `index` ( `id` int(11) NOT NULL AUTO_INCREMENT, `lat` double NOT NULL, `lng` double NOT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;

创建完成后我们可以查看一下,应该是这个样子

mysql> desc `index`;+-------+---------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+---------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || lat | double | NO | | NULL | || lng | double | NO | | NULL | |+-------+---------+------+-----+---------+----------------+3 rows in set (0.00 sec)

接着我们来制造点儿数据,便于等下测试,写了个python脚本来实现:

import MySQLdbimport randomtry: conn=MySQLdb.connect(host='localhost',user='eslizn',passwd='123456',db='geo',port=3306) cur=conn.cursor() for i in range(2000000): lat = random.randint(-9000000,9000000)/100000.0 lng = random.randint(-18000000,18000000)/100000.0 sql = "insert into `index` (`lat`,`lng`) values (%f,%f)" % (lat,lng) cur.execute(sql) print "[%d]%s" % (i,sql) cur.close() conn.close()except MySQLdb.Error,e: print "Mysql Error %d: %s" % (e.args[0], e.args[1])

为了便于等下测试添加索引和没有添加索引的效果,还需要复制一份表出来做对照:

mysql> create table unindex select * from `index`;Query OK, 2000838 rows affected (0.93 sec)Records: 2000838 Duplicates: 0 Warnings: 0

对index表的lat,lng字段设置一个B-tree索引:

mysql> ALTER TABLE `index` ADD INDEX `lat_lng` USING BTREE (`lat`, `lng`) ;Query OK, 2000838 rows affected (10.94 sec)Records: 2000838 Duplicates: 0 Warnings: 0

根据两点的经纬度计算其距离以前也做过,不过毕竟图样,直接就拿平面上的那一套弄上了,这样简直就是大错特错,首先,虽然纬度转换成距离是乘以一个常量,但是计算经度的距离则是需要通过三角函数来计算,具体计算公式如下:

R = earth’s radiusΔlat = lat2 lat1Δlng = lng2 lng1a = sin(Δlat/2) + cos(lat1) * cos(lat2) * sin(Δlng/2)c = 2*atan2(√a, √(1a))dist = R*c

根据公式编写Sql查询语句:

mysql> set @er=6366.564864;#earth’s radius (km)Query OK, 0 rows affected (0.00 sec)mysql> set @lat=56.14262; #Search origin latQuery OK, 0 rows affected (0.00 sec)mysql> set @lng=37.605853; #Search origin lngQuery OK, 0 rows affected (0.00 sec)mysql> set @dist=20;#Search radius (km)Query OK, 0 rows affected (0.00 sec)mysql> SELECT id,lat,lng,@er*2*ASIN(SQRT(POWER(SIN((@lat - lat)*pi()/180 / 2), 2) + COS(@lat * pi()/180) * COS(lat * pi()/180) * POWER(SIN((@lng - lng) * pi()/180 / 2), 2) )) as dist FROM `unindex` having dist < @dist ORDER BY dist;+---------+----------+----------+------------------+| id | lat | lng | dist |+---------+----------+----------+------------------+| 1618442 | 56.12129 | 37.51233 | 6.25753112752837 || 53613 | 56.05718 | 37.70809 | 11.4140654631309 || 1485350 | 56.24562 | 37.68273 | 12.392725454166 || 757733 | 56.09484 | 37.418 | 12.7905134964855 || 1657748 | 56.15971 | 37.38095 | 14.0488218629237 || 481209 | 56.2635 | 37.40645 | 18.2296307623964 |+---------+----------+----------+------------------+6 rows in set (2.17 sec)

虽然实现了查询,但是时间着实蛋疼(由于没有设置条件,mysql进行了表扫描,约200万条记录,你说疼不疼).所以必须修改下思路,圈出大致范围后进行查询.

首先要计算出经纬度范围,由于经度这个bitch的存在,我们又得进行三角函数计算:

set @lat=56.14262;set @lng=37.605853;set @dist=20;#kmset @lat_length=20003.93/180;#lat lengthset @lat_left=@lat-(@dist/@lat_length);set @lat_right=@lat+(@dist/@lat_length);set @lng_left=@lng-@dist/abs(cos(radians(@lat))*@lat_length);set @lng_right=@lng+@dist/abs(cos(radians(@lat))*@lat_length);

进行查询:

mysql> set @er=6366.564864;#kmQuery OK, 0 rows affected (0.00 sec)mysql> set @lat=56.14262;Query OK, 0 rows affected (0.00 sec)mysql> set @lng=37.605853;Query OK, 0 rows affected (0.00 sec)mysql> set @dist=20;#kmQuery OK, 0 rows affected (0.00 sec)mysql> set @lat_length=20003.93/180;#lat lengthQuery OK, 0 rows affected (0.00 sec)mysql> set @lat_left=@lat-(@dist/@lat_length);Query OK, 0 rows affected (0.00 sec)mysql> set @lat_right=@lat+(@dist/@lat_length);Query OK, 0 rows affected (0.00 sec)mysql> set @lng_left=@lng-@dist/abs(cos(radians(@lat))*@lat_length);Query OK, 0 rows affected (0.00 sec)mysql> set @lng_right=@lng+@dist/abs(cos(radians(@lat))*@lat_length);Query OK, 0 rows affected (0.00 sec)mysql> SELECT id,lat,lng,@er*2*ASIN(SQRT(POWER(SIN((@lat - lat)*pi()/180 / 2), 2) + COS(@lat * pi()/180) * COS(lat * pi()/180) * POWER(SIN((@lng - lng) * pi()/180 / 2), 2) )) as dist FROM `unindex` WHERE lat BETWEEN @lat_left AND @lat_right AND lng BETWEEN @lng_left AND @lng_right having dist < @dist ORDER BY dist;+---------+----------+----------+------------------+| id | lat | lng | dist |+---------+----------+----------+------------------+| 1618442 | 56.12129 | 37.51233 | 6.25753112752837 || 53613 | 56.05718 | 37.70809 | 11.4140654631309 || 1485350 | 56.24562 | 37.68273 | 12.392725454166 || 757733 | 56.09484 | 37.418 | 12.7905134964855 || 1657748 | 56.15971 | 37.38095 | 14.0488218629237 || 481209 | 56.2635 | 37.40645 | 18.2296307623964 |+---------+----------+----------+------------------+6 rows in set (0.30 sec)

通过结果可以看出查询结果有很大的改善,但是事实上我们还可以进行优化,因为我们现在所操作的是没有建立索引的数据表,接下来我们改用建立过索引的数据表看看效果:

mysql> set @er=6366.564864;#kmQuery OK, 0 rows affected (0.00 sec)mysql> set @lat=56.14262;Query OK, 0 rows affected (0.00 sec)mysql> set @lng=37.605853;Query OK, 0 rows affected (0.00 sec)mysql> set @dist=20;#kmQuery OK, 0 rows affected (0.00 sec)mysql> set @lat_length=20003.93/180;#lat lengthQuery OK, 0 rows affected (0.00 sec)mysql> set @lat_left=@lat-(@dist/@lat_length);Query OK, 0 rows affected (0.00 sec)mysql> set @lat_right=@lat+(@dist/@lat_length);Query OK, 0 rows affected (0.00 sec)mysql> set @lng_left=@lng-@dist/abs(cos(radians(@lat))*@lat_length);Query OK, 0 rows affected (0.00 sec)mysql> set @lng_right=@lng+@dist/abs(cos(radians(@lat))*@lat_length);Query OK, 0 rows affected (0.00 sec)mysql>mysql> SELECT id,lat,lng,@er*2*ASIN(SQRT(POWER(SIN((@lat - lat)*pi()/180 / 2), 2) + COS(@lat * pi()/180) * COS(lat * pi()/180) * POWER(SIN((@lng - lng) * pi()/180 / 2), 2) )) as dist FROM `index` WHERE lat BETWEEN @lat_left AND @lat_right AND lng BETWEEN @lng_left AND @lng_right having dist < @dist ORDER BY dist;+---------+----------+----------+------------------+| id | lat | lng | dist |+---------+----------+----------+------------------+| 1618442 | 56.12129 | 37.51233 | 6.25753112752837 || 53613 | 56.05718 | 37.70809 | 11.4140654631309 || 1485350 | 56.24562 | 37.68273 | 12.392725454166 || 757733 | 56.09484 | 37.418 | 12.7905134964855 || 1657748 | 56.15971 | 37.38095 | 14.0488218629237 || 481209 | 56.2635 | 37.40645 | 18.2296307623964 |+---------+----------+----------+------------------+6 rows in set (0.04 sec)

至此,我们就实现了一个类似微信的"查看附近的人"的功能

声明：本网页内容旨在传播知识，若有侵权等问题请及时与本网联系，我们将在第一时间删除处理。TEL:177 7030 7066 E-MAIL:11247931@qq.com

mysql实现地理位置搜索_MySQL

推荐度：

点击下载本文 文档为doc格式

标签：查找地理位置实现

热门焦点

mysql实现地理位置搜索_MySQL

mysql实现地理位置搜索_MySQL

mysql实现地理位置搜索_MySQL

最新推荐

猜你喜欢

热门推荐