1

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.

j0k
  • 22,600
  • 28
  • 79
  • 90
Pierce
  • 11
  • 4

1 Answers1

2

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... ;)

plaes
  • 31,788
  • 11
  • 91
  • 89