如何限制MySQL距离查询 [英] How to limit a MySQL Distance Query

问题描述

我正在尝试进行距离计算，以返回一定距离内的地点列表.这是基于使用邮政编码数据库并确定从原点到每个位置的距离.我想做的是将结果限制在距原点一定距离之内，但是我在使用MySQL查询时遇到了麻烦.这是基本查询:

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

这很好用，并为我提供了每个位置的所有信息，包括距原邮政编码的距离...正是我想要的.但是，我希望将结果限制在一定距离内(即WHERE distance <= 50).

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).

我的问题是，我不知道要在上面的查询中包含(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.

推荐答案

您有两个选择:

1. 重述WHERE子句中的逻辑，以便您可以对其进行过滤:

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 

这是更好的选择，因为它只需要对数据进行一次传递.可悲的是，它要求您重复逻辑-如果您使用的是GROUP BY或HAVING子句中的信息，则MySQL支持在其中引用列别名.

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.

使用子查询:

  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 

这篇关于如何限制MySQL距离查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！