The Cities table has just the 400 major cities in the US and Canada, the
ZipCode table has every zip code and postal code for every corner of the two
countries.
The ZipCode table has Longitude and Latitude coordinates for each zip code
(basically each city). The Cities table has Longitude and Latitude
coordinates for each City (or CityID).
What I need to do is match a CityID with a ZipCode. I'd need to select a
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.
For example: (irrelevant data excluded)
ZipCodes Table
ZipCode Latitude Longitude CityID
90210 50 80 NULL
Cities Table
CityID Latitude Longitude
10 49 79
11 35 53
So I need to take the ZipCode 90210, ensure that CityID 10 is the nearest
city and place the CityID in the ZipCodes table for ZipCode 90210. Of
course I need to do this for all 900k ZipCodes.
Does that make more sense?
Ben
_____
From: Ben Arledge [mailto:[EMAIL PROTECTED]
Sent: October 1, 2003 3:37 PM
To: CF-Talk
Subject: RE: SQL - Zip Code/Nearest City
Thanks for the response Ed.
The Cities table has just the 400 major cities in the US and Canada, the
ZipCode table has every zip code and postal code for every corner of the two
countries.
The ZipCode table has Longitude and Latitude coordinates for each zip code
(basically each city). The Cities table has Longitude and Latitude
coordinates for each City (or CityID).
What I need to do is match a CityID with a ZipCode. I'd need to select a
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.
For example: (irrelevant data excluded)
ZipCodes Table
ZipCode Latitude Longitude CityID
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
