Multi-touch in Silverlight 3: Part 1

 

1. Introduction

1.1 Introduction

One of the cool features in Silverlight 3 is the Multi-touch capabilities.   This feature allows Silverlight developers to create fun and interactive applications that can be used with standard inputs, such as mouse and keyboard, as well as with touch-enabled devices. 

If you have seen Minority Report, you are already aware of the gesture-based technology.  The user can interact with the device to drag objects around, flick to navigate, and pinch to adjust the size of an object.  These concepts have become a standard feature on a variety of devices, including Microsoft Surface, Windows 7, and mobile phones.

Silverlight 3 provides the  fundamentals of receiving touch events from the hardware to the application.  By default, the touch events are fired from Windows 7 to the browser (IE / Firefox).  If the application is not processing the touch events, these events are registered as standard mouse events.

Here are two sample applications that utilize Multi-touch concepts.  You will need a touch-enabled device and Windows 7 to get the full experience.

3D SlideView | SlideView

In this two-part blog series, I’ll show you the process that I went through in developing a Silverlight class library with Multi-touch gestures and events.  This process was used in SharePoint Silverview to add Silverlight / Windows 7 touch functionality in a SharePoint environment.  You can find the complete source code at http://code.msdn.microsoft.com/ssv.

1.2 Online Research

Jesse Bishop, Program Manager on the Microsoft Silverlight team, wrote an amazing article about Multi-touch Gesture recognition in Silverlight 3.  The article illustrates on how to register touch events and process standard gestures on individual elements.  The primary concept is to have a single entity that reads the incoming events and determine which element that is being accessed.  After reading Jesse’s article, I got inspired to extend his concept with a variety of gestures and events that can be used in a standard Silverlight application.

1.3 Hardware

Before you can develop or run Silverlight application with Multi-touch, you need the right equipment.  I have been developing on the Dell SX2210T and the HP TouchSmart IQ586.  The HP TouchSmart TX2 is also a good choice.  One interesting thing that I learned was the HP TouchSmart IQ586 only had support for a single touch point.  There is an updated driver to ensure that the monitor utilizes two touch points.  You can check for this by viewing the Pen and Touch information under System Information.  It is ideal to have 2 Touch Points, if you plan to support two-finger gestures such as Scale and Rotate. With the recent release of Windows 7, there may be drivers for existing hardware that will need to be updated. 

1.4 Touch 101

The Touch framework has two primary concept: the TouchPoint object and the FrameReported event.

The TouchPoint object stores data related to a single touch point.  This data is passed from the hardware as the user interacts with the touch device.  The number of touch points vary on the supported hardware.   The first touch point that is fired to the application is called the primary touch point.  The object has two important properties: Position and Action.  The Action property stores the current action state, such as Up, Down, and Move.  The Up and Down actions are similar to the mouse’s MouseLeftButtonUp and MouseLeftButonDown events.  The Move action is the transition action that is constantly called until the user releases the Touch Point, which also invokes the Up action.

The FrameReported event is an application-wide event that sends data from the hardware to the application.  One thing to note is that this event is global to the application rather than an individual element.  The application doesn’t directly know the element that the event is processing.  It is possible to retrieve this information using the VisualTreeHelper.GetPointsUnderCoordinates method.  The TouchFrameEventArgs argument has methods of getting information about touch points.  You can get the current touch points by calling the GetPrimaryTouchPoint and GetTouchPoint methods.

2. Basic Implementation

Demo | Source Code

2.1 Creating the MultiTouch Class Library

Now, let’s start by creating a class library.  The library will be useful for  rapidly developing touch applications.  We will only need two objects for the library: TouchElement control and TouchProcessor (explained in Jesse’s article).

In Visual Studio 2008 (or Visual Studio 2010 Beta 2), create a new Silverlight Application.  Right-click on the Solution in the Solution Explorer and select Add New Project.  In the Project Dialog box, select Silverlight Class Library and name it TouchLibrary.  Remove the default Class1.cs file. 

2.2 TouchElement

We will define the TouchElement control as a content control to allow you to place any element, such as an image, movie clip, or UIElement, into the control.   This is not a requirement for deploying Touch applications, but rather a personal choice for development.  One useful scenario is having a couple of TouchElement controls with images to allow users to drag and scale images around the user interface.

