mysql距离范围实现,如何限制MySQL距离查询

今天药忘吃喽~ 2023-01-24 03:30 24阅读 0赞

![Image 1][]

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

[Image 1]: