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