0 Replies Latest reply on Sep 26, 2008 4:37 PM by matttoca

    Proximity Search

    matttoca
      So I have .cfc that takes a zipcode and radius and queries a database to locate the other zipcodes close to the entered zipcode.

      Here is the query:

      <cfquery name="qSearch" datasource="#application.dsn#">
      SELECT zip, latitude, longitude,
      #application.earthRadius# * (ACOS((SIN(#z1.latitude#/57.2958) * SIN(latitude/57.2958)) +
      (COS(#z1.latitude#/57.2958) * COS(latitude/57.2958) *
      COS(longitude/57.2958 - #z1.longitude#/57.2958)))) AS distance
      FROM zipcodes
      WHERE
      #application.earthRadius# * (ACOS((SIN(#z1.latitude#/57.2958) * SIN(latitude/57.2958)) +
      (COS(#z1.latitude#/57.2958) * COS(latitude/57.2958) *
      COS(longitude/57.2958 - #z1.longitude#/57.2958)))) <= #arguments.radius#
      </cfquery>

      I then have a .cfm invoke the the .cfc and it queries the query above with:

      <cfquery name="get_members" datasource="#application.dsn#">
      SELECT * FROM members WHERE zipcode IN (#ListQualify(ValueList(results.zip),"'")#) ORDER BY companyname
      </cfquery>

      What I'm trying to do is order query by distance, but I can't get it to do that.

      I tried having the information put into an array and then update the database with that information like this:

      <cfset distArray = listToArray(valueList(qSearch.distance))>
      <cfset zipArray = listToArray(valueList(qSearch.zip))>

      <cfquery name="uDist" datasource="#application.dsn#">
      UPDATE members SET dist = ValueList(qSearch.distance, ",") WHERE zip = ValueList(qSearch.zip, ",")
      </cfquery>

      but that doesn't work. Is there any suggestions on how I can do this? The information is there I just can't seem to order it or put it where I want to.