get postcode from latitude,longitude

Question

I have downloaded the UK postcode list from http://www.ordnancesurvey.co.uk/oswebsite/opendata/index.html and installed it in a MSSQL 2005 database. The table consists of postcode, latitude and longitude. I need an efficient method to get the nearest postcode to a given lat/long. Calculating the distance between two points and choosing the smallest would mean cycling through all the data for each query. There are approx 1.7 million entries. Thanks.

Answer 1

You could fasten up your calculations by first filter your query using a "circle approximation" - ie. return all postcodes inside a determined radius delta. The basic query should be something like this:

SELECT postcode, x, y FROM table WHERE ((x BETWEEN x - delta AND x + delta) AND (y BETWEEN y - delta AND y + delta))

And now amount of data left should be a bit more manageable.

Also, if you are developing something "mission critical", be sure to take a look at PostGIS. It might be that they have already solved some problems you might run into... ;)