mysql - 根据邮政编码和它在 MySQL 中的半径选择记录

Question

下面是我的 postal 表，其中有几条记录。事实上，这张表包含所有城市和所有邮政编码。

id    city                 postalcode    latitude          longitude
 1    Drogteropslagen      7705 PA       52.61666700       6.50000000
 2    Coevorden            7740 AA       52.66666700       6.75000000
 3    Emmen                7812 TN       52.78333300       6.90000000
 4    Emmer-Compascuum     7881 PZ       52.81666700       7.05000000
 5    Nieuw-Dordrecht      7885 AA       52.75000000       6.96666700

下面是我的公司表，其中包含邮政编码和每家公司能够提供服务的半径(以千米为单位)。

id   company_name   city              postalcode   radius   latitude      longitude
1    A              Emmen             7812 TN      10       52.78333300   6.90000000
2    B              Nieuw-Dordrecht   7885 AA      5        52.75000000   6.96666700
3    C              Drogteropslagen   7705 PA      25       52.61666700   6.50000000
4    D              Foxhol            9607 PR      0        53.16666700   6.71666700
5    E              Westerbroek       9608 PA      15       53.18333300   6.68333300

我想选择具有特定邮政编码的公司，例如7740 AA 居住在邮政编码加公司半径范围内。请注意，特定的邮政编码可能并不总是存在于 company 表中，但始终存在于 postal 表中。如何编写一个 sql 查询来选择那些公司。

Answer 1

此方法在包含感兴趣公司的单个记录的子查询与包含所有公司的子查询之间执行CROSS JOIN。该查询使用 Haversine 公式计算两家公司之间的距离(以公里为单位)(您可以阅读更多关于 here 的信息)。

此查询用于获取邮政编码 7740 AA 20 公里范围内的所有公司。

SELECT t2.company_name,
    (6371 * acos(cos(radians(t1.lat1)) * cos(radians(t2.lat2)) 
    * cos(radians(t2.lng2) - radians(t1.lng1)) + sin(radians(t1.lat1)) * sin(radians(t2.lat2)))) AS distance
FROM
(
    SELECT p.latitude AS lat1, p.longitude AS lng1
    FROM postal p
    WHERE p.postalcode = '7740 AA'
) t1
CROSS JOIN
(
    SELECT c.company_name, p.latitude AS lat2, p.longitude AS lng2
    FROM company c INNER JOIN postal p
        ON c.postalcode = p.postalcode
) t2
HAVING distance < 20