几种实现经纬度查询排序
-- -- 表的结构 `ts_brand_store` -- CREATE TABLE IF NOT EXISTS `ts_brand_store` ( `sid` int(11) unsigned NOT NULL, `store_name` char(200) NOT NULL COMMENT '门店名称', `introduction` text NOT NULL COMMENT '简介', `store_id` int(11) unsigned NOT NULL COMMENT '关联的邻铺store_id', `uid` int(11) unsigned NOT NULL COMMENT '所属用户UID', `logo` int(11) unsigned NOT NULL COMMENT 'logo附件ID', `tel` varchar(100) NOT NULL COMMENT '联系电话', `province` int(11) NOT NULL COMMENT '所在省ID', `city` int(11) NOT NULL COMMENT '所在市ID', `area` int(11) NOT NULL COMMENT '所在区县ID', `address` varchar(100) NOT NULL COMMENT '门店地址', `last_latitude` float(10,6) NOT NULL COMMENT 'last_latitude', `last_longitude` float(10,6) NOT NULL COMMENT '实体店纬度', `photo_attach_ids` varchar(100) NOT NULL COMMENT '门店图片附件id集合', `verify` tinyint(3) NOT NULL COMMENT '门店审核 1通过,0平台拒绝,-1品牌商拒绝,10平台审核中,20品牌商审核中', `verifyremark` varchar(50) NOT NULL COMMENT '拒绝理由', `ctime` int(11) NOT NULL COMMENT '发布时间' ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COMMENT='门店表';
$last_latitude = floatval($this->data['last_latitude']); $last_longitude = floatval($this->data['last_longitude']); if(!$last_latitude || !$last_longitude) { return $this->error("请开启定位服务"); } $weiba_id = intval($this->data['weiba_id']); //圈子id if($weiba_id == 0) { return $this->error("丢失重要参数"); } $page = $this->data['weiba_id'] ?:1; $map['weiba_id'] = $weiba_id ; $map['status'] = 1 ; $map['verify'] = 1 ; $relation_weiba = M('brand_store_relation')->where($map)->field('sid')->select(); $sids = getSubByKey($relation_weiba, 'sid'); if(empty($sids)){ return $this->success(array('data'=>array())); } $R = 6371.393;//地球半径 $distanceSql = '( '.$R.' * acos( cos( radians('.$last_latitude.') ) * cos( radians( last_latitude ) ) * cos( radians( last_longitude ) - radians('.$last_longitude.') ) + sin( radians('.$last_latitude.') ) * sin( radians( last_latitude ) ) ) )'; $field = '`sid`, `store_name`,`logo`, `province`, `city`, `area`, `last_latitude`, `last_longitude`, '.$distanceSql.' AS `distance`'; $data['list'] = M('brand_store')->where(array('sid'=>array('IN', $sids)))->order('`distance` ASC')->field($field)->select(); return $this->success($data);