我写的漂亮lil mySQL函数绝对应该为你做窍门。

  BEGIN 
 DECLARE x decimal（18,15）; 
 DECLARE EarthRadius十进制（7,3）; 
 DECLARE distInSelectedUnit decimal（18，10）; 

 IF originLatitude = relativeLatitude AND originLongitude = relativeLongitude THEN 
 RETURN 0; - 相同的经纬度点，0距离
 END IF; 

  - 默认计量单位将是英里
如果测量！='英里'AND measure！='公里'THEN 
 SET measure ='Miles'; 
 END IF; 

  -  lat和lon值必须在-180和180之间。
 IF originLatitude  RETURN 0; 
 END IF; 

 IF originLatitude> 180或相对位置> 180 OR originLongitude> 180或相对长度> 180 THEN 
 RETURN 0; 
 END IF; 

 SET x = 0.0; 

  - 从度数转换为弧度
 SET originLatitude = originLatitude * PI（）/ 180.0，
 originLongitude = originLongitude * PI（）/ 180.0，
 relativeLatitude = relativeLatitude * PI（）/ 180.0，
 relativeLongitude = relativeLongitude * PI（）/ 180.0; 

  - 距离公式，准确到30英尺以内
 SET x = Sin（originLatitude）* Sin（relativeLatitude）+ Cos（originLatitude）* Cos（relativeLatitude）* Cos（relativeLongitude  -  originLongitude ）; 

 IF 1 = x THEN 
 SET distInSelectedUnit = 0; - 同样的纬度/长点
  -  MySQL中没有足够的精度来检测此函数
 END IF; 

 SET EarthRadius = 3963.189; 
 SET distInSelectedUnit = EarthRadius *（-1 * Atan（x / Sqrt（1-x * x））+ PI（）/ 2）; 

  - 如果需要，将结果转换为公里
 IF measure ='Kilometers'THEN 
 SET distInSelectedUnit = MilesToKilometers（distInSelectedUnit）; 
 END IF; 

 RETURN distInSelectedUnit; 
 END 
 

它以originLatitude，originLongitude，relativeLatitude，relativeLongitude和measure作为参数。度量可以简单地为 Miles 或公里

希望帮助！

I have been able to create php function to determine a list of zip codes within a certain range of a given zip code. However, my next task is a bit more complex.

Lets say the table has the following columns:

id,
username
info
latitude
longitude
range

Each record has a unique row id, some information, a latitude, a longitude, and a maximum range from those coordinates that the person wants this entry to be found for. So, if I create an entry with the coordinates -20, 50 with a range of 15, that means I only want people within 15 miles of the coordinates -20, 50 to be able to see my entry. Figuring out the latitude and longitude of the user running the search is a trivial matter.

When a user is searching through the database, all records should be retrieved for latitude and longitude coordinates within the value of range from the users lat/long.

So, a non-functional example of code using the distance formula to illustrate this would be

$userLat = The latitude of the user that is running the search

$userLong = The longitude of the user that is running the search

SELECT info FROM table 
WHERE sqrt(($userLat - lat)^2 - ($userLong - long)^2) <= range 
ORDER BY username ASC

That would be ideal, but it is not valid from a coding standpoint. Is there any way to run the above comparisons using PHP and MySQL in one query? This would involve being able to run operations using multiple column values from a given row.

---

UPDATE + SOLUTION

I created another question entry that addresses this issue and has a very compact function to do what this question wanted. The function given by coderpros served as the inspiration for it (his function has a lot better handling for certain situations). However, since I am using my function with a great degree of control over the input, I created a separate function. It can be found at the link below:

MySQL User Defined Function for Latitude Longitude Syntax

This nifty lil mySQL function that I wrote should definitely do the trick for you.

BEGIN
   DECLARE x decimal(18,15);
   DECLARE EarthRadius decimal(7,3);
   DECLARE distInSelectedUnit decimal(18, 10);

   IF originLatitude = relativeLatitude AND originLongitude = relativeLongitude THEN
          RETURN 0; -- same lat/lon points, 0 distance
   END IF;

   -- default unit of measurement will be miles
   IF measure != 'Miles' AND measure != 'Kilometers' THEN
          SET measure = 'Miles';
   END IF;

   -- lat and lon values must be within -180 and 180.
   IF originLatitude < -180 OR relativeLatitude < -180 OR originLongitude < -180 OR relativeLongitude < -180 THEN
          RETURN 0;
   END IF;

   IF originLatitude > 180 OR relativeLatitude > 180 OR originLongitude > 180 OR relativeLongitude > 180 THEN
         RETURN 0;
   END IF;

   SET x = 0.0;

   -- convert from degrees to radians
   SET originLatitude = originLatitude * PI() / 180.0,
       originLongitude = originLongitude * PI() / 180.0,
       relativeLatitude = relativeLatitude * PI() / 180.0,
       relativeLongitude = relativeLongitude * PI() / 180.0;

   -- distance formula, accurate to within 30 feet
   SET x = Sin(originLatitude) * Sin(relativeLatitude) + Cos(originLatitude) * Cos(relativeLatitude) * Cos(relativeLongitude - originLongitude);

   IF 1 = x THEN
          SET distInSelectedUnit = 0; -- same lat/long points
          -- not enough precision in MySQL to detect this earlier in the function
   END IF;

   SET EarthRadius = 3963.189;
   SET distInSelectedUnit = EarthRadius * (-1 * Atan(x / Sqrt(1 - x * x)) + PI() / 2);

   -- convert the result to kilometers if desired
   IF measure = 'Kilometers' THEN
          SET distInSelectedUnit = MilesToKilometers(distInSelectedUnit);
   END IF;

   RETURN distInSelectedUnit;
END

It takes originLatitude, originLongitude, relativeLatitude, relativeLongitude, and measure as parameters. Measure can simply be Miles or Kilometers

Hope that helps!