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

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!

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

Is this where you guys started?

http://tutorial153.easycfm.com/

If not, maybe that can help...

BTW, what DB are you using?

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:




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).

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?

social network? ^^