Right-click on the TouchLibrary and select Add New Item.  In the Add New Item Dialog box, select User Control and name it TouchElement.  Replace the TouchElement.xaml content with the following:

<ContentControl x:Class="TouchLibrary.TouchElement"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    RenderTransformOrigin="0.5,0.5"           
    >
    <ContentControl.RenderTransform>
        <TransformGroup>
            <ScaleTransform x:Name="scale" />
            <TranslateTransform x:Name="translate" />
        </TransformGroup>
    </ContentControl.RenderTransform>
    <ContentControl.Template>
        <ControlTemplate>
            <ContentPresenter Margin="0" Content="{TemplateBinding Content}" ContentTemplate="{TemplateBinding ContentTemplate}">
            </ContentPresenter>
        </ControlTemplate>
    </ContentControl.Template>
</ContentControl>

Note that this is a Content Control rather than a UserControl.  We have added TranslateTransform and ScaleTransform transforms to render the control’s transformation based on the touch events.

Now it’s time for some code action.  In Jesse Bishop’s article, he implements the concept using ITouchElement as an interface that describes the supported gestures and ManipulateElement as a UserControl that implements the ITouchElement interface.  I decided to merge the code from his elements into the TouchElement content control.   I highly recommend checking out his article if you want to learn the details of the concept.

We will implement the Translate and Scale gestures.  The Translate gesture occurs when the user drags the element from one position to another.  The Scale gesture occurs when the user pinch and scales using two touch points.  There may be scenarios when the developer wants to create their own custom translate and scale events.  For this reason, we will add support for default transforms as well as add custom events that will send information back to the application.

The TransformEventArgs class stores basic data for both Translate and Scale events.

public class TransformEventArgs : EventArgs
{
    public TouchElement.TransformType Type { get; set; }
    public Point Translate { get; set; }
    public double Scale { get; set; }

    public TransformEventArgs(TouchElement.TransformType type, Point translate)
    {
        Type = type;
        Translate = translate;
        Scale = 1;
    }

    public TransformEventArgs(TouchElement.TransformType type, double scale)
    {
        Type = type;
        Scale = scale;
        Translate = new Point(0, 0);
    }
}

The TransformHandler delegate is used for both events, with the TransformEventArgs object as the second parameter.

public delegate void TransformHandler(object sender, TransformEventArgs e);
public event TransformHandler ScaleChanged;
public event TransformHandler TranslateChanged;

We will add three properties to the TouchElement class.  The TouchEnabled property will be used in the TouchProcessor class to check which TouchElement objects need to be processed.  The TouchDrag and TouchScale boolean properties enable the default touch behaviors for drag (translate) and scale events.  By default, the properties are set to false to allow the user to create their own events.

public bool TouchEnabled
{
    get { return (bool)GetValue(TouchEnabledProperty); }
    set { SetValue(TouchEnabledProperty, value); }
}

public bool TouchDrag
{
    get { return (bool)GetValue(TouchDragProperty); }
    set
    {
        SetValue(TouchDragProperty, value);

        if (value)
            this.TranslateChanged += new TransformHandler(TouchElement_TranslateChanged);
        else
            this.TranslateChanged -= new TransformHandler(TouchElement_TranslateChanged);
    }
}

public bool TouchScale
{
    get { return (bool)GetValue(TouchScaleProperty); }
    set
    {
        SetValue(TouchScaleProperty, value);

        if (value)
            this.ScaleChanged += new TransformHandler(TouchElement_ScaleChanged);
        else
            this.ScaleChanged -= new TransformHandler(TouchElement_ScaleChanged);
    }
}

public static readonly DependencyProperty TouchEnabledProperty =
    DependencyProperty.Register("TouchEnabled", typeof(bool), typeof(TouchElement), new PropertyMetadata(true));

public static readonly DependencyProperty TouchDragProperty =
    DependencyProperty.Register("TouchDrag", typeof(bool), typeof(TouchElement), new PropertyMetadata(false));

public static readonly DependencyProperty TouchScaleProperty =
    DependencyProperty.Register("TouchScale", typeof(bool), typeof(TouchElement), new PropertyMetadata(false));

The TouchDrag and TouchScale properties connect the default event handlers, which validates the input value and directly updates TouchElement’s transform values.

void TouchElement_ScaleChanged(object sender, TransformEventArgs e)
{
    double scale = e.Scale;

    double s = this.scale.ScaleX + scale;
    double MIN_SCALE = 0.25;
    double MAX_SCALE = 5.0;

    if (s > MIN_SCALE && s < MAX_SCALE)
    {
        this.scale.ScaleX += scale;
        this.scale.ScaleY += scale;
    }
    else if (s < MIN_SCALE)
    {
        this.scale.ScaleX = MIN_SCALE;
        this.scale.ScaleY = MIN_SCALE;
    }
    else if (s > MAX_SCALE)
    {
        this.scale.ScaleX = MAX_SCALE;
        this.scale.ScaleY = MAX_SCALE;
    }
}

void TouchElement_TranslateChanged(object sender, TransformEventArgs e)
{
    this.translate.X += e.Translate.X;
    this.translate.Y += e.Translate.Y;
}

The TranslateElement and ScaleElement methods calculate the appropriate transforms from the positions based on the touch events.  After the value is calculated, the event is fired with the TransformEventArgs as a parameter.  This will process the default transformation as well as any custom work that is connected to the event.

private void TranslateElement(Point oldPosition, Point newPosition)
{
    double xDelta = newPosition.X - oldPosition.X;
    double yDelta = newPosition.Y - oldPosition.Y;

    if (yDelta != 0 || xDelta != 0)
        RaiseTranslate(new TransformEventArgs(TransformType.TRANSLATE, new Point(xDelta, yDelta)));
}

private void ScaleElement(Point primaryPosition, Point oldPosition, Point newPosition)
{
    double prevLength = GetDistance(primaryPosition, oldPosition);
    double newLength = GetDistance(primaryPosition, newPosition);

    if (prevLength == double.NaN || newLength == double.NaN)
        return;

    double scale = (newLength - prevLength) / newLength;

    if (scale != 0)
        RaiseScale(new TransformEventArgs(TransformType.SCALE, scale));
}

The TouchProcessor class calls the TouchPointReported method to process the touch event based on the TouchPoint’s Action property.  On the Down action, we collect information on the TouchPoint.  If it is the initial touch point, then the position is later used for distance calculation.  In the next blog post, I will go into additional functionality that can be added in the TouchDown method, such as a start time for timing touch event for other gestures.  On the Touch Up action, we free up the current point.  The TouchUp method can also be used to process any gestures, which will be detailed in the next blog post.  The Touch Move is where the fun’s at.  After the Down action occurs, the Move action constantly runs until the Up action is called.  If there is only a single touch point, the TranslateElement method is called.  The ScaleElement method is called if there are multiple points.  In Jesse’s article, he also has Rotate functionality, which is processed with multiple points.

public void TouchPointReported(TouchPoint touchPoint)
{
    switch (touchPoint.Action)
    {
        case TouchAction.Down:
            TouchDown(touchPoint.TouchDevice.Id, touchPoint);
            break;
        case TouchAction.Move:
            TouchMove(touchPoint.TouchDevice.Id, touchPoint);
            break;
        case TouchAction.Up:
            TouchUp(touchPoint.TouchDevice.Id, touchPoint);
            break;
    };
}

private void TouchDown(int id, TouchPoint touchPoint)
{
    _points.Add(id, touchPoint.Position);

    if (_points.Count == 1)
    {
        _startPoint = touchPoint.Position;
        _primaryId = id;
    }
}

private void TouchMove(int id, TouchPoint touchPoint)
{
    Point oldPoint = _points[id];
    Point newPoint = touchPoint.Position;

    if ((_points.Count == 1 || id == _primaryId))
        TranslateElement(oldPoint, newPoint);
    else
        ScaleElement(_points[_primaryId], oldPoint, newPoint);

    _points[id] = newPoint;
}

private void TouchUp(int id, TouchPoint touchPoint)
{
    Point oldPoint = _points[id];
    Point newPoint = touchPoint.Position;
    _points.Remove(id);
}

