MySQL函数来确定邮政编码接近度/范围 [英] MySQL Function to Determine Zip Code Proximity / Range
问题描述
我已经能够创建php函数来确定给定邮政编码的一定范围内的邮政编码列表。然而，我的下一个任务有点复杂。
可以说这个表有以下列：
id，
用户名
信息
纬度
经度
范围
每个记录都有一个唯一的行ID，一些信息，一个纬度，一个经度和一个人物想要找到该项的坐标的最大范围。因此，如果我使用坐标-20和50创建一个条目范围为15的条目，这意味着我只希望坐标-20和50的15英里内的人能够看到我的条目。计算用户运行搜索的经度和纬度是一件小事。
当用户搜索数据库时，所有记录都应该检索经度和纬度坐标在用户lat / long的范围内。
因此，使用距离公式来说明这一点的代码的非功能性示例将是
$ userLat =运行搜索的用户的纬度
$ userLong =运行搜索的用户的经度
SELECT info FROM table
WHERE sqrt（（$ userLat - lat）^ 2 - （$ userLong - long）^ 2）< =范围
ORDER BY用户名ASC
这是理想的，但从编码的角度来看它是无效的。有没有办法在一个查询中使用PHP和MySQL来运行上述比较？这将涉及到能够使用给定行中的多个列值运行操作。
UPDATE + SOLUTION
我创建了另一个问题条目来解决这个问题，并且具有非常简洁的功能来完成这个问题。 coderpros给出的功能是它的灵感（他的功能在某些情况下有更好的处理能力）。但是，由于我使用我的功能对输入进行了很大程度的控制，因此我创建了一个单独的函数。它可以在下面的链接找到：
MySQL用户定义的Latitude Longitude语法功能
我写的漂亮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 < -180或相对的< -180 OR起始长度< -180或相对长度< -180 THEN
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!
这篇关于MySQL函数来确定邮政编码接近度/范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
