How to Work with Geography Data Types in SQL Server

Jan 142019

A wooden world map with blue pins on it.

If you need to work with geography data types, specifically to determine distances as well as to determine if a point was contained in a geographic area, here’s an approach I recently used.

Latitude and Longitude

The first thing I learned was to cast a latitude/longitude as a geography point. The syntax for this is:

GEOGRAPHY::Point(Latitude, Longitude, SRID)

SRID stands for Spatial Reference Identifier. The most common SRID is 4326, which has the information in meters. For other type of SRID’s run this query in SQLServer:

select * from sys.spatial_reference_systems

If we cast the Latitude and Longitude for the Willis Tower (formerly the Sears Tower) in Chicago, it looks like this:

Geography::Point(41.878899,-87.636297, 4326)

Note: spatial data types (Point) are case sensitive. Other spatial data types are described here:

https://docs.microsoft.com/en-us/sql/relational-databases/spatial/spatial-data-sql-server?view=sql-server-2017

Geography data types are more than just data types, they are objects, and therefore have methods. For example, to find the distance between the Willis Tower and the Empire State Building we can run this query:

   DECLARE @WillisTower      geography = geography::Point(41.878899,-87.636297, 4326) ,
           @EmpireStBuilding geography = geography::Point(40.749014,-73.985317, 4326)
SELECT
    @WillisTower                                          AS [WillisTowerlocation] 
   , @EmpireStBuilding                                    AS [EmpireStBuildinglocation]  
   , @WillisTower.STDistance(@EmpireStBuilding)           AS [Willis Twr to Emp St meters]
   , @WillisTower.STDistance(@EmpireStBuilding) * 0.000621371  AS [Willis Twr to Emp St miles]

Note: Methods are case sensitive. There are several different methods, refer here for more information:

https://docs.microsoft.com/en-us/sql/t-sql/spatial-geography/ogc-methods-on-geography-instances?view=sql-server-2017

Polygons

Now let’s look at another Spatial Data type, the Polygon. Polygons do not have to be limited to classic geometric shapes like Squares, Hexagons, etc. but can also be geographic borders. The SQL server sample database World Wide Imports has Point data as the center of a city in the [WideWorldImporters].[Application].[Cities] table, and Polygon data as the borders of the states in the [WideWorldImporters].[Application].[StateProvinces] table. One method to use between Points and Polygons is Intersect. The STIntersect() method returns 1 or 0 (T/F). It can be used when joining two tables with the ON statement like this:

  
  FROM [WideWorldImporters].[Application].[StateProvinces] sp
  LEFT JOIN  [WideWorldImporters].[Application].[Cities] c
    ON c.[Location].STIntersects(sp.[Border]) = 1

I suggest only using this method in the Join Clause if there is not another option to join with. In this example, there is a StateProvinceID in both the City table and the StateProvinces table.

  FROM [WideWorldImporters].[Application].[StateProvinces] sp
  LEFT JOIN  [WideWorldImporters].[Application].[Cities] c
    ON c.[StateProvinceID] = sp.[StateProvinceID]

With nearly 38,000 rows in the City table, the join using the Intersects option takes 1:34, while the join on the StateProvinceID takes 0:01. Methods can be expensive in Join clauses.
Other spatial data types are described here:

https://docs.microsoft.com/en-us/sql/relational-databases/spatial/spatial-data-sql-server?view=sql-server-2017

Finally I have a query that uses the STDistance, STArea, STIntersects methods to provide a sample as a reference to work with.

DECLARE @WillisTower geography = geography::Point(41.878899,-87.636297, 4326)

SELECT 
    c.[CityID]
  , c.[CityName]
  , c.[StateProvinceID]
  ,sp.[StateProvinceCode]                                                  AS [State]
  , c.[Location]
  , geography::Point(41.878899,-87.636297, 4326)                           AS [WillisTowerlocation]  
  , c.[Location].STDistance(geography::Point(41.878899,-87.636297,4326))   AS [MetersFromWillisTower]
  , c.[Location].STDistance(@WillisTower) * 0.000621371                    AS [MilesfromWillisTower]
  , c.[Location].STSrid                                                    AS [SRID]
  ,sp.[StateProvinceID]
  ,sp.[StateProvinceName]
  ,sp.[CountryID]
  ,sp.[SalesTerritory]
  ,sp.[Border]
  ,sp.[Border].STArea()                                                    AS [Area in Sq Meters]
  ,sp.[Border].STArea() * 3.8610215854245E-7                               AS [Area in Sq Miles]
FROM [WideWorldImporters].[Application].[StateProvinces] sp
LEFT JOIN  [WideWorldImporters].[Application].[Cities] c
  --ON c.[Location].STIntersects(sp.[Border]) = 1
  ON c.[StateProvinceID] = sp.[StateProvinceID]
  ORDER BY [StateProvinceName]

I’ve commented out a few columns to allow the output to fit on the page.