February 6th, 2004, 03:36 PM
Zip code radius?
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
<!---Retrieve zip codes that fall within searcher's range--->
<CFQUERY name="getlocs" datasource="#datasource#">
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
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
<!---Narrow member search further and include above results--->
SELECT membername, etc.
FROM other tables...
WHERE zipcode IN (#ValueList(getlocs.zip_code)#)
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,
February 6th, 2004, 04:28 PM
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!
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!
Is this where you guys started?
If not, maybe that can help...
BTW, what DB are you using?
Last edited by bfolger71; May 10th, 2004 at 05:02 PM.
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.
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#)
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 01:33 PM.
September 4th, 2004, 12:30 PM
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?
September 7th, 2004, 02:48 PM
February 9th, 2010, 09:55 AM
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:
RECNO (primary key)
The dealers table has these columns:
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.
November 16th, 2012, 09:09 AM
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