Google Fusion Tables is great for creating interactive maps from a spreadsheet – but it isn’t too keen on easting and northing. That can be a problem as many government and local authority datasets use easting and northing to describe the geographical position of things – for example, speed cameras.

So you’ll need a way to convert easting and northing into something that Fusion Tables does like – such as latitude and longitude.

Here’s how I did it – quickly.

Find an API to do the work for you

The first thing I needed was an online tool that will do the conversions. Nearby.org.uk is pretty useful for doing so manually – and there’s an API as well – but I wanted something that would give me a nice JSON feed for Google Refine.

So I asked Twitter.

This is where being a part of communities of practice is important for journalists. (Samuel Johnson once said that there are two types of knowledge: “We know a subject ourselves, or we know where we can find information upon it.” Those communities are an example of the latter).

Stuart Harrison very helpfully said he would adapt his postcodes API to convert easting and northing – and within an hour it was ready.

Using Google Refine to work with the API

The API works by generating information in JSON format based on a URL (I explain JSON in this post).

For example, the following URL generates a page of JSON with the latitude and longitude for easting 492412, northing 329757:

http://www.uk-postcodes.com/eastingnorthing.php?easting=492412&northing=329757

I know that Google Refine will be able to use that JSON to extract the latitude and longitude for dozens of rows with different values and add them to the spreadsheet (here’s a post explaining that in more detail).

So here’s what I do:

Generating the end bit of the URLs

I need a new column in my spreadsheet that fetches information from those URLs – there are a couple of ways of doing this but I’m going to show the simplest way for a beginner (rather than the simplest method programmatically*)

This involves creating a new column which conveniently puts together the end part of the URL that I’ll be calling: in this case easting=492412&northing=329757 (where the numbers change in each cell).

1. Click on the drop-down arrow at the top of the Easting column and select Edit column > Add column based on this column…
2. In the window that appears type the following GREL (Google Refine Expression Language): “easting=”+cells[“Easting”].value+”&northing=”+cells[“Northing”].value
3. This assumes that the column with the easting values is called ‘Easting’ (note the capital E) and the northing column is called ‘Northing’. Change these to the names of your columns if they’re different.
4. Give the new column a name in the box at the top and save it. You should see a new column appear, populated with values like easting=492412&northing=329757 – in each cell the process is simply writing a string of characters that begins with easting=, then adds the value in the cell within the ‘Easting’ column, adds &northing=, then adds the value in the cell within the ‘Northing’ column.

These are the second parts of the URLs we’re going to fetch lat-long values from.

Fetching data from those URLs

At the top of this new column, then:

1. Click on the drop-down arrow of your newest column and select Edit column > Add column by fetching URLs…
2. In the window that appears type the following GREL (Google Refine Expression Language): “http://www.uk-postcodes.com/eastingnorthing.php?”+value

3. As you can see, this simply looks at a URL that begins http://www.uk-postcodes.com/eastingnorthing.php? and ends with the value in each cell of the column selected. It will then populate a new column of cells with the JSON returned by each different URL.
4. Give the new column a name in the box at the top and save it. You should again see a new column appear – but this will take longer, because it is going to that website and gathering information. Make a cup of tea.

Extracting the latitude and longitude into separate cells

Great – now we have the lat-long values for each row. But to visualise this data we need separate columns for latitude and longitude, so this is how we get that out of the JSON. UPDATE: In the 2.0 version of Refine the old GREL (struck through below) no longer seems to work – thanks to Tom in the comments for pointing this out and adding the new code which is shown below.

1. Click on the drop-down arrow and select Edit column > Add column based on this column…
2. In the window that appears type the following GREL (Google Refine Expression Language): value.parseJson()lng parseJson(value).get(“lng”)

3. This will look at the value of each cell, and pull out the bit after “lng” and populate a new column of cells with each value
4. Give the new column a name in the box at the top (e.g. longitude) and save it.
5. Repeat the process for latitude – the GREL you need is value.parseJson()lat parseJson(value).get(“lat”)

You should now have a spreadsheet of data that includes latitude and longitude for each row. Click on Export in the upper right corner and select Comma-separated value.

Visualise it in Fusion Tables

Go to Google Fusion Tables and upload that file. Then open it. Click on Visualize and you should have a map option. Once visualised you can embed it elsewhere by clicking on Get embeddable link.

For an example of how that embed code looks on a page, here is one I prepared earlier. (And here is the data it is pulling from).

*The simpler way programmatically is to go straight to ‘Fetching data from URLs’ and use the following GREL code:

“http://www.uk-postcodes.com/eastingnorthing.php?easting=”+cells[“Easting”].value+”&northing=”+cells[“Northing”].value