How to limit a MySQL Distance Query

Question

I'm trying to preform a distance calculation to return a listing of places within a certain distance. This is based on using a zip code database and determining the distance from the origin to each location. What I want to do is limit the results to be within a certain distance from the origin, but I'm having trouble with my MySQL query. Here's the basic query:

SELECT *, 
       ROUND(DEGREES(ACOS(SIN(RADIANS(42.320271)) * SIN(RADIANS(zip_latitude)) + COS(RADIANS(42.320271)) * COS(RADIANS(zip_latitude)) * COS(RADIANS(-88.462832 - zip_longitude))))) * 69.09 AS distance 
  FROM locations 
LEFT JOIN zip_codes USING (zip_code)  
 ORDER BY distance ASC

This works great and gives me all the info for each location including the distance from the origin zip code...exactly what I want. However, I want to limit the results to fall within a certain distance (i.e., WHERE distance<=50).

My question and problem is I can't figure out where to include (WHERE distance<=50) into the query above to make it all work. Everything I've tried gives me an error message. Any help would be great.

Answer 1

A simple solution is to wrap your query in another query and put the condition and order there. This should work:

SELECT * FROM (
SELECT *, ROUND(DEGREES(ACOS(SIN(RADIANS(42.320271)) * SIN(RADIANS(zip_latitude)) + COS(RADIANS(42.320271)) * COS(RADIANS(zip_latitude)) * COS(RADIANS(-88.462832 - zip_longitude))))) * 69.09 AS distance FROM locations LEFT JOIN zip_codes USING (zip_code)
) WHERE distance <= 50 ORDER BY distance ASC

The middle line is your query without the ORDER BY

Answer 2

You have two options:

1. Restate the logic in the WHERE clause so you can filter by it:

      SELECT *, 
             ROUND(DEGREES(ACOS(SIN(RADIANS(42.320271)) * SIN(RADIANS(zip_latitude)) + COS(RADIANS(42.320271)) * COS(RADIANS(zip_latitude)) * COS(RADIANS(-88.462832 - zip_longitude))))) * 69.09 AS distance 
        FROM locations 
   LEFT JOIN zip_codes USING (zip_code)  
       WHERE (ROUND(DEGREES(ACOS(SIN(RADIANS(42.320271)) * SIN(RADIANS(zip_latitude)) + COS(RADIANS(42.320271)) * COS(RADIANS(zip_latitude)) * COS(RADIANS(-88.462832 - zip_longitude))))) * 69.09) <= 50
    ORDER BY distance 
   

   This is the better choice, because it requires only one pass over the data. Sadly, it requires you to duplicate the logic -- if you were using the information in the GROUP BY or HAVING clause, MySQL supports referencing a column alias in those.

2. Use a subquery:

     SELECT x.* 
       FROM (SELECT *, 
                    ROUND(DEGREES(ACOS(SIN(RADIANS(42.320271)) * SIN(RADIANS(zip_latitude)) + COS(RADIANS(42.320271)) * COS(RADIANS(zip_latitude)) * COS(RADIANS(-88.462832 - zip_longitude))))) * 69.09 AS distance 
               FROM locations 
          LEFT JOIN zip_codes USING (zip_code)) x
      WHERE x.distance <= 50 
   ORDER BY x.distance