# Friday, 09 August 2013

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

  1. We can define database columns with these data types
  2. Built-in functions exist to do things like draw shapes and compute distances. We can call these functions within a SQL SELECT statement.
  3. 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.

Fig1-SampleLatLongData[1]

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.

Fig2-SampleDataWithGeoPoints[1]

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.