Adding geography to your CSV files
Over the past few years at ODI Leeds I've found it is often useful to show open data from Local Authorities on a map e.g. this map of Leeds City Council recycling sites or a map of Calderdale's food premises register. To show data on a web-based map you often need it to be in a special format but, first of all, you need each record to have a Latitude and Longitude. But many datasets that councils publish as open data don't contain coordinates. Our recent Business Rates project has shown this exact problem; several councils haven't been able to add the Latitude/Longitude columns required by the business rates data format defined by the ODI Leeds Open Data Collaboration group.
Local Authorities are varied. Some are big, some are small. Some are metropolitan and some are rural. How they publish open data and who publishes open data within a Council varies a lot. Even if a Council has a GIS (Geographic Information Systems) team, the people who are actually tasked with publishing datasets may not have ready access to those experts. At the front line of open data publishing, there can often be a shortage of the skills, resources, and/or time necessary to add geography. With reducing budgets, that only seems to get harder to fix. But we can make tools to help.
Yesterday we created a very simple web-based tool to add Latitude and Longitude columns to a dataset that has a Postcode column. You just drop a CSV file into it and it gives you an updated file that you can save. It also warns you if it has trouble with any of your postcodes so you can fix them. If you don't have a CSV file handy, and just want to see what happens, there is a "Load example" button that loads in an old version of Calderdale Council's Business Rates dataset that doesn't have coordinates.
Credit: ODI Leeds
The tool relies on the ONS's very useful postcode look up file published under the Open Government Licence (OGL). I've cut that file down and split it into each postcode area e.g. LS (Leeds), BD (Bradford), M (Manchester) etc. Not only does this make the data more managable for a web-based tool, it also acts to protect privacy because our server only ever gets to know which postcode areas are in your file rather than the individual postcodes.
The tool does one self-contained job. That is, in itself, useful for many different people (not just inside Local Authorities). However, we can be even more helpful. Once the latitudes and longitudes are added, you get a new button that lets you send the data to another tool we created to convert CSV files to GeoJSON. This second tool puts the data on a map so you can quickly check everything looks sensible and you haven't got some data in weird locations. In one more click you can also save a GeoJSON representation of the dataset if you want to. The approach here has been to treat tools a bit like Lego bricks. Each one is self contained and does a specific job as simply as possible. You can also connect them together to do more.
Hopefully these tools are useful. If they are, please let us know. You can also report bugs or issues on Github.
