Under the Table - Visualizing Spatial Data

By Bob Beauchemin | November 2009

In SQL Server 2008, Microsoft introduced spatial data support with two new built-in data types, geometry and geography. Although you could “see” the data in spatial columns in three formats—Well-Known Text, Well-Known Binary, and Geographic Markup Language (GML)—the only built-in way to visualize your data on a map was via the Spatial Results tab that was added to SQL Server Management Studio. This was a boon to developers who were visualizing map polygons or even geometric data unrelated to a map (the geometric layout of a warehouse, perhaps), but if you had a collection of points with locations of cities, you could see only the points. To add a “base map” (for example, a map of the world to layer your city locations on), you could use the UNION ALL syntax with a SELECT statement and visualize it with the Spatial Results tab:

SELECT city_name, geog FROM cities
UNION ALL 
SELECT NULL, geog FROM map_of_the_world_table;

But SQL Server Management Studio is an administrator and programmer tool. You’d like to be able to visualize your data inside the reports you’re producing for management. It’s easier to see trends when graphics are involved, and as Waldo Tobler’s First Law of Geography states, “Everything is related to everything else, but closer things are more closely related.” In this article, I want to show you three new arrivals on the SQL Server spatial visualization scene: the map control in SQL Server 2008 R2 Reporting Services (SSRS), the ESRI MapIt product, and the MapPoint Add-In for SQL Server 2008.

Reporting Services Map Control

Map visualizations use a layer concept. The background or base map (for example, the map of the world) is overlaid with one or more layers of spatial information and database information. Spatial information might consist of sets of polygons like your sales territories, linestrings like roads or rivers, or points like store locations. One of the most common types of map for analyzing business data is a choropleth, a thematic map in which areas are shaded in proportion to the statistical variable you’d like to analyze. For example, a choropleth map showing sales by state in the United States might look like the screenshot in Figure 1. Add a second layer that shows the location of your stores, and you have another layer of analysis available.

Figure 1** Choropleth Map of Sales in the U.S. by State**

As a report programmer, you can access SQL Server 2008 R2 Map Control through either Report Builder 3.0 or a Reporting Services project in Business Intelligence Development Studio (BIDS). The Map appears as one of the choices on the main Report Builder template. In a BIDS project, you add a Map Control to a report by dragging it from the toolbox. Either way, the map wizard walks you through the basics.

First, you choose your base map using the New Map Layer dialog. The base map can come from the built-in Map Gallery, an ESRI shapefile (a standard data format for representing geospatial data), or a SQL Server spatial query. This is shown in Figure 2.

Figure 2 ** New Map Layer Dialog in SSRS 2008 R2**

If you choose a SQL Server spatial query, you’ll obtain map data from a spatial column in a SQL Server table. The wizard walks you through creating a Data Connection when you are creating a query with the Query Designer. Your query must contain at least one column of data type geometry or geography or you’ll get an error. The next dialog, Choose Spatial Data and Map View options, allows you to choose a spatial field (in case your query includes multiple spatial fields) and a Layer Type as shown in Figure 3. Ordinarily, a spatial column in a table will contain all points, all linestrings or all polygons, and the Map wizard looks at the data and returns the kind of layer type that corresponds. You can change the layer type; however, if you choose a field that contains all polygons (or multipolygons) but select Layer Type: Point, no map data will appear in the preview pane. One nice feature is the option to include a Bing Maps background, so if you have point data, you can use Bing Maps tiles as the base map background for your map control. With Bing Maps tiles as a background, you can choose Street Map, Aerial, or Hybrid view. If you choose Bing Maps, your spatial data will be layered on top of the Bing Map base layer.

Figure 3** Choose Spatial Data and Map Options**

Once you have set your base layer or layers, you’re presented with a set of map type choices, which vary depending whether your spatial data consists of points, linestrings or polygons. For example, if your layer contains polygons, you can choose between Basic Map (just the spatial data you’ve selected), Color Analytical Map (the choropleth maps mentioned earlier where the color of each polygon is based on an analytic variable), or Bubble Map (where a symbol in the center of each area is sized proportionally by an analytic variable). Linestring data gives you a choice of Basic Line Map or Analytical Line Map. Point data gives you a choice of Basic Marker Map, Analytical Marker Map or Bubble Map. The choropleth map in Figure 1 is a Color Analytical Map produced by the wizard.

Choosing a color analytical or bubble map leads to a panel that lets you select the data column to analyze. This data may be in the same dataset as your spatial data or in a different dataset with a related field. For example, you might have a table that contains state information and another table that contains SalesTotals for each state. Although the map shape data can only come from shapefiles, map gallery or SQL Server spatial tables, the analytic data can come from any data source, including SQL Server Analysis Services.

After you’ve chosen your analytic data source, if needed, the final panel allows you to choose common visualization aspects, such as the size of bubbles in a bubble map or polygon colors, to visualize data. You can also choose whether your layers’ labels will appear. For example, if your map consists of polygons that represent shapes of states, the label might be the name of the state. Bear in mind that labels only appear in polygons where the polygon is large enough to hold the text.

Of course, as with wizard-based development in general, the wizard only scratches the surface of what you can do. The Map portion of the control is contained within a Viewport, which is a separate control that enforces the boundaries of the map on the report page. The map control properties are divided roughly into Viewport Properties, Map Properties and Layer Properties. You can change the properties using either the context menus or a more detailed view provided in the Properties Windows. The set of properties that you see in the corresponding Properties Window depend on which part of the Map Control has the focus.

The Map Projection is specified in the Viewport’s properties. Your choices depend on the spatial column type you’re using in the layer, and it’s important to realize how your data and SRID (spatial reference ID) affects your choice. To start with the simplest case, suppose you’d like a map of the layout of your company’s warehouse (a physical warehouse where goods are stored, not a data warehouse). You’d likely measure the warehouse, draw a floor plan, and map the placement of the goods based on their location in the floor plan. In this case, the distance being mapped is so small that the fact that the Earth is round rather than flat does not matter. You’re measuring in terms of a geometric coordinate system (X and Y coordinates) and SQL Server’s geometry data type. In this case, you set the Viewport’s Coordinate System property to Planar.

The more common case is that you’re plotting positions on the Earth in terms of latitude and longitude. Your map data contains latitude and longitude, so you’re probably using the geography data type. To produce a flat map from the earth (which is technically an oblate spheroid), the map control provides a set of map projections. To use the map projections appropriately, your geography data type column should be using SRID 4326, 4269 or one of the other common Earth-related SRIDs that the map control is expecting. In this case, you set the Viewport’s Coordinate System property to Geographic and choose a map projection as the Viewport’s Projection property list.

The last case is when your data is using a projected coordinate system. In this scenario, you’re using SQL Server’s geometry data type, not geography. The map control will do nothing to project the data, because the projection information is already part of the data type itself. Common SRIDs for the projected coordinate system include the State Plane data projection or the British National Grid. In this case, you set the Viewport’s Coordinate System property to Planar.

The reason I mention all this is that, except for projecting geography coordinates (which are assumed to be 4326 - WGS84 coordinates), the map control will not automatically reproject between different coordinate systems. For example, you can’t mix a Bing Maps Tile Layer (geographic) with a British National Grid Layer (planar) on the same map. The result wouldn’t be pretty.

The big win with the Reporting Services 2008 R2 Map Control is that the SQL Server spatial data is automatically visualizable in a variety of map formats. Correlations between the business data and spatial data are easy to accomplish with the wizard, and all the additional power you need is available with a host of customizations through properties. One of my favorite (nonspatial) features of the map control is the ability to enable drilldown through the normal “Action” mechanism in SQL Server Reporting Services. Simply select the Action tab in the Map Properties dialog and you can link together reports that contain maps that show Sales by Country on a World map, then drill down to State or Region, then to City. At the City level, you could add a point layer with the location of your stores. For additional information about preparing and using spatial data with the SQL Server 2008 R2 Reporting Services map control, see Ed Katibah’s excellent blog series starting at blogs.msdn.com/edkatibah/archive/2009/05/09/cartographic-adjustment-of-spatial-data-for-sql-server-reporting-services-part-1.aspx.

The ESRI MapIt Product

ESRI, the world’s leading GIS company, released a product named MapIt at its 2009 Users Conference. MapIt is actually a set of components that make it easier to work with existing business data that involves location. This product directly produces and consumes SQL Server 2008 spatial data types, so no additional ESRI software is required to use it.

The most common example of location-based business data is address data, and so MapIt includes a program, the Spatial Data Assistant, that performs geocoding on address data in different formats. The Spatial Data Assistant will add a new Geometry column to any SQL Server table with addresses and populate it by calling Web-based services. You have a choice of the ESRI Map Service or the Bing Maps Geocoding service. Once the existing data is updated, you can use geocoding in a trigger to keep the location in sync. An example of such a trigger is available on the MapIt support site.

To display your business data along with other map layers (for example, addresses of students along with a layer that consists of school district boundaries), you’ll need to import the additional location information into SQL Server 2008. The Spatial Data Assistant can import existing GIS data in ESRI Shapefile format or sets of free ESRI map data available online. On import, you’re allowed to change the data projection to correspond to the projection used by Bing Maps or the ARCGIS Server. You can even specify the SRID of your choice from a list of supported SRIDs. The import function produces a SQL Server geometry column in the imported table.

Once you have your business data and other map layer data in SQL Server, you can visualize it in your own programs. ESRI provides a free Silverlight and Windows Presentation Foundation (WPF) API that can be used to create rich Web and Windows-based applications. To enable the use of your SQL Server spatial data with these APIs, MapIt includes a REST-based Spatial Data Service, which allows you to expose one or more Web endpoints that produce JSON or HTML output in the format that the Silverlight APIs consume. It includes a Spatial Data Services directory that allows you to browse your tables and views, and query the ones that include geometry and geography columns. Once you’ve decided on the data you’d like to use in your map, simply copy that table or view’s URL from the Spatial Data Services directory location and paste it into an application that uses the API. Here’s an example of exposing the Customer addresses in the AdventureWorks2008 database using the Spatial Data Service with a view, Sales.vIndividualCustomerSpatial, that I created by adding the SpatialLocation column to an existing AdventureWorks2008 view (Figure 4). This map includes one of my favorite features—adding a clustering component to cluster points at lower map resolutions. As you drill down into higher map resolutions, the point clusters are visible as individual points.

Figure 4 Using the Spatial Data Service with a View

<!--UserControl element and namespace declarations elided for clarity -->
<Grid x:Name="LayoutRoot">
    <esri:Map x:Name="MyMap" >
        <esri:Map.Layers>
            <esri:ArcGISTiledMapServiceLayer ID="StreetMapLayer"
                Url="https://server.arcgisonline.com/ArcGIS/rest/services/ESRI_
StreetMap_World_2D/MapServer"/>
                <esri:FeatureLayer ID="MyFeatureLayer"
                    Url="https://zmv10/SDS/databases/AdventureWorks2008/Sales.
vIndividualCustomerSpatial"
                Where="CountryRegionName = 'United States'">
                <esri:FeatureLayer.Clusterer>
                    <esri:FlareClusterer FlareBackground="#99FF0000"
                        FlareForeground="White" MaximumFlareCount="9" />
                    </esri:FeatureLayer.Clusterer>
                </esri:FeatureLayer>
            </esri:Map.Layers>
        </esri:Map>
</Grid>

This code produces the results shown in Figure 5. Note that you can mix and match SQL Server 2008 spatial data layers with ARCGIS or Bing Maps base map layers, as well as other layers you’ve imported with the Spatial Data Assistant. For example, you can add a layer containing SalesPerson addresses to visualize the location of your salespeople vis-à-vis your customers. A Layer type is also available for Graphics and a Drawing surface is supported in the API for further map interactivity. Finally, there are specialized layers available at the ESRI support Web site for GEORSS and KML. I’ve barely scratched the surface of the API’s functionality. For more information about the Silverlight and WPF API, see resources.esri.com/arcgisserver/apis/silverlight/index.cfm.

Figure 5** MapIt-Generated Map with Customer Locations Showing Point Clustering**

