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)