Snowflake Data Marketplace: UK Administrative Boundaries
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
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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
- 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
- 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.
- 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.
- 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’.
- 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.
- 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’.
- 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
Sample queries
Get all the Local Government Districts in Northern Ireland
|
1 2 3 4 5 6 |
SELECT * FROM V_ONS_UK_LOCAL_AUTH_DIST_201912 WHERE LAD19CD LIKE 'N09%'; |
Get all the wards in Scotland
|
1 2 3 4 5 6 |
SELECT * FROM V_ONS_UK_WARDS_201912 WHERE WD19CD LIKE 'S13%'; |
Get all wards in london region
|
1 2 3 4 5 6 7 8 |
SELECT WARDS.* FROM V_ONS_UK_LKP_WARD_LOCAL_AUTH_DIST_COUNTY_REGION_COUNTRY_201912 LKP INNER JOIN V_ONS_UK_WARDS_201912 WARDS ON WARDS.WD19CD = LKP.WD19CD WHERE RGN19CD LIKE 'E12000007'; |
Get all Local Authority Districts of county ‘Greater Manchester’
|
1 2 3 4 5 6 7 8 |
SELECT LAD.* FROM V_ONS_UK_LKP_LOCAL_AUTH_DIST_COUNTY_201912 LKP INNER JOIN V_ONS_UK_LOCAL_AUTH_DIST_201912 LAD ON LAD.LAD19CD = LKP.LAD19CD WHERE CTY19CD LIKE 'E11000001'; |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
SELECT RGN19NM, COUNT(*) AS COUNT_OF_RESTAURANT FROM ( SELECT RGN19NM, CASE WHEN COORDINATES IS NULL THEN POLYGON_COORDINATES ELSE COORDINATES END AS COORDINATES FROM V_ONS_UK_REGION_201912 REGION LEFT OUTER JOIN V_ONS_UK_REGION_POLYGON_201912 RP ON REGION.OBJECTID = RP.OBJECTID ) AS R INNER JOIN V_OSM_UK_AMENITY_SUSTENANCE AMENITY_SUSTANCE ON ST_COVERS(R.COORDINATES, AMENITY_SUSTANCE.COORDINATES) WHERE AMENITY = 'restaurant' GROUP BY RGN19NM ORDER BY COUNT_OF_RESTAURANT DESC; |
Get all shops in Northern Ireland
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SELECT SHOP.* FROM ( SELECT LAD19NM, CASE WHEN COORDINATES IS NULL THEN POLYGON_COORDINATES ELSE COORDINATES END AS COORDINATES FROM V_ONS_UK_LOCAL_AUTH_DIST_201912 L LEFT OUTER JOIN V_ONS_UK_LOCAL_AUTH_DIST_POLYGON_201912 LP ON L.OBJECTID = LP.OBJECTID WHERE LAD19CD LIKE 'N09%' ) AS LAD INNER JOIN V_OSM_UK_SHOP SHOP ON ST_COVERS(LAD.COORDINATES, ST_CENTROID(SHOP.COORDINATES)); |
We are offering various GEO datasets. You can find our offering here:
Uli Bethke
Uli has been rocking the data world since 2001. As the CEO of Sonra, the data liberation company, he’s on a mission to set data free. A self-proclaimed data evangelist, Uli doesn’t just talk the talk—he writes the books, leads the communities, and takes the stage as a conference speaker. Uli is a co-founder of DAMA Ireland, helping build a thriving community of data enthusiasts, and a proud Snowflake Data Superhero, leading the charge as the chapter lead of the Snowflake User Group Ireland. Whether sharing insights, empowering others, or championing the coolest data tools, Uli makes data fun. Growing up in Germany and making Ireland his home, Uli brings a global perspective to everything he does. On the academic side, he holds degrees in political science from Freie Universität Berlin, Albert Ludwigs Universität Freiburg, and the University of Ulster, Coleraine, adding a unique interdisciplinary flair to his work.