2.3 TouchProcessor

The TouchProcessor class manages the global touch functionality by connecting the Touch’s FrameReported event.  Jesse goes into detail about the TouchProcessor class in his article.  Add the following class to the Class Library.  The primary difference in this version is the RootElement property, which is used to connect to the current application’s visual root.  This property is required for the VisualTreeHelper.FindElementsInHostCoordinates method.

public class TouchProcessor
{
    #region Data Members
    private static Dictionary<int,TouchElement> touchControllers = new Dictionary<int, TouchElement>();
    private static bool _istouchEnabled = false;
    #endregion

    #region Properties
    public static UIElement RootElement
    {
        get;
        set;
    }

    public static bool IsTouchEnabled
    {
        get { return _istouchEnabled; }

        set
        {
            if (value && !_istouchEnabled)
                Touch.FrameReported += new TouchFrameEventHandler(Touch_FrameReported);
            else if (!value && _istouchEnabled)
            {
                Touch.FrameReported -= new TouchFrameEventHandler(Touch_FrameReported);
                touchControllers.Clear();
            }

            _istouchEnabled = value;
        }
    }
    #endregion

    #region Constructor
    public TouchProcessor()
    { }
    #endregion

    #region Events
    private static void Touch_FrameReported(object sender, TouchFrameEventArgs e)
    {
        TouchPointCollection touchPoints = e.GetTouchPoints(null);

        foreach (TouchPoint touchPoint in touchPoints)
        {
            int touchDeviceId = touchPoint.TouchDevice.Id;

            switch (touchPoint.Action)
            {
                case TouchAction.Down:
                    TouchElement hitElement = GetTouchElement(touchPoint.Position);

                    if (hitElement != null)
                    {
                        if (!touchControllers.ContainsKey(touchDeviceId))
                            touchControllers.Add(touchDeviceId, hitElement);

                        // Prevent touch events from being promoted to mouse events (only if it is over a TouchElement)
                        TouchPoint primaryTouchPoint = e.GetPrimaryTouchPoint(null);
                        if (primaryTouchPoint != null && primaryTouchPoint.Action == TouchAction.Down)
                            e.SuspendMousePromotionUntilTouchUp();

                        ProcessTouch(touchPoint);
                    }

                    break;
                case TouchAction.Move:
                    ProcessTouch(touchPoint);
                    break;
                case TouchAction.Up:
                    ProcessTouch(touchPoint);

                    touchControllers.Remove(touchDeviceId);
                    break;
            };
        }
    }
    #endregion

    #region Touch Process Events
    private static void ProcessTouch(TouchPoint touchPoint)
    {
        TouchElement controller;
        touchControllers.TryGetValue(touchPoint.TouchDevice.Id, out controller);

        if (controller != null)
            controller.TouchPointReported(touchPoint);
    }

    private static TouchElement GetTouchElement(Point position)
    {
        foreach (UIElement element in VisualTreeHelper.FindElementsInHostCoordinates(position, RootElement))
        {
            if (element is TouchElement)
                return (TouchElement)element;

            // immediately-hit element wasn't an TouchElement, so walk up the parent tree
            for (UIElement parent = VisualTreeHelper.GetParent(element) as UIElement; parent != null; parent = VisualTreeHelper.GetParent(parent) as UIElement)
            {
                if (parent is TouchElement)
                    return (TouchElement)parent;
            }
        }

        // no TouchElement found
        return null;
    }
    #endregion
}

3. Test the TouchLibrary

In the Silverlight Project, add TouchLibrary to the project’s References.  This will allow you to add the custom touch functionality to your project. 

In MainPage.xaml, add the following namespace to connect to the new library.

xmlns:touch="clr-namespace:TouchLibrary;assembly=TouchLibrary"             

Add the following to MainPage.xaml to create three TouchElements (two with color rectangles and one with an image).  The image source has the project name pre-pended to ensure that it loads the image from the correct project.  The TouchDrag and TouchScale properties are enabled to allow you to manipulate the items.

