查找附近的邮政编码myquery优化

Question

I have need find nearby postcode of point in google map , I am using SQL – Haversine formula. below is my query

 $query = $this->db->query("SELECT Postcode,latitude,longtitude, ACOS(COS(RADIANS(latitude)) *
    COS(RADIANS(longtitude)) * COS(RADIANS($latitude)) * COS(RADIANS($longtitude)) +
    COS(RADIANS(latitude)) * SIN(RADIANS(longtitude)) * COS(RADIANS($latitude)) * 
    SIN(RADIANS($longtitude)) + SIN(RADIANS(latitude)) * SIN(RADIANS($latitude))) * 
    3963.1 AS Distance
    FROM postalscheme   
    HAVING Distance <= 4  "); 

Data is fetched correctly using this query, but my problem is its taking long time to fetch data [3 or 5 min for fetch 20 thousand to 60 thousand data ] .

please help me to optimize this query and work fast .

Answer 1

you can limit the range of data you have to calculate distances for when you limit the latitude/longitude values to the ones nearby. (formulas coming from these slides (page 12ff) This works when you calculate by miles... otherwise you have to adjust the "69" with the right value for kilometers.

1° of latitude ~= 69 miles 
1° of longitude ~= cos(latitude)*69

When you want to calculate the longitude and latitude for the smaller rectangle, you can use (with your variables above):

$dist = 4; // the distance from your query above
$lon1 = $longtitude - $dist / abs(cos(deg2rad($latitude))*69);
$lon2 = $longtitude + $dist / abs(cos(deg2rad($latitude))*69);
$lat1 = $latitude - ($dist / 69);
$lat2 = $latitude + ($dist / 69);

and then you modify your query like so:

$query = $this->db->query("SELECT Postcode,latitude,longtitude, ACOS(COS(RADIANS(latitude)) *
    COS(RADIANS(longtitude)) * COS(RADIANS($latitude)) * COS(RADIANS($longtitude)) +
    COS(RADIANS(latitude)) * SIN(RADIANS(longtitude)) * COS(RADIANS($latitude)) * 
    SIN(RADIANS($longtitude)) + SIN(RADIANS(latitude)) * SIN(RADIANS($latitude))) * 
    3963.1 AS Distance
    FROM postalscheme   
    WHERE longitude BETWEEN $lon1 AND $lon2 
    AND latitude BETWEEN $lat1 AND $lat2
    HAVING Distance <= $dist  ");

This should improve the overall speed drastically.