根据经纬度获取附近的数据、和与当前经纬度的距离
环境:java+mysql
一:添加TestUsers表
CREATE TABLE `TestUsers` (
`userId` INT(11) NOT NULL AUTO_INCREMENT COMMENT '用户编号',
`lat` DOUBLE DEFAULT NULL COMMENT '纬度',
`lon` DOUBLE DEFAULT NULL COMMENT '经度',
PRIMARY KEY (`userId`)
) ENGINE=INNODB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8 COMMENT='用户测试表'
二:创建存储过程(mysql)
#根据经纬度查询附近的数据(:存储过程)
#传入参数:
#double mylon, 当前所在经度
#double mylat, 当前所在纬度
#int beginId, 取值开始
#int counts 获取条数
#dist 查询范围 1km 5km 10km.......
DROP PROCEDURE IF EXISTS pro_test_GetNearData;
DELIMITER //
CREATE PROCEDURE pro_test_GetNearData(IN mylon DOUBLE,IN mylat DOUBLE,IN beginId INT,IN counts INT,IN dist DOUBLE)
BEGIN
DECLARE lon1 FLOAT;
DECLARE lon2 FLOAT;
DECLARE lat1 FLOAT;
DECLARE lat2 FLOAT;
-- calculate lon and lat for the rectangle:
SET lon1 = mylon-dist/ABS(COS(RADIANS(mylat))*69);
SET lon2 = mylon+dist/ABS(COS(RADIANS(mylat))*69);
SET lat1 = mylat-(dist/69);
SET lat2 = mylat+(dist/69);
-- run the query:
SELECT dest.userId,ROUND(
3956 * 2 * ASIN(SQRT(POWER(SIN((mylat - dest.lat) * PI()/180 / 2), 2) +
COS(mylat * PI()/180) * COS(dest.lat * PI()/180) * POWER(SIN((mylon -dest.lon) * PI()/180 / 2), 2))),3)
AS distance -- 两点间距离km 四舍五入,小数点后保留三位
FROM TestUsers dest
WHERE dest.lon BETWEEN lon1 AND lon2
AND dest.lat BETWEEN lat1 AND lat2
HAVING distance < dist ORDER BY dest.userId LIMIT beginId,counts;
END;
//
DELIMITER ;
SHOW WARNINGS;
#--------------------------------------------------------------------------------
SHOW CREATE PROCEDURE pro_test_GetNearData;
CALL pro_test_GetNearData(22.591047,113.895543,0,5,1);
三:Dao调用执行存储过程方法
// 执行存储过程
@Override
public List<Map<String, Object>> execProcQuery(final String procName,
final Object[] params, final String[] resultParams) {
return (List<Map<String, Object>>) getHibernateTemplate().execute(
new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
CallableStatement cstmt = session.connection()
.prepareCall(procName);
List<Map<String, Object>> resultMapList = null;
Map<String, Object> resultMap = null;
if (params != null) {
for (int i = 0; i < params.length; i++) {
cstmt.setObject(i + 1, params[i]);
}
}
ResultSet rs = cstmt.executeQuery();
while (rs.next()) {
if (resultMapList == null) {
resultMapList = new ArrayList<Map<String, Object>>();
}
resultMap = new HashMap<String, Object>();
for (int i = 0; i < resultParams.length; i++) {
resultMap.put(resultParams[i],
rs.getString(resultParams[i]));
}
resultMapList.add(resultMap);
}
return resultMapList;
}
});
}
四:service调用存储过程
@Override
public List<Map<String, Object>> getNearData(Double longitude,
Double latitude, Integer beginId, Integer count,Double dist) {
List<Map<String, Object>> resultMapList=null;
try {
//调用查询附近数据存储过程
resultMapList= testDao.execProcQuery("{CALL pro_test_GetNearData(?,?,?,?,?)}",//要调用的存储过程
new Object[] { longitude, latitude, beginId, count, dist }, //参数列表
new String[] {"userId","distance"});// 要获取的值 key
} catch (Exception e) {
e.printStackTrace();
}
return resultMapList;
}
五:resultMapList结果
[{distance=0.026, userId=101}, {distance=0.052, userId=102}, {distance=0.112, userId=103}, {distance=0.016, userId=104}]