mysql - Select towns that have nearby things -

i have 2 tables examples below, towns , things, , need list of towns have nearby things within x distance town record. latitude , longitude used distance calculation.

i've looked @ other questions , have managed records things single specified town, can't think how list of towns have nearby things closer x distance them.

being able sort resulting towns number of nearby things within x distance bonus.

towns +--------+----------+---------+---------+ | townid | townname | townlat | townlng | +--------+----------+---------+---------+ |      1 | town   |     1.5 |     1.9 | |      2 | town b   |     1.4 |     3.8 | |      3 | town c   |     2.3 |     2.7 | |      4 | town d   |     3.2 |     1.6 | |    ... | ...      |     ... |     ... | +--------+----------+---------+---------+  things +---------+-----------+----------+----------+ | thingid | thingname | thinglat | thinglng | +---------+-----------+----------+----------+ |       1 | thing   | 2.1      | 3.1      | |       2 | thing b   | 1.1      | 2.3      | |       3 | thing c   | 3.2      | 0.2      | |       4 | thing d   | 1.3      | 1.1      | |     ... | ...       | ...      | ...      | +---------+-----------+----------+----------+ 

thanks in advance

you can cross join obtain possible combinations of towns , things, , calculate haversine distance between each town , thing. use select distinct make sure town listed once in result set.

select distinct towns.townname towns cross join things 3959 * acos(    cos(radians( towns.townlat )) * cos(radians( things.thinglat )) * cos(radians( towns.townlng ) - radians( things.thinglng )) + sin(radians( towns.townlat ))  * sin(radians( things.thinglat )) ) < x 

the formula used x in miles (the mean radius of earth 3959 miles).