#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    1
    Rep Power
    0

    Zip code radius?


    Hi, all:

    My search page allows members to search for each other within a
    selected mile range based on their zip code. I've downloaded a
    list of zip codes (with their respective latitude and longitude
    coordinates) from the U.S. Census Bureau. However, my coding
    below doesn't work:

    <!---Retrieve latitude/longitude of searcher's zip code--->
    <CFQUERY name="userzip" datasource="#datasource#">
    SELECT zip_code, latitude, longitude
    FROM zipcodelist
    WHERE zip_code='#form.zipcode#'
    </CFQUERY>

    <!---Retrieve zip codes that fall within searcher's range--->
    <CFQUERY name="getlocs" datasource="#datasource#">
    SELECT zip_code,
    ROUND((ACOS((SIN(#userzip.latitude#/57.2958) * SIN(latitude/57.2958)) +
    (COS(#userzip.latitude#/57.2958) * COS(latitude/57.2958) *
    COS(longitude/57.2958 - #userzip.longitude#/57.2958)))) * 3963, 3) AS distance
    FROM zipcodelist
    WHERE (latitude >= #userzip.latitude# - (#FORM.miles#/111))
    AND (latitude <= #userzip.latitude# + (#FORM.miles#/111))
    AND (longitude >= #userzip.longitude# - (#FORM.miles#/111))
    AND (longitude <= #userzip.longitude# + (#FORM.miles#/111))
    ORDER BY distance
    </CFQUERY>

    <!---Narrow member search further and include above results--->
    <CFQUERY...>
    SELECT membername, etc.
    FROM other tables...
    WHERE zipcode IN (#ValueList(getlocs.zip_code)#)
    AND etc...
    </CFQUERY>

    The error message I'm getting is:

    ODBC Error Code = 22003 (Numeric value out of range)
    ...Arithmetic overflow error converting numeric to data type numeric.

    Anyone know how to fix? Or perhaps have another equation I can use?

    The only reason I could think of for why I was having the problem was because maybe the lat./long. coordinates in my DB are in degrees and not radians? (Positive and negative numbers such as these are listed: +2.00042, .166345,
    -4.36345, etc.) But then again, I read that dividing by 57.2958 solves that (which is what I did above).

    Also, is using ValueList (in my 3rd query) correct?

    Thanks for the help,
    J
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,264
    Rep Power
    968
    Your use of valueList() looks correct. Beyond that, I have no idea, I've never tried or seen anyone else try this before. Sorry, and good luck!
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2004
    Posts
    1
    Rep Power
    0

    Hey, did you figure it out?


    Hi! I'm trying to do the exact same thing. Did you happen to get this to work? If so, would you mind helping me out, because I don't even know where to begin with the coding. I found the zips/states/lat/long and put them into a mysql database, but I don't know where to go from there1

    Thanks a lot!

    Mike
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Boston, MA
    Posts
    47
    Rep Power
    11
    Is this where you guys started?

    http://tutorial153.easycfm.com/

    If not, maybe that can help...

    BTW, what DB are you using?
    Last edited by bfolger71; May 10th, 2004 at 04:02 PM.
  8. #5
  9. No Profile Picture
    Freelance hockey player
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2001
    Location
    in a van down by the river
    Posts
    54
    Rep Power
    14
    I solved this problem by adding Geo_Latt and Geo_Long fields to the same table where member info is stored, and recorded the coordinates to every member according to their zip code . This needs to be done prior to search. (it is a very simple operation, just take member's zip, find it in your zip code lookup list and record longtitude and lattitude)

    When member submits a search I use a simpler formula that also takes earth curvature into account. Plug this into WHERE clause of the SQL search query:


    Code:
    .....
    WHERE (SQRT(69.1*(Geo_Latt-#qStartCoord.Lattitude#)*69.1*(Geo_Latt-#qStartCoord.Lattitude#) + 53*(Geo_Long-#qStartCoord.Longtitude#)*53*(Geo_Long-#qStartCoord.Longtitude#)) <= #Form.Radius#)
    Geo_Latt and Geo_Long are known coordinates listed in a member table for all members.
    qStartCoord.Longtitude, qStartCoord.Lattitude are coordinates of a zip code submitted by member doing the search.


    I also figured out how to find all zip codes within given radius around a specified city (zip code is not provided).
    Last edited by SayHelloTMLF; May 14th, 2004 at 12:33 PM.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Posts
    1
    Rep Power
    0

    Where did you find the zip code info?


    Where in the US Census Bureau website did you find the zip code information with the longitude and latitude information?
    Can you give me the url?
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2004
    Posts
    48
    Rep Power
    11
    social network? ^^
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2007
    Posts
    2
    Rep Power
    0

    Newbie Help?


    Hi,

    I'm trying to do the same thing (radius search with user defined number of miles from the zip code submitted AND the results mapped on a Google map), and I have tried different code, and have tried the cf Tutorial, but I must be missing something, because I keep getting errors.

    Would someone be willing to help me? I have a MySQL database with two tables (Dealers, zipcodes) The zip code database was one that I found free. It has the following columns:
    ZIP
    LATITUDE1
    LONGITUDE1
    STATE
    TOWN
    RECNO (primary key)

    The dealers table has these columns:
    DealerID
    DealerName
    DealerAddress1
    DealerAddress2
    DealerCity
    DealerState
    DealerZipCode
    DealerPhone
    Latitude
    Longitude

    I'm using ColdFusion to query the database. I can get the radius search to work, and display the "miles away" from the submitted zip, but I don't know how to then translate the results to a Google Map.

    Any help would be appreciated.

    Elizabeth ;-)
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    1
    Rep Power
    0

    PHP code for finding lattitude and longitude


    You can pass the zip code to google map the returning datawill contail lattitude and longitude of that place

IMN logo majestic logo threadwatch logo seochat tools logo