1

I have a function like this in SQL Server. I can not understand what is it. Can anyone please explain it to me briefly?

6371 *  ACOS(ROUND(
            COS(RADIANS(Latitude1)) *
            COS(RADIANS([Latitude2])) *
            COS(RADIANS(Longitude1) - RADIANS(Longitude2)) +
            SIN(RADIANS(Latitude1)) *
            SIN(RADIANS([Latitude2])), 15, 2))*1000 <= 500
D Stanley
  • 149,601
  • 11
  • 178
  • 240
Mahavir Kumbharvadia
  • 98
  • 2
  • 13

3 Answers3

2

You are looking at the Haversine formula, which computes the distance on a sphere using their latitude and longitude coordinates. Here is a query using the full Haversine formula:

SELECT 
    ACOS(
       COS(RADIANS(Latitude1))
     * COS(RADIANS(Latitude2))
     * COS(RADIANS(Longitude1) - RADIANS(Longitude2))
     + SIN(RADIANS(Latitude1)) 
     * SIN(RADIANS(Latitude2))
) AS haversineDistance
 FROM yourTable

Have a look at this SO article or Wikipedia for more information.

Community
  • 1
  • 1
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Strictly speaking I believe it's the spherical cosine formula. Also, why not use the Geography class if you're using SQL server? https://msdn.microsoft.com/en-gb/library/microsoft.sqlserver.types.sqlgeography.aspx – Tom Sharpe Jan 24 '16 at 15:08
1

Just like @TimBiegeleisen and @RobertRodkey have said, it's an old school formula for calculating the distance between two points on a Earth.

So this leads to some other questions:

  1. Q: Doesn't SqlServer have geospatial methods available? A: Yes for sql server version 2008 and later.
  2. Q: If Sql has this available using the Geography methods, should I use the above math? A: No if you're on Sql Server 2008 or later. If before that, then you have no choice but you have to use that way/formula.
  3. Q: Are Narwhales, unicorns of the ocean? A: Yes.

Further show off tip - use STDistance .. that's your friend :)

(browser code, not tested, etc)...

-- Arrange.
DECLARE @longitude1 FLOAT = -122.360,
        @latitude1 FLOAT = 47.656,
        @longitude2 FLOAT = -122.343,
        @latitude2 FLOAT = 47.656;
DECLARE @point1 GEOGRAPHY = GEOGRAPHY::STGeomFromText('POINT(' + CAST(@longitude1 AS VARCHAR(10)) + ' ' + CAST(@latitude1 AS VARCHAR(10)) + ')', 4326);
DECLARE @point2 GEOGRAPHY = GEOGRAPHY::STGeomFromText('POINT(' + CAST(@longitude2 AS VARCHAR(10)) + ' ' + CAST(@latitude2 AS VARCHAR(10)) + ')', 4326);

-- Determine the distance (which would be in metres because we're using the 4326 == [common GPS format](http://spatialreference.org/ref/epsg/wgs-84/)).
SELECT @point1.STDistance(@point2);
Pure.Krome
  • 84,693
  • 113
  • 396
  • 647
0

I'm no expert in the field, but it looks like it's selecting points within a bounding circle:

http://www.movable-type.co.uk/scripts/latlong-db.html

Robert Rodkey
  • 423
  • 3
  • 9