MySQL Select N Closest Zip Codes For Each Row

Question

I have a table full of zip codes. For example:

------------------------------
| zip | latitude | longitude |
------------------------------
|00001| 35.34543 | -76.34234 |
|00002| 43.23423 | -80.32423 |
...
|00008| 24.34543 | -20.53454 |
------------------------------

For each row I want to select the n closest zip codes. However, I can only seem to figure out how to select the single closest zip code. How can I adapt this for all zip codes:

SELECT
  zip, (
    3959 * acos (
      cos ( radians(78.3232) )
      * cos( radians( latitude ) )
      * cos( radians( longitude ) - radians(65.3234) )
      + sin ( radians(78.3232) )
      * sin( radians( latitude ) )
    )
  ) AS distance
FROM zipcodes
ORDER BY distance
LIMIT 0 , 20;

This would select the 20 closes zip codes, but I would need to apply this to every row. How can I do this for the entire table at once?

Is that SQL correct? is there a function called "distance"? you have zip, distance(formula) as distance. Maybe you just want formula as distance? — koriander, Feb 17 '15 at 21:54
btw, it can be done, but it will be heavy in computation. maybe have a look at this post first http://stackoverflow.com/questions/1006654/fastest-way-to-find-distance-between-two-lat-long-points — koriander, Feb 17 '15 at 21:56
check out the second formula on [philcolbourn answer](http://stackoverflow.com/questions/481144/equation-for-testing-if-a-point-is-inside-a-circle) i think its more suitable for you. — josegomezr, Feb 18 '15 at 07:01
@koriander What are you talking about? `distance` is an alias for everything that precedes it. — Strawberry, Feb 18 '15 at 16:30

score 0 · Answer 1 · answered Feb 18 '15 at 16:26

After reading this, I think you can use the Haversine formula to calculate the distance between two points, given the latitude and longitude of each one:

Here's an example:

select zc.*
     -- First, convert the latitude and longitude to radians:
     , @lat1 := radians(@latitude) as lat1_rad  
     , @lon1 := radians(@longitude) as lon1_rad
     , @lat2 := radians(zc.latitude) as lat2_rad
     , @lon2 := radians(zc.longitude) as lon2_rad
     -- Calculate the differences in latitude and longitude:
     , @delta_lat := @lat2 - @lat1 as delta_lat
     , @delta_lon := @lon2 - @lon1 as delta_lon
     -- The Haversine Formula:
     , @a := pow(sin(@delta_lat / 2), 2) + cos(@lat2) * cos(@lat1) * pow(sin(@delta_lon / 2), 2) as a
     , @c := 2 * atan2(sqrt(@a), sqrt(1 - @a)) as c
     , @d := @R * @c as d -- Distance (Km)
from 
    (select @R := 6371  -- The radius of the earth (Km)
          , @latitude := 65.3234    -- Latitude of the initial point
          , @longitude := -78.3232  -- Longitude of the initial point
    ) as init,
    zip_codes as zc
-- Add any WHERE conditions and/or ORDER
;

If you want to put this in a function:

delimiter $$
create function haversine_distance(latitude1 double, longitude1 double
                                 , latitude2 double, longitude2 double)
returns double
-- Input: Latitude and longitude of the points you want to calculate,
          given in degrees
begin
    declare lat1, lon1, lat2, lon2 double;
    declare delta_lat, delta_lon double;
    declare a, c, d double;
    declare R double default 6371; -- The radius of the Earth
    -- Convert the inputs to radians
    set lat1 = radians(latitude1);
    set lon1 = radians(longitude1);
    set lat2 = radians(latitude2);
    set lon2 = radians(longitude2);
    -- Calculate the differences between latitudes and longitudes
    set delta_lat = lat2 - lat1;
    set delta_lon = lon2 - lon1;
    -- The Haversine formula
    set a = pow(sin(@delta_lat / 2), 2) +
            cos(lat2) * cos(lat1) * pow(sin(delta_lon / 2), 2);
    set c = 2 * atan2(sqrt(a), sqrt(1 - 1);
    set d = R * c;
    return d;
end $$
delimiter ;

And finally, you can use this function to calculate the distances between two points, and filter the nearest n. I'll use user variables to define the latitude and longitude of the origin point:

set @latitude1 = 65.3234, @longitude1 = -78.3232;
set @n = 5; -- The number of nearest points
select zc.*
     , haversine_distance(@latitude1, @longitude1, 
                          zc.latitude, zc.longitude) as distance_km
from zip_codes as zc
order by distance_km
limit @n;

MySQL Select N Closest Zip Codes For Each Row

1 Answers1