MySQL查询附近的人
数据库结构
-- ---------------------------- -- Table structure for lt -- ---------------------------- DROP TABLE IF EXISTS `lt`; CREATE TABLE `lt` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', `name` varchar(255) DEFAULT NULL COMMENT '名称', `lng` double(10,3) DEFAULT NULL, `lat` double(10,3) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of lt -- ---------------------------- INSERT INTO `lt` VALUES ('2', '宝安区美丽365花园 ', '114.039', '22.669'); INSERT INTO `lt` VALUES ('3', '龙岗区佳兆业上品雅园', '114.089', '22.639'); INSERT INTO `lt` VALUES ('4', '龙岗区万科金色半山', '114.095', '22.634'); INSERT INTO `lt` VALUES ('5', '宝安区金港华庭', '113.868', '22.588');
查询语句
SELECT s.id,s.name,s.lng,s.lat, (st_distance (point (lng, lat),point(113.858202,22.583819) ) / 0.0111) AS distance FROM wlsq_base.lt s HAVING distance<10 ORDER BY distance
例子
按我的坐标计算周边坐标的距离并由近到远排序
select name,st_distance(point(113.327955,23.129717),point)*111195 as distance,address from table1 where st_distance(point(113.327955,23.129717),point)*111195 < 100 order by distance asc limit 100
注意:其中point字段类型是 point,其值可以通过以下方法写入:
update table1 set point = point(113.123232,24.1324234)
st_distance
计算的结果单位是度
,需要乘111195(地球半径6371000*PI/180) 是将值转化为米