I notice that both tables have a cityName column. Could you join on these:
select *
from zipcodes z inner join cities c on z.cityname = c.cityname
? Or is this field null as well?
It would seem to me that your cities should have more then one set of
long/lat coordinates, if you are two find zip codes that belong to (are
within) a city:
(0,0)--------------------------------(50,0)
| |
| x (zip code = 25,25) |
| |
(0,50)------------------------------(50,50)
Or perhaps I'm making assumptions about zip codes that aren't true?
Ed.
----- Original Message -----
From: "Ben Arledge" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, October 01, 2003 12:39 PM
Subject: SQL - Zip Code/Nearest City
> Hey CF SQL'ers,
>
> My SQL skills are failing me so I need your help in creating some SQL I
can
> use in CF or run through Query Analyzer (using SQL Server). I have two
> tables as follows:
>
> ZipCodes (900,000 records, CityID column is currently null)
> - ZipCode (varchar)
> - CityName (varchar)
> - Country (varchar)
> - Latitude (float)
> - Longitude (float)
> - CityID (int)
>
> Cities (400 records or so...)
> - CityID (int)
> - CityName (varchar)
> - Country (varchar)
> - Latitude (float)
> - Longitude (float)
>
> What I need to do is set the CityID column in the ZipCodes table with the
> nearest CityID in the Cities table for each ZipCode. The nearest city
would
> be calculated via Latitude and Longitude coordinates. With the magnitude
of
> records to update and loop through, I'm having difficulty creating a query
> that would do this in a reasonable amount of time.
>
> Any ideas on how best to do this? Can this be done with a SELECT query
> within an UPDATE query?
>
> Thanks,
> Ben
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
