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） 是将值转化为米