Thursday, March 18, 2010

Splitting a polygon using SQL Server Spatial queries

A recent project involved drawing regions on a map of a country using the Bing Maps Silverlight control. The user could zoom in to a suburb and draw a region (polygon) around an area on the map. Another feature, and hence the purpose of this blog post, involved enabling a user to draw a line through a region (polygon) and split the region in two: how was that splitting done?
Note: you will probably need some database experience and ideally some Bing/Google Maps experience for this to make sense

A quick intro to SQL Server Spatial queries

SQL Server 2008 introduced support for geo-spatial data. The two new spatial data types are:
  • Geometry: supports flat data surfaces. The following will create a polygon and a line and you can see that our resulting rectangle and line are straight:

DECLARE @Geometry GEOMETRY = 'POLYGON((10 10, 40 10, 40 40, 10 40, 10 10))'
DECLARE @Linestring GEOMETRY = 'LINESTRING(5 5, 50 50)'
SELECT @Geometry
SELECT @Linestring

  • Geography: enables you to define a region on a curved 3D surface. If you consider that the surface of the world is curved (the globe being a sphere), then you can appreciate that this data type is the type to use for storing geographic data accurately.  Notice how, in the following example, the square “bulges” and the line is curved, even though the polygon and linestring points are the same as the geometry data above.

DECLARE @Geography GEOGRAPHY = 'POLYGON((10 10, 40 10, 40 40, 10 40, 10 10))'
DECLARE @Linestring GEOGRAPHY = 'LINESTRING(5 5, 50 50)'
SELECT @Geography
SELECT @Linestring

There are many sites around the web which display the difference between the two types in nice pretty images. Also, John Nelson's site describes why you would want to use one over the other and Craig Dunn’s site gives a wealth of useful information about SQL Server Spatial queries.

Splitting basic geographic data

To split a polygon in two, we are going to use a linestring. Lets take the above geographic polygon and use the above geographic linestring to perform the split. Do you remember Set Theory (Intersection, Difference, etc.) from school? Well, to make the split happen we are going to get the difference between the polygon and the linestring:

DECLARE @Geography GEOGRAPHY = 'POLYGON((10 10, 40 10, 40 40, 10 40, 10 10))'
DECLARE @Linestring GEOGRAPHY = 'LINESTRING(5 5, 50 50)'
DECLARE @SplitRegion GEOGRAPHY = @Geography.STDifference(@Linestring.STBuffer(100000.0000)) 
SELECT @SplitRegion

We are using the built in STDifference() method to perform the split and we are giving the linestring a little bit of padding using STBuffer() to help make the split happen. I'd suggest you tweak the STBuffer value to suit your needs: depending on your polygon, you might find that a value of 0.0001 is all you need.
The resulting @SplitRegion object above is a MultiPolygon with two inner polygons defined. We can add the following to our above query to loop through each of the polygons:
DECLARE @i int = 1;
WHILE @i <= @SplitRegion.STNumGeometries() 
    DECLARE @InnerGeography GEOGRAPHY = @SplitRegion.STGeometryN(@i)
    SELECT @InnerGeography
    -- Next polygon
    SET @i = @i + 1

A more complex example

Lets for example, imagine that a company has two offices in Ireland with both offices maintaining a different range of customer and supplier locations. One office manages the bottom part of the country, the other office manages the rest of the country.
Each of these customer/supplier locations could be stored in a SQL Server spatial-aware database and another table could store a polygon shape of the country.
You could then split that polygon shape of the country based on where the zone of influence ends for the offices and use the resulting data to draw on a Bing/Google map which customers and suppliers belong to which office.

Monday, March 15, 2010

Using Get & Post from Silverlight to a RESTful service

A recent project I was working on needed a lot of data to be passed quickly between a Silverlight client and a .Net server. The approach taken, instead of using a relatively-heavy XML SOAP implementation, was to go with a lightweight JSON REST web service.

Information on the web is moderately clear on how this works, but I found the POST part of the picture to be virtually non-existent. As I would be passing in more data than some browsers support through the querystring, I didn't have any other option: I had to figure it out. Thus, if you need that info, read on...


