January 10, 2011 at 4:50 am
Does anyone see what I am doing wrong in this query? I have been pulling my hair out on this for a few days now. What I am trying to accomplish is querying a Zip Code database, using a Central Zip Code. LAT and LON are stored in numeric format and look like this, in separate fields.
26.472274
-81.705902
My objective is to retrieve a list of zip codes to use in a query for a given proximity from a central zip code, Lat, Lon combination. (Return all zip codes within a proximity of this zip code)
Here's the query which is only ever returning one result, regardless of how wide the search proximity is.
<cfquery datasource="ProxisellZips" name="getlocs" >
SELECT zipcode, latitude, longitude,
ROUND((ACOS((SIN(#Session.HomeLat#/57.2958) * SIN(latitude/57.2958)) +
(COS(#Session.HomeLat#/57.2958) * COS(latitude/57.2958) *
COS(longitude/57.2958 - #Session.HomeLon#/57.2958))))
* 3963,2) AS distance
FROM Zip_Codes
WHERE (latitude >= #Session.HomeLat# - (#Session.passedradius#/111))
And (latitude <= #Session.HomeLat# + (#Session.passedradius#/111))
AND (longitude >= #Session.HomeLon# - (#Session.passedradius#/111))
AND (longitude <= #Session.HomeLon# + (#Session.passedradius#/111))
ORDER BY distance
</cfquery>
Any help would be greatly appreciated.
January 10, 2011 at 2:47 pm
At a quick glance, couldnt see why you would only be getting one hit.
But some quick observations:
1)Your code is looking for zip codes within a search box, not radius (as in, a hit can be your set distance away in both latitude and longitude)
2)If you only have one lat/long per zip code, it is probably of a central point in the zip code, in which case your proximity search, even if you get it working, will be horribly inaccurate (several zip codes could have their border within the proximity area, but not have their "point" within it).
If the performance isnt too bad, I'd say the easiest thing is to do a derived table calculating distance for each zip code, and then select from that derived table those records within the proximity you want. That would give you your radius, although #2 would still be an issue.
January 11, 2011 at 2:03 pm
I did something like this some years ago and I don't have the code available or I'd just paste it here for you. The application had to do with car dealerships and people living within a certain radius. We had a zip code table with the mean latitude and longitude for each zip code. This was considered good enough in urban areas (won't be much good in rural areas where zip codes take up a considerable amount of real estate).
I translated the miles from the starting zip code into degrees of arc for latitude and longitude and used that to get a temp table with the zip codes within those latitude and longitude boundaries. The latitude and longitude columns in the table were not floating point so they could be indexed.
This resulted in a box, rather than a circle, of zip codes within the boundaries in the temp table. Another pass at the temp table using the Trig functions to delete zip codes in the corners of the box left zip codes in a radius. Again, this is only using the zip code mean latitude and longitude as the criteria.
The temp table of zip codes was then used to select people in those zips.
The application worked just fine and the users were happy with the performance.
I hope my ramblings here make sense.
Todd Fifield
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply