i.e.
ZipCodes Table
ZipCode Latitude Longitude CityID
90210 50 80 NULL
Cities Table
CityID Latitude Longitude
9 51 81
10 49 79
11 35 53
Or perhaps the lat is right on but the two lon are different:
i.e.
ZipCodes Table
ZipCode Latitude Longitude CityID
90210 50 80 NULL
Cities Table
CityID Latitude Longitude
6 45 80
7 50 85
8 50 75
9 51 81
10 49 79
11 35 53
My point is there's a relevant piece of information that is missing from
this problem -
What does the lon/lat coordinate represent? The center of a city? The
right uppermost coordinate? And how do you resolve descrepencies between
two zip codes that are equidistant to a particular coordinate?
Without knowing any more about the problem. It seems to me that you can:
1. create a cursor that steps through the 900k records in the zip table
2. compare the current record to the cities tables using one of the
following (depending on the answers to the above):
a. select min(lon) from cities where lon > zip.lon
b. select min(lat) from cities where lat > zip.lat
c. select max(lon) from cities where lon < zip.lon
d. select max(lat) from cities where lat < zip.lat
3. then select * from cities where lon = ? and lat = ? (? parms filled from
step 2)
HTH
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
