Pulling policing data into google sheets

Last week I had a regional newspaper group in for a few days exploring lots of different things. One area that popped up was data and we did a quick breakout looking at some data from an FOI on 101 calls.  It was a whirlwind overview – you can see the much more considered and in-depth take on it here.

The sessions are always a great motivator for me to explore and one of the things that came up was mapping areas.

It turns out that data.police.uk publishes KML files for all the police force boundaries and the neighborhood policing boundaries – great stuff.  That means you can import a KML file straight into Google Maps and get something like this:

Except that when I downloaded the boundary files from Lancashire police. Instead of files named after the areas, they had cryptic numbers like D2.kml.  Matching the numbers to the area name ended up being a slog through police.uk finding the area and then fishing the code out of the URL. Not great.

The code is in there somehwere
The code is in there somehwere

What you can do is query the data.police.uk api (the thing that drives part of police.uk.

Try firing this into your browser :https://data.police.uk/api/lancashire/neighbourhoods

Depending on your browser you’ll get something like:

The raw output of an API call to data.police.uk
The raw output of an API call to data.police.uk


Now you could try Alt+F and do a quick search….hmm

Importing data into a spreadsheet

You can see the problem. Unless you’re doing it all programatically, matching the codes to the areas so you can get some simple mapping or analysis done is a bit of a pain. So I decided to explore if I could use the api to try and pull it all together into a spreadsheet as a kind of look-up table:

The spreadsheet contains links to api calls to pull in boundary data as well as other stuff. It uses a script called importJSON which means you can query the data from the police api directly and have it appear nicely in a spreadsheet. More on that in this article, but I’d recommend a play with it because once you have the script in, and now we know stuff like the neighbourhood ID,  we can query the api directly we can pull all sorts of data into a spreadsheet

This example pulls in street level crime based on location

So we can start using the api in a semi automated way to quickly pull in data to process.

Let me know what you think.

3 Replies to “Pulling policing data into google sheets”

  1. Hello Andy,
    and thank you for the above post.

    I am very new to programming and am trying to do something similar with the following data sites:

    The problem is that I don’t know what an API is, a “scraper”, but I have opened a google spreadsheet doc. and installed “importjason”. I just have not been able to figure out how to fetch any data.
    Any help you could give me would be greatly appreciated.

    Best regards,


  2. Hi John.

    Sorry for not replying sooner. The best site to use is the https://www.govtrack.us/developers/api link. The Github is the code that makes the govtrack site work. An api is an application protocol interface. It essentially gives you an easy way to ask for information from an application online, in this case the Congress data store. All you need is the right request and then some code that will sort it out and display it properly. That’s what the importJSON code does in the google sheet.

    I’ve set up a new spreadsheet with the importJSON code added and put in some examples using the Govtrack api. Hope that helps


Leave a Reply