MapForums

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 1^st 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.

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-Areaa><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.CurrentCultu  re = System.Globalization.CultureInfo.CreateSpecificCul  ture("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.

(to be continued)