Visualizing Spatial Data – Toggling map layers

Last time I showed you how to add a PushPin to your map control. But what if you want to toggle that (or any other) layer in your map control?

The reason I wanted to do this, is that I want to allow the users of my report to toggle different layers in the map control. That way, you can pre-build all the different layers you think your users need into a single report, and just let them toggle the layers themselves. That saves you a lot of time building a report for every single combination of layer-types.

Multi-layer map control
In order to keep it simple, I used the report from my previous post. Specifically, the version with the standard PushPins. And the only layer I want them to be able to toggle, is the PushPin layer on top of the basic map.

Creating a parameter
Because your want to present an easy solution to your users, I want to create a Boolean parameter that they can use to toggle the PushPin layer. To create this parameter, right-click on Parameters in Report Builder, and choose “Add Parameter”:

In the general options window, make sure you fill in a Parameter Name, a question for your users (so they understand what they need to do), and a Data Type (in this case a Boolean, that’s either True or False):

Because you don’t want to keep your users waiting if they open your report, you might want to give the Boolean a default value. You can do this by adding a default value to your Boolean:

By doing this, the Boolean is True by default. So the user doesn’t have to pick a value if he runs the report for the first time. This might come in handy if you need this as an extra feature in your report, and not a feature you need to configure before the report shows your data.

Now you need to configure your map layer to use the parameter you created to toggle it. Double-click on your map control, and once you see the Map Layers menu on the right, you can do this by right-clicking on “Layer Data”:

Now click on Visibility, and under Layer visibility, click the last option, and press the Fx button:

In the Expression window, fill in the following expression:

Now if you run the report, you’ll see the Boolean appear in the top bar of your report. By default, it’ll show the PushPins:

And if you change the Boolean to False and press “View Report”, you’ll see the may without PushPins:

With a little help from my friends…
This post isn’t just written by me, but also with the help of Valentino Vranken (Blog | @ValentinoV42. He helped me out on Twitter, because my expression didn’t work as expected. So Valentino: I really appreciated your help! Thank you! ;)

Visualizing Spatial Data – Show locations with a PushPin

After my last two posts (Creating a spatial report, Create an interactive report), I came across a feature I never saw before. Apparently if you add a point layer to your report, you get different layer-options in the list.

Starting with a single layer
To start off easy, you could use the same steps I used to create my first report. This is a single layer report with a full map of (in this case) the Netherlands.

The next step is adding a few pushpins. How about visualizing the three biggest cities in the Netherlands: Amsterdam (790.000 people), Rotterdam (616.000) and Den Haag (The Hague, 502.000).

Creating a Point layer
Before creating a point layer, you need to determine what data you want to present to your users. In my case, I chose the center of each city. This might not be the actual center, but I tried to get as close to the center as I could.

Now that you have your data, you can create the Point layer in your report. Start by double-clicking on your map control, and in the “Map Layers” window, click on “New layer wizard:

Now choose the bottom option “SQL Server spatial query”, and click next.

Choose the bottom option to add a new dataset to your report, and click next.

Click next to choose the datasource that’s already in your report, and click next.

Now click on the option “Edit as Text” in the query designer:

Type or paste your query in the editor, and press the Run button. Once you’ve done that, you’ll see the result in the bottom window:

In the next window, make sure you add a Point layer, and not a Polygon layer:

Now you will see the new layers I mentioned before:

For this report, choose the bottom one: Analytical Marker Map, and click next.

In the next window, select the dataset that contains your points, and click next.

In the window with options, make sure you select the right type of marker (in this case PushPin):

Once you’ve done that, click Finish. Now you see a double layer map with 3 PinPoint on your map:

Using a custom PinPoint
I can imagine that you might want to use your own PinPoint image on your report. This is possible, but remember this might screw up the readability of your report.

In the Map Layers menu, right click your point layer, and click Point Properties:

Now change your marker type from PinPoint to Image, and click Import:

Now if you select an image, the name of your image will show in the “Use this image” text box. If you want to, change the “Marker size”, so your logo will appear larger on the map. Now if you click OK, and run the report, you’ll see something like this:

And if you can’t resist, you can download this EPIC marker right here ;)

Visualizing Spatial Data – Create an interactive report

In my previous blog post I showed you how to create a report containing spatial data. But what if you want to give your users a little bit more information. How about a mouse-over tooltip, of what they’re actually looking at? Or maybe even a click-through to another report or website? Let’s take a look at how to achieve this.

I’m starting with the report that I created in my previous blog post. I’m not going to add data to the report, but instead change some options.

Mouseover tooltip
Because you’re working with a full map in your report, it might be hard to see what kind of location you’re looking at. So the easiest way to fix this, is to add labels to your map control. But if you have a lot of small polygons, you might end up with somethings that looks like this:

In the image above you see the province of South-Holland. In the red rectangle, you see the 2nd largest city of the Netherlands: Rotterdam. But this illustrates the issue, that if you have a lot of small polygons, you won’t be able to see the labels.

So a workaround for this, is to add a tooltip (a mouseover label) to your report. This will show the label you selected, if you (or the user of your report) can see what the polygon represents. So how can we do that?

