Geospatial mapping made simple with Microsoft SQL Server
Part 1 | Part 2 | Part 3
One of the most exciting features with Report Builder 3.0 is the new map functionality that lets
you add maps made up of one or more layers that together display spatial and analytical data. The
maps can also integrate with Microsoft Virtual Earth to provide a data
visualization tool that is both interesting and informative.
Although you can create a map that contains only spatial data, the most effective maps are those
that include analytical data
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
Dig Deeper
-
People who read this also read...
-
This was first published in February 2010
as well. For example, you might want to create a map that shows the
total amount of sales in each county in a U.S. state. The sales amounts would represent the
analytical data and the mapped county locations would represent the spatial data.
A Report Builder map is made up of one or more layers. Each layer can be configured as one of
the following types:
- Polygon -- Displays outline areas for regions such as cities, states, or countries.
- Point -- Displays specific points on a map.
- Line -- Displays paths or routes between points.
- Tile -- Provides a background that consists of a Microsoft Virtual Earth (Bing) map.
When you create a map, you do so by adding one layer at a time. You can then configure each
layer to meet your specific needs. Also, the configuration options tend to vary from one layer type
to the next, although there are a number of similarities.
In this series I will demonstrate how to create a map that includes a polygon layer, point
layer, and tile layer with Report Builder 3.0. The map will display the continental United States
and the locations of sales people with over $1 million in sales. In addition to the three layers,
the map will include the legends, tooltips, and color-coding necessary to show the amount of sales
for each person and how those sales compare to one another.
Setting up the data source and dataset
To demonstrate how to create this map, I first created a report and added a data source and data
set. The data source connects to the AdventureWorks2008R2 sample database on a local instance of
SQL Server 2008 R2. I named the data source the same as the database.
I installed a local instance of the November 2009 CTP release of SQL Server 2008 R2 on a Windows Server 2008
machine. The installation includes the database engine and SQL Server Reporting Services (SSRS). I
also used the AdventureWorks
sample databases provided for the SQL Server 2008 R2 release and installed the November CTP release of Report Builder 3.0 on the same server.
After I set up the data source, I created a dataset named SalesPeople. The dataset uses
the following Transact-SQL statement to retrieve data through the AdventureWorks2008R2 data
source:
SELECT
(p.FirstName + ' ' + p.LastName) AS FullName,
a.City,
RTRIM(st.StateProvinceCode) AS StateProvinceCode,
sp.SalesYTD,
a.SpatialLocation
FROM
Sales.SalesPerson sp
INNER JOIN Person.Person p
ON sp.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.BusinessEntityAddress bea
ON bea.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.Address a
ON a.AddressID
= bea.AddressID
INNER JOIN Person.StateProvince st
ON st.StateProvinceID = a.StateProvinceID
WHERE
p.PersonType = 'SP'
AND st.CountryRegionCode
= 'US'
AND SalesYTD >
1000000;
The query retrieves a list of sales people in the U.S. who have over $1 million in sales. In
addition to retrieving the names of the sales staff, I retrieved the city, state, total sales, and
spatial location for each sales person. The spatial data is retrieved from the SpatialLocation
column, which is configured with the geography data type, one of the new SQL Server 2008 spatial
data types. Also, notice that I used the RTRIM function to trim the
StateProvinceCode column. Later on, you'll see why it is necessary to remove the trailing
spaces.
After you've created a report and set up the data source and dataset, you're ready to add a map
to your report. Note that while a map is often only one element among other elements in a report,
here I will focus only on adding the map. To learn about adding other elements to a report or for
details about setting up a data source or dataset, see the Report Builder 3.0 Help or check out my
previous articles on working with gauges and charts.
Continue to
part two
ADDING MAPS IN REPORT BUILDER 3.0
Part 1: Geospatial mapping
Part 2: Adding a polygon
layer
Part 3: Finalizing
maps
ABOUT THE AUTHOR
Robert Sheldon is a technical consultant and the author of numerous books, articles,
and training material related to Microsoft Windows, various relational database management systems,
and business intelligence design and implementation. You can find more information at www.rhsheldon.com.
Disclaimer:
Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation