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 |
|
biggs
Starting Member
10 Posts |
 Posted - 2006-04-12 : 16:45:58
|
| Hi.In a few days I am going to need to tackle the problem of searching for records in a database based on proximity to a given zipcode.In short, Customers go to a website and search for inventory from vendors. Assume we have a VENDOR table that includes a zipcode, and an INVENTORY table that is joined with the VENDOR table and filtered to produce our search results.I know that this project can be handled in dozens of ways. I have thought up a few of my own, but I don't want to reinvent the wheel here. If anyone has had to do this type of task before, I would appreciate any input as to approach.Thanks.Tony |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-04-12 : 16:58:36
|
| didn't aiken post something about this 6 months ago or so?-ec |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-13 : 02:22:29
|
| I still reckon that storing the Lat/Long and then doing a "square" from that to get things within the vicinity is the best way. Then on that resultset you can do polar con-ordinates for more accuracy if you need to. But saying that the lat & long need to be between the values of the other site - plus-or-minus some permitted travelling distance - or sorted by the difference in Lat & Long so that they are ranking nearest-first - takes a lot less CPU than the full Polar thingie; or less disk space than pre-calculating all inter-zipcode distances. And scalable to whole world too!heavens-above.com have a nice location finder that allows you to choose Country and hen type in the name of your city/town/village/hamlet! and it gives you Lat/Long. Works worldwide and they built it from publicly available data. I don't know what area a Zip code covers in the USA (the UK postcodes range between single property and about 1 street's worth of houses), but if they cover a large area then getting a tighter identifier with lat + Long might have additional proximity accuracy benefits too.Kristen |
|
|
|
|
|
|
Radius Based Zipcode Searching?