Transact-SQL in SQL Server 2008 : Spatial Data Types (part 3) - Spatial Data Support in SSMS

Spatial Data Support in SSMS

When querying spatial data in SSMS, you’ll find that SSMS has a built-in capability to plot and display some basic maps of your spatial data.

To demonstrate this, you can run the following query in the AdventureWorks2008R2 or AdventureWorks2008 database in SSMS:

select SpatialLocation
    from person.Address a
        inner join
    person.StateProvince sp
        on a.StateProvinceID = sp.StateProvinceID
        and sp.CountryRegionCode = 'US'

After the query runs, you should see a Spatial Results tab next to the Results tab (see Figure 1). Click on this tab, and the location points are plotted on a map. Select the Bonne Projection. If you look closely, you can see that the geographical points plotted roughly provide an outline of the United States. If you mouse over one of the points, SSMS displays the associated address information displayed in the Person.Address table.

Figure 1. Displaying a map of Person.Address records in SSMS.

In addition to displaying maps of geography data values, SSMS can also display geometry data, showing lines, points, and polygons in an X-Y grid. For example, if you run the following query and click on the Spatial Results tab, it should display a box like the one shown in Figure 2:

declare  @smallBox GEOMETRY  = 'polygon((0 0, 0 2, 2 2, 2 0, 0 0))';
select @smallbox

Figure 2. Displaying a polygon in SSMS.

If you want to display multiple polygons, points, or lines together at the same time, they have to be returned as multiple rows in a single table. If you return them as multiple columns, SSMS displays only one column at a time in the Spatial Results tab. For example, if you run the following query, SSMS displays two boxes, the polygon defined by the intersection of the two boxes, as well as the overlapping line defined by the LineString, as shown in Figure 3:

Figure 3. Displaying intersecting polygons and an overlapping Line in SSMS.

declare  @smallBox GEOMETRY  = 'polygon((0 0, 0 2, 2 2, 2 0, 0 0))';
declare @largeBox GEOMETRY = 'polygon((1 1, 1 4, 4 4, 4 1, 1 1))';
declare  @line GEOMETRY  = 'linestring(0 2, 4 4)';

select @smallBox
union all
select @largeBox
union all
select @smallBox.STIntersection(@largeBox)
union all
select @line

Spatial Data Types: Where to Go from Here?

The preceding sections provide only a brief introduction to spatial data types and how to work with geometry and geography data. For more information on working with spatial data, you might want to visit the Microsoft SQL Server 2008 Spatial Data page at http://www.microsoft.com/sqlserver/2008/en/us/spatial-data.aspx. This page provides links to whitepapers and other technical documents related to working with spatial data in SQL Server 2008.

In addition, all examples here deal with spatial data only as data values and coordinates. Spatial data is often most useful when it can be displayed visually, such as on a map. SQL Server 2008 R2 Reporting Services provides new map controls and a map wizard for creating map reports based on spatial data.