mysql实现地理位置搜索

随着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 MySQLdb
import random

try:
    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− lng1
a = sin²(Δlat/2) + cos(lat1) * cos(lat2) * sin²(Δlng/2)
c = 2*atan2(√a, √(1−a))
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 lat
Query OK, 0 rows affected (0.00 sec)
mysql> set @lng=37.605853; #Search origin lng
Query 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;#km
set @lat_length=20003.93/180;#lat length
set @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;#km
Query 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;#km
Query OK, 0 rows affected (0.00 sec)
mysql> set @lat_length=20003.93/180;#lat length
Query 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_rig
ht 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;#km
Query 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;#km
Query OK, 0 rows affected (0.00 sec)
mysql> set @lat_length=20003.93/180;#lat length
Query 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)

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

题外话: sae海外线路最近抽的不行,还是找机会备案了走国内流量吧. python越用越顺手

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏james大数据架构

通用分页存储过程

/*通用分页存储过程*/ USE HotelManagementSystem GO IF EXISTS(SELECT * FROM sys.objects WH...

2158
来自专栏一名合格java开发的自我修养

mysql,SQL标准,多表查询中内连接,外连接,自然连接等详解之查询结果集的笛卡尔积的演化

a,系统首先执行from子句,这里from子句列出有两个表teacher表和course表,DBMS讲计算这两个表的笛卡尔积,列出这两个表中行的所以可能组合,形...

1362
来自专栏Java Web

模仿天猫实战【SSM版】——项目起步

3224
来自专栏乐沙弥的世界

SQL server 2005 切换分区表

如转载,请注明出处:http://blog.csdn.net/robinson_0612/archive/2009/11/10/4794371.aspx

933
来自专栏数据和云

利用分析函数改写范围判断自关联查询

分析、定位数据库的主要负载是这条语句引起的过程相对简单,通过AWR报告就可以比较容易的完成定位,这里就不赘述了。

1084
来自专栏数据和云

MySQL - 8种常见的SQL错误用法

前言:MySQL在2016年仍然保持强劲的数据库流行度增长趋势。越来越多的客户将自己的应用建立在MySQL数据库之上,甚至是从Oracle迁移到MySQL上来。...

3824
来自专栏杨建荣的学习笔记

sql语句的简化(r2第7天)

今天碰到一个sql语句简化的问题,虽然也不复杂,但是也值得从中学习一些东西 SELECT MOD(((SELECT TO_NUMBER(TO_CHAR(LOG...

2686
来自专栏杨建荣的学习笔记

oracle坏块修复实例

最近几天发现库里有坏块了,环境是11gR2, linux平台的64位的库。以下是我的修复办法,基于dbms_repair做的在线修复,也可以基于备份rman来修...

3449
来自专栏Grace development

电商系统设计之商品 (下)

完成上述流程则是完成了一笔交易,经常网上购物的童鞋都懂这个。今天我们讲下从商品系统到交易系统和订单系统的存储过程及其设计上的应该注意的“坑”。

6502
来自专栏芋道源码1024

电商系统设计之订单

1. 前言2. 付款2.1 成功2.2 人祸2.4 天灾2.4 注释2.5 表结构2.5.1 交易表2.5.2 支付记录表2.5.3 订单表3. 运输4. 收货...

1353

扫码关注云+社区