Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    Oct 2003
    Posts
    603
    Thanks
    2
    Thanked 1 Time in 1 Post

    geolocation-based querying

    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.

    PHP Code:
    $query="SELECT a.id,a.gender,a.last_activity,a.photo_url,a.username,a.city,a.state,a.zipcode,a.country,a.lat,a.lon,b.status,
    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"


  • #2
    Regular Coder Stooshie's Avatar
    Join Date
    Mar 2008
    Location
    Dundee, Scotland
    Posts
    380
    Thanks
    9
    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.
    Regards, Stooshie
    O


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •