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.
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:
<a>Show Outbreak Until:</a><input id="txtUpTo" type="text" value="23 Feb 2001"/><br />
//VEShape-Objects ... var slFMDArea = new VEShapeLayer();
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)