There's only a little over 42,000 zip codes in the United States. The only
thing I could think of is that you've included all of the alternate city
names... or that you're not really using zip codes from the USA. Canadian
postal codes is somewhere around 3/4 million records... so that's why I was
curious.
-Novak
----- 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]
