Select towns that have nearby things

Question

I have 2 tables like the examples below, towns and things, and need to get a list of towns that have nearby things that are within x distance from that town record. Latitude and longitude will be used to do the distance calculation.

I've looked at some other questions and have managed to get records from things for a single specified town, but can't think how to get a list of all towns that have nearby things that are closer than x distance from them.

Being able to sort the resulting towns by number of nearby things within x distance would be a bonus.

TOWNS
+--------+----------+---------+---------+
| townId | townName | townLat | townLng |
+--------+----------+---------+---------+
|      1 | town a   |     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 a   | 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

Answer 1

You can do a CROSS JOIN to obtain all possible combinations of towns and things, and then calculate the Haversine distance between each town and thing. I use SELECT DISTINCT to make sure a town is only listed once in the result set.

SELECT DISTINCT TOWNS.townName FROM
TOWNS CROSS JOIN THINGS
WHERE 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 I used is for x in miles (the mean radius of Earth is 3959 miles).