<Grid Background="Black">
    <touch:TouchElement TouchDrag="True" TouchScale="True" HorizontalAlignment="Left">
        <Rectangle Fill="Red" Width="200" Height="200" />
    </touch:TouchElement>

    <touch:TouchElement TouchDrag="True" TouchScale="True" HorizontalAlignment="Right">
        <Rectangle Fill="Blue" Width="200" Height="200" />
    </touch:TouchElement>

    <touch:TouchElement TouchDrag="True" TouchScale="True" HorizontalAlignment="Center" VerticalAlignment="Bottom">
        <Image Source="/TouchApp;component/Rocks.jpg"  Width="200" Height="200" />
    </touch:TouchElement>
</Grid>

In MainPage.xaml.cs, add the following code to initialize the TouchProcessor object.

TouchProcessor.RootElement = this;
TouchProcessor.IsTouchEnabled = true;

One thing to note is that the Touch functionality doesn’t work in Windowless mode.  By default, Windowless mode is set to false and it is a feature that should be used with caution.  For more information on the limitations of Windowless mode, check out this article.

Compile and run the application.  As mentioned before, you will need the required hardware to recognized the Touch events.  You can drag the three items across the screen as well as scale them to different sizes.  The item is also brought to the front of the screen after release the touch point.

The demo of this version can be found here. 

4. Wrapping Up

Thanks for checking out this article.  Stay tuned for my next blog post, where I introduce more customization for the TouchLibrary, including Gesture support, parented selection, and touch events in a ChildWindow.

http://www.silverlighttoys.com/Articles.aspx?Article=SilverlightMultiTouch

Data-Tier Applications in SQL Server 2008 R2

In a previous post, I discussed some of the new multi-server management capabilities in SQL Server 2008 R2. One of the new features I conspicuously side-stepped covering in that post is the concept of a data-tier application – and that’s what I want to describe in this post.

Data-tier applications provide a useful way to encapsulate all of the logical and physical components of an application that need to be deployed and managed as a unit on a SQL Server instance. For example, consider a typical business application. It probably consists of a number of tiers, including a presentation tier (which might be a Windows Form application or an ASP.NET Web application), a middle-tier (for example a library of .NET assemblies that provide objects to manage the business logic of the application), and a data-tier. The data-tier consists primarily of a logical database (and all the schemas, tables, views, and so on it contains) but it also includes server-level objects (such as any logins that the middle-tier uses to connect to the database server) and the physical database and log files used to store the database.

In the past, deploying or migrating the data-tier of an application has involved examining the database to find its server-level dependencies and physical storage properties, moving the database from its test/staging server to the production server (via backup and restore, SSIS, or a Transact-SQL script to recreate the database schema and data – taking into account any differences in physical storage media), and creating a script to recreate any server-level objects used by the database.

In SQL Server 2008 R2, this task has been simplified through the concept of data-tier applications. Software developers using Visual Studio 2010 will be able to create data-tier applications that encapsulate the entire data tier, or alternatively you can use new wizards in SQL Server Management Studio to create a data-tier application from an existing database, and deploy a data-tier application to a new database.

To create a data-tier application from an existing database, right-click the database you want to package and start the data-tier extraction wizard as shown in the following screenshot.

This opens the following wizard screen:

