CODEWITHSUNDEEP
sqlsql-server
Mahavir Kumbharvadia
Mahavir Kumbharvadia

Reputation: 98

Query using Longitude and Latitude in SQL Server

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

Upvotes: 1

Views: 424

Answers (3)

Pure.Krome
Pure.Krome

Reputation: 87087

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

Upvotes: 1

Robert Rodkey
Robert Rodkey

Reputation: 423

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

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522817

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.

Upvotes: 2

Related Questions