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 mult开发者_StackOverflow社区iple 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!
精彩评论