Zip code proximity search using zipfinder.cfc  - need to show distance for each zip code

Hi,

I'm working on a zip code proximity search using zipfinder.cfc that was written by Robert Capili.  The proximity search is working great, but I need to show the distance from the origin zip to the zipcodes in the returned array.  I'm sure it can be done - but not sure to how to go about it.

The query qSquareSearch in zipfinder.cfc includes distance from original zip as dist.  I've pasted the major part of the cfc below:

The key area of zipfinder.cfc:
<cfcomponent>      
      <cffunction name="zipToLL" access="public">
            <!---
                  This is a helper function.  Given a zip code, it will look up the
                  relevant lats and longs (and their corresponding values in radians)
                  and then pass back a structure containing this information --->
                  
            <cfargument name="zip" type="string" required="true">
            
            <!--- gets a new coordinate pair --->
            <cfset cp = getNewCoordinate()>      
            
            <cfquery name="qGetLL" datasource="#application.dsn#">
            select latitude,longitude,rlatitude,rlongitude from zip
            where zip = <cfqueryparam value="#zip#" cfsqltype="CF_SQL_CHAR">
            </cfquery>
            
            <cfif qGetLL.recordcount gt 0>
                  <cfset cp.latitude            = qGetLL.latitude>
                  <cfset cp.longitude            = qGetLL.longitude>
                  <cfset cp.rlatitude       = qGetLL.rlatitude>
                  <cfset cp.rlongitude       = qGetLL.rlongitude>
            </cfif>
            
            <cfreturn cp>
    </cffunction>

      <!--- +++++++++++++++++++++++++++++++++++++++++++++ --->            
      
      <cffunction name="getNewCoordinate" access="public">
            <!---
                  Basically, this is a constructor that gives us a blank coordinate pair.
             --->
             
            <cfset retVal                         = structNew()>
            <cfset retVal.latitude            = 0>
            <cfset retVal.longitude            = 0>
            <cfset retVal.rlatitude            = 0>
            <cfset retVal.rlongitude      = 0>
            <cfreturn retVal>            
    </cffunction>
      
      
      <!--- +++++++++++++++++++++++++++++++++++++++++++++ --->            
      
      <cffunction name="squareSearch" access="public">      
            <!---
                  This function performs a proximity search by building out a rectangle
                  from a given set of coordinates, and then returning matching items that
                  fall within that area.  It is not the most accurate way to search, but
                  for smaller distances, it is okay.  It is also very fast.
            --->
            <cfargument name="radius"       type="numeric"       required="true">
            <cfargument name="zip"             type="string"       required="true">
        <cfargument name="type"     type="numeric"  required="true">
            
            <cfset radius             = arguments.radius>
            <cfset zip                  = arguments.zip>            
            <cfset z1                  = zipToLL(zip)>
            
            <cfset lat_miles      = application.latitudeMiles>      <!--- You can change this if you need more precision --->
            <cfset lon_miles      = abs(lat_miles * cos(z1.latitude * application.piDivRad))>
            <cfset lat_degrees      = radius / lat_miles>
            <cfset lon_degrees      = radius / lon_miles>
            
            <!--- This is where we calculate the bounds of the search rectangle --->
            <cfset lat1                  = z1.latitude - lat_degrees>
            <cfset lat2                  = z1.latitude + lat_degrees>
            <cfset lon1                  = z1.longitude - lon_degrees>
            <cfset lon2                  = z1.longitude + lon_degrees>
            
            
            <!---
                  To perform the search, we're going to use trigonometry.  Remember the equation, x^2 + y^2 = z^2,
                  aka the Pythazizzle Thizzle? If you look closely, you can see that we are using that in order
                  to calculate the distance (dist) in the query below.
                  
                  This is good, because it is a fast calculation.  But, it is bad because it is calculating the
                  distance as if it were a line.  If the world were flat, this would be perfect.  But, since it isn't,
                  this will start to show errors the larger the radius gets.
                  
                  Still, for your applications, the errors might be small enough to justify the BLAZING SPEED.
             --->
            <cfquery name="qSquareSearch" datasource="#application.dsn#">
            select       zip, state, city,
                  SQRT(
                              SQUARE(#lat_miles# * (latitude - (#z1.latitude#)))
                              +
                              square(#lon_miles# * (longitude - (#z1.longitude#)))
                        ) as dist
            from       zip
            where
                  latitude between #lat1# AND #lat2#
                  AND
                  longitude between #lon1# AND #lon2#
                  
            order by dist asc
            </cfquery>
            
            <!---
                  This is just a quick filter query that will remove some of the zips that get erroneously
                  included in the result set.  This helps to offset the errors that this method introduces,
                  but only just a little.
             --->
            <cfquery name="qRefine" dbtype="query">
            select * from qSquareSearch where dist < <cfqueryparam value="#radius#" cfsqltype="CF_SQL_INTEGER">
        </cfquery>
            
            <cfreturn qRefine>      
    </cffunction>

On the results page, zipSearch.cfm, the cfc is invoked:

<cfinvoke component="zipfinder" method="squareSearch" radius="#FORM.miles#"
zip="#FORM.zip#" type="#FORM.type#" returnvariable="results"></cfinvoke>

I then look up my customers with a query using the returned zip codes in the radius:

    <cfquery name="hospital" datasource="#application.dsn#">
        select *
        from vwServiceLookup
        where zip IN (#ListQualify(ValueList(results.zip),"'")#) AND svcType = 1
        order by memberLevel DESC, custName ASC
      </cfquery>

I output the query, and it shows the basics from the database.  However, I need to show the distance from the current row's zipcode to the zipcode in #FORM.zip#.  Assuming I can use the returned value dist from the cfc, but not sure how to do it.  Trying to avoid re-querying as much as I can.

Thanks in advance!
Todd
Start Free Trial