SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2017
download SQL Server 2016
download SQL Server 2014



How to Calculate Distance Between Two Points in SQL Server 2008 Spatial Data (Geography)


This t-sql tutorial demonstrates "how to calculate distance between two points" using spatial data methods in SQL Server 2008 and later versions.

Microsoft SQL Server 2008 introduced Spatial Data to SQL developers.
Spatial data in Microsoft SQL Server enables sql programmers to use Geography and Geometry data types.
The easiest way to find distance between two points (Geography Point data type) is using Geography data type STDistance method.

Here is a t-sql spatial sample which is calculating distance between two points on earth surface.
Since we are dealing with points on earth, then we are required to work with geography data types.

DECLARE @point1 geography, @point2 geography
SET @point1 = CAST('POINT (63 40)' as geography)
SET @point2 = CAST('POINT (70 -10)' as geography)
SELECT @point1.STDistance (@point2);

The default Spatial Reference Id (SRID) for Geography types is 4326 in Microsoft SQL Server.
So SRID 4326 is not used in the t-sql example of Spatial Geography Point representation.

Please note that Geography STDistance() method return values are in units that the spatial reference system used for related geography data.
So in our case using the Microsoft SQL Server default SRID, the Geography STDistance() method output value is in meters unit for SRID 4326.
Although the latitude and longitude values are in degrees, the unit of measure of distance between two points is meters.

For detailed information about which SRID or spatial reference system uses which unit of measure, please check the system view sys.spatial_reference_systems





Here is another sql spatial distance calculation using Geography STDistance method.
Here the Geography Point variables are declared in a different way.
But the method used to find distance between two points is same : STDistance() method.

declare @point1 geography
set @point1 = geography::STGeomFromText('POINT (120 80)', 4326);
declare @point2 geography = geography::STGeomFromText('POINT (100 70)', 4326);
select @point1.STDistance (@point2)





Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems