Recently we launched a website and platform for iGo Gift Vouchers. One of the screens shows an interactive list of the shops where vouchers can be used. The classic interface design pattern for this is to ask users to provide a postcode, or the name of a city or town, and then display the shops in that area on a map.
As usual, we decided to try something new and improve a little on the traditional functionality. We wanted to automatically detect where the user is right now, so we can display shops in their area as soon as they arrive on the page, without them having to enter any information. You can see how it works at www.igogiftvouchers.co.uk/myigo – when your browser asks if you would like to share your location with the page, just click the option to accept.
So why Geolocation and SQL? The first part of this task, identifying where the user is, is handled on the client side usining JavaScript. But SQL comes in as the best and fastest tool for the second part of the task: identifying which shops are nearest and should be displayed on the map.
Getting the user’s location
From a user’s IP address, you can generally figure out what country they are in, and sometimes even the city, using technology like Maxmind’s. It’s entirely transparent to the user, but it’s not particularly accurate or reliable, and it’s nowhere near good enough to suggest a list of shops in the area that a customer might want to visit.
The Geolocation API, on the other hand, enables developers to query the browser and get spectacularly reliable details of the user’s location, accurate in some areas to 10 metres or less. The under-the-bonnet mechanics of this vary from platform to platform, but Firefox and Chrome, for example, both use Google Location Services. Ingeniously, this maps details of nearby wifi networks (and where applicable, cell masts) to known locations, which were originally recorded while Google was driving around the world collecting images for its street view feature.
The API is supported by and built in all major modern web browsers – Internet Explorer, Firefox, Safari, Chrome and Opera, on both desktops and mobile devices.
The W3C specification for the Geolocation API is clear and simple:
interface NavigatorGeolocation {
readonly attribute Geolocation geolocation;
};
interface Geolocation {
void getCurrentPosition(in PositionCallback successCallback,
in optional PositionErrorCallback errorCallback,
in optional PositionOptions options);
long watchPosition(in PositionCallback successCallback,
in optional PositionErrorCallback errorCallback,
in optional PositionOptions options);
void clearWatch(in long watchId);
};
interface PositionCallback {
void handleEvent(in Position position);
};
interface PositionErrorCallback {
void handleEvent(in PositionError error);
};It’s a breeze to implement in JavaScript, with an asynchronous callback. At its simplest:
function showPosition(position) {
alert('Lat: ' + position.coords.latitude + ', Lon: ' + position.coords.longitude);
}
if (navigator.geolocation){
navigator.geolocation.getCurrentPosition(showPosition);
}Easy, and handy, isn’t it?
Distance calculation: JavaScript vs MySQL
So now we have the user’s position, the next step is to detect objects (in our case, shops) within a certain distance. However, doing this with coordinates in the WGS84 system is not a straightforward operation.
One option would be to do this on the client side, using the Google Maps API. The simplest solution in v2 would be to use GlatLng.distanceFrom(). But there is a disadvantage: you have to load all the objects into JavaScript, and then leave it up to API to identify those objects within your defined distance. The more objects you have, the heavier the load and the slower your app is going to get.
A nice solution would be to use the MySQL spatial extension, implementing the OpenGIS specification, store latitude and longitude as points, and then just calculate and sort the shop records by their distance from the origin using the Distance() function. Just one small problem – Distance() is not implemented in MySQL yet.
To solve this we wrote an SQL calculation based on the Haversine formula, and to make the code cleaner we encapsulated it into an SQL function calcDistance():
DELIMITER $$
DROP FUNCTION IF EXISTS `calc_distance` $$
CREATE FUNCTION `calc_distance`(lat FLOAT, lon FLOAT, locate_latitude FLOAT, locate_longitude FLOAT) RETURNS FLOAT
DETERMINISTIC
BEGIN
DECLARE calcDistance FLOAT;
SET calcDistance = ( 3959 * acos( cos( radians(lat) ) * cos( radians( locate_latitude ) ) * cos( radians( locate_longitude ) - radians(lon) ) + sin( radians(lat) ) * sin( radians( locate_latitude ) ) ) );
RETURN calcDistance;
END $$
DELIMITER ;Then, using the user’s latitude and longitude obtained from the browser Geolocation object, we can select the nearest stores within, say, 10 miles like this:
SELECT store_id, name, locate_latitude, locate_longitude, calc_distance({$lat},{$lng}, locate_latitude, locate_longitude) AS distance
FROM stores
HAVING distance < {$distance}
ORDER BY distance LIMIT 0, 50;The advantage of this solution over the JavaScript option is speed. Even working with a few thousand records it’s fast. And speed is always a limiting factor on a website: wait too long and the user is gone.
One possible disadvantage is that the distance is calculated as the crow flies. Sometimes driving or walking distance is more useful to users. But the solution here is to combine the two to give users the best over all experience: use SQL to calculate direct distance and select a wide range of stores in the locality, then use the Google Maps API to get driving or walking distances on the client side, and let the user decide how they would like to sort them.
