Calculating a distance between two points of interest using SQL Server 2008 geography data type

With this post we want to present an example how to use the new SQL Server 2008 Spatial Data-types by calculating the distance between two points of interest.

SQL Server 2008 introduced two spatial data types:

· geography and

· geometry.

Lots of explanations and examples for these already exist on the Internet (e.g. see https://msdn.microsoft.com/en-us/library/cc280766.aspx and https://msdn.microsoft.com/en-us/library/cc280487.aspx for reference). Both data types include methods that are defined by the Open Geospatial Consortium (OGC) standard (see https://www.opengeospatial.org/standards) and a set of Microsoft extensions to that standard.

The geography data type represents data in round-earth coordinate system and stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates. The data is stored in WGS84 (World Geodetic System 1984, see https://en.wikipedia.org/wiki/WGS84) on which GPS relies.

The geometry data type is a planar spatial data type that represents data in a Euclidean (flat) coordinate system.

Now we are ready for a practical example using the geography data type: in this example we want to know the linear distance between Microsoft Photogrammetry (located in Graz) [1] and Microsoft Austria (located in Vienna) [2]. The geography spatial data type, geography, is implemented as a .NET common language runtime (CLR) data type in SQL Server. This type represents data in a round-earth coordinate system. The SQL Server geography data type stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates.

In order to do this we need to store these two Microsoft subsidiaries in a geography data type. For that we need to find the geo coordinates of these two points. We can do this using Microsoft VirtualEarth 3D (see https://maps.live.com/).

The image above shows the MS Austria subsidiary in Vienna [2]. In the right lower corner the geo coordinates are displayed in Latitude (North or South) and Longitude (East or West) with the coordinates 48.1708 N 16.3338 E. We repeat the procedure for MS Photogrammetry in Graz [1] and we get the coordinates 47.0622 N 15.4423 E.

For storing these locations in our example, I created a table called my_locations consisting of the columns id, name and geo_coordinates (using the geography data type!).

I will declare a variable @ms_at as geography data type and fill it with the geo coordinates of MS Austria subsidiary.

DECLARE @ms_at geography;

SET @ms_at = (select geo_coordinates from my_locations l where l.name like '%Microsoft Österreich');

Now I will use the method STDistance (see https://msdn.microsoft.com/en-us/library/bb933808.aspx) to get the linear distance from MS Austria [2] to MS Photogrammetry [1]. STDistance returns the shortest distance between two points in two geography instances in meters.

select @ms_at.STDistance((select l.geo_coordinates from my_locations l where l.name like '%Photogrammetry%'));

The query returned 154360,31 (rounded up to two digits) meters or 154 km linear distance.

Therefore we need to insert the geo coordinates for the famous St Stephan Cathedral in Vienna, Austria (48.2083 N 16.3732 E) in my_locations table. Finally let us see how far away is St Stephan Catedral (Stephansdom on German) from MS Austria [2]?

select @ms_at.STDistance((select l.geo_coordinates from my_locations l where l.name like '%Stephansdom%'));

Here SQL Server returned 5921,46 (rounded up to two digits) meters.