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.
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
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
I think I'm in way over my head on this one but will take a look at this first thing, when I am fresh. I think my largest hurdle is understanding the calculation such that I can anticipate a result. From what I understand about the SQL that I have provided is that it is creating a rectangle over a spherical object to return the values (the results of the four coordinates at the end of the statement). This said, I doubt the results would be too accurate but for my purposes, it will be fine. I was guessing that the problem had to do with the architecture of the query rather than the data formats of the cells. The LAT LON results are in float fields and the Session.PassedRadius is a number set in a session variable for a particular user. It is a number totalling 5, 10, 20, 50, or 100. The fact I received one result, given I had only looked at another sample of similar code, was reassuring. I was hoping it was something simple. The programming language I am using is ColdFusion and the database is MSSQL Server 2005. Should the LAT LON be stored in a decimal field rather than a float field?
Thanks In Advance. I feel like a complete underling but with the utmost respect.