Postcode Information Generator

We currently provide a service available at geoconvert.ukdataservice.ac.uk, which allows a user to discover information about a UK postcode or convert data between its census geographies.

A typical output looks something like this:GeoConvert Output

This provides useful information for academics, researchers and other users who understand these types of things but can be a little bit more difficult to interpret for new and novice users.

I set about trying to create an application that can connect to the same database but pull out similar information in a more user-friendly and ‘simpler’ format.

The first step was to download the most recent version of the open data from the Office of National Statistics (ONS) geography portal.

I also found the relevant lookup files; E01005302 is called Manchester 028C, for example.

After downloading and unzipping, I imported all the data into SQL Server and made some minor adjustments such as removing terminated postcodes and creating the appropriate links between them.

My preference for programming languages at this moment in time is Python as it is easy to use and very versatile. Therefore, I searched online for the best method of using Python to query a SQL database. Very quickly I came across a module called ‘pyodbc‘.

Incidentally, if you’d like to attempt something similar yourself but are stuck with where to start, there is a helpful document on the Microsoft site that takes you through the process from start to finish.

The resulting information looked something like this:

Simple Postcode InfoWith my Python instance now able to pull out information for a particular postcode, I wanted to be able to display that information in a slightly more aesthetically pleasing fashion.

I discovered a module called ‘yattag‘, which allows you to generate HTML using Python. Using a bit of formatting and splitting them into sections, I made a simple table in which to display the results:

Elaborate Postcode InfoOne of the characteristics included in the database is the Latitudinal and Longitudinal information – for those not au fait with this, it provides a way of describing any point on Earth using just two numbers. A similar system is used in satnavs and GPS.

I’ve used these coordinates in the past for mapping and other GIS projects and thought it would be useful in this case to have a visual representation of the postcode.

A nice way of displaying maps is ‘Leaflet‘. It uses JavaScript to create interactive maps, which can be highly customisable. For Python to interface with Leaflet, another step was required. The final module for this was ‘geocoder‘, a ‘simple and consistent geocoding library’.

All these steps in place produced the result you see here:

Postcode MapI have plans to develop the program further by possibly adding the ability to type the name of an area in lieu of a postcode and pull the information for that, also adding in boundaries so that a user can see the boundaries for their Local Authority, for example.

Any other suggestions for enhancements or features are encouraged!

# -*- coding: utf-8 -*-
"""
Created on Mon Dec 31 20:35:46 2018
@author: Jamey Hart
"""
#Import modules
from yattag import Doc
import pyodbc
import os
import geocoder

#HTML Generator Setup
doc, tag, text = Doc().tagtext()

#SQL Server Connection
con = pyodbc.connect('Driver={SQL Server};'
                     'Server=local;'
                     'Database=GeoConvert;'
                     'Trusted_Connection=yes;')

pcd = input('Enter postcode: ')

sql_string = "SELECT * FROM ONSPD_NOV_2018_UK_NAMES WHERE pcds LIKE '" + pcd + "'"

cursor = con.cursor()
cursor.execute(sql_string)

#Create HTML
with open('output.html', 'w') as outfile:
    outfile.write("<html>\n<head>\n<link rel=\"stylesheet\" href=\"https://unpkg.com/leaflet@1.4.0/dist/leaflet.css\" />\n<script src=\"https://unpkg.com/leaflet@1.4.0/dist/leaflet.js\"></script>\n<style>\n#map{ height: 50%; width: 100%;}\n</style><link rel=\"icon\"\ntype=\"image/png\"\nhref=\"https://www.ukdataservice.ac.uk/Images/UKDataService/favicon.ico\">\n</head>\n\n<body>\n<hr/>\n<div style=\"width:800px; margin:0 auto;\">\n<table cellspacing=0 border=1 width=100%>")

    
for row in cursor:
    #Dates
    with tag("tr"):
        with tag("th", colspan="2"):
            text("Dates")
        
    with tag("tr"):
        with tag("td"):
            text("Date of introduction")
        with tag("td"):
            text(str(row[3]))
    
    with tag("tr"):
        with tag("td"):
            text("Date of termination")
        with tag("td"):
            text(str(row[4]))

    #Administrative
    with tag("tr"):
        with tag("th", colspan="2"):
            text("Administrative Geographies")
                
    with tag("tr"):
        with tag("td"):
            text("Country")
        with tag("td"):
            text(str(row[24]))
            
    with tag("tr"):
        with tag("td"):
            text("Region")
        with tag("td"):
            text(str(row[26]))
            
    with tag("tr"):
        with tag("td"):
            text("County")
        with tag("td"):
            text(str(row[6]))
            
    with tag("tr"):
        with tag("td"):
            text("Local Authority")
        with tag("td"):
            text(str(row[10]))
    
    with tag("tr"):
        with tag("td"):
            text("Electoral Division")
        with tag("td"):
            text(str(row[8]))

    with tag("tr"):
        with tag("td"):
            text("Ward")
        with tag("td"):
            text(str(row[12]))
            
    with tag("tr"):
        with tag("td"):
            text("Parish")
        with tag("td"):
            text(str(row[14]))
    
    #Census
    with tag("tr"):
        with tag("th", colspan="2"):
            text("Census Geographies")

    with tag("tr"):
        with tag("td"):
            text("Built-Up Area (2011)")
        with tag("td"):
            text(str(row[63]))
            
    with tag("tr"):
        with tag("td"):
            text("Built-Up Area Sub-Division (2011)")
        with tag("td"):
            text(str(row[65]))
    
    with tag("tr"):
        with tag("td"):
            text("Statistical Ward")
        with tag("td"):
            text(str(row[40]))
            
    with tag("tr"):
        with tag("td"):
            text("Census Area Statistic Ward")
        with tag("td"):
            text(str(row[43]))
    
    with tag("tr"):
        with tag("td"):
            text("Middle Super Output Area (2001)")
        with tag("td"):
            text(str(row[49]))
    
    with tag("tr"):
        with tag("td"):
            text("Middle Super Output Area (2011)")
        with tag("td"):
            text(str(row[58]))
        
    with tag("tr"):
        with tag("td"):
            text("Lower Super Output Area (2001)")
        with tag("td"):
            text(str(row[47]))
    
    with tag("tr"):
        with tag("td"):
            text("Lower Super Output Area (2011)")
        with tag("td"):
            text(str(row[56]))
            
    with tag("tr"):
        with tag("td"):
            text("Travel to Work Area")
        with tag("td"):
            text(str(row[35]))
    
    #Electoral
    with tag("tr"):
        with tag("th", colspan="2"):
            text("Electoral Geographies")
            
    with tag("tr"):
        with tag("td"):
            text("European Electoral Region")
        with tag("td"):
            text(str(row[31]))
            
    with tag("tr"):
        with tag("td"):
            text("Parliamentary Constituency")
        with tag("td"):
            text(str(row[29]))        
    
    #Health
    with tag("tr"):
        with tag("th", colspan="2"):
            text("Health Geographies")
            
    with tag("tr"):
        with tag("td"):
            text("Health Authority")
        with tag("td"):
            text(str(row[20]))
    
    with tag("tr"):
        with tag("td"):
            text("NHS Electoral Region")
        with tag("td"):
            text(str(row[22]))
            
    with tag("tr"):
        with tag("td"):
            text("Primary Care Trust")
        with tag("td"):
            text(str(row[37]))
            
    with tag("tr"):
        with tag("td"):
            text("Clinical Commissioning Groups")
        with tag("td"):
            text(str(row[61]))
            
    with tag("tr"):
        with tag("td"):
            text("National Cancer Vanguard")
        with tag("td"):
            text(str(row[80]))
            
    #Other
    with tag("tr"):
        with tag("th", colspan="2"):
            text("Other Geographies")
            
    with tag("tr"):
        with tag("td"):
            text("National Park")
        with tag("td"):
            text(str(row[45]))
            
    with tag("tr"):
        with tag("td"):
            text("Local Learning and Skills Council (LLSC)")
        with tag("td"):
            text(str(row[33]))
            
    with tag("tr"):
        with tag("td"):
            text("Local Enterprise Partnerships")
        with tag("td"):
            text(str(row[73]))
            
    with tag("tr"):
        with tag("td"):
            text("Local Enterprise Partnerships (second instance)")
        with tag("td"):
            text(str(row[75]))
            
    with tag("tr"):
        with tag("td"):
            text("Police Force Areas")
        with tag("td"):
            text(str(row[77]))
            
    #Indicators and Classifications
    with tag("tr"):
        with tag("th", colspan="2"):
            text("Indicators and Classifications")
            
    with tag("tr"):
        with tag("td"):
            text("Rural/Urban Indicator (2001)")
        with tag("td"):
            text(str(row[51]))
            
    with tag("tr"):
        with tag("td"):
            text("Rural/Urban Indicator (2011)")
        with tag("td"):
            text(str(row[67]))
    
    with tag("tr"):
        with tag("td"):
            text("Output Area Classification (2001)")
        with tag("td"):
            text(str(row[53]))
    
    with tag("tr"):
        with tag("td"):
            text("Output Area Classification (2011)")
        with tag("td"):
            text(str(row[69]))
            
    #Geospatial
    with tag("tr"):
        with tag("th", colspan="2"):
            text("Geospatial Information")
            
    with tag("tr"):
        with tag("td"):
            text("Latitude")
        with tag("td"):
            text(str(row[70]))
            
    with tag("tr"):
        with tag("td"):
            text("Longitude")
        with tag("td"):
            text(str(row[71]))