The first step is to set the properties of the data-tier application (note that the wizard uses the abbreviation “DAC” – technically, this stands for “Data Tier Application Component”, which you can think of as a unit of deployment, or a deployable package for a data-tier application. The term “data-tier application” is usually taken to mean a deployed instance of a DAC.

As well as standard properties such as a name, version, and description for your data-tier application, you specify the file location where the DAC package should be created.

The wizard then examines the database and its dependencies, and lists the objects that will included in the DAC. In the November CTP, not all database objects are supported in DACs – for example, you can’t include columns with spatial data types such as geometry or geography. The list of supported objects will no doubt expand over time. In this example, the wizard has identified the database objects included in the database, and also the users and associated logins that are required.

Finally, the wizard builds the package for the DAC. The package itself is a single file with the extension .dacpac, as shown here:

This file is actually a zip archive that contains a number of XML files describing the components of the DAC. If you append a.zip extension to the filename, you can examine these files as shown here:

To deploy the data-tier application defined in the DAC, right-click the server you want to deploy it to and click Deploy Data-tier Application:

This starts another wizard, as shown here:

The first step is to select the DAC package file you want to deploy:

Then you can change the database name and file locations if desired.

The wizard summarizes the settings, …

 

…,and then deploys the data-tier application to the server.

You can then use SQL Server Management Studio to confirm that the database and any dependent objects has been deployed. In this case, you can see that the MyAppLogin login has been recreated on the target server along with the database.

This ability to treat the entire data-tier as a single, encapsulated package should simplify database application deployment and management significantly.

Multi-Server Management with SQL Server 2008 R2

A key challenge for many medium to large businesses is the management of multiple database server instances across the organization. SQL Server has always had a pretty good story with regards to multi-server management through automated multi-server jobs, event forwarding, and the ability to manage multiple instances from a single administrative console. In SQL Server 2008, Microsoft introduced a new solution called Data Collector for gathering key server performance data and centralizing it in a management data warehouse; and in SQL Server 2008 R2, this technology underpins a new way to proactively manage server resources across the enterprise.

With SQL Server 2008 R2, database administrators can define a central utility control point (UCP) and then enroll SQL Server instances from across the organization to create a single, central dashboard view of server resource utilization based on policy settings that determine whether a particular resource is being over, under, or well utilized. So for example, a database administrator in an organization with multiple database servers can see at a glance whether or not overall storage and CPU resources across the entire organization are being utilized appropriately, and can drill-down into specific SQL Server instances where over or under utilization is occurring to identify where more resources are required (or where there is spare capacity).

Sounds pretty powerful, right? So you’d expect it to be complicated to set up and configure. However, as I hope to show in this article, it’s actually pretty straightforward. In SQL Server Management Studio, there’s a new tab named Utility Explorer, and a Getting Started window that includes shortcuts to wizards that you can use to set up a UCP and enroll additional server instances.

Clicking the Create a Utility Control Point link starts the following wizard:

The first step is to specify the SQL Server instance that you want to designate as a UCP. This server instance will host the central system management data warehouse where the resource utilization and health data will be stored.

Next you need to specify the account that will be used to run the data collection process. This must be a domain account rather than a built-in system account (you can specify the account that the SQL Server Agent runs as, but again this must be a domain account).

Now the wizard runs a number of verification checks as shown here:

Assuming all of the verification checks succeed, you’re now ready to create the UCP.

The wizard finally performs the tasks that are required to set up the UCP and create the management data warehouse.

After you’ve created the UCP, you can view the Utility Control Content window to see the overall health of all enrolled SQL Server instances. At this point, the only enrolled instance is the UCP instance itself, and unless you’ve waited for a considerable amount of time, there will be no data available. However, you can at least see the dashboard view and note that it shows the resource utilization levels for all managed instances and data-tier applications (another new concept in SQL Server 2008 R2 – think of them as the unit of deployment for a database application, including the database itself plus any server-level resources, such as logins, that it depends on).

To enroll a SQL Server instance, you can go back to the Getting Started window and click Enroll Instances of SQL Server with a UCP. This starts the following wizard:

As before, the first step is the specify the instance you want to enroll. I’ve enrolled a named instance on the same physical server (actually, it’s a virtual server but that’s not really important!), but you can of course enroll any instance of SQL Server 2008 R2 in your organization (It’s quite likely that other versions of SQL Server will be supported in the final release, but in the November CTP only SQL Server 2008 R2 is supported).

As before, the wizard performs a number of validation checks.

Then you’re ready to enroll the instance.

The wizard performs the necessary tasks, including setting up the collection set on the target instance.

When you’ve enrolled all of the instances you want to manage, you can view the overall database server resource health from a single dashboard.

In this case, I have enrolled two server instances (the UCP itself plus one other instance) and I’ve deliberately filled a test database. Additionally, the virtual machine on which I installed these instances has a small amount of available disk space. As a result, you can see that there is some over-utilization of database files and storage volumes in my “datacenter”. To troubleshoot this overutilization, and find the source of the problem, I can click the Managed Instances node in the Utility Explorer window and select any instances that show over (or under) utilization to get a more detailed view.

Of course, your definition of “over” or “under” utilized might differ from mine (or Microsoft’s!), you can configure the thresholds for the policies that are used to monitor resource utilization , along with how often the data is sampled and how many policy violations must occur in a specified period before the resource is reported as over/under utilized.

These policy settings are global, and therefore apply to all managed instances. You can set individual policy settings to override the global polices for specific instances, though that does add to the administrative workload and should probably be considered the exception rather than the rule.

My experiment with utility control point-based multi-server management was conducted with the November community technology preview (CTP), and I did encounter the odd problem with collector sets failing to upload data. However, assuming these kinks are ironed out in the final release (or were caused by some basic configuration error of my own!), this looks to be the natural evolution of the data collector that was introduced in SQL Server 2008, and should ease the administrative workload for many database administrators.

del.icio.us Tags: SQL Server 2008,SQL Server 2008 R2,Utility Control Point

Further Adventures in Spatial Data with SQL Server 2008 R2

Wow! Doesn’t time fly? In November last year I posted the first in a series of blog articles about spatial data in SQL Server 2008. Now here we are over a year later, and I’m working with the November CTP of SQL Server 2008 R2. R2 brings a wealth of enhancements and new features – particularly in the areas of multi-server manageability, data warehouse scalability, and self-service business intelligence. Among the new features that aren’t perhaps getting as much of the spotlight as they deserve, is the newly added support for including maps containing spatial data in SQL Server Reporting Services reports. This enables organizations that have taken advantage of the spatial data support in SQL Server 2008 to visualize that data in reports.

So, let’s take a look at a simple example of how you might create a report that includes spatial data in a map. I’ll base this example on the same Beanie Tracker application I created in the previous examples. To refresh your memory, this application tracks the voyages of a small stuffed bear named Beanie by storing photographs and geo-location data in a SQL Server 2008 database. You can download the script and supporting files you need to create and populate the database from here. The database includes the following two tables:

-- Create a table for photo records
CREATE TABLE Photos
([PhotoID] int IDENTITY PRIMARY KEY,
[Description] nvarchar(200),
[Photo] varbinary(max),
[Location] geography)
GO

-- Create a table to hold country data
CREATE TABLE Countries
(CountryID INT IDENTITY PRIMARY KEY,
CountryName nvarchar(255),
CountryShape geography)
GO

The data in the Photos table includes a Location field that stores the lat/long position where the photograph was taken as a geography point. The Countries table includes a CountryShape field that stores the outline of each country as a geography polygon. This enables me to use the following Transact-SQL query to retrieve the name, country shape, and number of times Beanie has had his photograph taken in each country:

SELECT CountryName,
CountryShape,
(SELECT COUNT(*)
FROM Photos p
WHERE (Location.STIntersects(c.CountryShape) = 1))
AS Visits
FROM Countries c

With the sample data in the database, this query produces the following results:

CountryName	CountryShape	Visits
France	0xE6100000 … (geography data in binary format)	1
Egypt	0xE6100000 … (geography data in binary format)	2
Kenya	0xE6100000 … (geography data in binary format)	1
Italy	0xE6100000 … (geography data in binary format)	2
United States of America	0xE6100000 … (geography data in binary format)	7
United Kingdom	0xE6100000 … (geography data in binary format)	2

To display the results of this query graphically on a map, you can use SQL Server Business intelligence Development Studio or the new Report Builder 3.0 application that ships with SQL Server 2008 R2 Reporting Services. I’ll use Report Builder 3.0, which you can install by using Internet Explorer to browse to the Report Manager interface for the SQL Server 2008 R2 Reporting Services instance where you want to create the report (typically http://<servername>/reports) and clicking the Report Builder button.

When you first start Report Builder 3.0, the new report or dataset page is displayed as shown below (if not, you can start it by clicking New on the Report Builder’s main menu).

This page includes an option for the Map Wizard, which provides an easy way to create a report that includes geographic data. To start the wizard, select the Map Wizard option and click Create. This opens the following page:

SQL Server 2008 R2 Reporting Services comes with a pre-populated gallery of maps that you can use in your reports. Alternatively, you can import an Environmental Systems Research Institute (ESRI) shapefile, or you can so what I’m doing and use a query that returns spatial data from a SQL Server 2008 database.

After selecting SQL Server spatial query and clicking Next, you can choose an existing dataset or select the option to create a new one. Since I don’t have an existing dataset, I’ll select the option to Add a new dataset with SQL Server spatial data and click Next, and then create a new data source as shown here:

On the next screen of the wizard, you can choose an existing table, view, or stored procedure as the source of your data, or you can click Edit as Text to enter your own Transact-SQL query as I’ve done here:

The next page enables you to select the spatial data field that you want to display, and provides a preview of the resulting map that will be included in the report.

Note that you can choose to embed the spatial data in the report, which increases the report size but ensures that the spatial map data is always available in the report. You can also add a Bing Maps layer, which enables you to “superimpose” your spatial and analytical data over Bing Maps tiles as shown here:

Next you can choose the type of map visualization you want to display. These include:

• Basic Map: A simple visual map that shows geographical areas, lines, and points.
• Color Analytical Map: a map in which different colors are used to indicate analytical data values (for example, you could use a color range to show sales by region in which more intense colors indicate higher sales)
• Bubble Map: A map in which the center point of each geographic object is shown as a bubble, the size or color of which indicates an analytical value.

To show the number of times Beanie has visited a country, I’m using a bubble map. Since the bubbles must be based on a data value, I must now choose the dataset that contains the values that determine the size of the bubbles.

Having chosen the dataset, I now get a confirm or chance to change the default matches that the wizard has detected.

Finally, you can choose a visual theme for the map and specify which analytical fields determine bubble size and the fill colors used for the spatial objects.

Clicking Finish, generates the report, which you can make further changes to with Report Builder.

Selecting the map reveals a floating window that you can use to edit the map layers or move the area of the map that is visible in the map viewport (the rectangle in which the map is displayed).

You can make changes to the way the map and its analytical data are displayed by selecting the various options on the layer menus. For example, you can:

• Click Polygon Properties to specify a data value to be displayed as a tooltip for the spatial shapes on the map.
• Click Polygon Color Rule to change the rule used to determine the fill colors of the spatial shapes on the map.
• Click Center Point Properties to add labels to each center point “bubble” on the map.
• Click Center Point Color Rule to change the rule used to determine the color of the bubbles, including the scale of colors to use and how the values are distributed within that scale.
• Click Center Point Size Rule to change the rule used to determine the size of the bubbles, including the scale of sizes to use and how the values are distributed within that scale.
• Click Center Point Marker Type Rule to change the rule used to determine the shape or image of the bubbles, including a range of shapes or images to use and how the values are matched to shapes or images in that range.

At any time, you can preview the report in Report builder by clicking Run. Here’s how my report looks when previewed.

When you’re ready to publish the report to the report server, click Save on the main menu, and then click Recent Sites and Servers in the Save As Report dialog box to save the report to an appropriate folder on the report server.

After the report has been published, users can view it in their Web browser through the Report manager interface. here’s my published report:

I’ve only scratched the surface of what’s possible with the map visualization feature in SQL Server 2008 R2 Reporting Services. When combined with the spatial data support in SQL Server 2008 it really does provide a powerful way to deliver geographical analytics to business users, and hopefully you’ve seen from this article that it’s pretty easy to get up and running with spatial reporting.

del.icio.us Tags: SQL Server,SQL Server 2008 R2,Spatial Data,Reporting Services

Windows Virtual PC

I've just installed Windows 7 and started to have a look at the new Windows Virtual PC. There are a couple of nice new features.

First of all, you can now save notes as part of the Virtual PC configuration file, so you can record any relevant information about the virtual machine. In addition to that you can also save the logon credentials for the virtual machine in the configuration file, so you don't have to worry about remembering passwords for all various virtual machines you might have.

Second, it's now much easier to access the contents of virtual hard drives without firing up a virtual machine. If you run the Windows disk management console (diskmgmt.msc), you'll see that there are two new options on the Actions menu that enable you to mount a virtual hard disk in Windows, and to create a brand new virtual hard disk. Once you’ve created your virtual hard disk, you can initialise it and format it just like any other disk on the system.