给定用户的位置，选择在半径范围内包括该用户的行 [英] Given a user's location, select rows that include that user in their radius

问题描述

我有一个MySQL表，其中有latitude，longitude和radius行.纬度和经度的类型为DOUBLE； radius是INT类型，以米为单位.本质上，每一行代表地球上的一个圆.

I have a MySQL table with rows latitude, longitude and radius. Latitude and longitude are of type DOUBLE; radius is of type INT and measured in meters. Essentially, each row represents a circle on Earth.

给出user_latitude和user_longitude的值，如何选择圈子中包含用户的所有行?

Given values of user_latitude and user_longitude, how can I select all the rows that include the user in their circles?

我做了一个简短的图表来说明我的问题不清楚的情况.每个红色点代表一个user_latitude和user_longitude点，每个圆圈代表数据库中的一个latitude，longitude和radius行.

I made a quick diagram to illustrate in case my question wasn't clear. Each red dot represents a user_latitude and user_longitude point, each circle represents a latitude, longitude and radius row in the database.

推荐答案

步骤1:实施Haversine距离公式

step1: implement haversine distance formula

delimiter //
create function DistanceInKm(
        lat1 FLOAT, lon1 FLOAT,
        lat2 FLOAT, lon2 FLOAT
     ) returns float
    NO SQL DETERMINISTIC
begin
    return degrees(acos(
              cos(radians(lat1)) *
              cos(radians(lat2)) *
              cos(radians(lon2) - radians(lon1)) +
              sin(radians(lat1)) * sin(radians(lat2))
            )) * 111.045;
END//
delimiter ;

(魔术数字111.045将其转换为公里，使用69表示英里)

(the magic number 111.045 converts it to km, use 69 for miles)

第2步:使用该功能

select * from areas where distance(lat, long, USERLAT, USERLONG) < radius

参考

这篇关于给定用户的位置，选择在半径范围内包括该用户的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！