Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2011
    Posts
    2

    Unanswered: Proximity Search Query - HELP!!!

    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.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Can this article help?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Are you aware that if Session.passedradius is an integer, then "#Session.passedradius#/111" will always return a result truncated to an integer? Your search terms appear to be decimal.

    To get a decimal result from division, at least one of the components must be decimal.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jan 2011
    Posts
    2

    Thanks - I think

    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.

    Chris

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •