确定距离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 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;