My (in)experience with Open Data

October 4, 2010 at 5:32 pm 9 comments

My relationship to Open Data is like my relationship to skiiing: no experience, no training, and it shows. In my first attempt through trial-and-error on the baby slope I achieved some sort of forward motion, but an enthusiast would spot immediately that I’m uneducated in the basics.

Despite this I’m willing to persevere with the data, since unlike skiing it has a point.

Unaware of any schools of chiseled austrian programmers that teach aspiring developers what they need to know, I decided that I can’t be alone in beginning this process by googling ‘API’ and Asking Jeeves what xml is. After all the whole point of open data is that it’s available for anyone to use it, but can anyone use it?

It seems feasible to me that untrained people would want to. As a designer (not a web designer, obviously) I know that if you’ve thought of the perfect design solution to your problem then you don’t want a little thing like ‘no basic training’ to stop you from trying to realise it. Creating a personal device? Learn home electronics. Redesigning office furniture? Better head to the workshop. If you don’t ‘have a go’ then your solutions to design problems will be drastically limited by your skill set.

Of course you quickly realise that ‘jack of all trades, master of none’ is not a compliment and find the money to hire a professional, but you still need to prove to yourself that your idea is feasible before spending money you don’t have .

In this case, my amateur method of learning-by-googling wouldn’t yield any results if it wasn’t for others who ‘show their workings’. So in the interests of sharing, this is my account of how (not) to make a Google Map.

I was using data on Wandsworth’s public toilets in the perfectly common and respectible format of .csv (it must be, since I’ve heard of it), which can be opened in Excel.

Now, public toilets are not exactly complex.  The most basic information you could want is ‘what they are’ and ‘where they are’, so perfect for a Google Map (although other things like opening hours and if you have to pay would be very useful), This data had the columns:

Venue,Address,Postcode,Disabled access?,Type of toilet

I learnt (through googling of course) that you can create a Google Map quickly by importing data as a .kml file, a special kind of file created just for this purpose. So what is .kml? What does one look like and what do I write it in? More googling…

No layman answers but I find a .csv to .kml converter in this forum. This provides an Excel template for your data, with a macro to do the conversion. As if by magic this converter could turn boring data into .kml that, when uploaded to Google Maps, would appear as a series of ‘placemarks’, i.e. markers that pinpoint something (like a toilet), with a name and description. And to position the placemarks the converter could use postcodes rather than longitude/latitude, hurrah! ’cause that’s what I have.  At this rate I’ll be done in time for Neighbours.

So, I…

  • Download .csv to .kml converter.
  • Rearrange .csv file into ‘Name’ ‘Postcode’ ‘Description’ using super excel skills of copy, paste, and one of my favourites, concatenate, so that it fits the template.
  • Hit <alt> F8 to run macro.
  • Read error message…
  • Try again.
  • Same error message…
  • Google some more and read on a forum that macros don’t work in Excel for Mac, not least because it’s written in Visual Basic, but please could I take the time to complain so that future versions of it do work? Hmm, maybe later.
  • Switch on PC, heat up soup, watch Libby Kennedy do some whining, come back to see if PC’s  booted up yet. Nearly…
  • Email file to myself and download it onto PC.
  • Run macro…
  • A new error message! seems I’ve named my file wrong.
  • Try again and…. A .kml file!
  • Email .kml back to myself and download on Mac.
  • Upload to my Google Map… nothing.
  • Google some more and find that postcode-to-placemark converters don’t work on Google Maps, they only work on Google Earth… sigh!

Now I’m sure if I googled some more I could find a postcode-to-long/lat converter, but I want the map for a presentation that I need to send off by the end of the day and it’s already half-past-two, so I admit defeat and begin adding 56 toilets one-by-one through the Google Map interface. C’est la vie.

Being a perfectionist this inevitably means searching by postcode then trawling through StreetView until I find the offending Automatic Public Convenience, or the pub/cafe/library participating in the Community Toilet Scheme.  Consequently it took me about 5 hours and one weekend detour through a cemetery to complete the map of 56 loos. This ridiculous activity did at least expose one of the weaknesses of open data – the reliability of the data itself.

Firstly I learnt that postcodes are a rubbish way to pinpoint a business. One postcode represents, on average, 15 addresses. So if you used a map based on the raw data (without my fine tuning) you would have to check, on average, 15 businesses for a ‘Community Toilet’ sticker before you’d find the one you can use.  Not ideal, and in any case, Google Maps seems way less accurate than this.

Secondly, Google Maps are rubbish!  If you search for ‘Public and Community Toilets in Wandsworth’, the name of my map, you get… nothing.  If you filter for  ‘user-created maps’ and search again, you get… nothing.  It seems they’ve disabled this feature of Google Maps and the only way someone can view your map is if they know the web address, so for all I know 100s of people have already created toilet maps of Wandsworth (and who can blame them).

And finally, data has mistakes in it; in this case 2 wrong postcodes (one of which, for Sainsburys in Earlsfield, is wrong on their own website, so they won’t be getting any christmas cards this year).

An automatic program to process data into applications is not going to spot these mistakes.  So I can conclude that even the finest developer will still need people with real-world knowledge to give feedback on their applications, polishing them into accurate, reliable services.

So here’s my feedback on my own map: If you’re ever in St Mary’s Cemetery in Battersea you’d better cross your legs; I walked round the whole thing and couldn’t find anything resembling a public facility. Perhaps it’s a ghost toilet.

For a different perspective (but with many similarities), the ‘Lessons from the Open Election Data Project’ provides a complete list on the problems of providing open data.


Entry filed under: Open Data. Tags: , , .

… Wandsworth … Community Toilet Schemes

9 Comments Add your own

  • 1. william perrin  |  October 4, 2010 at 6:03 pm

    great post – you illustrate beautifully the next big challenge for the opendata movement – now we can persuade the public secot rto release data how can we help regular non geek folk to use it for public good.

    on making a map from a csv file try this article i wrote when struggling with a similar problem. fusion tables only allows you a certain number of geocodes a day but you shouldn’t hit that

    • 2. gailknight  |  October 5, 2010 at 3:27 pm

      Thanks William – it’s only taken me 1 hour and 3 attempts to achieve the same with a Google Fusion Table so it’s definitely better for a quick visualisation (though bad news for Putney Heath Bus Stand – relocated to New Hampshire!)

  • 3. Karl Wilding  |  October 4, 2010 at 7:55 pm

    Just a note to say that I really enjoyed this. There are a few things I would like to map – in London, motorcycle parking bays would be a start, the only website that does this is crap – and I would love to make a start. But I’m not a geek (or more to the point, not the right type of geek).

    Don’t give up Gail! And maybe I will start.
    Will – motorcycle parking bays in London would be a truly fab google map if you know how to do it…

    • 4. gailknight  |  October 5, 2010 at 3:42 pm

      That’s me too – wrong sort of geek! I’ve got a year to get somewhere so we’ll see – thanks Karl for your support.

  • 5. Neil  |  October 5, 2010 at 7:02 am

    It’s too late now, but I think you can upload in Google Earth, and then re-download a format that works for Google Maps (Normal edition!)

  • 6. Gregory Marler  |  January 12, 2011 at 12:49 pm

    Postcodes are really horrible for locations. Public toilets are often in parks for example, well how far away from the postcode centre could they be? Or they could even be using the postcode for a park gate at the other side of the park, or the council offices.

    When you manually marked the toilets based on Google StreetView/Aerial Imagery/Maps, your data became derived from Google data/images. It is no longer open data but data owned by Google.

  • 7. …the magic of open data | Public Toilets and ...  |  May 21, 2015 at 11:34 am

    […] The first toilet map I made was in 2010, a Google Map of 56 Public and Community Toilets in Wandsworth. took me 5 hours and a trip to a cemetery.  […]

  • 8. Funding  |  June 2, 2015 at 7:59 am

    Thanks verƴ nice blog!

  • 9. National Penn Bank Login  |  April 3, 2017 at 5:16 pm

    Hey nice post! I hope it’s ok that I shared this on my FB, if not, no issues just
    tell me and I’ll delete it. Either way keep up the good


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed

Inclusive Design Guide


Sorry, there's a problem with this image

Recent Posts

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 1,238 other followers

Blog Stats

  • 43,933 hits

%d bloggers like this: