Homebrew geolocation

  • By John
  • April 13th, 2011

Very simple geolocation (for UK places) now available via the Gatekrash API

I'm in the process of rebuilding Gatekrash Mobile, a touchscreen-optimised version of Gatekrash. One of the main features of Gatekrash Mobile is the ability to dynamically show you events happening around your exact location.

For a while now, browsers have been able to detect a user's location and allow a website to use that data to personalise the experience for them. In Gatekrash Mobile's case, I'll be taking the user's location and displaying things happening near them right now and starting soon.

In the previous iteration of the site, I used an external third-party service to do this work for me. This time, however, I've opted to use the huge database I currently use to power the site to also power the geolocation service. The database is a result of the freely available UK Open Gazetteer (a hugely useful database!)

The database contains the latitudes and longitudes of every place in the UK, as well as a unique ID and the county.

Here's the SQL to locate the nearest place (by latitude and longitude) from a specified latitude and longitude:

SELECT id, name, county, lat, lon, SQRT(POW(69.1 * (lat - SPECIFIED_LATITUDE), 2) + POW(69.1 * (SPECIFIED_LONGITUDE - lon) * COS(lat / 57.3), 2)) AS distance FROM place HAVING distance < 20 ORDER BY distance LIMIT 0,1;

Don't forget to put indexes on the latitude and longitude columns (whatever you call them), otherwise it will be a slow process indeed.

The query above selects the ID, name, county, latitude, longitude and distance from the specified latitude and longitude. It limits the query to places within a specified distance, and only shows the closest. Obviously customising this query to alter the distance covered by the query and number of results returned is a simple case of altering the HAVING distance < 20 value and the LIMIT.

This query is the one powering the Gatekrash API's geolocation service at the moment.