A simple tool to build a geography lookup table

If you have a dataset that has data by UK local authorities and you want to combine that data into UK regions, you'll need a table that tells you which region each local authority is in. The best place to go is the ONS geoportal which will often have lookup tables for common UK geographies. But sometimes one or more of your geographies are things that the ONS don't know about e.g. your own definition of the north south divide. In that case you'll have to make your own lookup table.

A first port of call - for finding which areas overlap between two different sets of polygons - may be to use the excellent QGIS. But what do you do if you have a corporate IT policy that doesn't let you install it? Or perhaps you find QGIS has too steep a learning curve for you? To help in those situations, we've made a simple web-based tool.

Our geography lookup builder will take two GeoJSON files as input and then, for each feature in the first file, it will find the most overlapping feature in the second file. It will then build a simple CSV lookup table that gives the two areas and the fractional overlap. The overlap column may be useful as it gives an idea of how well those two areas match. You could use the overlap to omit areas that cross borders or perhaps even to apportion values.

If just knowing the most overlapping region is not enough, you can also get JSON output that gives the full breakdown of the areas in the second file that overlap an area in the first.

Limitations

The results won't be "perfect" but should give a broad idea of overlap. There are some limitations:

• If the sea borders in one file aren't in the same place as in another, you could find that a coastal area may end up with a tiny fraction "missing".
• Differences in resolution or precise placement of points/edges in the polygons of the two files can lead to tiny slivers of an area appearing to overlap with another that it shouldn't
• The size of "edge effects" will be larger if both the geographies consist of small areas.
• This tool is only working out the area overlap between two areas. If you are looking at, say, people, they won't be uniformly distributed across area so area-based apportioning may not be appropriate. But it should certainly let you see if an area is entirely within another or not. That may be good enough.
  

To help with the problem of tiny slivers showing up in the JSON output, you can set a threshold below which overlaps will be ignored.

So there it is. It is quite basic and has limitations but we've used this tool for one internal project already and it was very useful. By sharing it openly, we hope that others can get some use of it too.