Hi Guys,
How do you calculate the distance between two points?
I have two tables with post codes and have the latitude, longtitude each postcode.
How can I get the distance between two points (comparing two points in two different tables).
Thanks!
Calculate distance in miles between two coordinates
Kristen
#2
How accurately do you want it? You can just use Pythagoras for a rough calculation, but for more accuracy (and longer distances) you need to take the curvature of the earth into account.
There is functionality in recent versions of SQL that help with geo-locating, but our code is ancient! and in order to answer questions like "What places are within 50 miles" we calculate the Lat/Long for the top-left and bottom-right corners of a square, which contains the 50-mile radius circle, and then do a query to find locations that have Lat-Long in those two ranges. This is a very quick index query. For the ones that are satisfied by that we then use the appropriate, much slower, calculation to find the actual distance.
If you do something like
WHERE CalculateMileage(Lat, Long, @FromLat, @FromLong) <= 50
your query won't use any index (i.e. on Lat & Long columns) and will be much slower
ismailm
#3
Hi @Kristen,
Thank you for your response.
I just need a relatively accurate mileage distance calculation.
Can you help me to construct one using Pythagoras? (or any other calculation that would give me the same result).
So assuming we have a point A with Lat=57.148239, Lon=-2.096668 and point B with Lat=57.148088, Lon=-2.094684, I need a query that will calculate the distance in miles between A and B.
Thanks
ismailm
#5
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
Kristen
#6
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
JeffModen
#7
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/
ismailm
#8
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) 
bitsmed
#9
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)
;
ismailm
#10
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!
bitsmed
#11
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)
;
ismailm
#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:
ismailm
#14
Same error still I'm afraid...I believe it may be because acos is returning a value > 1.
bitsmed
#15
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)
ismailm
#16
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.
bitsmed
#17
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
bitsmed
#19
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.