Pulling policing data into Google sheets

Andy Dickinson -

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.

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:

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.