Virtual Earth & SQL Server 2008 – Part 4: Integrating SQL Server 2008 and Virtual Earth (3/6)

Using Spatial-Methods

Now we will start using spatial methods in SQL Server 2008. We will extend the previous example where we mapped occurrences of “Foot and Mouth Disease” (FMD) in a way that we can analyse the outbreak. We want to be able to map the area that has been infected after the 1st day, after the 2nd day and so on.

SQL Server 2008 has a number of methods which will support this scenario. Ultimately we need to create a CONVEXHULL, i.e. the smallest convex polygon that contains the given geometry instance. The method to use is STConvexHull and it can be applied to any geometry but if applied to a POINT or a LINESTRING it will return a geometry of the same type. In our case we have multiple points which represent the occurrences of FMD and we have to aggregate them into a MULTIPOINT first before we can apply the STConvexHull function. We can create such a MULTIPOINT by opening a cursor which returns all the points representing infections before a key date. While we loop through this cursor we use the STUnion method to aggregate the single points into a single MULTIPOINT.

image

This logic is implemented in a stored procedure as shown below:

CREATE PROCEDURE GetConvexHull @UpTo nvarchar(11)
AS

DECLARE @Point geometry;
DECLARE @MPoint geometry;
DECLARE @i INT;

SET @i=0;

DECLARE GeomCursor CURSOR FOR SELECT Geom2 FROM FMD WITH (NOLOCK) WHERE Date <= @UpTo;
OPEN GeomCursor;
FETCH NEXT FROM GeomCursor INTO @Point;
WHILE @@FETCH_STATUS = 0
    BEGIN
        IF (@i=0) BEGIN
            SET @Mpoint = @Point;
        END
        ELSE BEGIN
            SET @MPoint = @MPoint.STUnion(@Point);
        END;
        SET @i = @i+1
        FETCH NEXT FROM GeomCursor INTO @Point;
    END;
CLOSE GeomCursor;
DEALLOCATE GeomCursor;

SELECT @MPoint.STConvexHull().STAsText();

The result of the stored procedure will be a string representing a polygon like the following:

POLYGON ((51.594378140755 0.2863533329218626, 51.590768386609852 0.31522062048316, 51.556323000229895 0.24923799932003021, 51.594378140755 0.2863533329218626))

You probably noticed that we aren’t using the original spatial-column Geom but a column Geom2. Why is that? Not all spatial functions are available for both the geography and the geometry data type but fortunately it is pretty simple to convert between the two. In our example the method STConvexHull can only be applied to a geometry data type and not to the geography data type we used before. We could have used the geometry data type right from the start but we wanted to demonstrate how you can easily create a geometry from a geography. Consequently, we added a new column, Geom2, of type geometry and simply execute the following SQL statement:

UPDATE FMD 
  SET Geom2 = 
    geometry::STPointFromText('POINT(' + STR(Geom.Lat, 20, 16) + ' ' + STR(Geom.Long, 20, 16) + ')', 4326)

Now that we have extended our database schema and added a stored procedure, we will add the function to our application. First we add another checkbox and a textbox to our accordion pane “VEShape-Objects”. The function that will be executed when we check the checkbox shall be AddFMDArea:

<input id="cbFMDArea" type="checkbox" onclick="AddFMDArea(‘cbFMDArea’)"/><a href=’javascript:ShowLocation(54.41893, -3.735352, 6);’>FMD-Area</a><br />
<
a>Show Outbreak Until:</a><input id="txtUpTo" type="text" value="23 Feb 2001"/><br />

It is probably a good idea to add the polygon to a new VEShapeLayer, so we define a new global variable in our JavaScript:

//VEShape-Objects
...
var slFMDArea = new VEShapeLayer();

When we check the control we build a URL which contains a relative path to another HTTP-handler (VEShapeFMDArea.ashx) and adds a parameter “upto”. This parameter is retrieved directly from the textbox in our accordion pane and represents the end date for the aggregatation of the FMD infections. As before, we then determine the XMLHTTP-object that can be used in our browser and execute the AJAX-call asynchronously. Now we wait for the HTTP-handler to respond with a JavaScript which will then be executed using the eval-function:

function AddFMDArea(control)
{
    if (document.getElementById(control).checked==false)
    {
        slFMDArea.DeleteAllShapes();
    }
    else
    {
        try
        {
            map.AddShapeLayer(slFMDArea);
        }
        catch(e)
        {
        }
        //Build URL to call the server
        var url="./VEShapeFMDArea.ashx?";
        url += "&upto=" + document.getElementById('txtUpTo').value;

        //Get the appropriate XMLHTTP object for the browser
        var xmlhttp = GetXmlHttp();

        //if we have a valid XMLHTTP object
        if (xmlhttp)
        {
            xmlhttp.Open("GET", url, true); // varAsynx = true

            //set the callback
            xmlhttp.onreadystatechange = function()
            {
                if (xmlhttp.readystate ==4) //4 is a success
                {
                    //server code creates JavaScript "on the fly" for us to
                    //execute using eval()
                    var result = xmlhttp.responseText
                    eval(result);
                }
            }
            xmlhttp.send(null);
        }
    }  
}

In the HTTP-Handler we set the culture again to make sure we don’t get into trouble with the decimal separators in the latitudes and longitudes, as mentioned before. Then we retrieve the target date from the URL-parameter, prepare our database connection and execute the stored procedure GetConvexHull. As a result, we receive a polygon object in string format and we can now use some string manipulation methods to create a JavaScript which will add this polygon to our Virtual Earth map:

'set culture to en-UK to avoid potential problems with decimal-separators
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-UK")

'Fetch URL-parameter
Dim upto As String = context.Request.Params("upto")

'Query database(s) and create JavaScript
Dim settings As ConnectionStringSettings
Dim sb As StringBuilder = New StringBuilder
Dim SqlPoly As String = ""

settings = ConfigurationManager.ConnectionStrings("SpatialDB")
Dim myConn As New SqlConnection(settings.ConnectionString)
myConn.Open()

Dim cmd As New SqlCommand()
'Set SQL Parameters
cmd.Connection = myConn
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("UpTo", upto))

'Specify the stored procedure name as the command text
cmd.CommandText = "GetConvexHull"

Dim geomRdr As SqlDataReader = cmd.ExecuteReader()
While (geomRdr.Read())
    SqlPoly = SqlPoly + geomRdr(0)
End While

'Create the JavaScript
SqlPoly = Replace(SqlPoly, "POLYGON ((", "")
SqlPoly = Replace(SqlPoly, "))", "")
Dim PointArray() As String = Split(SqlPoly, ",")

Dim VEPoly As String = ""
Dim i As Integer = 0
While i <= PointArray.Length - 1
    VEPoly = VEPoly + "new VELatLong(" + Replace(LTrim(PointArray(i)), " ", ",") + "),"
    i = i + 1
End While
VEPoly = Left(VEPoly, VEPoly.Length - 1)
VEPoly = "var shape = new VEShape(VEShapeType.Polygon, [" + VEPoly + "]);" + _
         "shape.SetTitle('Infections until " + upto + "');" + _
         "shape.SetLineColor(new VEColor(255,0,0,1.0));" + _
         "shape.SetFillColor(new VEColor(255,0,0,0.5));" + _
         "slFMDArea.AddShape(shape);"

sb.Append(VEPoly)
geomRdr.Close()
myConn.Close()

context.Response.Write(sb.ToString())

We can now compile the code and compare the results for 2 different days. You can clearly see that the infection started at the east coast of England and after only 4 days it already reached the west coast of Wales.

image

(to be continued)

Advertisements
This entry was posted in SQL Server Spatial. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s