foolfillment: the blog

Dynamically create Google Maps from Google Spreadsheets

8:51 am on the 18th of August, 2007

Jim McDougall left a comment last night saying he’d added my blog to his map of ScotEduBlogs.

It go me thinking about my map of Scottish Schools again, there is already a list of ScotEduBlogs with certain pieces of data : blog’s name; blogger’s name; description; tags. If one of the bits of data was a postcode then it should be possible to output that to another application. I suddenly realised that there must be an easy way to get data into Google Maps dynamically using it’s API, how else would all of the mashups work?

So one quick search later and I’ve found this map generator from the Official Google Maps API blog which takes data from a Google Spreadsheet and creates a Google Map. I’ve not tested it yet but hope to find time soon. It should then be easy as pie to make a map for all schools in Scotland from a database that already exists, if I can get access to it.

With flickrvision going 3D this week that sort of toy was nearer the front of my mind than it might otherwise have been, it gave me this idea: what about a dynamically refreshing picture of the latest posts from ScotEduBlogs? While there’s a lot of activity I think you might still be looking for a while before it changes :).

Tags: , , , ,


  1. OllieBray

    I think that this is a great idea – good luck witht he project!

  2. Ewan McIntosh

    I’ve been working on something this week, using the data we have on file, splitting it up and creating each LA as a set of ‘business branches’ on Google Maps. Hoping to have something by the SLF.

  3. John

    Hi Stuart,
    I am late seeing this. If the blogs in ScotEdublogs are geotagged it would be possible to do this sort of thing: grab the opml, Get the rss feed for every blog and dig out the geotag, pass to the google maps api.
    I’ve messed about with something similar, using the flickr api to grab my geotagged photos data and map them:
    flickr maps mashup
    Of course the whole process would take a while, but it would only need to be done occasionally, say once a day. I guess the problem would be that not many blogs are geotagged?

  4. stuart

    Hi John, the blogs all have a Local Authority location, it’d be possible to create at least a rudimentary map that way.

    How heavy is your map on resources, and do you know how things like twittervision and flickrvision can refresh so frequently?

    If the data for schools were in any sort of database then I guess it should be possible to create a map. I think with a manipulated WordPress install for instance it should be possible to allow for easily updated records of schools and LA services in a way that is getting close to what (I think) Ewan envisages. Also with good tagging should allow for easy manipulation of the records.

  5. John

    hi Stuart,
    well I don’t poll flickr every time, a separate file does that and holds the data in a text file (I am to lazy and not cleaver enough for xml) Getting the data from flickr is slow because there is a bug in getting geo machine tags, so you have to make a call to the flickr api for each photo. I then use the google api to build the page from the data file, it seems quick enough. My php and javascript is pretty basic so I assume that this could be accelerated.
    I was hoping that more folk had tagged there rss feeds with geo stuff, but I seem to be the only scotEduBlogger who does(can’t even remember when or why i did it).
    I’ve got some ideas to run by robert now.

  6. Trackbacks/Pings

      Leave a Reply

      Add a link to your comment