SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    wrong results being returned.

    Hi,

    Been working at this for a few days now, and thought it was done but; it isn't giving me the correct/desired results. Almost ready to give up with frustration.

    The problem is that no matter the value of business_id (ie no matter where it is), the query shows each listed business, as being nearby, to be the same distance away. It's like saying I am the same distance from the North pole as anyone else here. Clearly wrong.

    Quite a long query; would someone mind please telling me what I need to do to make it work. The longitude and latitude are decimal gps values.

    I would appreciate any advice or suggestion that I should try this totally differently. A tip on what that way should be would be greatly welcome.

    Code MySQL:
    SELECT SQL_CALC_FOUND_ROWS 
        bd.business_id
      , bd.business
      , bd.business_sub_type
      , bd.business_category
      , bd.web_url
      , dist_mi
    FROM business_details bd
     inner  
     JOIN address addr
       ON addr.business_id = bd.business_id
     left outer 
     JOIN 
       (SELECT 
           ((2 * 6378 * ASIN(d / 2 / 6378)) * 0.621371192) AS dist_mi
           , business_id
     FROM
          (SELECT
             SQRT(dx * dx + dy * dy + dz * dz) AS d
             , business_id
       FROM
            (SELECT
                  p1.business_id
                , (6378 * COS(RADIANS(p1.Latitude)) * COS(RADIANS(p1.Longitude))) - (6378 * COS(RADIANS(p2.Latitude)) * COS(RADIANS(p2.Longitude)))  AS dx
                , (6378 * COS(RADIANS(p1.Latitude)) * SIN(RADIANS(p1.Longitude))) - (6378 * COS(RADIANS(p2.Latitude)) * SIN(RADIANS(p2.Longitude)))  AS dy
                , (6378 * SIN(RADIANS(p1.Latitude))) - (6378 * SIN(RADIANS(p2.Latitude))) AS dz
          FROM address AS p1
       left outer 
            JOIN address AS p2 
              ON p1.business_id = p2.business_id
            ) t1
         ) t2
      ) qq1 
     
      on qq1.business_id = bd.business_id
      where qq1.dist_mi <= 5
         and bd.business_type = 'DiningOut'
         and bd.business_sub_type= 'Bistro'

  2. #2
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The only data being returned wrongly is the distance measurement. All else is correct.

    bazz


Bookmarks

Posting Permissions

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