Ordnance Survey now make available a number of mapping data/geographic datasets to download for free, allowing unrestricted use for commercial and non-commercial use. One of these is the Code-Point Open dataset, which gives a precise geographic location of each post code in Great Britain as a CSV file.
This file contains the Easting and Northing coordinates for each postcode. You may want to convert these to Latitude/Longitude coordinates to then load into a GEOGRAPHY column in SQL Server (as of 2008) and do all kinds of spatial wizardry in the database. But how?
If at first you don’t find, search, search and search again
I did quite bit of googling and found various complex calculations to convert from Eastings/Northings to Lat/Lon. I also tried some conversion apps that didn’t convert into the decimal degrees format I wanted. I finally found the exact kind of .NET library code I was looking for, which is available under the GNU General Public License v3 : geocoordconversion. I downloaded the DLL and from there it only took a matter of minutes of playing about to work out the API to convert the Eastings/Northings from the Code-Point data files into Lat/Lon coordinates (in the WGS84 coordinate system).
Conversion snippet
After referencing the geocoordconversion.dll in your .NET project, converting coordinates is as simple as:
long easting, northing;
// assume easting & northing are set.
GridReference gridRef = new GridReference(easting, northing);
PolarGeoCoordinate polarCoord = GridReference.ChangeToPolarGeo(gridRef);
polarCoord = PolarGeoCoordinate.ChangeCoordinateSystem(polarCoord,
CoordinateSystems.WGS84);
// Pull Lat/Lon coordinates from polarCoord.Lat & polarCood.Lon
End to end import process
Using this, I knocked up a C# console app to:
- read in all the Code-Point data from the .csv files downloaded from the Ordnance Survey (approx 1.7 million rows of data)
- convert the Eastings/Northings to WGS84 Lat/Lon coordinates
- bulk load all the data to a new table in SQL Server
- populate a GEOGRAPHY column with the point data for each post code
- create a spatial index on that column
If you’d like a copy of/source code for this app, just ping me and I’ll be happy to share. I plan to put the code up somewhere when I get around to it. The code is now available on GitHub.
Example query
Here’s a quick, very basic demo of querying of geography data, which finds all post codes within about 5 miles of a postcode.
DECLARE @home GEOGRAPHY
SELECT @home = GeoLocation FROM dbo.PostCodeData WHERE OutwardCode = 'AB12' AND InwardCode = '3CD'
SELECT OutwardCode, InwardCode
FROM dbo.PostCodeData
WHERE GeoLocation.STDistance(@home) <= (5 \* 1609) -- 1609 = approx metres in 1 mile
This returned me 8354 rows in around 500ms, with a spatial index in place on the GeoLocation column.
UPDATE (14 March 2012)
See my recent post for information on the accuracy of the GeoCoordConversion third party library I’m using to convert Eastings/Northings to Latitude/Longitude.