The Problem
Open data often exists in disparate types and locations; static files, live databases, remote sensors and web services. Our approach makes collating information from multiple sources in a central, access controlled database simple. At the heart of nquiringminds’ Open City Data Platform (OCDP) is the idea that data can be used to help solve social and economic problems. By collecting data from disparate sources and collating this information in a central and secure hub, users can explore and analyse the data with models and visualisations to find rich understanding. However data is not always readily available and even though it may be ‘open’, it requires some technical prowess to structure and clean the data before valuable insight can be found. To overcome this difficulty and ensure that data can be applied to answer real world problems, the OCDP provides an all-encompassing analysis suite where the user can combine data from various sources. With its easy to use front end, users can combine data with simple drag and drop functionality, use statistical models at the click of a button and then employ visualisations and dashboards to convey the results.
Data and Tools used
- Land registry details of house sales over the last 10 years
- Income data from Office of National Statistics
- Travel to work (commute) areas as defined by Office of National Statistics
- Hampshire Hub Data
- Nquire Toolbox
- Nquiringminds Open City Data Platform
Scenario
Parts of the UK are facing a housing crisis. We currently have insufficient usable housing stock for the current population leading to unaffordable housing. This effect is not uniform but variations both regionally and locally.
In order to explore how affordable housing really is, it is necessary to combine data on earnings and housing prices. While there is an abundance of data online it can sometimes be difficult to find accurate, reliable data. Luckily, the Land Registry provides a comprehensive dataset of house price information including 24 million records as far back as 1995. Next up, for the earnings information, the ONS provides a dataset of earnings in the Annual Survey of Hours and Earnings. Once the data has been cleaned there are a few decisions to be made in order to properly structure the data: the Land Registry provides data at the post code level while the Annual Survey of Earnings is at a less granular level – the earnings are reported by Travel to Work (TTW) area. Travel to work areas are generally areas with a population of at least 3,500 where at least 75% of an area’s residents work in the same area and live in the same area. Such an area is ideal for our problem at hand because we can be sure that those people work and live in the same area, meaning that the earnings data and house prices can be directly compared. Before constructing the map, it is necessary to sort the house prices by travel to work areas so that the mean (average) for each TTW can be calculated.
Using the Nquire Toolbox we generated some visualisation and analysis that created insight into the nature of the problems based on the data held in Hampshire County Councils Hampshire Hub. An interactive visual map was produced to see how Housing Affordability has changed over time, the data spans from 2010 to 2015.
Using detailed house price data for small areas, we can intelligently reconcile affordability of housing with where people work and average salaries for the area.
Outcome
The OCDP goes above and beyond, by creating applications from open data that are easy to use and yet technically and visually advanced. The Housing Affordability showcase map exemplifies how data that is freely available to everyone can be combined to address real world problems. While not everyone has the technical skills to blend the data, the OCDP platform allows anyone to seamlessly interact with the data, use statistical models and create visualisations.