Go to your Design window, and right-click on a polygon in your map control, and click on “Polygon Properties”:

In the next window, click on the “Fx” button next to the Tooltip textblock:

In this window, enter the following expression:

Now if you hover over your report, you’ll see the following tooltip:

Clickable polygons
If you want to spice things up even more, you could also add a click-event to your report. In my case, I wanted to add a click-event that showed the users the Wikipedia page of the city.

Start by right-clicking again on a polygon in your map control, and click “Polygon Properties”. Now go to the Action tab, and click the “Fx” button again:

Now fill in the following expression:

Now if you go to your report, you can click on a polygon and it will open up the Wikipedia page for that specific city.

Another option would be to open up a report via a click-event, to show your users more information from another report. Or maybe you want to redirect your users to a specific page on your intranet. Just imagine all the opportunities… ;)

Visualizing Spatial Data – Creating a spatial report

In the last few posts in the series, I showed you how to enrich your dataset with spatial data. Now you need to start thinking about how you want to visualize your data, so that (for example) your business analysts can start using the data. But how do you start with that?

For every report I create, I use Report Builder 3.0 myself. This tool is a bit smaller and more responsive than the Business Intelligence Development Studio (BIDS).

Create the basic report first
Starting to build your report starts with creating a blank report from the menu in Report Builder:

Once you’ve done that, you need to add a data source to your report:

Then add a map control to your report, by right-clicking on you report, and choose Map:

Double-click on the map control, and in the menu that appears on the right, choose “New layer wizard”:

Click the last option (SQL Server spatial query) and click next.

Now you’re asked if you would like to create a new dataset in you report. In this case, you can just click next.

The same counts for the next step, datasources. Just select the datasource you created earlier, and click next.

In the next menu, we can select the data we want to use. In this case, I just want to select a city name and a spatial object. You can write this query in your SQL Server Management Studio (SSMS), or in this window. I wrote it in SSMS, and I want to copy it to my report. If you want to do the same, you need to click the option “Edit as Text”:

Just paste your query into the editor, and press the run button. Report Builder will retrieve your data, and shows you the results below:

Click next, and select the options you like in the next few menus. I’ll stick with the basic options. I didn’t change anything in the first menu, and in the next chose “Basic Map”:

Click next, click Finish. There you go, you just created your very first Spatial Report! :)

Enriching your dataset – What are your technical options?

In my previous post I tried to explain how to enrich your dataset, so you can start using spatial data. You’ve seen that there are free options, or you can buy a bunch of files with the information you need.

This time I’ll try to explain the technical option you have. The options all contain the Google Geocoding API, because this is free, and it’s fairly accurate. But this will also work for the files you buy from (for example) GfK GeoMarketing, which I did business with once.

Write your own application
One of the easiest an quickest options for me was to write an application to fetch my data from the Google API. I’m not a die-hard programmer, but I know my way around C# as far as I need to. So with a little help from my colleagues, I managed to write a Windows Forms application that calls the Geocoding API. The results of the API call are either just shown on screen (in a GridView), or exported to Excel (using LinqToExcel and ExcelExporter).

Another option I intend to build in, is the export from and to a SQL Server database. But because this is a project I work on in my own time, this could take a couple of weeks.

One of the other options I’ve found, is a CLR that calls the API. The CLR you write, is basically a .NET application that you load into SQL Server. It’s more complicated than that, but to keep this story moving on, I’ll leave it at that.

One of the many downsides of using a CLR, is your local DBA. Every DBA I’ve encountered in my life told me: “Don’t use a CLR, EVER!!! Because that’s the biggest security hole you can open up on a SQL Server”. To be completely honest, I’m not that familiar with CLR’s, but I guess it isn’t as easy as that. There might be some pros and cons regarding that…

SSIS Package
In my quest to find all possible options, I found this great article by Donabel Santos (Blog | @sqlbelle). In her article she describes how you can create an SSIS package that fetches a Lat/Long for every record in the dataset you use as input.

Talking about this with Koen Verbeeck (Blog | @Ko_Ver) on Twitter made me realize that for normal businesses, this might be the best option. In every company I know, there’s always a server running SQL Server Integration Services that you can use to execute such a package.

So, what’s your choice?
Looking at the options above, I’m guessing the option you choose depends on the company you work for. I guess that smaller companies would choose the SSIS package over building a tool. But if your company has a development department with a bunch of software developers, writing your own tool might be a better option. In that case writing your own tool gives you more flexibility, because not only your DBA can debug an error in an SSIS package, but every developer can debug the tool they’ve written.

If you’ve got some .NET/C# knowledge, and you have the time to dive into CLR’s, that might be your best option. CLR’s are loaded into SQL Server, and are (as far as I can tell) blazing fast. So if you’re looking for performance, and don’t want external tools or packages to fetch your data, go for it!

Starting with Spatial Data – Enriching your dataset

The biggest issue when you start working with spatial data, is asking yourself the question: “What do I want to visualize?”. Do you want to visualize the areas your clients come from? Or do you want to calculate in which areas your new potential clients live? The next hurdle to take, is thinking about how to enrich the data you have now.

