Solved

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

Posted on 2008-06-25
11
1,441 Views
Last Modified: 2013-12-24
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
0
Comment
Question by:skodama2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
11 Comments
 
LVL 14

Accepted Solution

by:
Scott Bennett earned 500 total points
ID: 21868120
You could use query of queries to pull the distance from the results query while you are displaying th results from the hospital query. something like this:
<cfinvoke
	component="zipfinder"
	method="squareSearch"
	radius="#FORM.miles#"
	zip="#FORM.zip#"
	type="#FORM.type#"
	returnvariable="results"
	/>
 
<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>
 
<cfloop query="hospital">
<cfquery name="getDistance" dbtype="query">
SELECT dist
FROM results
WHERE Zip = '#hospital.zip#'
</cfquery>
<cfoutput>#Hospital.Zip# - #GetDistance.Dist#</cfoutput>
</cfloop>

Open in new window

0
 
LVL 2

Expert Comment

by:Matt Grofsky
ID: 21880801
To find a distance between two zips where you have the lat and long for each zip youd like to know the distance between.  (for northern and western Hemisphere, AKA USA)  :)

SELECT zipcode, latitude, longitude, statename, city,
                           ROUND((ACOS((SIN(#passedzip.latitude#/57.2958) * SIN(latitude/57.2958)) +
                                    (COS(#passedzip.latitude#/57.2958) * COS(latitude/57.2958) *
                                     COS(longitude/57.2958 - #passedzip.longitude#/57.2958))))
                                      * 3963,3) AS distance
                          FROM zipcodes1
                          WHERE (latitude >= #passedzip.latitude# - (#Attributes.passedradius#/69.1))
                          And (latitude <= #passedzip.latitude# + (#Attributes.passedradius#/69.1))
                          AND (longitude >= #passedzip.longitude# - (#Attributes.passedradius#/69.1))
                          AND (longitude <= #passedzip.longitude# + (#Attributes.passedradius#/69.1))
                          ORDER BY distance
0
 
LVL 14

Expert Comment

by:Scott Bennett
ID: 21885121
@Michin,
The query in the zipfinder.cfc is already calculating the distance between the zips. His challenge is that he wants to match the distance that is calculated and returned with the result set from  the squaresearch function of the cfc to the result set that is returned with the query named hospital.
0
Manage your data center from practically anywhere

The KN8164V features HD resolution of 1920 x 1200, FIPS 140-2 with level 1 security standards and virtual media transmissions at twice the speed. Built for reliability, the KN series provides local console and remote over IP access, ensuring 24/7 availability to all servers.

 
LVL 2

Expert Comment

by:Matt Grofsky
ID: 21885163
Ah very good, miunderstood the question.
0
 

Author Comment

by:skodama2
ID: 21885317
SBennett,

I tried yours, and I'm not getting any errors, but the miles are not showing up....  I'll paste below exactly what I have (stripped out some table formatting - but that's it)



<cfloop query="emergency">
 
  <cfquery name="getDistance" dbtype="query">
    SELECT dist
    FROM results
    WHERE zip = '#emergency.zip#'
  </cfquery>
 
<cfoutput>
 
     #emergency.custName#<br>
     #emergency.address1#<br>
     #emergency.city#, #emergency.state# #emergency.zip#<br>
     #emergency.phone#<br><br>
     #getDistance.dist# Miles
 
</cfoutput>
 
</cfloop>

Open in new window

0
 

Author Comment

by:skodama2
ID: 21885356
Oh - and sorry about the name change - I'm working on a smaller page to work out the kinks.  Hospital and Emergency are interchangeable.
0
 

Author Comment

by:skodama2
ID: 21885434
Just another idea - I just tried to do a simple query against the results, and got an 'Invalid Object' error for results....


<cfquery name="testDist" datasource="zip">
  SELECT dist
  FROM results
  WHERE zip = '99205'
</cfquery>

Open in new window

0
 
LVL 14

Expert Comment

by:Scott Bennett
ID: 21885562
To do a query of queries you would need to use the attribute dbtype="query" l ike this:


<cfparam name="form.zip" 	default="99205">
<cfparam name="form.radius" default="30">
 
<cfinvoke 
	component = "zipfinder" 
	method = "squareSearch" 
	radius = "#form.radius#"
	zip = "#form.zip#"
	returnvariable= "results">
</cfinvoke>
 
 <cfquery name="getDistance" dbtype="query">
    SELECT dist
    FROM results
    WHERE zip_code = '99205'
  </cfquery>
<cfdump var="#getDistance#">

Open in new window

0
 

Author Comment

by:skodama2
ID: 21885582
Nevermind - got it.  I forgot to change one of my queries back.  I was selecting all records instead of using the below.  Once I changed it back, the miles worked!  Thanks for your help!


<cfquery name="emergency" datasource="zip">
  SELECT *
  FROM vwServiceLookup
  where zip IN (#ListQualify(ValueList(results.zip),"'")#) AND svcType = 1
  ORDER BY memberLevel DESC, custName ASC
</cfquery>

Open in new window

0
 

Author Closing Comment

by:skodama2
ID: 31470644
Wish there was an A+ bonus response - this solution helped me get unstuck and back on track!
0
 
LVL 14

Expert Comment

by:Scott Bennett
ID: 21885614
Also I use the zipfinder.cfc on one of my applications and I just ran some code that is exactly the same what I posted for you, and it worked. Maybe you can try dumping out the results and see what is going on under the hood a little?

<cfloop query="emergency">
 
  <cfquery name="getDistance" dbtype="query">
    SELECT dist
    FROM results
    WHERE zip = '#emergency.zip#'
  </cfquery>
 <cfdump var="#getDistance#">
<cfoutput>
 
     #emergency.custName#<br>
     #emergency.address1#<br>
     #emergency.city#, #emergency.state# #emergency.zip#<br>
     #emergency.phone#<br>
     #getDistance.dist# Miles<br><br>
 
</cfoutput>
 
</cfloop>

Open in new window

0

Featured Post

How To Reduce Deployment Times With Pre-Baked AMIs

Even if we can't include all the files in the base image, we can sometimes include some of the larger files that we would otherwise have to download, and we can also sometimes remove the most time-consuming steps. This can help a lot with reducing deployment times.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question