Hi Guys,
I'm trying to integrate a zip code proximity search function into my website, where as users can enter their zip code and a declare an acceptable distance. ie 50 miles. I know this might seem like a programming issue, and it is, but this also involves some geographical math so I thought non programmers might have some insight as well. Mods please move to programming forum if I'm out of bounds here.
I downloaded the $5 team redline zip code database that is recommended on countless sites and that works fine. I was able to import the CSV text file into SQL Server without a problem and setup the datasource. I can do queries on the datasource now and it works fine.
Each zip code record includes postal code, city, state, latitude and longitude. I have a basic test page for this function, which includes a form field for the zip code and a drop down menu for the mileage radius.
I'm using code found from this tutorial.
http://tutorial153.easycfm.com
Here is the code that is in question
<cfquery datasource="zip_codes" name="getlocs">
SELECT zipcode, latitude, longitude, state, city,
ROUND((ACOS((SIN(#passedzip.latitude#/57.2958) * SIN(latitude/57.2958)) +
(COS(#passedzip.latitude#/57.2958) * COS(latitude/57.2958) *
COS(longitude/57.2958 - #passedzip.longitude#/57.2958))))
* 3963, 3) AS distance
FROM zipcodes
WHERE (latitude >= #passedzip.latitude# - (#FORM.passedradius#/111))
And (latitude <= #passedzip.latitude# + (#FORM.passedradius#/111))
AND (longitude >= #passedzip.longitude# - (#FORM.passedradius#/111))
AND (longitude <= #passedzip.longitude# + (#FORM.passedradius#/111))
ORDER BY zipcode
</cfquery>
What is happening is that if I select a mileage that is below 111, I only get 1 or 2 results back. Even if I put in the central St. Louis zip code, 63101 and do a search for 50 mile radius, I only get 2 zip codes back. There are many more zip codes than that. If I put the search to 110 mile radius, I still only get 1 or 2 results.
However, if I search for 111 or more, it seems that the search becomes accurate and returns 100's of results.
Now, 111 seems to be the magic number. There is approx 111 miles in a longitude/latitude degree, which is why the mileage is divided by 111 in the SQL statement above.
(#FORM.passedradius#/111)
My guess is that if the radius value is less than 111 and is divided by 111, the value is less than 1, and that seems to break the function. Any ideas why? Perhaps once it gets to a sub 1 value like .80 it doesn't calculate the radius correctly?
I'm trying to integrate a zip code proximity search function into my website, where as users can enter their zip code and a declare an acceptable distance. ie 50 miles. I know this might seem like a programming issue, and it is, but this also involves some geographical math so I thought non programmers might have some insight as well. Mods please move to programming forum if I'm out of bounds here.
I downloaded the $5 team redline zip code database that is recommended on countless sites and that works fine. I was able to import the CSV text file into SQL Server without a problem and setup the datasource. I can do queries on the datasource now and it works fine.
Each zip code record includes postal code, city, state, latitude and longitude. I have a basic test page for this function, which includes a form field for the zip code and a drop down menu for the mileage radius.
I'm using code found from this tutorial.
http://tutorial153.easycfm.com
Here is the code that is in question
<cfquery datasource="zip_codes" name="getlocs">
SELECT zipcode, latitude, longitude, state, city,
ROUND((ACOS((SIN(#passedzip.latitude#/57.2958) * SIN(latitude/57.2958)) +
(COS(#passedzip.latitude#/57.2958) * COS(latitude/57.2958) *
COS(longitude/57.2958 - #passedzip.longitude#/57.2958))))
* 3963, 3) AS distance
FROM zipcodes
WHERE (latitude >= #passedzip.latitude# - (#FORM.passedradius#/111))
And (latitude <= #passedzip.latitude# + (#FORM.passedradius#/111))
AND (longitude >= #passedzip.longitude# - (#FORM.passedradius#/111))
AND (longitude <= #passedzip.longitude# + (#FORM.passedradius#/111))
ORDER BY zipcode
</cfquery>
What is happening is that if I select a mileage that is below 111, I only get 1 or 2 results back. Even if I put in the central St. Louis zip code, 63101 and do a search for 50 mile radius, I only get 2 zip codes back. There are many more zip codes than that. If I put the search to 110 mile radius, I still only get 1 or 2 results.
However, if I search for 111 or more, it seems that the search becomes accurate and returns 100's of results.
Now, 111 seems to be the magic number. There is approx 111 miles in a longitude/latitude degree, which is why the mileage is divided by 111 in the SQL statement above.
(#FORM.passedradius#/111)
My guess is that if the radius value is less than 111 and is divided by 111, the value is less than 1, and that seems to break the function. Any ideas why? Perhaps once it gets to a sub 1 value like .80 it doesn't calculate the radius correctly?