#If no postcode entered, use location of computer
#NB This is not accurate as it works from the location of the closest DNS, which could be quite far away!
if (pcd == ""):
    g = geocoder.ip('me')

    with open('output.html', 'a') as outfile:
        outfile.write(doc.getvalue())
    #Add Leaflet map and marker
        outfile.write("</table><p></p>\n<div id=\"map\"></div><script>\n//Initialize the map\nvar map = L.map('map').setView(" + str(g.latlng) + ", 16);\n//Load a tile layer\nL.tileLayer('https://api.tiles.mapbox.com/v4/{id}/{z}/{x}/{y}.png?access_token=pk.eyJ1IjoibWFwYm94IiwiYSI6ImNpejY4NXVycTA2emYycXBndHRqcmZ3N3gifQ.rJcFIG214AriISLbB6B5aw',\n{\nmaxZoom: 18,\nid: 'mapbox.streets'\n}).addTo(map);\n//Add Marker\nL.marker([" + str(row[70]) + ", " + str(row[71]) + "]).addTo(map);\n</script>\n</div>\n<hr/>\n</body>\n</html>")
#Otherwise use Latitude and Longitude from database    
else:
    with open('output.html', 'a') as outfile:
        outfile.write(doc.getvalue())
    #Add Leaflet map and marker
        outfile.write("</table><p></p>\n<div id=\"map\"></div><script>\n//Initialize the map\nvar map = L.map('map').setView([" + str(row[70]) + ", " + str(row[71]) + "], 16);\n//Load a tile layer\nL.tileLayer('https://api.tiles.mapbox.com/v4/{id}/{z}/{x}/{y}.png?access_token=pk.eyJ1IjoibWFwYm94IiwiYSI6ImNpejY4NXVycTA2emYycXBndHRqcmZ3N3gifQ.rJcFIG214AriISLbB6B5aw',\n{\nmaxZoom: 18,\nid: 'mapbox.streets'\n}).addTo(map);\n//Add Marker\nL.marker([" + str(row[70]) + ", " + str(row[71]) + "]).addTo(map);\n</script>\n</div>\n<hr/>\n</body>\n</html>")

print("HTML generated.")
os.startfile('output.html')


Leave a Reply

Your email address will not be published. Required fields are marked *