So I have a 'Shop' Table and an 'Orders' table as well as a 'Postcodes' table.
The orders table has the Customer's post code and the shop's table has the shop's post code.
The lat and long are in a postcodes table (each postcode has a lat and long in this table).
Can you please help me to adapt the query/formula so I can find the distance between a shop and the Customer who placed the order?
This seems to work when I put the lat and long values in:
declare @orig_lat float;
declare @dest_lat float;
declare @orig_long float;
declare @dest_long float;
declare @phi_orig float;
declare @phi_dest float;
declare @delta_lat float;
declare @delta_long float;
declare @a float;
declare @c float;
declare @d float;
declare @r float= 3963.1676;-- earths radius, in miles
set @phi_orig = radians(@orig_lat);
set @phi_dest = radians(@dest_lat);
set @delta_lat = radians(@dest_lat - @orig_lat);
set @delta_long = radians(@dest_long - @orig_long);
set @a = sin(@delta_lat/2) * sin(@delta_lat/2) + cos(@phi_orig)
* cos(@phi_dest) * sin(@delta_long/2) * sin(@delta_long/2);
set @c = 2 * atn2(sqrt(@a), sqrt(1 - @a));
set @d = @r * @c;
But what I want help with is getting the orderdistance from the select:
select shopid, shopname, shoppostcode, orderid, customerpostcode, @orderdistance
from shop s
join orders o
WHERE o.Orderdate BETWEEN '2015-12-01 00:00:00' AND '2015-12-31 23:59:59'
AND o.status IN (2, 3, 11, 12)
How do I get the @orderdistance calculated using the query I posted earlier (that uses the 'Haversine' formula) - or any other one that gives the same/near enough answer?
@orig_lat=select lat from shop s join postcodes p on s.shoppostcode=p.postocode
@dest_lat=select lat from orders o join postcodes p on o.customerpostcode=p.postocode
@orig_long=select long from shop s join postcodes p on s.shoppostcode=p.postocode;
@dest_long=select lat from orders o join postcodes p on o.customerpostcode=p.postocode
(where they match the customer's and shop's postcodes from an order within the date range)
I think I have explained it properly!
Thanks and much appreciated (and if I forget to like or write thanks then like and thanks in advance)