|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
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
|
|||
|
|||
|
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!
|
|
#3
|
|||
|
|||
|
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 |
|
#4
|
|||
|
|||
|
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. |
|
#5
|
|||
|
|||
|
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. |
|
#6
|
|||
|
|||
|
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? |
|
#7
|
|||
|
|||
|
social network? ^^
|
![]() |
| Viewing: Dev Shed Forums > Programming Languages - More > ColdFusion Development > Zip code radius? |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|