Sql Distance Query Without Trigonometry

SQL Distance Query without Trigonometry

If your points are within reasonable distance of each other (i.e. not across half the world, and not across the date line), you can make a correction for the difference between latitude and longitude (as a longitude degree is shorter, except at the Equator), and then just calculate the distance as if the earth was flat.

As you just want to sort the values, you don't even have to use the square root, you can just add the squares of the differences.

Example, where @lat and @lng is your current position, and 2 is the difference correction:

select *
from Points
order by (lat - @lat) * (lat - @lat) + ((lng - @lng) * 2) * ((lng - @lng) * 2)

You can calculate the difference correction for a specific latitude as 1 / cos(lat).

---

Cees Timmerman came up with this formula which also works across the date line:

pow(lat-lat2, 2) + pow(2 * min(abs(lon-lon2), 360 - abs(lon-lon2)), 2)

SQL query to pull locations based on distance from lat/lng, in LINQ2SQL?

The query you've posted should work reasonably well.

However, if you are in the position where you can change your data schema then SQL Server has some native Geography types - and if you could use these then there are built-in functions and built-in indexing which would allow you to execute this type of query much more efficiently - see http://www.microsoft.com/sqlserver/2008/en/us/spatial-data.aspx

Working with Trigonometric Functions SQL Server

To slightly improve John's answer, this is what you could do:

CREATE TABLE YourTable (
    ID INT PRIMARY KEY
    , Lat FLOAT
    , Lon FLOAT
    , Location AS GEOGRAPHY::Point(Lat, Lon, 4326));

INSERT INTO YourTable (ID, Lat, Lon)
VALUES
    (1,-8.157908, -34.931675)
    , (2,-8.164891, -34.919033)
    , (3,-8.159999, -34.939999);
GO

CREATE FUNCTION GetCloserThanOneKilometer (
    @Lat FLOAT
    , @Lon FLOAT
    , @Distance FLOAT)
RETURNS TABLE 
AS
RETURN
SELECT *
FROM YourTable
WHERE GEOGRAPHY::Point(@Lat, @Lon, 4326).STDistance(Location) <= @Distance;

You'll have a calculated column that will store coordinates. So in order to abstract and simplify querying you can create a inline function which I skillfully named GetCloserThanOneKilometer and then run query as follows to get data based on your given latitude and longitude and distance that has to be lower than your input (@Distance param):

SELECT *
FROM GetCloserThanOneKilometer(-8.157908, -34.931675, 1000);
GO

Calculating distance between two points (Latitude, Longitude)

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

---

Related Topics