I have recently been working with The National Statistics Postcode Lookup Data (NSPL) and while considering other ONS code relationship data it became apparent that more use could be made of this data.
It should be noted that initially the consensus was that only the most recent version of the data was need but more detailed analysis of the data showed that multiple versions are needed as relationships can change over time. With this in mind measures need to be taken to include the correct time series information.
To make use of this data it is loaded into a set of SQL tables. Supporting processes are used to mange the addition of time series details.
The primary use of this data is to match postcodes to the specific ONS codes as needed. The data contains the various ONS codes (Local Authority District, County, Region, etc) at an individual Post Code level, it must, by implication, contain all the relationships between the different codes. Therefore, the data could also be used to determine the relationships between the different codes, (Local Authority Codes to Regions, County to Regions, etc).
As the volume of data is large and for this purpose the Postcode and detailed geography information is not relevant, to improve performance, a smaller cross reference table can be built.
For this example, I’m using a small set of codes. Below is the list together with the name used in the NSPL data.
Local Authority District laua
County cty
Region rgn
Westminster parliamentary constituency pcon
European Electoral Region eer
Use a query along these lines to build the cross reference NSPL table.
SELECT DISTINCT laua, cty, rgn, pcon, eer
INTO [tb_NSPL_cross_reference]
FROM [tb_NSPL]
To make usage easier create a view for each relationship. Create a view along these lines to cover Local Authority District to Region.
CREATE VIEW [vw_NSPL_cross_reference_LAD_RGN]
AS
SELECT [ONSCode],[RegionCD]
FROM
(SELECT DISTINCT [laua] AS [ONSCode],[rgn] AS [RegionCD]
FROM [tb_NSPL_cross_reference]) AS SQ
To be sure of quick responses indexes are needed, the addition of these indexes changes quick responses to instantaneous responses. Create this index to support the LAD to RGN view.
CREATE NONCLUSTERED INDEX [uix_NSPL_cross_reference_LAD_RGN] ON [tb_NSPL]
([laua] ASC)
INCLUDE
([rgn])
These techniques are not limited to the NSPL data but can be equally applied to the ONS Postcode Directory. To enhance the data, use the ONS Code History Databases to extract the names associated with the codes.
