Saturday, July 24, 2010

SQL Server Geospatial Part 3 – Visualizing Spatial Data in SSRS

In the previous post, we finished by calculating population density for the city of Chicago. Today we will continue to build on that same dataset and will look at a cool way to visualize our spatial data using SSRS in SQL Server 2008 R2. If you haven’t done so yet, run through the tutorials in posts one and two to bring your database up to speed. 

First of all, let’s look at an interesting way to join spatial datasets, as well as building a little on the knowledge from the last post about geography vs. geometry data. If you remember my comments about spatial reference systems, you’ll remember that the only way to truly measure distance is to use the geography type, which maps to three-dimensional space. However, you’ll also remember that the geography type supports far less functions that the geometry type. One of the convenient, and missing, functions from the geometry type is STCentroid() which returns a point at the center of mass of any shape. There’s a good explanation here.

Thankfully with some casting back and forth, we can find the center points of two geometry objects, and calculate distance between them in meters. The comments in the query below illustrate what’s happening. Of note is also the join between the two datasets, you’ll find yourself using this type of join over and over when working with spatial data, whether you’re calculating distances or overlaps, ranking overlaps to find primary geographical relationships between towns and zip codes, the list goes on.

select places.NAME
,referenceplace.name
,places.geom
,pops.POP_2008
/*
Find the distance between the centroid of the place and
Chicago's centroid.
Both need to be converted to geometry
to find the centroid and then this point
is converted
back to geography
*/
,dbo.MakeValidGeographyFromText
(places.geom.MakeValid().STCentroid().STAsText(),4326)
.STDistance(dbo.MakeValidGeographyFromText
(referenceplace.geom.MakeValid().STCentroid().STAsText(),4326))
as CentroidDistanceFromChicagoCentroid
/*
This finds the population density per square mile. See last
post for an
explanation. The large number represents the
number of square meters in a
square mile.
*/
,convert(int,pops.POP_2008
/ (dbo.MakeValidGeographyFromText
(places.geom.STAsText(),4326).STArea() /  2589988.11))
as PopDensity
from tl_2009_17_place as places
inner join ILPlacesCensusPops as pops
on pops.place = places.PLACEFP
/*
This syntax joins the data sets together on distance < 20000
meters to find towns
with a center less than 20 kilometers from
the center of Chicago. See centroid
logic above.
*/
join tl_2009_17_place as referenceplace
on dbo.MakeValidGeographyFromText
(places.geom.MakeValid().STCentroid().STAsText(),4326)
.STDistance(dbo.MakeValidGeographyFromText
(referenceplace.geom.MakeValid().STCentroid().STAsText(),4326))
< 20000
where referenceplace.Name  = 'Chicago'
and pops.STATENAME = 'Illinois'

 

You’ll see that we return a nice little dataset, with population, population density, and distance from Chicago for 49 communities, including Chicago itself.

image

Now let’s see what we can do with our little dataset. I am going to go ahead and assume that you have BIDS installed along with SQL Server 2008 R2, if not you can install the free evaluation from this site. Fire up Visual Studio, and select “File –> New –> Project”. Select “Report Server Project” and call your new project GeoExamples.

image

Add a new report through the solution explorer and call it ChicagoAreaPopulation.rdl.

image

image

Right-click “Data Sources” and select “Add Data Source”. Connect to your local GeoExamples database.

image

Now right-click the data source we just added, and select “Add Dataset”. Name it ChicagoAreaPopulation and paste in our query from the beginning of this post. It should look like the image below.

image

Now right click in the main report area and select “Insert –> Map”

image

Select “SQL Server spatial query” and click “Next.”

image

Select the dataset we just created and click “Next.”

image 

Now this SSRS report starts getting cool. Make sure the geom field is selected as the Spatial field, select “Add a Bing Maps layer” and choose “Aerial” as the type. Leave all other default options selected and click next.

image

On the next pane, select “Color Analytical Map” as the map type and go to the next screen.

image

We are going to add a new dataset using the same query. Really you should be writing different queries for the spatial data and the analytical data but for this example using the same query in both places will work. If you use the same dataset in both places it will only allow you to display aggregates of our analytical field (population density) so that won’t do. This is also a good time to point out that for any type of production environment you’ll want to materialize all the spatial calculations, running them at execution time is not recommended. Select “Add a dataset that includes fields that relate to the spatial data that you chose earlier” and hit “Next”.

image

Paste in our same query and hit “Next.”

image

Accept all the defaults on this page (after all, we’re using the same query twice) and hit “Next.”

image

Now, leave the theme “Generic”, leave the color rule “Green-Yellow-Red”, select #PopDensity as the visualization field, Select “Display labels” and choose #NAME (this second one, in all caps), and hit “Finish.”

image

Edit the map title to read “Chicago Area Population Density” and toy around with the font sizes to display more place names. This is the result! A pretty little map showing population density per square mile for towns within 20km of Chicago. All based on free data, and using technologies you already own.

image

There are many other ways to unlock the power of your spatial data, SSRS is just one of many outlets. Stay tuned for more in future posts!