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