Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
Thread: geolocation-based querying
02-23-2011, 10:26 AM #1
- Join Date
- Oct 2003
- Thanked 1 Time in 1 Post
is there a more efficient way of calculating/querying and sorting by "distance" based on latitude and longitude? this query works well but it is rather slow (0.9-1.7 seconds) and as the table grows (currently 150,000 rows), it slows down more.
3956*2*ASIN(SQRT(POWER(SIN((".$mylat." - abs(a.lat))*pi()/180/2),2)+COS(".$mylat."*pi()/180)*COS(abs(a.lat)*pi()/180)*POWER(SIN((".$mylon."-a.lon)*pi()/180/2),2))) as distance
FROM `accounts` a, `profiles` b WHERE b.account_id=a.id AND a.zipcode != '' AND a.zipcode > 0 AND b.status != '##private##'
AND a.lat != '0.0000000000' AND a.lon != '0.0000000000' ORDER BY distance ASC,a.last_activity DESC LIMIT $limit";
02-23-2011, 01:50 PM #2
- Join Date
- Mar 2008
- Forfar, Scotland
- Thanked 39 Times in 39 Posts
Firstly, I would use the inbuilt mysql radians() function. It will be faster as it will be native.
Also, I would calculate the radian angles as return parameters first. e.g. RADIANS(mylat-lat) as latanglerad and then use that to calculate the distance parameter (again, prevents mysql calculating it twice).
Also, anywhere you are multiplying or dividing constants (pi/180 or 3956*2) calculate them manually and use that figure. Avoid having mysql do that calculation each time.
Hope that helps.