根据邮政编码及其在 MySQL 中的半径选择记录 [英] Select records based on the postal code and it's radius in MySQL

问题描述

下面是我的 postal 表，其中包含一些记录.事实上，该表包含所有城市和所有邮政编码.

Below is my postal table with a few records. In fact this table contains all cities and all postal codes.

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

下面是我的 company 表格，其中包含邮政编码以及每家公司能够提供服务的半径(以公里为单位).

Below is my company table with it's postal code and the radius in kilometers where each company is able to provide his services.

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 查询来选择这些公司.

I would like to select the companies which a particular postal code e.g. 7740 AA lives in the area of the postal code plus the radius of a company. Note that the particular postal code might not always exist in the company table but always exist in the postal table. How to write a sql query to select those companies.

推荐答案

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

This approach performs a CROSS JOIN between a subquery containing a single record for the company of interest against a subquery containing every company. The query uses the Haversine formula for computing the distance between two companies in kilometers (and you can read more about that here).

您可以使用此查询来获取邮政编码 7740 AA 半径 20 公里范围内的所有公司.

This query is what you would use to get all companies within a 20km radius of postal code 7740 AA.

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

这篇关于根据邮政编码及其在 MySQL 中的半径选择记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！