Just curious... why are there 900,000 records in the zip codes table?
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
zip code + 4?
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 01 October, 2003 16:09
To: CF-Talk
Subject: Re: SQL - Zip Code/Nearest City
Just curious... why are there 900,000 records in the zip codes table?
There's only a little over 42,000 zip
:09 PM
To: CF-Talk
Subject: Re: SQL - Zip Code/Nearest City
Just curious... why are there 900,000 records in the zip codes table?
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
Hi Ben,
I notice that both tables have a cityName column.Could you join on these:
select*
fromzipcodes 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
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
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
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.
these sorts of problems are best
Okay, here's what I've been working with... probably should have posted this
to begin with.
UPDATEZipCodeTable as m
SETm.CityID = (SELECT CityID
FROM(SELECT TOP 1 CityID,
DistanceAssistant(m.latitude,m.longitude,c.Latitude,c.Longitude) as Distance
FROMCitiesTable as c
ORDER BY Distance))
8 matches
Mail list logo
