Results 1 to 2 of 2
Thread: wrong results being returned.
Jan 18, 2010, 23:36 #1
- Join Date
- Sep 2008
- 0 Post(s)
- 0 Thread(s)
wrong results being returned.
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.
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'