Heim >Datenbank >MySQL-Tutorial >mysql实现地理位置搜索_MySQL

mysql实现地理位置搜索_MySQL

WBOY
WBOYOriginal
2016-06-01 13:08:071298Durchsuche

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

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

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

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

<code>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)</code>

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

<code>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])</code>

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

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

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

<code>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</code>

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

<code>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</code>

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

<code>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 </code>

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

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

<code>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);</code>

进行查询:

<code>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 </code>

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

<code>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 </code>

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

Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn