Things I learnt by doing…


I’m generally a throw myself into things and look at the instructions later person (though I’m sure reading them first might be a good idea), so at the moment I’m looking for datasets to practice visualisation on.

I’m going to try blogging about some of my experiments so at least I’ll have a record of some of the tricks I’ve picked up.

Yesterday I wrote a nib, based on this.

Which led me to the list of premises that have signed up to the breastfeeding welcome scheme, which is a horrible way to display location data. So I decided to try mapping it.

First thing was to pull the postcode from the cell containing the full address. I’ve now found a quicker way to do it – using the =left, =mid or =right formula in Excel to pull a certain number of characters from the beginning, middle or end of each cell.

Works great for postcodes and left me with just a few I had to tidy up by hand.

With postcodes to hand I could run them through the batch postcode geocoder and match up the postcodes again to get the latitude and longitude for them, leaving just a few again to do by manually.

Then I set about putting the data on a map.

Actually mapping it took a while – I tried Google Spreadsheet Mapper – nice but way to much for what I needed but should I ever need Google Maps pins with pictures and links, I’m good.

Then I tried Google Fusion Tables, which worked fine (and I could have avoided having to get the postcodes and latitude and longitude), but looks a bit basic.

Then I headed to BatchGeo and chucked the whole spreadsheet into the box – then took it out again and cleaned up the addresses a bit by removing the line breaks (Find and Replace \n with space with regular expressions turned on in Open Office).

Back to BatchGeo and it worked fine this time and after a bit of fiddling with the settings, I have a map:

View Breastfeeding Friendly Places in Wales in a full screen map

All of which handily ties in to the Infant Feeding Survey stats that were out this morning, and will be in the paper tomorrow, so hopefully the map will be online (if it can be embedded…but that’s a whole different problem).

Next job is turning it into a Google Maps layer that can be accessed on smartphones so people can actually use the data.


And back to Google Maps again because they are the only thing that can be embedded in our website.

Downloaded the KML file from BatchGeo and imported it into Google Maps (for some reason Google Fusion Tables generated KML files wouldn’t import). Had a lot of question marks instead of titles until I looked it up and found if I had headed that column Title in the first place then the title box would automatically generate.

View Breastfeeding Welcome in a larger map

Problem with this is there are so many points they don’t all show up on one page, sort of missing the point of plotting all the locations.

Leave a Reply

Required fields are marked *.