Snowflake Data Marketplace: UK Administrative Boundaries  

Uli Bethke Uncategorized

Overview

We have published administrative boundary data for the UK on the Snowflake data marketplace.

In this blog post we document what you need to know about the data including the data model and sample queries. Reach out to us if you have a requirement for administrative boundaries from other countries.

Administrative boundary data covers the geographic hierarchy of national and local government in the UK. This multi-layered hierarchy is complicated by the differing structure in each constituent country of the UK.

Admin boundaries of the UK are divided into various groups which get further divided into sub groups. For a better understanding let's go through this diagram.

From the diagram we can see that the UK is divided into 4 countries, (England, Wales, Scotland and Northern Ireland). Each country is further divided, e.g. England is divided into Regions and then a Region is further divided into subcategories.

To understand this complex hierarchy, let’s understand each group

England

  1. Regions

Regions formerly known as GOR (Government offices for regions) were set up to work with local people and organizations to maximise the prosperity and quality of life in the area. There were 10 regions but in 1998 Merseyside was merged with the rest of the North West. Currently we have 9 regions in England. On 31st March 2011, regions were closed and focus was shifted to local areas but there is still a requirement to maintain the geography for statistical purposes.

  1. Greater London Authority and London Boroughs

London administration has a two tier structure where in the lower tier we have 32 London Boroughs. London Boroughs are then further subdivided into Electoral Wards. Codes beginning with ‘E09’ in the LAD file are a part of London Boroughs.

  1. Metropolitan Counties and Districts

In 1974 a two tier architecture was created for counties which are heavily built up apart from Greater London. These 6 metropolitan counties were further subdivided into metropolitan districts. In 1986, metropolitan county council was abolished, thus making it a single tier architecture where there are 36 metropolitan districts. The data for Metropolitan counties is maintained for statistical purposes. Metropolitan Districts are further subdivided into electoral wards.

  1. Counties, Non-metropolitan Districts and Unitary Authorities

In 1974 two architectures were created where the first tier consisted of counties and the second tier consisted of non metropolitan districts. Work was divided among the tier according to which they could be practiced more efficiently. In the 1990's major changes were done for better administration of the local areas. The key change was the introduction of unitary authorities which is a single tier administration. There were many medium sized urban areas which got classified as Unitary authority and rest maintained the two tier architecture. Currently there are 56 Unitary Authorities, 26 counties and 192 non metropolitan districts. Unitary Authorities and non metropolitan districts get further divided into electoral wards.

  1. Electoral Wards

Electoral Wards are key building blocks of UK administration. They are spatial units used to elect councillors at various levels across the UK. Most of the UK has Electoral Wards but some parts like the Isle of Wight and several Unitary Authorities have electoral divisions which are equivalent to electoral wards. The only part of the UK without electoral wards/divisions is Isle of Scilly. For statistical purposes, ONS treats parishes of the Isle of Scilly as Electoral Wards. Electoral wards cover the whole of the UK.

Electoral Wards of England can be identified by looking at the beginning three letters of the code in the Ward file. For England, the code will begin with ‘E05’. As of May 2019, the UK has 8,887 electoral wards/divisions.

  1. Parishes

The smallest type of administrative area in England is the civil parish. The equivalent of parishes in Wales is known as the communities. As of Dec 2019, there are 11341 parishes in England and Wales.

  1. Combined Authority

Combined Authorities were introduced to enable councils to integrate economic development and transport functions across a functional economic area. Combined Authorities operate as public bodies with their own legal personality. There are 10 Combined authorities in England which are formed by dividing Regions.

  1. Non-civil Parish Areas

Non-civil parish areas refer to the area in a local authority not composed of parishes.

Scotland

Scotland is divided into council areas which is further subdivided into electoral wards

  1. Council Areas

Scotland is divided into 32 Council Areas. LAD code beginning with ‘S12’ in the LAD file are the ones which belong to Council Areas in Scotland. Council Areas are further divided into Electoral Wards

  1. Electoral Wards

There are 354 electoral wards in Scotland. The initial three letter code to identify these electoral wards is ‘S13’

Wales

Wales is divided into Unitary Authority which is further subdivided into wards. Wards are further subdivided into communities.

  1. Unitary Authorities

Wales is divided into 22 Unitary Authorities. Eight UA’s (Bridgend, Cardiff, Merthyr Tydfil, Neath Port Talbot, Newport, Torfaen, Wrexham and Rhondda Cynon Taf) have county borough status, whilst the other 14 have county status. County Borough status is based on the existence of a large population centre while county status is based on some presence of rurality. The initial three letter code to identify Wale’s UA is ‘W06’ in the LAD file.

  1. Electoral Wards

Unitary Authorities of Wales are further divided into 852 Electoral Wards. The initial three letter code to identify wards in the Ward File is ‘W05’.

  1. Communities/Parishes

It is at the lowest level of hierarchy. It is formed from Electoral Wards. There are 877 communities/parishes in Wales. The initial three letter code to identify these parishes is ‘W04’ in the Parishes file.

Northern Ireland

Northern Ireland is divided into Local Government Districts which is further subdivided into Electoral Wards.

  1. Local Government Districts

