Saturday, August 30, 2008

Finding Distance based on Zipcode or City Name for USA

Finding Distance based on Zipcode or City Name for USA
A really good article sharing how to find distance between two zip codes or cities of USA.

For whom this article is useful

  • It finds distance in miles between two zipcodes of USA.
  • It finds distance in miles between two cities of USA.
  • Finding County based on city or zipcode name.
  • Free Zipcode database of USA
  • Article is written in C#
Finding Distance based on Zipcode or City Name for USA

Note: When you will download the Zipcode of USA Cities and Import to SQL Server, it append inverted comma to data, to remove that you might need to run following update cursor script. Remember you need to change name of column as mentioned in script.

DECLARE c1 CURSOR READ_ONLY
FOR
select ZipCode,Latitude,Longitude,City,State,County,ZipClass from Zip_Codes

declare @ZipCodeOriginal varchar(50)
declare @ZipCode varchar(50)
declare @Latitude varchar(50)
declare @Longitude varchar(50)
declare @City varchar(50)
declare @State varchar(50)
declare @County varchar(50)
declare @ZipClass varchar(50)

open c1
FETCH NEXT FROM c1
Into @ZipCode,@Latitude,@Longitude,@City,@State,@County,@ZipClass
WHILE @@FETCH_STATUS = 0

BEGIN
set @ZipCodeOriginal = @ZipCode
set @ZipCode = substring(@ZipCode,2,len(@ZipCode) - 2)
set @Latitude = substring(@Latitude,2,len(@Latitude) - 2)
set @Longitude = substring(@Longitude,2,len(@Longitude) - 2)
set @City = substring(@City,2,len(@City) - 2)
set @State = substring(@State,2,len(@State) - 2)
set @County = substring(@County,2,len(@County) - 2)
set @ZipClass = substring(@ZipClass,2,len(@ZipClass) - 2)


--Update
update Zip_Codes
set ZipCode=@ZipCode,
Latitude = @Latitude,
Longitude = @Longitude,
City = @City,
State = @State,
County = @County,
ZipClass = @ZipClass
where
--Uniqueness Checking
ZipCode = @ZipCodeOriginal


FETCH NEXT FROM c1
Into @ZipCode,@Latitude,@Longitude,@City,@State,@County,@ZipClass
END
CLOSE c1
DEALLOCATE c1

3 comments:

Imran said...

I personally believe, you should use Cursor; its not advisable due to performance issue.

You can directly write sql update statement by appyling self join.

DotNetGuts said...

Thanks Imranbhai, I appreciate your suggestion.

Yes you are right that performance wise its not good to use cursor, but as this is batch update statement, i need to go with Cursor, I would appreciate if you can share some way wherein we do this stuff in single update.

Saiful Alam said...

Nice post...

Most Recent Post

Subscribe Blog via Email

Enter your email address:



Disclaimers:We have tried hard to provide accurate information, as a user, you agree that you bear sole responsibility for your own decisions to use any programs, documents, source code, tips, articles or any other information provided on this Blog.
Page copy protected against web site content infringement by Copyscape