Month: September 2009

Warning: This post contains material of a technical and mathematical nature, if you start reading and feel dizzy or nauseous then stop reading immediately and go and lie down.

Google Latitude is an interesting little app which allows you to advertise your location. On the Android G1 the GoogleMaps application comes with the ability to update your Latitude location directly from the phone.

The usual way of displaying your Latitude location is using the iframe that Google provide which looks like this:

Part of the integration work that I’ve done to link the blogs here to my brother’s CanalPlan website (which is based very loosely on work I did for my ‘O’ Level computer studies way back in 1981/82 – and yes we did actually have microcomputers back then) involved using his list of locations to allow you to insert links easily into the blog post . I then extended that to allow you to easily embed Google maps of those locations into your posts. For example Wigan Pier  :

So the next logical step was to link Latitude locations back to CanalPlan locations.

first of all I looked at scraping the contents of the iframe but its a complete mess, and apparently against the Google TOS, and I was struggling of finding a neat way of getting data from Latitude. Then I found that Google provide two “feeds” for third party applications (i.e. its acceptable under the TOS to take data). The support extracts in JSON and KMZ formats. I choose the JSON one (http://www.google.com/latitude/apps/badge/api?user=-2983480754731033286&type=json)  because I’m already parsing JSON data from my brother’s site so I could just re-use some of the code. So that’s brilliant, I now have my Latitude and Longitude from Google Latitude and I have a list of Canalplan places with latitudes and longitudes so in the words of Aleksandr Orlov, Simples, just match the two and the problem is solved.

Actually its far from simples because the locations inside CanalPlan are for very specific points and the chance of ever being on exactly the same co-ordinates as the CanalPlan location are pretty damned slim, especially given that a lot of the locations in CanalPlan are derived from other data sources. But it would be really nice to able to show the nearest Canalplan location to your current Latitude location wouldn’t it? Especially if you were on the boat and moving.

There is a solution: Geospatial functions, and I think I just head a couple of heads hitting tables.

I was lucky that I’m running the blogs on MySQL 5.x because it supports some basic Geospatial functions. These are functions that allow you to perform calculations on places or rather sets of places, in my case that’s the latitude and longitude of two places. I had to create a new column for my places table (called lat_lng_point) which is of a special datatype called point, and then I had to populate that column with data calculated from the latitude and longitude for each place using the MySQL GeomFromTextfunction which creates a very odd looking data item.

It might look very odd but its quite powerful because you can do some very interesting things with it. If you take two “points” created using GeomFromText you can find the “distance” between them using the GLength function.

So using the following bit of code:

$findit="GeomFromText('Point(".($lat) ." ".($lng).")')";
$sql="SELECT place_name,lat,`long`,GLength(LineStringFromWKB(LineString(AsBinary(lat_lng_point), AsBinary('".$findit."')))) AS distance FROM canalplan_codes ORDER BY distance ASC LIMIT 1";
$res = mysql_query($sql);


We can take the latitude ($lat) and longitude ($lng) from the JSON feed and find the nearest CanalPlan location that matches it.

At the time of writing this post the code (with a few extra bits to make it look nice) produces:

Finding places closest to My current Latitude location at Cheltenham, Gloucestershire, UK (51.9031645,-2.0471622)

Avon Lock (51.9962,-2.15681) is 0.14382 from Cheltenham, Gloucestershire, UK

Now no doubt you’re scratching your head trying to work out what that distance is. Well its a distance expressed in degrees which is pretty meaningless to people like us.

So that is how things stand now, and that’s why the little Latitude Widget on the right hand bar has a little bit of text underneath it saying what the nearest Canalplan location is.

What comes next?

Well possibly implementing a “real” distance. As I said the distance given is in degrees so we can either trust that its right or we can recalculate from scratch. The calculation for this is quite simple (!) :

gcdr=acos(sin(lat1)*sin(lat2)+cos(lat1)*cos(lat2)*cos(lon1-lon2))

This gives the “Great Circle Distance” between the two points (lat1/lon1, lat2/lon2) in degrees. You then convert this to nautical miles by multiplying it by 60, and then finally multiplying that by 1852 you get the distance in metres which I can then convert into one of several “nicer” formats. So it could say:

Avon Lock is a distance of 9 miles, 7¼ flg from Cheltenham, Gloucestershire, UK

The other, much easier, thing to do is to create a new widget that replaces the Google Latitude iframe with a mini Googlemap with your nearest CanalPlan location on it (and saying where you actually are under it).

Well I think everyone’s brains are probably hurting now, so I think I’ll go for a walk.

Class Dismissed!