In my previous article, I covered creating geospatial SQL Server tables using the freely available Natural Earth resources.  Natural Earth is an extensive public domain map dataset available at 1:10m, 1:50m, and 1:110 million scales in  vector and raster data formats which can be used as an alternative to ESRI Shapefiles for geospatial data.   In this article, we’ll be creating a simple SQL Server Reporting Services report which utilizes this spatial data in lieu of the more commonly used shapefiles to plot data on a custom map.

To get started, we’ll first need to create some assets.  Follow the steps in the previous article to setup the Natural Earth tables.  Next, we’ll create some views to simplify queries, then we’ll create a report using these assets as well as some sample data.

Create Basic Spatial Views

To simplify our queries, we’ll create two basic SQL Server views.  The first view (v_StatesAndProvinces_USCA) provides spatial data for states and provinces in the United States and Canada.  The second view (v_MajorCities_USCA) provides spatial data for major cities in the United States and Canada.

 

 

Create a New SSRS Report

For this hypothetical scenario, we have a company and we are considering up to 5 new offices in the US Southeast.  We’d like a simple report which shows a map of the states in the southeast with the potential sales per state and the potential office locations.  To get started, create a new blank SSRS Report.

 

Data Source and Datasets

To get started, we’ll need to add one Data Source and 4 Datasets as follows:

Create a Data Source to point to your Natural Earth SQL database.

Next, create 4 new Datasets, each using this Data Source and an embedded query:

Dataset 1:  NE_StatesGeo — A simple query to get the geospatial state shapes that we are interested in – in this case, Alabama, Florida, Georgia, North Carolina, and South Carolina.

 

Dataset 2:  NE_CitiesGeo — A simple query to get the geospatial major cities and their locations for Alabama, Florida, Georgia, North Carolina, and South Carolina.

 

Dataset 3:  Data_EstNumSales — Sample data showing the potential number of sales per state.

 

Dataset 4:  Data_Offices — Sample data showing the potential office location for each state.

 

At this point, we should have a blank report with one data source and four datasets.  Previewing the report should produce a blank report without any errors.

Progress so far — a blank report with one Data Source and four Datasets.

 

Creating the Map

Drag the map component onto the canvas and the New Map Layer wizard will automatically start.  The goal of the first wizard will be to draw the states and color them based on our dataset.  Proceed through the wizard as follows:

Select “SQL Server spatial query.”

 

Select the States geospatial dataset, “NE_StatesGeo.”

 

This warning will pop up informing you that on the upcoming page, it isn’t sure if this is a point, line, or polygon, so you’ll have to set the dropdown box.

 

Select “GeoObject” as the Spatial field and “Polygon” as the Layer type.

 

Select “Color Analytical Map” for the state layer.

 

Next, we need to choose the layer which contains the data for determining color. Select the Estimated Sales dataset, Data_EstNumSales.

 

Ensure that the spatial data correctly maps to the analytical data, via the CountryCode and StateCode fields.

 

Choose the field to visualize (EstNumSales) and a color scheme (which can be modified later. Lastly, choose a label field if state labels on the map are desired. It is normal to have a blank preview.

 

Here’s what we have after completing the first wizard. The map is blank since the shapes are dynamic…they’ll show up when being executed.

 

Previewing the map after completion of the first wizard. It isn’t perfect, but it’s a good start.

 

Next, we want to add another layer to the map containing names of major cities.  This helps give a good reference to places on the map.

In the Map Layers box, visible from clicking on the map, choose the “New Layer Wizard.”

 

Choose “SQL Server spatial query” when the New Map Layer wizard opens for the new layer.

 

For this second layer, choose the dataset which contains the major city data, NE_CitiesGeo.

 

Select “GeoObject” as the Spatial field and “Point” as the Layer type for the cities layer.

 

These markers won’t change size or color, so choose “Basic Marker Map” as the visualization for the cities layer.

 

The labels are the most important part of this layer. Select “Display labels” and ensure the “LocationName” field is the data field for the city labels.

 

Previewing the map after the completion of the second (cities) layer. We’ll clean up the formatting after we get all of our data present.

 

Now, we need to add one final layer.  This third layer will show the potential office locations.  These will come from our second sample data dataset and we’ll use a different maker to differentiate them from the major cities.

As with the Cities layer, click on the map and then launch the New Layer Wizard.  Within the wizard, choose “SQL Server spatial query.”

For the source of the third layer, choose “Data_Offices” which will supply us with the locations of the potential offices.

 

As before, choose GeoObject as the spatial field and Point as the layer type for this third layer.

 

Choose Star as the Marker type and choose to display labels with OfficeName as the data field for the third layer.

 

The map after the completion of the third wizard. The design pane is still blank, but we now have three layers present in the Map Layers box.

 

Previewing the map with all three layers present. All the data we want is present, but it’s a bit ugly. The wizards get you close, but a little refinement is still needed.

 

Cleaning Up the Final Map

After proceeding through the New Layer Wizard three times to add three layers to the map, we have all of our data present.  We now just need to do a little housekeeping to make the map more presentable.  We’ll go through each layer and make slight tweaks to each.

Before adjusting the layers, first notice that we essentially have two legends.  The Legend box and the Map Scale box.  They both give us the same information.  Since the Legend is using more real estate, delete it.

Now, we’ll work on cleaning up the Polygon (state) layer:

Select the “Polygon Color Rule…” to adjust the colors of the state polygons.

 

For this dataset, change the Distribution range method to “Equal Interval.” This will give us more color regions on the map. Based on your data, you sometimes need to play with the number of ranges and type of distribution for what works best.

 

It turns out that the State labels are not helpful and cluttering things up a bit. Choose the Polygon Properties and then remove them by clearing out the next in the Label text field so the box is empty.

 

For the second layer, the major cities, the markers would look better as small black dotes instead of big white circles.

Select the “Point Properties…” option for this layer.

 

Ensure the Marker type is Circle and change the size from 10pt to 2pt.

 

Set the color for the second layer markers to Black.

 

For the third layer, we want to differentiate the office location markers from the city location markers.  For this, we want to use yellow stars.

For the third layer (office locations) select the “Point Properties…” option as was done on the second layer.

 

The default size (10pt) is good and we’ve already chosen the star shape. On the Fill dialog, choose a Solid Yellow color for the marker.

Now that the layers are cleaned up, set a nicer title for the map and stretch it to make it bigger.  The next time we look at it, it should look pretty good…

 

Conclusion – The Final Map

Preview the map and it should look similar to that below to give our final product.  Dynamic spatial queries driving by the Natural Earth datasets are incredibly powerful.  This example just scratches the surface of some of the things that can be done.  The completed report (.rdl file) is available at the end of this article.

 

 

 

Resources

Map Sample RDL File

Getting Started with Natural Earth