Ordnance Survey’s GB Postcode to Longitude / Latitude Dataset

Apart from the crippling UK debt, another legacy Gordon Brown’s government has left the British people is the rather wonderful data.gov.uk – credit where credit is due, the PM realises that the UK – the country that had the first industrial revolution, created the Royal Society, discovered & refined penicillin, created RADAR and the first modern computer, has of late been lacking on the innovation front; so he decided to have a chat with Sir Tim Berners-Lee and asked him how the Government could utilise the Web.

Berners-Lee has for the last few years been working on his vision of the future direction of the web – the Semantic Web – or more rather Semantic Data – so of course the first thing he asked the PM to do was liberate all the silos of data the Government has to allow the tax payers to have access and start to innovate. One of the biggest surprises was that the PM announced that he would be opening up the Ordnance Survey’s data sets (which generates several million pounds in revenue for the Government).

Ordnance Survey have the richest GIS data of the UK – the detail fair exceeds anything like Google or Bing Maps. While I’m sure the announcement sent shock waves through companies like Experian and many other who have built their businesses around supplying UK GIS data especially the converted Postcode / address information – the mother-load – has yet to be released – although in the first wave the Code-Point Open dataset has been published. Code-Point Open contains over 1.6 million GB postcodes (Isle of Man, The Channel Islands and Northern Ireland are not yet included) and their Longitude & Latitude. The data is available to download in CSV format.

This is a wonderful move and will benefit any UK developer who is interested in building in spatial services to their applications – but there is a massive difference between raw data and usable data. The Code-Point Open dataset comes in 120 separate CSV files, the data hasn’t been cleansed so some postcodes appear with ” and others don’t (which would affect searching) and there are many fields which wouldn’t be strictly relevant for developers doing spatial work.

So I set about the task of importing the data SSIS to deal with the bulk load – using some wonderful code supplied by SSIS MVP (and ex-colleague) Jamie Thompson. Once the data was in SQL Server it’s was much easier to convert it into various other formats:

SQL Server 2005* – with separate longitude / latitude columns.
SQL Server 2008* – utilising the Geography type for longitude / latitude.
MongoDB – in a format that works with MongoDB’s geospatial functions.

These datasets, the SSIS package and the console app for importing into MongoDB are available on my GitHub Repository.

Work smart, not hard.

* These are large SQL Files – I would advice using the SQL Command Line to import the data – as the files are large enough to crash Management Studio, should you try to use it to open the files.

Like this:

Be the first to like this post.

About this entry

You’re currently reading “Ordnance Survey’s GB Postcode to Longitude / Latitude Dataset,” an entry on Howard van Rooijen's Blog

Published:: May 5, 2010 / 12:33 pm

Category:: Open Source

Tags:: MongoDB, NoRM, OS OpenData, SQL Server 2008 Spatial

David 5.20.10 / 3pm

I tried downloading the files on github but they’re corrupted after i downloaded the latest 7zip….

Howard 5.20.10 / 9pm

Which files did you try?

jay 3.30.11 / 9am

Howard

any idea if/when full post code, lat long, street name, town, county etc may be made available FOC?

Jon 6.2.10 / 9am

I get the same problem, I tried downloading the SQLServer2005 7z file via chrome and firefox and the file isn’t downloaded correctly. my zip extractor won’t open it and the file size keeps changing. If there I way I can get a copy of this?

Howard 6.5.10 / 4pm

It might be best to clone the repository and use git to pull down all the source.

Barry 6.18.10 / 12pm

The SQL 2005 script is very large and all the editors which I have available struggle to load it, executing the script takes a long time and I’m having real problems just getting a table of postcode data together from these files.

Howard, I wonder if you could provide the data as a single CSV file which can be imported using the SSMS data import tool? Thanks.

howardvanrooijen 7.3.10 / 10am

Hi,

try using the SQL Command line tool to do the import – works very well.

ged 7.12.10 / 3pm

In the SQL bulk insert scripts are you storing lat values in the lon column and and lon values in the lat column?

howardvanrooijen 7.17.10 / 8am

@ged – I hope not. Process was something like import CSV > SQL Table Structure > Export to SQL Script. Will double check when I get time.

UK Spatial data for SQL Server – Free - Simons SQL Blog 8.13.10 / 9am

[...] http://howard.vanrooijen.co.uk/2010/05/05/ordnance-surveys-gb-postcode-to-longitude-latitude-dataset… [...]

Fabricio Lima 8.13.10 / 1pm

Have something from Brazil?

Tranks.

Gordon Meyer 8.19.10 / 2pm

Hi Howard, thx for this! However I found that Latitude and Longitude values were swapped around in the OS database

Matthew Chambers 9.6.10 / 9am

Firstly, thanks for getting this information together for us to use – I converted your sql document into MySQL queries which (after a while) has worked just fine but…

Having the lat / lng of a postcode is great but if the location to be identified is not exactly on that coordinate it is not in the postcode it should be. This made me realise there is probably a tolerance for each coordinate (say, 1 mile radius around the coordinate) but surely this cannot be the same for all postcodes?

If you can shed any light on this I’d appreciate it although you really have done enough for us already

Free our data 10.19.10 / 4pm

Fantastic job Howard – i would echo Matthew’s comments about the Long/Lat accuracy having a tolerance. Howard obviously didn’t create the data, OS did.

OS i believe works from their own Grid Reference system, and the long/lat co-ordinates may well be converted mathematically leading to a degree of error. Would love to have this confirmed though – a quick lookup of a postcode on Google maps shows the postcode in the right place but the equivalent long/lat pair being 4 or 5 streets out!

howardvanrooijen 10.24.10 / 11am

I need to take a look and see if a newer data set is available and will try and crank the handle and re-import the data again…

davidbridge 2.15.11 / 4pm

An excellent article Howard. Thanks for this.

I also had trouble downloading the SSIS package for 2008 but got around this by making my own.

A note to all who might try this…

If you’re having trouble getting the data in to sql due to the large number of files then, rather than go through the pain of creatign a file loop, simply concatenate the raw files in Windows. e.g. extract the zip to a folder, then issue a “copy *.csv uk_postcodes.csv” (can also be performed from a sql task if you can use xp_cmdshell and need to automate this step).

Once you have a single file with all the data in (which no longer appears to have problems with the missing quotes as of 15th Feb 2011), you can simply import it in to SQL using the data import wizard (right click on destination DB and choose Tasks->import data). I would recommend adding a header for column names in the CSV first rather than getting [column 0] etc from the wizard as there isn’t one in the CSV by default but this is up to you. Beware the last column too as this will not fit in the default sql type of varchar(50). Change it to 100. Also put double quotes as the text delimiter

Perhaps rather than run immediately you might want to try saving as SSIS to the local server and then open from Visual Studio (BIDS). This will allow you to fine tune the import and you can delete all the columns you don’t want to import.

This system worked for me.

Dave

Howard van Rooijen's Blog

Ordnance Survey’s GB Postcode to Longitude / Latitude Dataset

Like this:

About this entry

16 Comments

Leave a Reply Cancel reply

Recently

Categories

Follow “Howard van Rooijen's Blog”