In 2015, 26 single tier council areas were replaced by 11 LGD’s. LGD’s are Unitary administrations responsible for all areas of Local Government. To identify LGD’s in the LAD file, the initial three letter code is ‘N09’.

  1. Wards

LGD’s are further divided into 462 Electoral Wards. The initial three letter code to identify wards in the Ward File is ‘N08’.

Local Authority Districts

LAD is a generic term used to cover non-metropolitan districts, metropolitan districts, unitary authorities (UA) and London boroughs in England; Wales UAs; Scottish council areas and Northern Ireland local government districts. There are 382 LAD’s in the UK.

For further information please visit

https://www.arcgis.com/sharing/rest/content/items/a31e552c8a214ac2935dbb5701009a64/data

Data sets

The datasets from the Office for National Statistics are licensed under the Open Government Licence v.3.0.

Administrative boundaries

Regions

Combined Authorities

Counties

Counties and Unitary Authority

Metropolitan Counties

Parishes

Parishes and Non Civil Parishes

Wards

Local Authority Districts

Local Planning Authority

Limitations

Snowflake has a 16 MB data limit for column values. An area or admin boundary can be made up of multiple polygons. We merge those polygons into one multipolygon geography type but some geography objects exceed the 16MB limit.

For such an area we have kept the geography field in the main table as NULL and created a new table with suffix ‘_POLYGON_<Year><Month>’ which stores the data of the area in the form of Polygon and not MultiPolygon.

In the above figure we can see that there are some areas in the V_ONS_UK_REGION_201912 table which were throwing 16MB limit error and thus we can see a new table with POLYGON in between which stores the data in Polygon geography type.

There was no such error while creating the V_ONS_UK_WARD_201912 table and hence we cannot find the POLYGON table for it.

In the above figure we can see that the coordinates field for OBJECTID 9 is NULL as it was throwing the 16MB Limit error. Thus we have created a new table named V_ONS_UK_REGIONS_POLYGON_201912 which stores such data in segregated form.

Plotting data sets on maps

Region (LONDON)

Ward (Newcastle)

Combined Authority (Liverpool)

Local Authority District (Belfast)

Lookups and hierarchy

Lookups help us to drill up and down the hierarchy. We have around 10 lookup tables to drill up and down the geography hierarchy.

Lookup Details Lookup Table Name
LOCAL AUTHORITY DISTRICT -> COMBINED AUTHORITY (201912) V_ONS_UK_LKP_LOCAL_AUTH_DIST_COMB_AUTH_201912
LOCAL AUTHORITY DISTRICT -> COUNTY (201912) V_ONS_UK_LKP_LOCAL_AUTH_DIST_COUNTY_201912
LOCAL AUTHORITY DISTRICT -> REGION (201904) V_ONS_UK_LKP_LOCAL_AUTH_DIST_REGION_201904
LOCAL PLANNING AUTHORITY -> LOCAL AUTHORITY DISTRICT (201904) V_ONS_UK_LKP_LOCAL_PLAN_AUTH_LOCAL_AUTH_DIST_201904
LOWER TIER LOCAL AUTHORITY DISTRICT -> UPPER TIER LOCAL AUTHORITY DISTRICT (201912) V_ONS_UK_LKP_LT_LOCAL_AUTH_DIST_UT_LOCAL_AUTH_DIST_201912
NON CIVIL PARISHES AREA -> LOCAL AUTHORITY DISTRICT (201912) V_ONS_UK_LKP_NON_CIVIL_PARISHED_AREA_LOCAL_AUTH_DIST_201912
PARISH -> WARD -> LOCAL AUTHORITY DISTRICT (201912) V_ONS_UK_LKP_PARISH_WARD_LOCAL_AUTH_DIST_201912
WARD -> LOCAL AUTHORITY DISTRICT (201912) V_ONS_UK_LKP_WARD_LOCAL_AUTH_DIST_201912
WARD -> LOCAL AUTHORITY DISTRICT -> COUNTY -> REGION -> COUNTRY (201912) V_ONS_UK_LKP_WARD_LOCAL_AUTH_DIST_COUNTY_REGION_COUNTRY_201912
WARD -> LOCAL GOVERNMENT DISTRICT (201504) V_ONS_UK_LKP_WARD_LOCAL_GOVT_DIST_201504

Data Model

PDF Document

Sample queries

Get all the Local Government Districts in Northern Ireland

Get all the wards in Scotland

Get all wards in london region

Get all Local Authority Districts of county ‘Greater Manchester’

We can combine the data for administrative boundaries with data from OpenStreetMap UK, which is also available on the Snowflake data marketplace.

List of regions with number of restaurant in descending order

Get all shops in Northern Ireland

About the author

Uli Bethke LinkedIn Profile

Uli has 18 years’ hands on experience as a consultant, architect, and manager in the data industry. He frequently speaks at conferences. Uli has architected and delivered data warehouses in Europe, North America, and South East Asia. He is a traveler between the worlds of traditional data warehousing and big data technologies.

Uli is a regular contributor to blogs and books and chairs the the Hadoop User Group Ireland. He is also a co-founder and VP of the Irish chapter of DAMA, a non for profit global data management organization. He has co-founded the Irish Oracle Big Data User Group.