mysql - 如何按最接近的数值进行更新

Question

我有两个表 Cities_no_iata 该表有列(CountryID、iata_code、latitude、longtiute)

Cities_iata 此表包含列(CountryID、iata_code、纬度、经度)

第一个表包含没有 iata_code 的城市，因此 iata_code 列中的所有行均为空

第二个表包含具有 iata_code 的城市

所以我想将最近的城市 iata_code 复制到没有 iata_code 的城市，我想通过两件事来做到这一点

1- 两个城市最接近的(纬度和经度)

2- 两个城市的 CountryID 相同。

Update Cities_iata
JOIN Cities_no_iata USING (CountryID)
Where ABS( cities_iata.latitude)= ABS(Cities_no_iata.latitude)
AND  ABS( cities_iata.longitude)= ABS(Cities_no_iata.longitude)
AND cities_iata.CountryID = Cities_no_iata.country_id
set(   Cities_no_iata.iata_code =cities_iata.iata_code);

但不起作用

Answer 1

此查询使用欧几里得公式近似距离。实际上，即使对于近似值，您也需要考虑到纬度的单位差异与经度的单位差异不同，具体取决于纬度 - 这需要将纬度差异乘以余弦。

但是，我假设问题实际上是关于找到最接近的，而不是关于特定的距离公式。以下查询返回表中没有城市的最接近的 iata 代码:

select cni.*,
       (select iata_code
        from cities_iata ci
        order by pow(cia.latitude - ci.latitude, 2) + pow(cia.longitude - ci.longitude, 2)
        limit 1
       ) as new_iata_code
from cities_no_iata cni;

我们可以使用join将其转换为更新:

update cities_no_iata join
       (select cni.*,
               (select iata_code
                from cities_iata ci
                order by pow(cia.latitude - ci.latitude, 2) + pow(cia.longitude - ci.longitude, 2)
                limit 1
               ) as new_iata_code
        from cities_no_iata cni
       ) upd
       on cities_no_iata.latitude = upd.latitude and
          cities_no_iata.longitude = upd.longitude
     set iata_code = upd.iata_code;

两条评论。您可以将任何您喜欢的距离函数放入 order by 子句中，因此这应该概括为您想要的。其次，每一行都应该有一个唯一的标识符，因此最终的连接可以通过城市标识符而不是纬度和经度来完成。