The following is a sample web service with one GET method:

    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    public class MyTestService
        [OperationContract(Name = "MyTestMethod")]
        [WebGet(UriTemplate = "myTestMethod/{aParameter}", ResponseFormat = WebMessageFormat.Json)]
        public string MyTestMethod(string aParameter)
            return aParameter;

The Silverlight call could be similar to:

        string parameter = "Hello!";
        string serviceUriFormat = "http://someDomain/MyTestService.svc/myTestMethod/{0}";
        Uri serviceUri = new Uri(String.Format(serviceUriFormat, parameter), UriKind.Absolute);
        WebClient webClient = new WebClient();
        webClient.OpenReadCompleted += new OpenReadCompletedEventHandler(myOpenReadCompletedEventHandler);
        webClient.OpenReadAsync(serviceUri, myCallback);


While GET is straightforward POST is more complex as, it turns out, POST methods have an additional input Stream.
The following is a sample POST web service call which calls a method with a parameter and with an additional stream of POST data:

    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    public class MyTestService
        [OperationContract(Name = "MyTestMethodPost")]
        [WebInvoke(UriTemplate = "myTestMethodPost/{aParameter}", Method = "POST")]
        public string MyTestMethodPost(string aParameter, Stream input)
            string testParameter = aParameter;
            string sentence;
            using (StreamReader sr = new StreamReader(input))
                sentence = sr.ReadToEnd();
            return sentence;

Notice that the method is marked with Method="Post" and that it has an extra parameter of type Stream.

The associated Silverlight call would be as follows:

        string parameter = "test";
        string postData = "A really really really long string";
        string serviceUriFormat = "http://someDomain/MyTestService.svc/MyTestMethodPost/{0}";
        Uri serviceUri = new Uri(String.Format(serviceUriFormat, parameter), UriKind.Absolute);
        WebClient webClient = new WebClient();
        webClient.UploadStringCompleted += new UploadStringCompletedEventHandler(uploadStringCompletedEventHandler);
        webClient.Headers["Content-type"] = "application/xml";
        webClient.Encoding = Encoding.UTF8;
        webClient.UploadStringAsync(serviceUri, "POST", postData, callback);

Notice here that instead of using the OpenReadAsync method of the WebClient class, we use the UploadStringAsync method with the appropriate Encoding and Content Header set.

By using the above POST approach I was able to post relatively large amounts of data to the REST web service method

Wednesday, March 10, 2010

Single Installer for multiple environments

With quite a few contracts I have been working on over the years I have ended up helping out with the build process for ASP.Net websites. Many existing enterprise build processes I have encountered tend to involve making a custom build of the codebase for UAT and then once UAT is passed making another build for PROD. This process brings in a significant risk of a developer on a team checking in changes AFTER UAT was build and thus ending up with untested code in PROD.
This risk is unnecessary. The following is an approach I have used at a number of enterprises for creating a single .msi installer which can be run in a DEV environment (if it exists), then copied into a UAT environment for user testing before finally being copied and run in PROD. The single installer gets built once but can handle the different server/database configurations of the different environments and can install the correct .config files in each environment.
The methodology is as follows:
  1. Within Visual Studio, add a Setup Project to the solution
  2. Create different copies of the Web.config for each target environment. Each web.config (e.g. web.config.UAT) can then be tailored to each environment. You should end up with a project similar to the following:

  3. Change the Properties of the original web.config and set its "Build Action" to "None" 
  4. Right-click on the Installer project and View -> File System
  5. In the Application Folder, add the new web.config files (DEV, UAT and PROD). You should end up with the following: 
  6. For each of our three files set the following properties:   

Transitive: True
Vital: False
   ->Replace "DEV" with "UAT" and "PROD" for the other configs 
TargetName: web.config

    For example with the UAT file: 
Then, when installing at each environment, use similar to the following at the command line:
msiexec /q /i "Installer.msi" ENVIRONMENTNAME="PROD"

The ENVIRONEMNTNAME flag which is passed into msiexec tells the installer to use the file(s) marked with the same Condition property (step 6 above). Thus, the right config file gets chosen for each environment. I have used this approach successfully many times with Visual Studio 2003, 2005 and 2008.
Hope its of help!