MySQL 计算邮政编码之间的距离更快更准确？

Question

我有一个包含 42000 多个邮政编码、经度、纬度和州信息的表格。返回结果的最准确和最快的查询是什么，其中包含输入的邮政编码半径为 25 英里的所有邮政编码？

当前代码(我认为它不准确)

SELECT
zipcode, (
  3959 * acos (
  cos ( radians(78.3232) )
  * cos( radians( latitude ) )
  * cos( radians( longitude ) - radians(65.3234) )
  + sin ( radians(78.3232) )
  * sin( radians( latitude ) )
)
) AS distance
FROM Location
HAVING distance < 25
ORDER BY distance

Answer 1

关于准确性

准确计算距离的唯一方法是使用 3D 三角，正如您正在做的那样。您可以在此处阅读有关该主题的更多信息:https://en.wikipedia.org/wiki/Geographical_distance

尽管在邮政编码的经纬度中心点之间给出了相当准确的距离，这些中心点是任意选择的，并且“如乌鸦飞翔”一样计算距离，所以您赢了' 准确表示每个点内两点之间的实际行进距离。

例如，您可能在相邻的邮政编码中有两个彼此隔壁的房屋，或者在每个邮政编码的两端有两个房屋，根据此计算，它们将计算为等距。

解决这个问题的唯一方法是计算地址距离，这需要 USPS 数据将地址映射到更具体的点，或者使用像 Google map 这样的 API，它也会计算给定可用道路的实际行驶距离.

关于性能

有几种方法可以加快查询速度。

<强>1。减少实时数学运算

实时计算的最快方法是预先计算昂贵的三角值并将其存储在表的列中，例如:

ALTER TABLE Location
    ADD COLUMN cos_rad_lat DOUBLE,
    ADD COLUMN cos_rad_lng DOUBLE,
    ADD COLUMN sin_rad_lat DOUBLE;

然后

UPDATE Location
SET cos_rad_lat = cos(radians(latitude)),
    cos_rad_lng = cos(radians(longitude)),
    sin_rad_lat = sin(radians(latitude));

在查询之外进行 cos(radians(78.3232)) 类型的计算，这样就不会对每一行数据都进行数学计算。

因此将所有计算减少为常量值(在获取 SQL 之前)和计算列将使您的查询看起来像这样:

SELECT
    zipcode,
    3959 * acos(
        0.20239077538110228
        * cos_rad_lat
        * cos_rad_lng - 1.140108408597264
    )
    + 0.979304842243025 * sin_rad_lat AS distance
FROM Location
HAVING distance < 25
ORDER BY distance

<强>2。边界框减少

注意:您可以将此与方法 1 结合使用。

在执行触发之前，您可以通过在子查询中添加 zip 的边界框缩减来稍微提高性能，但这可能比您想要的更复杂。

例如，代替:

FROM Location

你可以做

FROM (
    SELECT * 
    FROM Location 
    WHERE latitude BETWEEN A and B
        AND longitude BETWEEN C and D
) AS Location

其中 A、B、C 和 D 是与您的中心点相对应的数字 +- 大约 0.3(因为在美国，纬度/经度的每十分之一度对应于大约 5-7 英里)。

此方法在 -180/180 经度处变得棘手，但这不会影响美国。

<强>3。存储所有计算的距离您可以做的另一件事是预先计算所有 zipper 的所有距离，然后存储在单独的表中

CREATE TABLE LocationDistance (
    zipcode1 varchar(5) NOT NULL REFERENCES Location(zipcode),
    zipcode2 varchar(5) NOT NULL REFERENCES Location(zipcode)
    distance double NOT NULL,
    PRIMARY KEY (zipcode1, zipcode2),
    INDEX (zipcode1, distance)
);

用 zip 及其计算距离的每个组合填充此表。

您的查询将如下所示:

SELECT zipcode2
FROM LocationDistance 
WHERE zipcode1 = 12345
    AND distance < 25;

这将是迄今为止最快的解决方案，尽管它涉及存储大约 10 亿条记录。