5 Replies Latest reply on Jun 9, 2009 4:09 PM by davella

    zipfinder.cfc cutting off the first digit in some zip codes

    davella Level 1

      I am using zipfinder.cfc to find out places local to certain zip codes.  For some reason, it works perfectly fine with some zip codes but for others it returns the local zip codes with the first digit removed.  When I search for 20002 the results seem to all work perfectly.  When i test it with 08826 nearly none of the zip codes work.  For instance, the first result is 7001 when in actuality it should be 07001.  Any idea why this would happen?  Whatever is causing this is really destroying any reliability of my results.

       

      When I search for "20002" as a zip code here are my result (local zipcodes)
      '20002','20003','20017','

      20018','20019','20020','20022','20023','20026','20029','20030','20033','20042','20050','20 061','20064','20066','20074','20075','20090','20098','20099','20233','20244','20262','2029 9','20303','20315','20318','20355','20370','20374','20376','20380','20388','20389','20390' ,'20391','20395','20398','20409','20528','20599','20601','20602','20603','20604','20606',' 20608','20609','20610','20611','20612','20613','20615','20617','20618','20619','20620','20 621','20622','20623','20624','20625','20626','20627','20628','20629','20630','20632','2063 4','20635','20636','20637','20639','20645','20646','20650','20653','20656','20657','20659' ,'20660','20661','20664','20667','20670','20674','20676','20678','20680','20682','20684',' 20685','20686','20687','20688','20689','20690','20692','20695','20697','20703','20706','20 710','20711','20712','20714','20715','20716','20717','20718','20720','20721','20722','2073 1','20732','20733','20735','20736','20737','20738','20741','20742','20743','20744','20745' ,'20746','20747','20748','20749','20750','20751','20752','20753','20754','20757','20758',' 20762','20764','20765','20769','20771','20772','20773','20774','20775','20776','20778','20 779','20781','20782','20784','20785','20787','20788','20790','20791','20792','20797','2079 9','21035','21037','21106','21140','21401','21402','21403','21404','21405','21411','21412' ,'21601','21606','21612','21613','21619','21622','21624','21625','21626','21627','21634',' 21638','21647','21648','21652','21653','21654','21657','21658','21662','21663','21665','21 666','21669','21671','21672','21673','21675','21676','21677','21679','22438','22442','2244 3','22469','22488','22509','22520','22524','22529','22558','22577','22581','56901'

      When I search for 08826:
      '7001','7002','7003','7004','7005','7006','7007','7008','7009','7011','7012','7013','7014' ,'7015','7016','7017','7018','7019','7021','7022','7023','7026','7027','7028','7029','7030 ','7031','7032','7033','7034','7035','7036','7039','7040','7041','7042','7043','7044','704 5','7046','7047','7050','7051','7052','7054','7055','7057','7058','7059','7060','7061','70 62','7063','7064','7065','7066','7067','7068','7069','7070','7071','7072','7073','7074','7 075','7076','7077','7078','7079','7080','7081','7082','7083','7086','7087','7088','7090',' 7091','7092','7093','7094','7095','7096','7097','7099','7101','7102','7103','7104','7105', '7106','7107','7108','7109','7110','7111','7112','7114','7175','7182','7184','7188','7189' ,'7191','7192','7193','7194','7195','7197','7198','7199','7201','7202','7203','7204','7205 ','7206','7207','7208','7302','7303','7304','7305','7306','7307','7308','7309','7310','731 1','7390','7399','7405','7407','7410','7424','7432','7440','7444','7450','7451','7452','74 57','7470','7474','7477','7501','7502','7503','7504','7505','7506','7507','7508','7509','7 510','7511','7512','7513','7514','7522','7524','7533','7538','7543','7544','7601','7602',' 7603','7604','7606','7607','7608','7630','7643','7644','7646','7649','7652','7653','7657', '7660','7661','7662','7663','7666','7676','7699','7701','7702','7703','7704','7709','7710' ,'7711','7712','7715','7716','7717','7718','7719','7720','7721','7722','7723','7724','7726 ','7727','7728','7730','7731','7733','7734','7735','7737','7738','7739','7746','7747','774 8','7751','7752','7753','7754','7755','7756','7757','7758','7762','7763','7764','7765','77 77','7799','7801','7802','7803','7806','7820','7821','7825','7828','7829','7830','7831','7 834','7836','7837','7838','7839','7840','7842','7843','7844','7845','7846','7847','7850',' 7852','7853','7856','7857','7863','7865','7866','7869','7870','7874','7876','7878','7879', '7880','7885','7901','7902','7920','7921','7922','7924','7926','7927','7928','7930','7931' ,'7932','7933','7934','7935','7936','7938','7939','7940','7945','7946','7950','7960','7961 ','7962','7963','7970','7974','7976','7977','7978','7979','7980','7981','7983','7999','801 0','8016','8022','8041','8042','8046','8060','8073','8075','8077','8501','8502','8504','85 05','8510','8511','8512','8514','8515','8518','8520','8525','8526','8527','8528','8530','8 533','8534','8535','8536','8540','8541','8542','8543','8544','8550','8551','8553','8554',' 8555','8557','8558','8559','8560','8561','8562','8570','8601','8602','8603','8604','8605', '8606','8607','8608','8609','8610','8611','8618','8619','8620','8625','8628','8629','8638' ,'8640','8641','8645','8646','8647','8648','8650','8666','8677','8690','8691','8695','8701 ','8720','8723','8724','8730','8733','8736','8738','8739','8742','8750','8755','8801','880 5','8807','8809','8810','8812','8816','8817','8818','8820','8821','8822','8823','8824','88 26','8827','8828','8829','8830','8831','8832','8833','8835','8836','8837','8840','8844','8 846','8850','8852','8853','8854','8855','8857','8858','8859','8861','8862','8863','8867',' 8868','8869','8870','8871','8872','8873','8875','8876','8877','8879','8880','8882','8884', '8885','8887','8888','8889','8890','8896','8899','8901','8902','8903','8904','8905','8906' ,'8922','8933','8988','8989','10004','10005','10006','10007','10008','10011','10013','1001 4','10038','10041','10043','10045','10047','10048','10080','10081','10161','10213','10242' ,'10249','10256','10260','10265','10268','10269','10270','10271','10272','10273','10274',' 10275','10277','10278','10279','10280','10281','10282','10285','10286','10292','10301','10 302','10303','10304','10305','10306','10307','10308','10309','10310','10311','10312','1031 3','10314','11209','11220','11228','11231','11252','18938','18940','18943','18954','18977' ,'19007','19020','19021','19030','19047','19048','19049','19053','19054','19055','19056',' 19057','19058','19059','19067','19154','19155'

      Here is the important part of zipfinder.cfc

          <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">
             
              <cfset radius         = arguments.radius>
              <cfset zip            = arguments.zip>       
               <cfset z1            = zipToLL(zip)>
             
              <cfset lat_miles    = 69.1>    <!--- You can change this if you need more precision --->
              <cfset lon_miles    = abs(lat_miles * cos(z1.latitude * 0.0174))>
               <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="TEST">
                  select       zipcode as Zip, state, city,
                         SQRT(
                                    (#lat_miles# * (latitude - (#z1.latitude#)))*(#lat_miles# * (latitude - (#z1.latitude#)))
                                    +
                                    (#lon_miles# * (longitude - (#z1.longitude#)))* (#lon_miles# * (longitude - (#z1.longitude#)))
                               ) as dist
                  from      ZipCodes
                  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>
         

       

       

      I will make one side note, for some reason I was constantly getting mysql errors for using "SQUARE" in my query.  I removed both of the references and just multipled each equation by itself, effectively squaring them.  I dont know if the square function was removed in my version of mysql or what but I dont think that could possibly be the cause of this because they are being squared just by being multipled by each other.

       

       

      Any help would be greatly appreciated!!  Thanks in advance!

       

      mark

        • 1. Re: zipfinder.cfc cutting off the first digit in some zip codes
          ilssac Level 5

          How are you parsing the list of zip codes?  Computers will not keep a leading zero on any value in a numeric variable.  You must make sure the variable is considered a string so that it keeps the leading zero.

          • 2. Re: zipfinder.cfc cutting off the first digit in some zip codes
            davella Level 1

            This is how I am importing the results into my application:

             

            <cfinvoke component="zipfinder" method="squareSearch" radius="25"
            zip="08826" returnvariable="results"></cfinvoke>


            <cfquery name="get_resellers" datasource="TEST">
            select *
            from PartnerView
            where zipcode IN (#ListQualify(ValueList(results.zip),"'")#)
            and Country = 'USA'
            order by CompanyName
            </cfquery>

             


            Here is the full zipfinder.cfc code.  Zip is set as a string in the first line (from what I can tell).  I understand what you are saying and that definitely seems to be the problem, but where does Zip stop being a string? I appreciate the help!

            <cfcomponent>   
                 <cffunction name="zipLocation" access="public" hint="Given a zip code, it will look up the city and state">
                     <cfargument name="zip" type="string" required="true">
                    
                     <cfquery name="qGetZip" datasource="sc">
                         select city,state
                         from zipcode
                         where zipcode = <cfqueryparam value="#zip#" cfsqltype="CF_SQL_CHAR">
                     </cfquery>
                    
                     <cfreturn qGetZip>
                 </cffunction>

                 <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="sc">
                     select latitude, longitude
                     from zipcode
                     where zipcode = <cfqueryparam value="#zip#" cfsqltype="CF_SQL_CHAR">
                     and latitude IS NOT NULL  <!--- tpullis fix --->
                     </cfquery>
                    
                     <cfif qGetLL.recordcount gt 0>
                         <cfset cp.latitude        = qGetLL.latitude>
                         <cfset cp.longitude        = qGetLL.longitude>
                         <cfset cp.rlatitude     = 0> <!--- qGetLL.rlatitude> --->
                         <cfset cp.rlongitude     = 0> <!--- 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">
                    
                     <cfset radius         = arguments.radius>
                     <cfset zip            = arguments.zip>       
                     <cfset z1            = zipToLL(zip)>
                    
                     <cfset lat_miles    = 69.1>    <!--- You can change this if you need more precision --->
                     <cfset lon_miles    = abs(lat_miles * cos(z1.latitude * 0.0174))>
                     <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="sc">
                         select     zipcode as zip, state, city,
                             SQRT(SQUARE(#lat_miles# * (latitude - (#z1.latitude#)))
                                 +
                                 square(#lon_miles# * (longitude - (#z1.longitude#)))
                                 ) as dist
                         from zipcode
                         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>
                
                 </cffunction>
            </cfcomponent>

            • 3. Re: zipfinder.cfc cutting off the first digit in some zip codes
              ilssac Level 5

              I'm sorry, I do not see any obvious line that jumps out as one that is converting the value.  This is an unfortunate side affect of the typless nature of ColdFusion.  I can only sugest steps to debug the problem.  Track down where the value gets turned into a number.  At the first point the zip value is used put a <cfoutput>#scope.zip#</cfoutput><cfabort>  See if this outputs "0zzzz" or just "zzzz".  If it does move the output-break to the next point it is used.

               

              There are some tricks that can force ColdFusion to sometimes use a specific data type that maybe able to help if you can find where it is being converted.

              • 4. Re: zipfinder.cfc cutting off the first digit in some zip codes
                davella Level 1

                Ok thank you I appreciate the help! I will test that as soon as I get home today and report back my finding and if I have found a solution!

                • 5. Re: zipfinder.cfc cutting off the first digit in some zip codes
                  davella Level 1

                  Just wanted to follow up, I figured out the issue.  I had my database field value set to 'int' which cause them to be truncated within the database.  So I changed the value to text and reuploaded all of my data and I am good as new!

                   

                  Thanks so much for your help you definitely opened up my eyes and let me figure it out!