Geographical status updates

I was recently asked whether it was possible to develop a graphical reporting tool, one that allowed you to flag a number of locations across the UK and for each to hold and present data about its location.

As is becoming more and more common nowadays, my first instinct was to turn to Google. And I never turned back, although I stumbled a little along the way.

First of all, I tried creating a personal map in the “My Maps” section of Google Maps. This allowed me to plonk markers on the map, but the data within each could not be updated other than in the map view itself. What was needed was a spreadsheet that drove the map data, both in terms of the locations’ coordinates and the data therein.

I found a Google Maps API that allowed the data presented by a Google Map to be driven from a Google Spreadsheet. Fabulous. As soon as the spreadsheet is updated, the URL dedicated to the map will display the new data. Any new rows’ locations will be displayed as new markers on the map, and the data that appears when you click on a marker will also be up-to-date.

The only missing link is the automated lookup of a location’s postcode to determine its latitude and longitude position—few people know their locations’ geo-coordinates. I’ve found this site which is great at returning the coordinates of a single postcode, but I’m yet to find a similar one that can return the coordinates of a whole bunch of postcodes.

Now all I need is a place to host the map. Google Sites couldn’t help, as the code uses tags that are deemed “untrusted”. Ha!

Comments

2 Responses to “Geographical status updates”

  1. Mercedes Car Finder on September 24th, 2008 09:18

    exogen.case.edu/projects/geopy/

    I use this python module to do the google postcode lookup. You don’t even have to use google, it can use a handfull of other providers too.

    You just put in address/post code and it gives you the lat/lng.

    Note: you will need a free google maps api key.

  2. Mercedes Car Finder on September 24th, 2008 11:45

    Or even better, just use the API google uses, no api key needed and what’s more, you can do it right in a google spreadsheet! You have your address column, which can be just postcode or a full address. So say A:A is your addresses, then in the B column use the formula: =ImportData(“http://maps.google.co.uk/maps/geo?output=csv&q=”&A2)

    To see what this url returns go to http://maps.google.co.uk/maps/geo?output=csv&q=AB12+1AA

    The hint is in the url: output=csv. So you get 200,5,57.098381,-2.172400 which is the http status code, the accuracy level, latitude and longitude.

    The ImportData formula fetches the page at this url and since it’s csv puts the results into the four cells B2:E2. Don’t you just love these features over Excel, not only can it fetch pages, it knows to spread out the results over several cells. You can then plug this straight into your map, handy or what?

    Then if you get really advanced, you can do this:
    =ImportXML(“http://maps.google.co.uk/maps/geo?output=xml&q=AB12+1AA”, “//LocalityName”), which gets the name of the town for you. In this case Aberdeen.

    It’s a powerful mashup: spreadsheet + geocoding + mapping.

Leave a Reply