I've got pointed today to this site where is sold a product that calculates the distance between 2 zip codes. And they have it for many different programming languages. But wait.. is it really so difficult to do that in the programming language you need? Or does it take so long time to do something like that so that it to worth buying such a product? Don't get me wrong, i don't have a problem with that guys or anything like that, i don't even know them. Just want to show that this isn't really a big deal. I remembered doing this few years ago and i did find that code, so if anyone needs it here it is:

<cffunction name="getDistanceBetweenZips" output="No">

   <cfargument name="zip1" required="Yes" type="string">

   <cfargument name="zip2" required="Yes" type="string">

   <cfset var getData = "">

   <cfset var dist = 0>

   <cfquery name="getData" datasource="#dsn#">

      Select distinct latitude, longitude

      From zipcodes

      Where zipcode = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#zip1#">

         or zipcode = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#zip2#">

   </cfquery>

   <cfreturn 69.1*(180/pi())*acos(sin(val(getData.latitude[1])*Pi()/180)*sin(getData.latitude[2]*Pi()/180)+cos(getData.latitude[1]*Pi()/180)*cos(getData.latitude[2]*Pi()/180)*cos(getData.longitude[2]*Pi()/getData.longitude[1]*Pi()/180))>

</cffunction>

Something like that.


Recently i needed to have a way to find all zip codes within a given radius. Did it long ago too, but wasn't able to find it plus this time i wanted to do it directly on MSSQL side and here is the result:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[zipSearch]

@myZip varchar(5),

@distance int



AS



Select o.zip, o.city, o.st as state, o.county, 

   (3956 * (2 * ASIN(SQRT(

      POWER(SIN(((z.lat-o.lat)*0.017453293)/2),2) +

      COS(z.lat*0.017453293) *

      COS(o.lat*0.017453293) *

      POWER(SIN(((z.long-o.long)*0.017453293)/2),2)

   )))) Distance



From zips z,

   zips o,

   zips a



Where z.zip = @myZip

   and   z.zip = a.zip

   and

      (3956 * (2 * ASIN(SQRT(

         POWER(SIN(((z.lat-o.lat)*0.017453293)/2),2) +

         COS(z.lat*0.017453293) *

         COS(o.lat*0.017453293) *

         POWER(SIN(((z.long-o.long)*0.017453293)/2),2)

      )))) <= @distance



Order By Distance

Yes, i did it as a Stored Procedure so that it to be executed as fast as possible.

If anyone finds any of this two being useful, please leave a comment.