吕滔博客

数据库结构

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