Calculating distance between two points (Latitude, Longitude)

by

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

More Related Contents:

  1. How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?
  2. T-SQL split string based on delimiter
  3. WHERE Clause vs ON when using JOIN
  4. Partition Function COUNT() OVER possible using DISTINCT
  5. Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
  6. Using IF ELSE statement based on Count to execute different Insert statements
  7. Check if table exists and if it doesn’t exist, create it in SQL Server 2008
  8. Is it possible to write single query on two table which are not connected to each other?
  9. SQL Server dynamic PIVOT query?
  10. SQL Server – Best way to get identity of inserted row?
  11. SQL Server Process Queue Race Condition
  12. Is the NOLOCK (Sql Server hint) bad practice?
  13. Creating an index on a table variable
  14. SQL query to split column data into rows
  15. What is the difference between using a cross join and putting a comma between the two tables?
  16. How to drop SQL default constraint without knowing its name?
  17. Equivalent of Oracle’s RowID in SQL Server
  18. Dynamic update statement with variable column names
  19. How to select only the first rows for each unique value of a column?
  20. SQL Server (TSQL) – Is it possible to EXEC statements in parallel?
  21. SQL “between” not inclusive
  22. Unordered results in SQL
  23. Using PIVOT in SQL Server 2008
  24. SQL Server replace, remove all after certain character
  25. Inserting rows into a table with one IDENTITY column only [duplicate]
  26. ORDER BY items must appear in the select list if SELECT DISTINCT is specified
  27. How do I compare 2 rows from the same table (SQL Server)?
  28. How to return default value from SQL query
  29. Group consecutive rows of same value using time spans
  30. Why do SQL Server Scalar-valued functions get slower?

Leave a Comment