选择最近的城市的MySQL查询

Question

我正在尝试为所有行重复以下查询。基本上，我正在尝试将最接近的城市（根据纬度和经度）映射到地点的纬度和经度。我有一个包含需要映射的地点的表places，以及一个包含要匹配的地点的CityTable表。我有以下查询适用于单个行：

SELECT p.placeID, p.State, p.City, p.County, p.name, 
       SQRT(POW((69.1 * (p.lat - z.Latitude)), 2 ) 
       + POW((53 * (p.lng - z.Loungitude)), 2)) AS distance,
       p.lat,p.lng,z.Latitude,z.Loungitude,z.City 
FROM places p,CityTable z 
WHERE p.placeID = 1 
ORDER BY distance ASC 
LIMIT 1;

这适用于单个位置。显然，我需要删除WHERE约束条件才能将其应用于整个表格。我遇到的问题是它似乎想要复制以与表中的每个其他元素进行比较。例如，如果p中有100行，z中也有100行，则生成的表似乎为10,000行。我需要表的大小为p的count(*)。有什么建议吗？如果我的p表包含超过一百万行，是否有更有效的方法来执行此操作？谢谢。

- user2694306

---

要为每个地点找到城市，您必须将每个地点与每个城市进行比较。 - Cthulhu

重复？阅读此内容：https://dev59.com/xnNA5IYBdhLWcg3wVcJx - iiro

如果我想将每个地方与每个城市进行比较，我该如何将其放入for循环中？谢谢。 - user2694306

Answer 1

您可以使用以下方法找到离某个地方最近的城市：

SELECT p.placeID, p.State, p.City, p.County, p.name, 
       (select z.City
        from CityTable z
        order by SQRT(POW((69.1 * (p.lat - z.Latitude)), 2 ) + POW((53 * (p.lng - z.Loungitude)), 2)) 
        limit 1
       ) as City,
       p.lat, p.lng
FROM places p
ORDER BY distance ASC;

如果您需要更多的城市信息，请在City上加入城市表。

这并没有解决必须进行笛卡尔积的问题。然而，它以不同的方式来构建它。如果您知道一个城市在任何地方的经度/纬度范围内五度之内，那么您可以使子查询更有效：

       (select z.City
        from CityTable z
        where z.lat >= p.lat + 5 and z.lat <= p.lat - 5 and
              z.long <= p.long + 5 and z.long <= p.lat - 5
        order by SQRT(POW((69.1 * (p.lat - z.Latitude)), 2 ) + POW((53 * (p.lng - z.Loungitude)), 2)) 
        limit 1
       ) as City,
       p.lat, p.lng;

这个查询将使用lat上的索引。它甚至可能会使用lat，long的索引。

如果这还不够，请考虑通过仅查看邻近的州（在美国）或国家来减少搜索空间。

最后，如果您经常处理此类数据，则可以考虑使用MySQL的地理空间扩展。

- Gordon Linoff