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).
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.LonEnd 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
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 mileThis 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.