之前很啥很天真地以为无非就是逐个计算距离,然后比较出来就行了,然后当碰到访问用户很多,而且数据库中经纬度信息很多的时候,计算量的迅速增长,能让服务器完全傻逼掉,还是老前辈的经验比我们丰富,给了我很大的启示。
sql语句查询经纬度范围
指定一个经纬度,给定一个范围值(单位:千米),查出在经纬度周围这个范围内的数据。
经度:113.914619
纬度:22.50128
范围:2km
longitude为数据表经度字段
latitude为数据表纬度字段
SQL在mysql下测试通过,其他数据库可能需要修改
SQL语句如下:
select * from location where sqrt( ( ((113.914619-longitude)*PI()*12656*cos(((22.50128+latitude)/2)*PI()/180)/180) * ((113.914619-longitude)*PI()*12656*cos (((22.50128+latitude)/2)*PI()/180)/180) ) + ( ((22.50128-latitude)*PI()*12656/180) * ((22.50128-latitude)*PI()*12656/180)
) )<2
MySQL性能调优 – 使用更为快速的算法进行距离
最近遇到了一个问题,通过不断的尝试最终将某句原本占据近1秒的查询优化到了0.01秒,效率提高了100倍.
问题是这样的,有一张存放用户居住地点经纬度信息的MySQL数据表,表结构可以简化 为:id(int),longitude(long),latitude()long. 而业务系统中有一个功能是查找离某个用户最近的其余数个用户,通过代码分析,可以确定原先的做法基本是这样的:
//需要查询的用户的坐标
$lat=20;
$lon=20;//执行查询,算出该用户与所有其他用户的距离,取出最近的10个
$sql='select * from users_location order by ACOS(SIN(('.$lat.' * 3.1415) / 180 ) *SIN((latitude * 3.1415) / 180 ) +COS(('.$lat.' * 3.1415) / 180 ) * COS((latitude * 3.1415) / 180 ) *COS(('.$lon.' * 3.1415) / 180 - (longitude * 3.1415) / 180 ) ) * 6380 asc limit 10';
而这条sql执行的速度却非常缓慢,用了近1秒的时间才返回结果,应该是因为order里的子语句用了太多的数学计算公式,导致整体的运算速度下降.
而在实际的使用中,不太可能会发生需要计算该用户与所有其他用户的距离,然后再排序的情况,当用户数量达到一个级别时,就可以在一个较小的范围里进行搜索,而非在所有用户中进行搜索.
所以对于这个例子,我增加了4个where条件,只对于经度和纬度大于或小于该用户1度(111公里)范围内的用户进行距离计算,同时对数据表中的经度和纬度两个列增加了索引来优化where语句执行时的速度.
最终的sql语句如下
$sql='select * from users_location where
latitude > '.$lat.'-1 and
latitude < '.$lat.'+1 and
longitude > '.$lon.'-1 and
longitude < '.$lon.'+1
order by ACOS(SIN(('.$lat.' * 3.1415) / 180 ) *SIN((latitude * 3.1415) / 180 ) +COS(('.$lat.' * 3.1415) / 180 ) * COS((latitude * 3.1415) / 180 ) *COS(('.$lon.'* 3.1415) / 180 - (longitude * 3.1415) / 180 ) ) * 6380 asc limit 10';
经过优化的sql大大提高了运行速度,在某些情况下甚至有100倍的提升.这种从业务角度出发,缩小sql查询范围的方法也可以适用在其他地方.
原文地址: http://www.scourgen.com/mysql_performance_optimize-using_a_better_way_to_compute_users_distinct
分享到:
相关推荐
LBS ,定位方式,通过经纬度来确定省市区,此文档提供了全国给个省份区的经纬度。
百度地图Api开发源码,包括地图初始化,百度地图和GPS坐标之间的转换,以及通过GET请求获取云端的详细数据,添加自定义图层并自定义点击标签事件
中国省市县三级政府驻地详细数据(含经纬度),省市县行政区由高德开发者中心获取,通过兴趣点查询接口,爬取经纬度等详细信息,整理成excel表,供行政区驻地配图等场景使用。 2、字段信息: typecode 省市县类型 ...
全国基站lbs定位库包含全国移动联通电信全网lbs位置数据,94万条
本例是用来获取用户地理位置信息中的经纬度,不过要完全正常运行本实例需要以下条件: 1. 一部 iphone (iOS3.0 ) 或 android (2.0 ) 的手机 2. 浏览器 Chrome 5 , Firefox 3.6 , Opera 10.6, Safari 5, IE 9
全国各省、市、县、镇、村、街道、居委会数据库,数据来源于国家统计局,70多万数据,MySQL 格式。 每一条都含经纬度,拼音,地址,上下级关系,非常适合LBS应用开发。 1、起因: 由于市面上的城市数据不是量太少...
最近在做一个车辆调度的应用系统,需要根据车辆和站点的位置做相应的规划,因此要调用第三方的地图接口来获取经纬度等信息。这里,我选择的是高德平台,下面是一个从注册到简单使用的例子,可供大家学习参考,更深入...
全国基站lbs定位库包含全国移动联通电信全网lbs位置数据,84万条 有需要可以交互更换所有资源 学生党不容易,如果觉得好的话就给个好评
主要涉及日常需要解决问题,例如类似于滴滴打车实现查找附近车辆业务逻辑和贝壳找房根据指定位置筛选出附近房源,上述两场场景应用可借鉴代码中两种方法予以解决,当然如果技术允许可以参照使用mongoDb数据库,内置...
传智播客iOS6免费公开课程-LBS经纬度定位
2、手机定位后将经纬度发送给服务器,服务器根据经纬度在数据库中匹配数据,并按照距离由近到远排序(此处省略一万字...) SQL实现经纬度匹配以及距离排序代码 1、SQL计算经纬度距离的自定义函数 CREATE ...
说明:数据库采用mysql,在数据库中添加经纬度和地点信息后,在模拟器或者真机中输入对应的经纬度,就能准确显示地点,并进行签到。 LBS_FOR_SISE功能简介: LBS_FOR_SISE是一款地理位置服务软件,此软件运行于...
最新全国基站lbs定位库包含全国移动联通电信全网lbs位置数据
说明:数据库采用mysql,在数据库中添加经纬度和地点信息后,在模拟器或者真机中输入对应的经纬度,就能准确显示地点,并进行签到。 LBS_FOR_SISE功能简介: LBS_FOR_SISE是一款地理位置服务软件,此软件...
d、图片图层对象初始化的方法有两种:1)根据指定经纬度坐标生成 2)根据指定区域生成; ------------------------------------------------------------------------------------- 八、 Demo名称:自定义绘制 ...
免费全国移动联通电信基站数据库,基站查询
#程序中**----**中的内容需要替换为自己的数据 #程序中设计到的矩形对角坐标采集需要通过https://lbs.amap.com/tools/picker 获取 #程序中types代码需要通过https://lbs.amap.com/api/webservice/download 下载解压...
大数据LBS全文共2页,当前为第1页。大数据LBS全文共2页,当前为第1页。 大数据LBS全文共2页,当前为第1页。 大数据LBS全文共2页,当前为第1页。 阿里与高德的数据整合为人们描绘的未来生活场景远不止如此,地图数据...
一个简单的获取当前位置经纬度的实例. 适合初学者,在Android Studio下编译通过.
1.包括省、市、区、镇/街道四级 2.包括经纬度信息 3.拉取数据截至2023年7月 4.数据来源:高德API=>https://lbs.amap.com/api/webservice/guide/api/district