当前位置 博文首页 > u012442504的专栏:MySQL根据经纬度按距离排序并根据距离返回自
DROP TABLE IF EXISTS `customer`;
CREATE TABLE `aitu_customer` (
`id` int(11) unsigned NOT NULL auto_increment COMMENT '自增主键',
`name` varchar(50) NOT NULL COMMENT '名称',
`lon` double(9,6) NOT NULL COMMENT '经度',
`lat` double(8,6) NOT NULL COMMENT '纬度',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COMMENT='商户和人的位置表';
INSERT INTO `aitu_customer` VALUES ('1', '天津市区', '117.315575', '39.133462');
INSERT INTO `aitu_customer` VALUES ('2', '北京市区', '116.407999', '39.894073');
INSERT INTO `aitu_customer` VALUES ('3', '保定', '115.557124', '38.853490');
INSERT INTO `aitu_customer` VALUES ('4', '石家庄', '114.646458', '38.072369');
INSERT INTO `aitu_customer` VALUES ('5', '昌平区1', '116.367180', '40.009561');
INSERT INTO `aitu_customer` VALUES ('6', '海淀区2', '116.313425', '39.973078');
INSERT INTO `aitu_customer` VALUES ('7', '海淀区1', '116.329236', '39.987231');
//st_distance 计算的结果单位是度,需要乘111195(地球半径6371000*PI/180)是将值转化为米。
SELECT
*,
(st_distance (point (lon,lat),point (116.3424590000,40.0497810000))*111195/1000 )as juli
FROM
aitu_customer
ORDER BY
juli ASC
SELECT
*,(
6371 * acos (
cos ( radians(21.785367) )
* cos( radians( lat ) )
* cos( radians( lon ) - radians(118.356734) )
+ sin ( radians(21.785367) )
* sin( radians( lat ) )
)
) AS distance //距离
FROM aitu_customer
HAVING distance < 2000 //筛选距离小于2km的商家(可不写),如果没查到数据就是没有小于2km的商家
ORDER BY distance //根据距离远近来排序 默认升序 (可不写)
LIMIT 0 , 20; //显示前20条数据(可不写)
public function order(){
$lon = input('lon');
$lat = input('lat');
$order = Db::query("SELECT *,( 6371 * acos ( cos ( radians($lat) ) * cos( radians( lat ) ) * cos( radians( lon ) - radians($lon) ) + sin ( radians($lat) ) * sin( radians( lat ) ) ) ) AS distance FROM aitu_customer HAVING distance < 2000 ORDER BY distance LIMIT 0 , 20");//距离小于2000米的前20条数据
return json($order);
}
public function order(){
$lon = input('lon');
$lat = input('lat');
$order = Db::query("SELECT *,( 6371 * acos ( cos ( radians($lat) ) * cos( radians( lat ) ) * cos( radians( lon ) - radians($lon) ) + sin ( radians($lat) ) * sin( radians( lat ) ) ) ) AS distance FROM aitu_customer HAVING distance < 2000 ORDER BY distance LIMIT 0 , 20");//距离小于2000米的前20条数据
foreach ($order as $key =>$value) {
echo $order[$key]['name'].$order[$key]['id']."<br/>";
}
}
然后结合自己的业务逻辑进行下一步即可。使用空间索引计算速度性能有所提高,具体提高多少还未测试,如果对数据有高要求的比如覆盖全国的那种建议建立索引或者使用其他更好的方法,这个方法目测适合中小企业使用,如果是大型企业如滴滴之类的就需要自己重新开发一套了。
cs