mysql - 确定距离 MySQL

Question

我的代码如下，应该可以工作，但由于某种原因，它无法识别 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; 

Answer 1

在您的查询中

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 columns '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;