JAVA or MYSQL 获取附近 distance 千米的商户

目录

一、MYSQL - 推荐

二、JAVA

---

一、MYSQL - 推荐

entity

/**
 * 范围
 */
private double distance;

/**
 * 经度 - 数据库字段
 */
private double lng;

/**
 * 纬度 - 数据库字段
 */
private double lat;

controller 

public class Controller {

    /**
     * @Function: TODO
     * @description: 获取附近 distance 千米的店铺
     * @par: distance=2&lng=121.518563&lat=31.194161
     * @author: NingZe
     * @date: 2021/3/22 15:41
     * @params: [c] [distance 距离范围 单位km, lng 当前经度, lat 当前纬度]
     * @version: 02.06
     * @return: java.lang.String
     */
    @RequestMapping("findNearby")
    @ResponseBody
    public String findNearby(Merchant c) {
        return NzResultMap.build(d.findNearby(c));
    }

}

sql

# 计算[当前经纬度]与[指定经纬度]之间的距离，返回[x.xxx km]
CREATE FUNCTION `get_distance`(lng1 DOUBLE, lat1 DOUBLE, lng2 DOUBLE, lat2 DOUBLE) RETURNS double
BEGIN
	RETURN ROUND(6378.138 * 2 * 
					 ASIN(
						 SQRT(
							    POW(SIN((lat1 * PI() / 180 - lat2 * PI() / 180) / 2), 2) + 
							    COS(lat1 * PI() / 180) * 
							    COS(lat2 * PI() / 180) * 
							    POW(SIN((lng1 * PI() / 180 - lng2 * PI() / 180) / 2), 2)
							 )
						 ) * 1000
				) / 1000;
END;

# 查询 distance 范围内的店铺
SELECT 
 *, get_distance(${lng}, ${lat}, lng, lat) distance
FROM merchant
HAVING distance <= ${distance}
ORDER BY distance ASC;

二、JAVA

pom

<dependency>
    <groupId>com.spatial4j</groupId>
    <artifactId>spatial4j</artifactId>
    <version>0.5</version>
</dependency>

entity

/**
 * 范围
 */
private double distance;

/**
 * 经度 - 数据库字段
 */
private double lng;

/**
  * min 经度
  */
private double minlng;

/**
  * max 经度
  */
private double maxlng;

/**
 * 纬度 - 数据库字段
 */
private double lat;

/**
  * min 纬度
  */
private double minlat;

/**
  * max 纬度
  */
private double maxlat;

controller

import com.spatial4j.core.context.SpatialContext;
import com.spatial4j.core.distance.DistanceUtils;
import com.spatial4j.core.shape.Rectangle;
import java.util.List;
import java.util.stream.Collectors;

public class Controller {

   private SpatialContext spatialContext = SpatialContext.GEO;

    /**
     * @Function: TODO
     * @description: 获取附近 distance 千米的店铺
     * @par: distance=2&lng=121.518563&lat=31.194161
     * @author: NingZe
     * @date: 2021/3/22 15:41
     * @params: [c] [distance 距离范围 单位km, lng 当前经度, lat 当前纬度]
     * @version: 02.06
     * @return: java.lang.String
     */
    @RequestMapping("findNearby")
    @ResponseBody
    public String findNearby(Merchant c) {
        // 1.获取外接正方形
        Rectangle rectangle = getRectangle(c.getDistance(), c.getLng(), c.getLat());
        // 2.获取位置在正方形内的所有用户
        c.setMinlng(rectangle.getMinX());
        c.setMaxlng(rectangle.getMaxX());
        c.setMinlat(rectangle.getMinY());
        c.setMaxlat(rectangle.getMaxY());
        List<Map<String, Object>> nearbys = d.findNearby(c);
        // 3.保存距离 x km
        for (int i = 0; i < nearbys.size(); i++) {
            Map<String, Object> mi = nearbys.get(i);
            mi.put("distance", getDistance(parse(mi.get("lng")), parse(mi.get("lat")), c.getLng(), c.getLat()));
        }
        // 4.剔除半径超过指定距离的多余用户
        // nearbys = nearbys.stream().filter(a -> parse(a.get("distance")) <= c.getDistance()).collect(Collectors.toList());
        return NzResultMap.build(nearbys);
    }

    private Double parse(Object obj) {
        if (obj == null) {
            return 0.00;
        }
        return Double.parseDouble(String.valueOf(obj));
    }
    
    private Rectangle getRectangle(double distance, double lng, double lat) {
        return spatialContext.getDistCalc().calcBoxByDistFromPt(spatialContext.makePoint(lng, lat), distance * DistanceUtils.KM_TO_DEG, spatialContext, null);
    }

    private double getDistance(Double longitude, Double latitude, double userLng, double userLat) {
        return spatialContext.calcDistance(spatialContext.makePoint(userLng, userLat), spatialContext.makePoint(longitude, latitude)) * DistanceUtils.DEG_TO_KM;
    }

}

sql

SELECT 
 * 
FROM merchant
WHERE (lng BETWEEN ${minlng} AND ${maxlng}) AND (lat BETWEEN ${minlat} AND ${maxlat})