Waller - 3 months ago 18x
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.

``````DECLARE @orig_lat DECIMAL
DECLARE @orig_lng DECIMAL
SET @orig_lat=53.381538 set @orig_lng=-1.463526
SELECT *,
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.

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)
``````

Gives

``````----------------------
538404.100197555

(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);

SELECT *,
@orig.STDistance(geography::Point(dest.Latitude, dest.Longitude, 4326))
AS distance
--INTO #includeDistances
FROM #orig dest
``````