Last Updated on August 15, 2022 by Sean Lee

Since version 2008, Microsoft SQL Server added the support for spatial data types called Geography and Geometry.

The geography type store GPS latitude and longitude coordinates in a binary format and can be used to query locations and calculate distances.

• We can create table columns of data type geography.
• SQL Server has built-in functions to compute distances. We can call these functions within a SQL SELECT statement.
• Geography data type can be used in persisted and non-persisted computed columns.
• Geography data type columns can be indexed.

SQl Server Function STDistance (geography Data Type) Returns the shortest distance in meters between a point in a geography instance and a point in another geography instance. Another function ShortestLineTo returns a LineString instance with two points that represent the shortest distance between the two geography instances. We can use STLength to find the length of the LineString instance (in meters).

Let’s create a table with geography data type and load some sample data into the table.

CREATE TABLE [sales].[CustomerLocation](

[CustomerID] [int] IDENTITY(1,1) NOT NULL,

[Address] [nvarchar](60) NOT NULL,

[City] [nvarchar](30) NOT NULL,

[State] [nvarchar](50) NOT NULL,

[PostalCode] [nvarchar](15) NOT NULL,

[DeliveryLocation] [geography] NULL,

CONSTRAINT [PK_CustomerLocation_CustomerID] PRIMARY KEY CLUSTERED

(

[CustomerID] ASC

)

)

I will insert some sample data into this table by grabbing some data in table [sales].[CustomerLocation]  in SQL Server sample database [AdventureWorks2017] (you can use  [AdventureWorks2014]  or [AdventureWorks2016] too) as [sales].[CustomerLocation] has a column [SpatialLocation]  with geography data type.

INSERT INTO [sales].[CustomerLocation]

(Address,City,State,PostalCode,DeliveryLocation)

SELECT AddressLine1, City, S.Name, PostalCode, SpatialLocation

FROM [Person].[Address] A

INNER JOIN [Person].[StateProvince] S

ON A.StateProvinceID = S.StateProvinceID

WHERE A.[StateProvinceID] = 9 AND AddressID = 26;

GO



INSERT INTO [sales].[CustomerLocation]

(Address,City,State,PostalCode,DeliveryLocation)

SELECT AddressLine1, City, S.Name, PostalCode, SpatialLocation

FROM [Person].[Address] A

INNER JOIN [Person].[StateProvince] S

ON A.StateProvinceID = S.StateProvinceID

WHERE A.[StateProvinceID] = 9 AND AddressID = 1018

GO



INSERT INTO [sales].[CustomerLocation]

SELECT AddressLine1, City, S.Name, PostalCode, SpatialLocation

FROM [Person].[Address] A

INNER JOIN [Person].[StateProvince] S

ON A.StateProvinceID = S.StateProvinceID

WHERE A.[StateProvinceID] = 9 AND AddressID = 301

GO



INSERT INTO [sales].[CustomerLocation]

SELECT AddressLine1, City, S.Name, PostalCode, SpatialLocation

FROM [Person].[Address] A

INNER JOIN [Person].[StateProvince] S

ON A.StateProvinceID = S.StateProvinceID

WHERE A.[StateProvinceID] = 9 AND AddressID = 317

GO



INSERT INTO [sales].[CustomerLocation]

SELECT AddressLine1, City, S.Name, PostalCode, SpatialLocation

FROM [Person].[Address] A

INNER JOIN [Person].[StateProvince] S

ON A.StateProvinceID = S.StateProvinceID

WHERE A.[StateProvinceID] = 9 AND AddressID = 328

GO



INSERT INTO [sales].[CustomerLocation]

SELECT AddressLine1, City, S.Name, PostalCode, SpatialLocation

FROM [Person].[Address] A

INNER JOIN [Person].[StateProvince] S

ON A.StateProvinceID = S.StateProvinceID

WHERE A.[StateProvinceID] = 9 AND AddressID = 11837

Query the table by the code below:

SELECT * FROM [sales].[CustomerLocation]

we get the following result:

As you can see, the 6 Customers ( CustomerID 1 to 6) are located in  the 6 Californian cities, San Francisco, San Jose, Santa Cruz, Los Angeles, Long Beach and San Diego,  from North to South, respectively.

We can use STDistance to calculate the distance (in meters) from a city to another. The TSQL Code below will list the distance of the other 5 Customers’ Delivery Locations to Customer1 in San Francisco in miles (Meter*0.0006213712):

SELECT TOP 5 B.CustomerID , B.City, 
A.DeliveryLocation.STDistance(B.DeliveryLocation)*0.0006213712 AS [Distance in Miles] 
FROM sales.CustomerLocation AS A CROSS JOIN sales.CustomerLocation AS B 
WHERE A.CustomerID = 1 
AND A.CustomerID <> B.CustomerID
ORDER BY [Distance in Miles]

The result is as below:

The following example uses ShortestLineTo and STLength to finds the distance between two geography instances in miles.

