How to: convert easting/northing into lat/long for an interactive map

A map generated in Google Fusion Tables from a geocoded dataset

A map generated in Google Fusion Tables from a dataset cleaned using these methods

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%5B“Easting”%5D.value+”&northing=”+cells%5B“Northing”%5D.value

Advertisements

8 thoughts on “How to: convert easting/northing into lat/long for an interactive map

  1. Tom Wills

    Thanks for this Paul. Using this in Conrad’s class we found a couple of gotchas:

    – there are a few curved quotes in the code snippets which need to be replaced with straight quotes
    – the final GREL snippet didn’t work as expected, at least not in the Google Refine v2.0 we have installed at City University. We used the following snippets instead:

    parseJson(value).get("lat")
    and
    parseJson(value).get("lng")

    Tom

    Reply
    1. Paul Bradshaw Post author

      Thanks Tom – very helpful as there were at least 2 ways of doing the same thing before. Will check if neither now work, or just the one.

      Reply
  2. Pingback: Huge list of data journalism resources | clairemiller.net

  3. Pingback: Step by step: how to start in a data journalist role | Online Journalism Blog

  4. Ændrew Rininsland

    If you need to do this for a boatload of coordinates and don’t feel like abusing somebody’s webservice, a fantastic library for PHP is phpCoord (http://www.jstott.me.uk/phpcoord/). There’s also a Javascript version by the same author at http://www.jstott.me.uk/jscoord/.

    I translated ~54,000 eastings/northings in about a minute with it for a scraper I’m working on — view the source at https://scraperwiki.com/scrapers/tfl_bus_routes_scraper/ to see how I used it.

    Reply
  5. Pingback: Excel 2013 Preview – Webservice Function and Flash Fill « The WorkerThread Blog

  6. Pingback: Questioning Election Data to See if It Has a Story to Tell | OUseful.Info, the blog...

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s