选择基于记录的邮政编码，它是MySQL中的半径，纬度和经度 [英] Select records based zipcode, it's radius, lat and lon in MySQL

问题描述

下面是我的company表，其中包含postalcode，lat，lon和radius(以公里为单位)，每个公司都可以提供其服务.

Below is my company table with it's postalcode, lat, lon and the radius in kilometers where each company is able to provide his services.

id  company_name   city                 postalcode  radiu   latitude       longitude
 1  A              Drogteropslagen      7705 PA     10      52.61666700    6.50000000
 2  B              Coevorden            7740 AA     15      52.66666700    6.75000000
 3  C              Emmen                7812 TN     5       52.78333300    6.9000000
 4  D              Emmer-Compascuum     7881 PZ     25      52.81666700    7.05000000
 5  E              Nieuw-Dordrecht      7885 AA     60      52.75000000    6.96666700

我想选择一个特定邮政编码的公司，例如7813 AB生活在他们的postalcode + radius中，即使该邮政编码7813 AB与公司也不完全相同.如何编写一个SQL查询来选择那些公司?

I would like to select the companies which a particular postalcode e.g. 7813 AB lives within their postalcode + the radius, even this postalcode 7813 AB is not exact the same as that of a company. how to write a sql query to select those companies?

推荐答案

SELECT t1.company_name, 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,
    t1.radius
FROM
(
    SELECT company_name, latitude AS lat1, longitude AS lng1,
        radius
    FROM company
    WHERE postalcode = '7813 AB'
) t1
CROSS JOIN
(
    SELECT company_name, latitude AS lat2, longitude AS lng2
    FROM company
) t2
HAVING distance < t1.radius AND t1.company_name != t2.company_name

这篇关于选择基于记录的邮政编码，它是MySQL中的半径，纬度和经度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！