mysql - 确定距离MySQL

我的代码如下，应该可以工作，但是由于某种原因，它无法在join语句中识别wm_stores.lat。有人有想法么？

SELECT wm_dcs.dc_id, wm_stores.store_id
FROM wm_dcs
JOIN wm_stores
    ON wm_stores.lat BETWEEN wm_dcs.lat - (250.0 / 69.0)
        AND wm_dcs.lat + (250.0 / 69.0)
        AND wm_stores.lon BETWEEN wm_dcs.lon - (250.0 / (69.0 * COS(RADIANS(wm_dcs.lat))))
        AND wm_dcs.lon + (250.0 / (69.0 * COS(RADIANS(wm_dcs.lat))))
        AND  (69.0 * DEGREES(ACOS(COS(RADIANS(wm_dcs.lat) * COS(RADIANS(stores.latitude))
            * COS(RADIANS(dc.longitude - stores.longitude))
            + SIN(RADIANS(dc.latitude))
            * SIN(RADIANS(wm_stores.lon)))))) <= 250.0;

不同版本的代码：

set @dc_lat = 40.811973 ;
set @dc_lon = -73.946299 ;

select wm_stores.store_id,
( 3959 * acos( cos( radians(@dc_lat) ) * cos( radians(wm_stores.lat ) )
* cos( radians( wm_stores.lon ) - radians(@dc_lon) ) + sin( radians(@dc_lat) )
* sin( radians( wm_stores.lat ) ) ) ) AS distance
from wm_stores
having distance <= 250
order by distance asc;

最佳答案

在您的查询中

SELECT wm_dcs.dc_id, wm_stores.store_id
FROM wm_dcs
JOIN wm_stores
    ON wm_stores.lat BETWEEN wm_dcs.lat - (250.0 / 69.0)
        AND wm_dcs.lat + (250.0 / 69.0)
        AND wm_stores.lon BETWEEN wm_dcs.lon - (250.0 / (69.0 * COS(RADIANS(wm_dcs.lat))))
        AND wm_dcs.lon + (250.0 / (69.0 * COS(RADIANS(wm_dcs.lat))))
        AND  (69.0 * DEGREES(ACOS(COS(RADIANS(wm_dcs.lat) * COS(RADIANS(stores.latitude))
            * COS(RADIANS(dc.longitude - stores.longitude))
            + SIN(RADIANS(dc.latitude))
            * SIN(RADIANS(wm_stores.lon)))))) <= 250.0;

我注意到没有像stores这样的表，但是您在查询中使用了stores.latitude和stores.longitude。可能是导致错误消息Unknown column 'stores.latitude' in 'on clause'的原因。

我建议您重新检查代码

AND  (69.0 * DEGREES(ACOS(COS(RADIANS(wm_dcs.lat) * COS(RADIANS(stores.latitude))
            * COS(RADIANS(dc.longitude - stores.longitude))
            + SIN(RADIANS(dc.latitude))
            * SIN(RADIANS(wm_stores.lon)))))) <= 250.0;

关于mysql - 确定距离MySQL，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/40453555/

Stores

mysql - 确定距离MySQL