You may want to provide professional-looking maps based on your SQL Server data without incurring the cost of programming and maintaining a Silverlight or WPF application. MapIt includes a Web Part that allows you to add maps to SharePoint sites with no programming. You can include spatial data from:

SharePoint lists that include addresses or latitude/longitude fields
SQL Server spatial data exposed with the Spatial Data Assistant
ARCGIS Server data

Editing an instance of the ESRI SharePoint Web Part permits you to import layers of spatial data by providing a URL or SharePoint list name. You can use either Bing Maps or ARCGIS Server as a base map. Additional dialogs enable you to specify filter expressions, define pop-ups that display additional information as you hover over individual points (these are known as MapTips in the Silverlight API), choose symbology and add point clustering by clicking a checkbox. The maps will refresh to display the most current information if you have dynamically changing data. You can configure data refresh and caching at an individual Web Part level, or globally for the Spatial Data Service.

MapPoint 2010 Add-In for SQL Server 2008

MapPoint is Microsoft’s original offering in the realm of business spatial data visualization and location-based queries. One of its strengths is that MapPoint comes with a rich collection of multi-level map information that works in an offline mode, as well as letting you add your own spatial layers. The latest versions of MapPoint even come with GPS integration. In August 2009, Microsoft released a free MapPoint Add-In for SQL Server 2008 spatial data, which makes it easy to add layers of SQL Server-based information, perform spatial queries against the data and the layers, and save the end product to disk for further refinement. The saved version of the map can be distributed without requiring access to SQL Server to use.

The WPF-based add-in uses a direct ADO.NET connection to talk to SQL Server. Once you’ve connected to the SQL Server instance and the database that contains your spatial data in the Add Layer tab (shown in Figure 6), you choose a table that contains a spatial column to use as your layer. You can reduce the amount of data that is returned (and therefore make the layer populate faster) by limiting features to the current map extent, as well as selecting a subset of columns and generalizing the data that’s returned. You can also specify a SQL WHERE clause on your query. The query interface was designed for maximum speed; to ensure the spatial index is used, you can choose to use a common table expression for more complex queries. Of course, a dialog is provided that gives you complete control of the symbology.

Figure 6** MapPoint Add-In Add Layer Dialog**

The Map Tips that appear when you select spatial features like points include all of the data in the table you’ve specified. This data is “live,” that is, you can edit the data corresponding to individual spatial feature tables and the updated data will be saved to the database when you hit the Save button. You can even add new features (rows containing spatial data) or delete features directly from the add-in.

Finally, you can refine queries based on an existing spatial data layer. For example, once I’ve retrieved a set of school locations based on a map extent that roughly corresponds to my city limits, I can query that set to find the schools within a mile of my house. Or I can query on any of the other fields I’ve retrieved. Each additional query will bring back an additional layer, and you can hide, show and refine your layers before saving the map. When you save the map, it not only saves the graphic layers but also the SQL queries that produced them, so you can update your layers if the data changes. For sub-layer queries, the generated SQL is visible in the window before you execute the query. There’s a user-settable timeout value, and you can cancel executing layer queries in progress. The fact that you’re connecting directly to SQL Server makes the experience much more interactive. There’s even a utility that allows you to import data into SQL Server from shape files or MapInfo .MIF files.

The products and features I’ve mentioned here are just the latest ones that expand the ways to use SQL Server spatial data. In addition, Safe Software’s FME for SQL Server 2008 product is integrated with SQL Server Integration Services (SSIS) to allow spatial ETL in SSIS workflows. Perhaps the next release of SQL Server will see more integration with SQL Server Analysis Services and even data mining features. The built-in and third-party visualization support expands the usefulness of location data beyond analyzing addresses, to make ordinary business data come alive.

Bob Beauchemin is a database-centric application practitioner and architect, course author and instructor, writer and Developer Skills partner at SQLskills. He’s written books and articles on SQL Server, data access and integration technologies, and database security. You can reach him at bobb@sqlskills.com.

Under the Table - Visualizing Spatial Data

Reporting Services Map Control

The ESRI MapIt Product

MapPoint 2010 Add-In for SQL Server 2008

Additional resources