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.
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'