http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=169630#664075
1 Like
Hi @Kristen,
Thank you for your help.
What do you think of this that a colleague helped me with:
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 @orig_lat=51.5533498;
set @dest_lat=51.6493413;
set @orig_long=-0.264603;
set @dest_long=-0.3987377;
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;
print @d
Doesn't look good for points that span the date line ... but that probably isn;t a problem for you.
Haven;t checked the formula, but its probably OK.
But what you want is a SET based solution, rather than just two-points, I presume? If you are doing this for "Which is the nearest 10" or "Who is within 50 miles" out of multiple rows in a table then don't use a one-by-one (Row By Agonising Row!!) solution
That's the correct formula for the "Haversine formula" but it's not setup to be set based. A better formula specifically written for SQL can be found at the following (although I've not personally tested/verified it)...
http://daynebatten.com/2015/09/latitude-longitude-distance-sql/
1 Like
Hey Guys,
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 @orig_lat=51.5533498;
set @dest_lat=51.6493413;
set @orig_long=-0.264603;
set @dest_long=-0.3987377;
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;
print @d
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
on s.shopid=o.shopid
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?
So:
@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) 
Refering to this and this:
you could do:
select shopid
,shopname
,shoppostcode
,orderid
,customerpostcode
,acos(sin(s.lat)
*sin(radians(o.lat))
+cos(radians(s.lat))
*cos(radians(o.lat))
*cos(radians(o.long)
-radians(s.long)
)
)
*3963.1676
as orderdistance
from shop as s
inner join orders as o
on o.shopid=s.shopid
where o.Orderdate>='2015-12-01 00:00:00'
and o.Orderdate<'2016-01-01 00:00:00'
and o.status in (2,3,11,12)
;
1 Like
Hi @bitsmed,
Thank you for your response.
The lat and long are in the postcodes table so I'm not sure the above would work as there is no mention of it in your query.
Any chance you could tweak it to get the Lat and Long from there 
Thanks!
That would be something like:
select shopid
,shopname
,shoppostcode
,orderid
,customerpostcode
,acos(sin(ps.lat)
*sin(radians(po.lat))
+cos(radians(ps.lat))
*cos(radians(po.lat))
*cos(radians(po.long)
-radians(ps.long)
)
)
*3963.1676
as orderdistance
from shop as s
inner join orders as o
on o.shopid=s.shopid
inner join postcodes as ps
on ps.postocode=o.shoppostcode
inner join postcodes as po
on po.postocode=o.customerpostcode
where o.Orderdate>='2015-12-01 00:00:00'
and o.Orderdate<'2016-01-01 00:00:00'
and o.status in (2,3,11,12)
;
That is great and seems to be trying to run, except:
Msg 3623, Level 16, State 1, Line 1
An invalid floating point operation occurred.
:(confused:
Try casting radius as float:
*cast(3963.1676 as float)
Same error still I'm afraid...I believe it may be because acos is returning a value > 1.
You probably need to convert all lat and long to float. Something like:
,acos(sin(cast(ps.lat)
*sin(radians(cast(po.lat as float)))
+cos(radians(cast(ps.lat as float)))
*cos(radians(cast(po.lat as float)))
*cos(radians(cast(po.long as float))
-radians(cast(ps.long as float))
)
)
*cast(3963.1676 as float)
Already tried that mate, still the same error.
If I change acos to cos then I don't get an error but the results are not correct.
Try this:
,acos(sin(radians(s.lat))
*sin(radians(o.lat))
+cos(radians(s.lat))
*cos(radians(o.lat))
*cos(radians(o.long)
-radians(s.long)
)
)
*3963.1676
Could you narow it down to a set of source- and destination coordinates that fail and then post them along with you expected distance, so I have something to work with?
Tried with a set of coordinates from Hamburg to Berlin, and calculated the distance in km, and it works fine for me. I'd like to see the coordinates that's failing for you.
@bitsmed, here you are:
Source Lat: 51.426242
Source Long: -0.053853
Dest Lat: 51.429427
Dest Long: -0.065341
Thanks for your help, I really appreciate it
This gives a result of 0.541910656329256 miles:
,2*3961
*asin(sqrt(power(sin(radians(po.lat-ps.lat)/2),2)
+cos(radians(ps.lat))
*cos(radians(po.lat))
*power(sin(radians(po.long-ps.long)/2),2)
)
)
1 Like
@bitsmed, that worked great (I did have to cast the lats and longs as floats for subtraction).
Thank you so much for your help.
I also thought I'd try and use your query as well as the calculation I used previously and that also works, with almost the same results (minor differences that I am happy to overlook, probably due to the 3961 vs 3963.1676)
3963.1676 * (2 * atn2(sqrt((sin(radians(cast(po.Lat as float) - cast(ps.Lat as float))/2) * sin(radians(cast(po.Lat as float) - cast(ps.Lat as float))/2) + cos(radians(ps.Lat))
* cos(radians(po.Lat)) * sin(radians(cast(po.Long as float) - cast(ps.Long as float))/2) * sin(radians(cast(po.Long as float) - cast(ps.Long as float))/2))), sqrt(1 - (sin(radians(cast(po.Lat as float) - cast(ps.Lat as float))/2) * sin(radians(cast(po.Lat as float) - cast(ps.Lat as float))/2) + cos(radians(ps.Lat))
* cos(radians(po.Lat)) * sin(radians(cast(po.Long as float) - cast(ps.Long as float))/2) * sin(radians(cast(po.Long as float) - cast(ps.Long as float))/2)))))
So if anyone else is interested, either one works pretty much.
Thanks to all who helped.