DECLARE @g1 geography 
SELECT @g1 = DeliveryLocation FROM  sales.CustomerLocation 
where CustomerID =1  --(CustomerID = 1 in San Francisco)
DECLARE @g2 geography 
SELECT @g2 = DeliveryLocation FROM  sales.CustomerLocation 
where CustomerID =4  --(CustomerID = 4 in Los Angeles)
SELECT @g1.ShortestLineTo(@g2).STLength()*0.0006213712 as [Distance in Miles]

The following code example shows the top 3 nearest customer delivery locations from Custermer1 in San Francisco.

DECLARE @g geography  
SELECT @g = DeliveryLocation FROM  sales.CustomerLocation 
WHERE CustomerID =1  --(CustomerID = 1 in San Francisco)
SELECT TOP(3) CustomerID, DeliveryLocation.ToString() AS DeliveryLocation, City 
FROM sales.CustomerLocation
WHERE CustomerID <> 1
ORDER BY DeliveryLocation.STDistance(@g);  

By now we have covered the basic knowledge of Geography data type and the key built-in methods, let’s try to answer a couple of SQL server 70-761 practice questions on this subject.

Practice Questions 1

You have a database that tracks orders and deliveries for customers in California USA. The database contains the following table:

Sales. CustomerLocation

Your company is developing a new application that connects customers to each other based on the distance between their delivery locations.

You need to write a query that returns the nearest customer.

Solution: You run the following Transact-SQL statement:

SELECT TOP 1 B.CustomerID, A.DeliveryLocation.STDistance(B.DeliveryLocation) AS Dist 
FROM sales.CustomerLocation AS A 
CROSS JOIN sales.CustomerLocation AS B 
WHERE A.CustomerID = @custID AND
A.CustomerID <> B.CustomerID ORDER BY Dist 

The variable @custID is set to a valid customer.

Does the solution meet the goal?

š A. Yes
š B. No

Answer & Explanation
Difficulty level:  
Correct Answer:    A

Explanation:
Function STDistance (geography Data Type) Returns the shortest distance in meters between a point in a geography instance and a point in another geography instance. In the code the TOP 1
… ORDER BY Dist (with default ascending ASC order) will return the closed custeomer.

We can actually set the @custID = 1 (CustomerID =1 in San Francisco) and run the code against our sample data in table [sales].[CustomerLocation]as below:

DECLARE @custID INT = 1
SELECT TOP 1 B.CustomerID, A.DeliveryLocation.STDistance(B.DeliveryLocation) AS Dist 
FROM sales.CustomerLocation AS A 
CROSS JOIN sales.CustomerLocation AS B 
WHERE A.CustomerID = @custID AND
A.CustomerID <> B.CustomerID ORDER BY Dist

The result is as below:

As we see in our sample table, customer 2 (customerID =2) is in San Jose and is the closest customer to customer 1 in San Francisco.

Practice Questions 2

You have a database that tracks orders and deliveries for customers in California USA. The database contains the following table:

Sales. CustomerLocation

Your company is developing a new application that connects customers to each other based on the distance between their delivery locations.

You need to write a query that returns the nearest customer.

Solution: You run the following Transact-SQL statement:

WITH DIST_CTE (CustA, CustB, Dist)
AS (
   SELECT A.CustomerID as CustA, B.CustomerID as CustB,
   B.DeliveryLocation.ShortestLineTo(A.DeliveryLocation).STLength() AS Dist
   FROM  sales.CustomerLocation AS A 
   CROSS JOIN sales.CustomerLocation AS B 
   WHERE A.CustomerID <> B.CustomerID 
)
SELECT TOP 1 CustB, Dist
FROM DIST_CTE
WHERE CustA = @custID 
ORDER BY Dist

The variable @custID is set to a valid customer.

Does the solution meet the goal?

š A. Yes
š B. No

Answer & Explanation
Difficulty level:  
Correct Answer:    A

Explanation:
ShortestLineTo (geography Data Type) Returns a LineString instance with two points that represent the shortest distance between the two geography instances. The length of the LineString instance returned is the distance between the two geography instances.

STLength (geography Data Type) returns the total length of the LineString instance (in meters).

Again, we can actually set the @custID = 1 (CustomerID =1 in San Francisco) and run the code against our sample data in table [sales].[CustomerLocation] as below:

DECLARE @custID INT = 1
;WITH DIST_CTE (CustA, CustB, Dist)
AS (
   SELECT A.CustomerID as CustA, B.CustomerID as CustB,
   B.DeliveryLocation.ShortestLineTo(A.DeliveryLocation).STLength() AS Dist
   FROM  sales.CustomerLocation AS A 
   CROSS JOIN sales.CustomerLocation AS B 
   WHERE A.CustomerID <> B.CustomerID 
)
SELECT TOP 1 CustB, Dist
FROM DIST_CTE
WHERE CustA = @custID 
ORDER BY Dist

The result is as below:

Once again as we see in our sample table, customer 2 (customerID =2) is in San Jose and is the closest customer to customer 1 in San Francisco.

If you like this post, please post your comment below. For more study guide like this, please check out our newly updated 2019 Version 70-761: The Complete Crash Course.