Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-07-13 : 10:18:35
|
| John South writes "I'm looking for the most efficient way to answer the "Nearest Location from your Postcode" question. I have a SQL table of (UK) postcodes that gives the a west and north distance for every postcode. A second table has the postcode for each event.Our wonderful customer types in his postcode and we want to tell him the 10 nearest events.The calculation is simple enough:distance= squareroot(north squared + west squared)but should I be doing this on the fly in a T-SQL stored procedure or is there a faster way?" |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-07-13 : 10:57:01
|
| You could skip the squareroot part as you are only trying to order postcodes by distanceorder by (power(north,2) + power(west,2))Corey |
 |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2004-07-13 : 15:32:34
|
| I think your main consideration should be how big the events table is, and whether it will be filtered by other criteria that the user selects. If the query ends up having to look at the distance for 10000 rows just to return the closest 10 for a single query, it's unlikely to be acceptable. If you can put an upper bound on the distance, you may be able to divide your search space into a grid that can be joined to the query position in a way that makes use of indexes rather than scanning the rows. There may be ways of doing this without imposing an distance bound, but other than expanding the grid iteratively until you have enough candidate events, I can't think of anything. |
|
|
|
|
|
|
Dynamic Pythagoras Calculations