In this blog series we will dig into spatial data available in SQL Server. This first post will be on the data types in SQL Server that allow storing spatial data.
Spatial data has many different names. Geographic information, geospatial data, GIS, map data, or location each one generally refers to the same thing. SQL Server has the capability to store and display spatial data for our applications. In this series, we will cover the details of what spatial data is, how to use it, and best practices for large scale analytics.
The first thing to spatial data is spatial is not special. There are no new installations, or check-boxes to enable features, or anything to configure. Right out of the box SQL Server can handle spatial data! SQL Server leverages spatial data with two data types called geometry and geography.
Finding installed data types
A couple of ways to see these data types is to query the sys.types view and look for geography and geometry. These are also found under every database, Programmability, Types, and Spatial Data Types. These two data types were added in the SQL Server 2008 release and continue to be maintained in 2019.
The big difference between these two data types is geometry is flat-earth (Euclidean) and geography is round-earth (ellipsoidal). This can be also be explained by saying geometry stores data similar to a map and geography stores data like a globe. Knowing this will determine which data type to select to store your data. If the data you are storing is at a grain of street level for a city then use geometry, but if you are storing data that goes across states like highways then use geography.
These data types are how spatial data is stored in SQL Server. In future posts of this series we will get into more details and create spatial data. Additional links to more information can be found below.