Select records based on the postal code and it's radius in MySQL

Question

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

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

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.

@Strawberry No algorithm is needed because he (smartly) already cached the latitude and longitude for each company. Instead, he can use the Havesine formula to determine the distance. — Tim Biegeleisen, May 24 '16 at 04:41
@Strawberry You were right about using `HAVING` instead of `WHERE`. You taste great on cereal and you help with SQL too :-) — Tim Biegeleisen, May 24 '16 at 10:08

score 1 · Accepted Answer · edited May 23 '17 at 12:15

1

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).

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

edited May 23 '17 at 12:15

Community

1
1

answered May 24 '16 at 04:40

Tim Biegeleisen

502,043
27
286
360

@Tim Biegeleisen: will it be easier if I have the latitude and longitude in the company table? – O Connor May 24 '16 at 07:20
@OConnor Based on the data in your question, I don't even really see the point in two separate tables. But you can give my query a try now. – Tim Biegeleisen May 24 '16 at 07:56
@TimBiegeleisen I do either see no point of separating tables, thus I have put the lat and lon the same table as company. Prevent confusing, I have added new question related to this topic with only one database table. – O Connor May 24 '16 at 09:20
My answer won't change much, it would simplify it as you would no longer need the `INNER JOIN` between the two tables. – Tim Biegeleisen May 24 '16 at 09:32
@TimBiegeleisen you can find the new question here http://stackoverflow.com/questions/37409252/select-records-based-zipcode-its-radius-lat-and-lon-in-mysql?noredirect=1#comment62325524_37409252 Sorry for late reply! – O Connor May 24 '16 at 09:41
@TimBiegeleisen I am trying to figure out where to put the particular postcalcode as the condition e.g. where postalcode = "7813 AB"; – O Connor May 24 '16 at 09:43
Replace `WHERE company_name = 'A'` with `WHERE postalcode = '7813 AB'`, that is all. – Tim Biegeleisen May 24 '16 at 09:45
I just updated my question here. Hope you still want to help me out. Thank you! – O Connor May 25 '16 at 08:40
@OConnor I updated my answer again. I hope this time it will meet your needs. – Tim Biegeleisen May 25 '16 at 09:25
your query compares the distance with a fixed 20 km. I have modified it a little bit so that the distance will compare with the radius (in km.) of each company dynamically, since each company provides his services in different km. of radius. Thanks for your time and help! I really appreciate that, man! – O Connor May 25 '16 at 10:49

score 0 · Answer 2 · answered May 25 '16 at 10:36

The query of Tim Biegeleisen inspires me to find the working solution as following.

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, 
       t2.radius 
FROM
    (
     SELECT p.latitude AS lat1, p.longitude AS lng1
     FROM postal p 
     WHERE p.postalcode = '7894 AA'
    ) t1
    CROSS JOIN
    (
     SELECT c.company_name, p.latitude AS lat2, p.longitude AS lng2,
            c.radius 
     FROM company c 
     INNER JOIN postal p
     ON c.postalcode = p.postalcode
    ) t2
HAVING distance < t2.radius

With the above query, the distance will not compare with a fixed numbers of kilometer (e.g. 20 or 50 km.) but with the dynamic radius (in km.) of each company.

Select records based on the postal code and it's radius in MySQL

2 Answers2

Linked