bnks.xyz

Menu

Mapping series – MySQL queries against latitude and longitude

I’m starting this series with the simplest aspect – a MySQL query to return rows based on radius from a point.
This is well trodden territory, although not always explained clearly.

The maths of the query is all done for you, all you need is to decide if you are using miles of kilometres, and supply a radius in the same units, along with a latitude and longitude for the centre of the search.

Units of search

Consistence matters here, rather than whether you choose miles or kilometres.
If you choose miles, then the earths radius value is 3959, and for kilometres use 6371. Then you must ensure that the radius is the in the same units otherwise you won’t get the results you expect.

The MySQL query

Here is an example query; select an ID, the latitude, longitude and distance from the centre of the search, ordered by distance from the centre of the search.

SELECT
	'ID',
	'latitude' as 'lat',
	'longitude' as 'lng',
	(
		{earths radius - miles or kilometres} * acos(
			cos(
				radians( {center for search - latitude} )
			) * cos(
				radians(
					'latitude'
				)
			) * cos(
				radians(
					'longitude'
				) - radians( {center for search - longitude} )
			) + sin(
				radians( {center for search - latitude} )
			) * sin(
				radians(
					'latitude'
				)
			)
		)
	) AS distance
FROM data_table
HAVING distance < {radius for search - units to match earths radius}
ORDER BY distance

I won’t attempt to explain the math of it – for that I suggest you read up on the Haversine formula – but implementation is very straight forward as you can see.

In a later part fo this series I’ll show how this can modified to work with data stored in WordPress and it’s table structure, as well as complicate it further with an Advanced Custom Fields repeater system.

Leave a Reply

Your email address will not be published. Required fields are marked *