maps | bnks.xyz

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.

Mapping series – Working with Algolia Places address autocompletion api

Posted on 19 - 05 - 1915 - 02 - 20 by Phil

For a recent mapping project I implemented Algolia Places for address autocompletion to turn an address into latitude and longitude for querying the database. In the past we’ve used Google Maps, but since this project wasn’t using Google Maps for the map display, using the Places API just for Geocoding is against their terms of use. It turns out this was a blessing in disguise – Algolia is fast, easy to implement, and very affordable. There was however one small hitch – the documentation get’s a bit patchy when you go past a basic implementation. To be fair to them – it’s actually because they start presuming that you’ll be using their multipurpose algoliasearchLite.js library rather than the simpler places.js.

Setting up autocompletion

The example from the documentation only needs a small extension to setup – populating hidden latitude and longitude fields from the returned data by using the ‘change’ event:

<script>
var places = places({
    appId: 'YOUR_PLACES_APP_ID',
    apiKey: 'YOUR_PLACES_API_KEY',
    container: address_input
});
places.on('change', function(e) {
    address_input.textContent = e.suggestion.value
    latitude_input.value = e.suggestion.latlng.lat || '';
    longitude_input.value = e.suggestion.latlng.lng || '';
});
</script>

Reverse geocoding

To give users a number of options, we also provided a geolocation button that uses the Geolocation API to let them search using the location reported by their system. The API returns latitude and longitude coordinates. While this is all that is needed to query the database – the UX isn’t ideal as it wouldn’t give a user readable representation of the location. This is important in case the returned location is wrong. Converting the coordinates into an address is called reverse geocoding.

The Places documentation has an example of reverse geocoding but unfortunately this is one that uses the wrong library. While there isn’t official support for Places, Algolia staff do monitor StackOverflow and help where they can. Luckily one such employee, Oliver, saw my query and got me on the right track.

To make a query you pass a compound latitude/longitude string value, and then an object of any options you want to change. For example:

places.reverse(
    '52.817078,-4.5697185',
    { hitsPerPage: 1 }
)

In another difference from algoliasearchLite.js – the response when using places.js is just the array of results. This makes utilising the results trivial. For example:

places.reverse(
    '52.817078,-4.5697185',
    { hitsPerPage: 1 }
).then(function(response){
    var suggestion = response[0];
    if (suggestion && (suggestion.suburb || suggestion.city)) {
        address_input.value = suggestion.suburb || suggestion.city || suggestion.county;
        address_input.value += ', ' + suggestion.country;
    }
});

Here I’ve chosen to populate the address text input field with the town (aka suburb) if available, and then the country. This gives enough information to orientate the user with what search is being done, without distracting them with the potentially/likely inaccurate house number and road level data.

From my experience so far I’d highly recommend you evaluate Algolia Places for your next autocompletion or geocoding project. The only downside I’ve found, common to all providers that rely on OSM data, is that you can’t reliable search by UK postcodes. In a subsequent post I’ll cover implementing getAddress.io – an API to turn UK postcodes into addresses using the Royal Mail PAF data.