根据经纬度获取附近的数据、和与当前经纬度的距离
环境: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='用户测试表'
- #根据经纬度查询附近的数据(:存储过程)
- #传入参数:
- #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(
- 6378 * 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(116.403869,39.915087,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}]
五:resultMapList结果
- [{distance=0.026, userId=101}, {distance=0.052, userId=102}, {distance=0.112, userId=103}, {distance=0.016, userId=104}]