v Preparations

Create a test database

CREATE TABLE `userposition` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `city` varchar(20) NOT NULL,
    `position` varchar(128) NOT NULL,
    `longitude` decimal(18,15) NOT NULL,
    `latitude` decimal(18,15) NOT NULL,
    PRIMARY KEY (`id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
insert into `userposition` values(1,'Beijing City','HUILONGGUAN NEW VILLAGE CENTRAL DISTRICT', 116.310771,40.06263);
insert into `userposition` values(2,'Beijing City','Jinyu Washington', 116.310127,40.064379);
insert into `userposition` values(3,'Beijing City','Rongze Jiayuan Central District', 116.311962,40.064822);
insert into `userposition` values(4,'Beijing City','Huilong Guanxincun East District', 116.312541,40.063246);
insert into `userposition` values(5,'Beijing City','Upper east side', 116.314168,40.033075);

The longitude and latitude of the test data can be used Gould map perhaps Baidu Maps Extraction.

People near v

principle

Firstly, four points of the rectangle around a coordinate position are calculated, and then longitude and latitude are used to directly match the records in the database.

thinking

Firstly, the coordinate range of "1000 meters near a given coordinate" is calculated. Although it is a circle, we can first find the circumferential square of the circle, and then search the database with the latitude and longitude range of the square. The circle is the required search range and the square is the indirect result range.

Let's first find the boundaries of the East and west. In haversin's formula, let 1 = 2, we can get

Java implementation

/**
     * Find people nearby
     * @param radii Radius Distance (Unit km)
     * @param lon longitude
     * @param lat latitude
     * @return
     */
    @GetMapping("/nearby")
    public List<UserPosition> getVicinity(double radii, double lon, double lat){
        double r = 6371;//Earth radius kilometers
        double dis = radii;
        double dlng =  2*Math.asin(Math.sin(dis/(2*r))/Math.cos(lat*Math.PI/180));
        dlng = dlng*180/Math.PI;//Angle to radian
        double dlat = dis/r;
        dlat = dlat*180/Math.PI;
        double minlat =lat-dlat;
        double maxlat = lat+dlat;
        double minlng = lon -dlng;
        double maxlng = lon + dlng;

        return userService.getVicinity(BigDecimal.valueOf(minlng), BigDecimal.valueOf(maxlng), BigDecimal.valueOf(minlat), BigDecimal.valueOf(maxlat));
    }

mybatis

<select id="getvicinity" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from userposition
    where longitude &gt;= #{minlng} and longitude &lt;= #{maxlng} and latitude &gt;= #{minlat} and latitude &lt;= #{maxlat}
  </select>

List<UserPosition> getvicinity(@Param("minlng") BigDecimal minlng,
                                  @Param("maxlng") BigDecimal maxlng,
                                  @Param("minlat") BigDecimal minlat,
                                  @Param("maxlat") BigDecimal maxlat);

Testing effect

Find the longitude and latitude of Huilong New Village on the map, and then test it.

v sorted by distance

Java code

 /**
     * Sort of people nearby
     * @param lon longitude
     * @param lat latitude
     * @return
     */
    @GetMapping("/nearbysort")
    public List<UserPosition> getVicinitySort(double lon, double lat){

        return userService.getvicinitysort(BigDecimal.valueOf(lon), BigDecimal.valueOf(lat));
    }

mybatis code

<select id="getvicinitysort" resultMap="BaseResultMap">
    SELECT id, city, position, longitude,latitude,
        (POWER(MOD(ABS(longitude - #{longitude}),360),2) + POWER(ABS(latitude - #{latitude}),2)) AS distance
        FROM `userposition`
        ORDER BY distance LIMIT 20
  </select>

List<UserPosition> getvicinitysort(@Param("longitude") BigDecimal longitude,
                                   @Param("latitude") BigDecimal latitude);

Testing effect

Plus, if you need to sort by distance, and return the field of distance. It can be implemented in the following way.

SELECT
    *,
    ROUND(
        6378.138 * 2 * ASIN(
            SQRT(
                POW(
                    SIN(
                        (
                            $latitude * PI() / 180 - latitude * PI() / 180
                        ) / 2
                    ),
                    2
                ) + COS($latitude * PI() / 180) * COS(latitude * PI() / 180) * POW(
                    SIN(
                        (
                            $longitude * PI() / 180 - longitude * PI() / 180
                        ) / 2
                    ),
                    2
                )
            )
        ) * 1000
    ) AS distance
FROM
    userposition
ORDER BY
    distance ASC

v Blog Summary

Other references:

• According to a given longitude and latitude point, query the location of 500 meters nearby uuuuuuuuuuuuu https://yq.aliyun.com/ziliao/230566
• Calculating the Distance between Longitudes and Latitudes of Two Points under mysql https://www.cnblogs.com/sandea/p/4673297.html
• MYSQL creates a function to calculate longitude and latitude distances http://xigua366.iteye.com/blog/2242818