mysql距离范围实现,如何限制MySQL距离查询-CSDN博客

该博客探讨了如何在MySQL查询中计算基于邮政编码的距离，并限制返回的结果在特定范围内。博主分享了两个解决方案：一是将距离计算逻辑置于WHERE子句中，二是使用子查询来过滤距离小于等于50的结果。这两个方法旨在优化查询性能并有效地筛选地理位置数据。

摘要由CSDN通过智能技术生成

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.

解决方案

You have two options:

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.

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