Waller Waller - 1 year ago 107
SQL Question

Calculating distance between two points (Latitude, Longitude)

I am trying to calculate the distance between two positions on a map.
I have stored in my data: Longitude, Latitude, X POS, Y POS.

I have been previously using the below snippet.

SET @orig_lat=53.381538 set @orig_lng=-1.463526
3956 * 2 * ASIN(
SQRT( POWER(SIN((@orig_lat - abs(dest.Latitude)) * pi()/180 / 2), 2)
+ COS(@orig_lng * pi()/180 ) * COS(abs(dest.Latitude) * pi()/180)
* POWER(SIN((@orig_lng - dest.Longitude) * pi()/180 / 2), 2) ))
AS distance
--INTO #includeDistances
FROM #orig dest

I don't however trust the data coming out of this, it seems to be giving slightly inaccurate results.

Some sample data in case you need it

Latitude Longitude Distance
53.429108 -2.500953 85.2981833133896

Could anybody help me out with my code, I don't mind if you want to fix what I already have if you have a new way of achieving this that would be great.

Please state what unit of measurement your results are in.

Answer Source

Since you're using SQL Server 2008, you have the geography data type available, which is designed for exactly this kind of data:

DECLARE @source geography = 'POINT(0 51.5)'
DECLARE @target geography = 'POINT(-3 56)'

SELECT @source.STDistance(@target)



(1 row(s) affected)

Telling us it is about 538 km from (near) London to (near) Edinburgh.

Naturally there will be an amount of learning to do first, but once you know it it's far far easier than implementing your own Haversine calculation; plus you get a LOT of functionality.

If you want to retain your existing data structure, you can still use STDistance, by constructing suitable geography instances using the Point method:

DECLARE @orig_lat DECIMAL(12, 9)
DECLARE @orig_lng DECIMAL(12, 9)
SET @orig_lat=53.381538 set @orig_lng=-1.463526

DECLARE @orig geography = geography::Point(@orig_lat, @orig_lng, 4326);

    @orig.STDistance(geography::Point(dest.Latitude, dest.Longitude, 4326)) 
       AS distance
--INTO #includeDistances
FROM #orig dest
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download