redisTemplate GEO MySql根据经纬度查询任意距离范围内数据

package com.um.my_demo;
 
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.geo.*;
import org.springframework.data.redis.connection.RedisGeoCommands;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.test.context.junit4.SpringRunner;
 
import java.util.List;
 
@RunWith(SpringRunner.class)
@SpringBootTest
public class MyDemoApplicationTests {
 
    @Autowired
    RedisTemplate redisTemplate;
 
    /**
     *  将指定的地理空间位置（纬度、经度、名称）添加到指定的key中。
     */
    @Test
    public void redisTestAdd() {
//        Long addedNum = redisTemplate.opsForGeo().add("geo", new Point(116.405285, 39.904989), "北京");
//        Long addedNum = redisTemplate.opsForGeo().add("geo", new Point(121.47, 31.23), "上海");
//        Long addedNum = redisTemplate.opsForGeo().add("geo", new Point(113.27, 23.13), "广州");
        Long addedNum = redisTemplate.opsForGeo().add("geo", new Point(114.05, 22.55), "深圳");//params: key, Point(经度, 纬度), 地方名称
        System.out.println(addedNum);
    }
 
    /**
     *  从key里返回所有给定位置元素的位置（经度和纬度）。
     */
    @Test
    public void redisTestGeoGet() {
        List<Point> points = redisTemplate.opsForGeo().position("geo","北京","上海","深圳","广州");//params: key, 地方名称...
        System.out.println(points);
    }
    //return: [Point [x=116.405283, y=39.904988], Point [x=121.470002, y=31.229999], Point [x=114.049998, y=22.550001], Point [x=113.270001, y=23.130001]]
 
    /**
     *  返回两个给定位置之间的距离。
     */
    @Test
    public void testDist(){
        Distance distance = redisTemplate.opsForGeo()
                .distance("geo","北京","上海", RedisGeoCommands.DistanceUnit.KILOMETERS);//params: key, 地方名称1, 地方名称2, 距离单位
        System.out.println(distance);
    }
    //return: 1067.6692 KILOMETERS
 
    /**
     *  以给定的经纬度为中心， 返回键包含的位置元素当中， 与中心的距离不超过给定最大距离的所有位置元素，并给出所有位置元素与中心的平均距离。
     */
    @Test
    public void redisTestNearByXY(){
        Circle circle = new Circle(new Point(114.05, 22.55), new Distance(200, Metrics.KILOMETERS));//Point(经度, 纬度) Distance(距离量, 距离单位)
        RedisGeoCommands.GeoRadiusCommandArgs args = RedisGeoCommands.GeoRadiusCommandArgs.newGeoRadiusArgs().includeDistance().includeCoordinates().sortAscending().limit(5);
        GeoResults<RedisGeoCommands.GeoLocation<String>> results = redisTemplate.opsForGeo()
                .radius("geo",circle,args);//params: key, Circle, GeoRadiusCommandArgs
        System.out.println(results);
    }
    //return: GeoResults: [averageDistance: 51.367149999999995 KILOMETERS, results: GeoResult [content: RedisGeoCommands.GeoLocation(name=深圳, point=Point [x=114.049998, y=22.550001]), distance: 3.0E-4 KILOMETERS, ],GeoResult [content: RedisGeoCommands.GeoLocation(name=广州, point=Point [x=113.270001, y=23.130001]), distance: 102.734 KILOMETERS, ]]
 
    /**
     *  以给定的城市为中心， 返回键包含的位置元素当中， 与中心的距离不超过给定最大距离的所有位置元素，并给出所有位置元素与中心的平均距离。
     */
    @Test
    public void testNearByPlace(){
        Distance distance = new Distance(200,Metrics.KILOMETERS);//params: 距离量, 距离单位
        RedisGeoCommands.GeoRadiusCommandArgs args = RedisGeoCommands.GeoRadiusCommandArgs.newGeoRadiusArgs().includeDistance().includeCoordinates().sortAscending().limit(5);
        GeoResults<RedisGeoCommands.GeoLocation<String>>  results = redisTemplate.opsForGeo()
                .radius("geo","深圳",distance,args);//params: key, 地方名称, Circle, GeoRadiusCommandArgs
        System.out.println(results);
    }
    //return: GeoResults: [averageDistance: 51.36685 KILOMETERS, results: GeoResult [content: RedisGeoCommands.GeoLocation(name=深圳, point=Point [x=114.049998, y=22.550001]), distance: 0.0 KILOMETERS, ],GeoResult [content: RedisGeoCommands.GeoLocation(name=广州, point=Point [x=113.270001, y=23.130001]), distance: 102.7337 KILOMETERS, ]]
 
    /**
     *  返回一个或多个位置元素的 Geohash 表示
     */
    @Test
    public void testGeoHash(){
        List<String> results = redisTemplate.opsForGeo()
                .hash("geo","北京","上海","深圳","广州");//params: key, 地方名称...
        System.out.println(results);
    }
    //return: [wx4g0b7xrt0, wtw3sj5zbj0, ws10730em80, ws0e9d8wn20]
 
}

mysql st_distance函数查距离排序

当前所处在的位置（113.858202 , 22.583819 ）,需要查询我附近1000米内的小区，并安装由近到远的顺序排列 
SELECT tsi.id,
       tsi.name,
       tsi.lng,
       tsi.lat,
       round((st_distance(point(lng, lat), point(113.858202, 22.583819)) / 0.0111) * 1000) AS distance
FROM t_store_info tsi
HAVING distance < 10
ORDER BY distance

假设我当时的坐标：115.067,34.76 需要查询我附近30KM内站点，并按照距离由近及远排
SELECT
   s.id,s.name,s.lng,s.lat, 
   (st_distance (point (lng, lat),point(115.067,34.76) ) / 0.0111) AS distance
FROM t_store_info s
HAVING distance<30
ORDER BY distance

MySql根据经纬度查询任意距离范围内数据

#当前位置经纬度 经度：117.215637   纬度：39.1373367 距离：500
#表中经纬度字段 经度：longitude    纬度：latitude   juli：500

SELECT id,
       lng,
       lat,
       ROUND(6378.138 * 2 * ASIN(SQRT(POW(SIN((31.203237244046 * PI() / 180 - lat * PI() / 180) / 2), 2) +
                                      COS(31.203237244046 * PI() / 180) * COS(lat * PI() / 180) *
                                      POW(SIN((121.519557087793 * PI() / 180 - lng * PI() / 180) / 2), 2))) * 1000) AS juli
FROM t_store_info
having juli < 500 ORDER BY jul

或者

#当前位置经纬度 经度：117.215637   纬度：39.1373367 距离：500
#表中经纬度字段 经度：longitude    纬度：latitude   juli：500
select * from(
SELECT id,longitude,latitude, 
ROUND(6378.138*2*ASIN(SQRT(POW(SIN((39.1373367*PI()/180-latitude*PI()/180)/2),2)+COS(39.1373367*PI()/180)*COS(latitude*PI()/180)*POW(SIN((117.215637*PI()/180-longitude*PI()/180)/2),2)))*1000) AS juli
FROM table_name) as tmp_table_name
where juli < 500
order by juli