The easy way out
Almost every company has a accessible database with client information. The easy way out is to download a free spatial data set (like described in an earlier post), and visualize your data by joining your data set on the downloaded data set, based on country-, province-, or city name. This way, you don’t alter your data, but you’re still able to visualize your data.

Enriching your data
The way to go (in my opinion), is to permanently enrich your data. In that case, you’re more flexible in visualizing your data. Another advantage is that you can always group your data in the same way as mentioned above. If you want to group it by country, province or city, you can still do it. But remember: you can always group your data, but mining your data is difficult without accurate coordinates. And there is a free option for enriching your data, if you don’t want to spend a lot of money and time on it.

Last year, I spend a lot of time working with spatial data. One of the biggest brainteasers was how to enrich our data set, without actually disrupting the normal running processes. You don’t want to change your underlying data set, and then find out you need to rewrite all your software.

No seriously, it’s free…
One of the options I thought of, was converting a postal code, or zip code for my American friends (Seriously USA?! Do you have a different word for EVERYTHING?! :)). The conversion can’t be done by number crunching, but needs to be done by looking it up. One of the options is to look up every postal code by hand. But if you have a few million addresses in your database, that’s not really an option.

One of the options I came across was the free Google Geocoding API. This allows you to translate a postal code into a Latitude and Longitude. And these coordinates can be plotted on a map.

And if you have a Latitude and Longitude, you can use Reverse Geocoding to translate your coordinates into a postal code.

A quick example of this, is a tool I’ve written. It translates a postal code into Lat/Long:

Or translates a Lat/Long into postal code, using the Reverse GeoCoding:

I know this isn’t a pretty tool yet, but I’m still working on it. Until now, it was only a trial to see if the Google API returns the data I need. This tool is also still based on the 2.0 API, which is deprecated since March 8, 2010. It will still work until March 8, 2013, but I need to rebuild it soon.

The next step of course, is to expand the tool to allow the user to translate a whole list of postal codes, all at once. You don’t want to copy-paste the millions of postal codes by hand into the tool, and copy-paste the result back into an Excel workbook or SQL Server table.

But since I’m not a .NET developer, this could take a while. If you’re a developer, and interested in expanding and developing the postal code finder, contact me. Then we can make it a publicly available tool.

Looking at all of this, you’ll see that it’s possible to enrich your data for free, and without a lot of work. Okay, you still need to figure out if your postal code information is correct. Or let the API respond back with the message “G_GEO_UNKNOWN_ADDRESS”, which tells you that the address might be incorrect. Another limit is the number of API calls: 15.000 calls per day. But then again, it’s free, and you can’t have it all…

Geometry vs Geography

In the last few months I often received the question: “What datatype do I need to use? Geometry or Geography?”. The answer I gave them, is the most used answer in the IT world: “It depends…”. Because the answer isn’t as easy as you might think…

The storage of both Geometry and Geography is the same. Even though they’re functionality is different, the system datatypes are the same:

FROM sys.types
WHERE system_type_id = 240

The content you will find in a Geometry or Geography column is binary. But by using a number of different methods, you can easily convert this to readable text.

Looking at performance, it could be an easy choice: Geometry will win this. The basic polygons aren’t that different. The extended methods on the other hand, do make a big difference! This is a whole different world, so I’ll write an article about this in the upcoming weeks.

Bob Beauchemin (Blog | @bobbeauch) also gave a session about this, and he had some great examples to illustrate this. You might want to watch his Spatial Data sessions!

If you’re looking for the biggest difference between the two datatypes, you’ll see it’s the functionality. A Geometry object is just a 2D, flat polygon. This means, it doesn’t matter if you take a country on top of the earth (for example Canada, which is “curved”), or a country close to the Equator (for example Brazil, which is “flat”).

A Geography object on the other hand, is a 3D (or even 4D) polygon, that has the same curve as the shape of the earth. That means that the difference between 2 point isn’t calculated in a straight line, but it’s need to consider the curvature of the earth.

A great example of this concept, is shown by Morten Nielsen (Blog | @dotMorten) on his blog. He made a tool (Shape2Sql, downloadable from his his blog), that can be used to import shape files into SQL Server.

He explains the difference between Geometry and Geography with 2 pictures, where he draws a straight line between Europe and the USA:



As you can see, the straight line is actually a straight line if you use Geometry (planar system). But if you use Geography (spherical system), the line isn’t straight but curved. This could become an issue if you want to know the distance between Berlin and Los Angeles, and you use Geometry data to calculate this. If you use that to calculate the amount of fuel for your plane, you might end up swimming the last part of your journey!

Popping The big question
The big question you need to ask yourself is: “want do I want to do with this?”. If you want to use it just for visualization purposed, you might want to stick with Geometry data. It’s accurate, fast, and doesn’t require difficult calculations. But if you need to calculate distances across the globe, or data that represents the earth, then Geography is the way to go.


Get every new post delivered to your Inbox.

Join 50 other followers