O Connor O Connor - 2 years ago 69
SQL Question

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

Below is my

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
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
table but always exist in the
table. How to write a sql query to select those companies.

Answer Source

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
    SELECT p.latitude AS lat1, p.longitude AS lng1
    FROM postal p
    WHERE p.postalcode = '7740 AA'
) t1
    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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download