> zipcode record, then loop through and calculate the distance of each of
the
> 400 cities based on the lat/lon of selected zip code, take the nearest/top
> CityID and update the ZipCode table.
these sorts of problems are best solved by a GIS. which i guess you don't
have access to since you asked here. do you have any larger political
boundaries (states/provinces/counties) that you can use to segregate your
data (that is if your logic won't let you cross these kinds of boundaries)?
teh lat/long distance calculations are nasty. best to use these as a last
resoort on some smaller subset of your data. i guess you might try setting a
"buffer" around each of your 400 cities. take each city's lat/long +/- some
(maybe start w/0.01 decimal degrees, if you can see these on a map you could
probably get a better idea) distance to create a box (buffer) around it (ie
the city would be in the center). use each city's buffer's 4 coords pairs to
create your WHERE clause to select from your 900k row table. if you don't
find any matches, expand the box & re-select--but loop over the other 399
rows first to keep things kosher. you'll need some kind of logic to handle
ties, maybe toss the found cities into another table & then calc distances
on cities with more than 1 match.
but i'd just find a GIS ;-)
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
