mysql - SQL 分组依据。不同城市的重复 latlng

Question

我很久以前购买了一个地理数据库，并且正在将其精度更新为纬度/经度值。但我发现了一些奇怪的东西。有些城市具有相同的纬度/经度坐标。这在地理上是不可能的。

id   City    State   Lat    Lng
1     A        sA     XX     XX
2     B        sA     XX     XX
3     C        sA     YY     YY
4     D        sA     ZZ     ZZ

所以我尝试了按城市、纬度、经度分组，但由于我需要 id 来更新记录，group by 子句会要求我添加“id”列。

表中的 id 1 和 2 应更新，而 3 和 4 除外。不应该是 2 个(或更多)具有相同纬度/经度的城市。该表有 22K 行。我可以将所有内容发送到 gmap API，但我正在寻找尽可能智能地使用 API 的时间、带宽和点击次数，但考虑到我可以使用免费 API 访问每秒发出一个请求，我已经没有时间了。

我已经尝试过

    SELECT DISTINCT postcodes_id, Latitude, Longitude, Region1Name, Region2Name, Nation_D
      FROM postcodes
      where Latitude + Longitude  IN 
      (
             SELECT Latitude + Longitude
             FROM
             (  
                        SELECT postcodes_id, Latitude, Longitude, count(distinct(Region2Name))  as cantidad 
                        FROM postcodes
                        where Nation_D is not null
                        GROUP BY Latitude, Longitude
                        having count(distinct(Region2Name)) > 1
             ) A
      )
      AND Nation_D IS NOT NULL

  ORDER BY  Latitude, Longitude, Region1Name, Region2Name, Nation_D

但没有按预期工作。我认为对于新人来说这是非常明显的。

我编写了一个 python 脚本来使用 Google map 地理编码来获取当前的纬度/经度，并在不同时更新它。这个脚本工作正常。

希望有人有想法。谢谢!!

在 CentOS 7 上运行 MySQL 5.5 和 Python 2.7。

Answer 1

给您一些提示，可能会有所帮助:

1. 您不应在纬度/经度或它们的任何组合上使用group by或distinct，因为它们是连续的 float ，而不是离散整数或字符串。

2. 出于同样的原因，您不应在纬度/经度或其总和上使用 WHERE 子句。如果您想检查两个位置的接近程度，请使用 st_distance() 函数。

3. 多个城市名称可以指代同一位置。例如，纽约州纽约市和纽约州曼哈顿。

还有一个非技术问题:将 Google 地理编码数据存储在您的数据库中违反了他们的许可协议(protocol)。