Spatial data types were introduced in SQL Server 2008. These data types allow you to store location information directly in your data rows and quickly query that information to create shapes and to determine locations, distances, and points within a given shape.
SQL Server defines two types of spatial data - Geography and Geometry. Both data types track locations as x, y coordinates. The difference is that the Geography data type takes into account the curve of the Earth, while the Geometry data type does not. Because of this, Geography data is often best suited for mapping locations by Latitude and Longitude and computing distances between locations that are far apart. With Geometry data, you can define your own coordinate (such as location in a warehouse) and you should focus on locations that are close enough that the Earths' curve is inconsequential (for example, the distance between storage bins in a warehouse or stations on a shop floor).
Both data types store x, y coordinates in a binary format and each can be used to query locations. A couple advantages of these data types are
- We can define database columns with these data types
- Built-in functions exist to do things like draw shapes and compute distances. We can call these functions within a SQL SELECT statement.
- These columns can be indexed, making calculations very fast.
Imagine a table with the following structure.
CREATE TABLE [dbo].[CustomerAddresses]( [id] [int] IDENTITY(1,1) NOT NULL, [CompanyName] [varchar](250) NOT NULL, [StreetAddress] [varchar](250) NOT NULL, [City] [varchar](250) NOT NULL, [State] [varchar](20) NOT NULL, [PostalCode] [char](25) NOT NULL, [Geo] [geography] NULL, [Latitude] [decimal](18, 14) NULL, [Longitude] [decimal](18, 14) NULL CONSTRAINT [PK_CustomerAddress] PRIMARY KEY CLUSTERED ( [id] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
The Geo column of this table is a Geography data type and is designed to store binary information about the location coordinates.
We can index this column with the following command:
CREATE SPATIAL INDEX SIndx_CustomerAddress_geo_col1 ON dbo.CustomerAddress(Geo);
If we have a table populated with latitude and longitude data (Fig 1), we can easily convert this data to the Geography data type using the Geography::Point function static extension method.
Fig. 1 - Latitude and Longitude data
We convert x and y coordinates to the Geography data type using the Geography::Point function static extension method. The Geography::Point function takes 3 parameters: longitude, latitude, and a code representing the SRID, which is a well-known coordinate system. The most popular system is represented by 4326 and defines 0 latitude at the equator and 0 longitude at a line passing through Greenwich, England. It’s worth pointing out that the Geography::Point parameters list Longitude before Latitude, even though most coordinate systems list Latitude first. This can be confusing, but it’s something you will want to be aware of.
For this table, the SQL statement is
UPDATE GeoDemo.dbo.CustomerAddress SET Geo = Geography::Point(Latitude,Longitude, 4326) WHERE Latitude IS NOT NULL AND Longitude IS NOT NULL
The SQL above populates a column of type Geography with point data representing latitude and longitude, as shown in Fig. 2.
One very useful extension method of a Geography Data Type is Distance, which accepts a parameter of another Geography point and returns the distance in meters between the 2 points.
The following code calculates the distance (in miles) between 2 points in our table.
DECLARE @GeoLocation1 GEOGRAPHY, @GeoLocation2 GEOGRAPHY, @MetersPerMile DECIMAL, @Distance DECIMAL (18,14) SET @MetersPerMile = 1609.344 SET @GeoLocation1 = (SELECT Geo FROM dbo.CustomerAddress WHERE Id = 1) SET @GeoLocation2 = (SELECT Geo FROM dbo.CustomerAddress WHERE Id = 2) SET @Distance = (SELECT @GeoLocation1.STDistance(@GeoLocation2)/@MetersPerMile) PRINT @Distance PRINT 'miles between Point 1 and Point 2'
Finally, we can use the STDistance extension method from within a SQL SELECT statement to calculate all points within a certain radius of a given point. Below is an example listing all addresses within 100 miles of Address #1
DECLARE @CustomerGeoLocation GEOGRAPHY, @RadiusInMiles INT, @MetersPerMile DECIMAL, @RadiusInMeters DECIMAL (22,14), @CenterId INT SET @CenterId = 1 SET @CustomerGeoLocation = (SELECT Geo FROM dbo.CustomerAddress WHERE Id = @CenterId) SET @RadiusInMiles = 100 SET @MetersPerMile = 1609.344 SET @RadiusInMeters = @RadiusInMiles * @MetersPerMile SELECT @CustomerGeoLocation.STDistance(c.Geo)/@MetersPerMile AS DistanceInMiles, c.* FROM dbo.CustomerAddress c WHERE c.Geo.STDistance(@CustomerGeoLocation) <= @RadiusInMeters AND c.id <> @CenterId ORDER BY DistanceInMiles
In this article, we introduced the SQL Server Spatial data types and showed how to use the Geography extension methods to calculate distance and find nearby locations.