Integrating SQL Server 2008 and Virtual Earth

So far we have given an overview about some of the important features in both SQL Server 2008 and Virtual Earth. Now we will bring the software and the service together.

We just learned that we have basically 3 options to add data from SQL Server 2008 to Virtual Earth:

• Adding individual VEShape-objects
• Importing data from an GeoRSS-feed into a VEShapeLayer
• Adding Tile-Layers

We will have a look at all of these options but we will also have a look at how we can draw on Virtual Earth maps and insert the resulting VEShape-objects as geography data into SQL Server 2008.

Geocoding in SQL Server 2008

Wait a minute: geocoding in SQL Server 2008? Is that possible? Geocoding is not a feature of SQL Server 2008 but it is a feature of Virtual Earth so maybe we can bring the software and the service together in a way that they complement each other even more. The Virtual Earth Platform spans actually over 2 groups of services. The MapPoint Web Service and Virtual Earth itself. As an enterprise customer, you have always access to both services but everybody can sign-up to a free developer account for the MapPoint Web Service as well.

The key is that since SQL Server 2005 we have a Common Language Runtime (CLR) in the database. The CLR allows us to write .NET managed code for procedures or triggers and that allow us to make a calls to the MapPoint Web Service.

Let’s assume we have a table to record occurrences of “Foot and Mouth Disease”. The table is defined as follows:

CREATE TABLE [FMD](

    [ID] [int] IDENTITY(1,1) NOT NULL,

    [Date] [date] NULL,

    [City] [varchar](50) NULL,

    [County] [varchar](50) NULL,

    [Country] [varchar](50) NULL,

    [Cattle] [int] NULL,

    [Pigs] [int] NULL,

    [Sheep] [int] NULL,

    [Latitude] [float] NULL,

    [Longitude] [float] NULL,

    [Geom] [geography] NULL,
 CONSTRAINT [PK_FMD] PRIMARY KEY CLUSTERED 
 (

    [ID] ASC
 ))

Usually people will insert the date, city and county as well as the number of diseased cattle, pigs and sheep. They wouldn’t want to bother to geocode the data. On the other side it will help us a lot if we can have the geocodes and use them for some geographic analysis within SQL Server 2008

Thus the plan is to intercept the INSERT-statement with an INSTEAD OF trigger. We use the address information in the original statement as parameters in a SOAP-call to the MapPoint Web Service which returns us a latitude and longitude. With this information we can create a geography object of type point for SQL Server 2008 and then we execute an INSERT statement which adds these values to the original statement.

Before we start to develop our CLR trigger we first need to change a setting in the database to allow .NET managed code to access external data:

ALTER DATABASE VESQLWP SET TRUSTWORTHY ON

Now we can start coding. We start up the Visual Studio 2005 or higher and create a new database project. In the database project we create a new CLR trigger and we will name the class “Geocode”.

In the project properties we make sure that the assembly will have external access:

Now we add a web reference to the MapPoint Web Service and we name it MWS (https://mappoint-css.live.com/MwsSignup/Eval.aspx). Please note: If you haven’t done so yet sign up for a free developer account since you will need the credentials in a few minutes.

OK, we’re ready to go. You will find the complete code ready for download here

Please note: You will have to enter your Virtual Earth Platform User ID and Password in the file Geocode.vb)

Basically here is what happens:

...
******* initialise the MapPoint Web Service FindService (Geocoder)
******* Dim findService As New FindServiceSoap
******* findService.Credentials = New Net.NetworkCredential(myUID, myPWD)

******* 'Determine the INSERT statement about to be executed
******* Dim command As SqlCommand
******* Dim reader As SqlDataReader
******* Dim pipe As SqlPipe
******* Dim triggContext As SqlTriggerContext
******* triggContext = SqlContext.TriggerContext
******* pipe = SqlContext.Pipe

******* Select Case triggContext.TriggerAction
*********** Case TriggerAction.Insert
*************** Using connection As New SqlConnection("context connection=true")
******************* connection.Open()
******************* command = New SqlCommand("SELECT * FROM INSERTED;", connection)

******************* reader = command.ExecuteReader()
******************* reader.Read()

******************* 'Define Address Object for MapPoint Web Service Call
******************* Dim myAddress As New Address
******************* myAddress.PrimaryCity = CType(reader(2), String)
******************* myAddress.Subdivision = CType(reader(3), String)
******************* myAddress.CountryRegion = CType(reader(4), String)

******************* 'Set Specification for Geocopder call
******************* Dim findAddressSpec As New FindAddressSpecification
******************* findAddressSpec.InputAddress = myAddress
******************* findAddressSpec.DataSourceName = "MapPoint.EU"

******************* 'Call the Geocoder
******************* Dim myFindResults As FindResults
******************* myFindResults = findService.FindAddress(findAddressSpec)

******************* 'Create Geography and replace original Statement
******************* If myFindResults.Results.Length > 0 Then
*********************** command = New SqlCommand("INSERT into fmd (date, city, county, country, cattle, pigs, sheep, Latitude, Longitude, Geom) VALUES ('" + reader(1).ToString & "', '" & reader(2).ToString & "', '" & reader(3).ToString & "', '" & reader(4).ToString & "', " & reader(5).ToString & ", " & reader(6).ToString & ", " & reader(7).ToString & ", " & myFindResults.Results(0).FoundLocation.LatLong.Lat itude & ", " & myFindResults.Results(0).FoundLocation.LatLong.Lon gitude & ", " & "geography::STPointFromText('POINT(" & myFindResults.Results(0).FoundLocation.LatLong.Lat itude & " " & myFindResults.Results(0).FoundLocation.LatLong.Lon gitude & ")', 4326)" & ");", connection)
*********************** pipe.Send("SQL-Statement: " & command.CommandText)
******************* Else
*********************** command = New SqlCommand("INSERT into fmd (date, city, county, country, cattle, pigs, sheep, Latitude, Longitude, Geom) VALUES ('" + reader(1).ToString & "', '" & reader(2).ToString & "', '" & reader(3).ToString & "', '" & reader(4).ToString* & "', " & reader(5).ToString & ", " & reader(6).ToString & ", " & reader(7).ToString & ", null, null, null);", connection)
*********************** pipe.Send("No address found")
******************* End If
...

Now we compile the project and load the assembly into the database:

CREATE ASSEMBLY Geocode 
 FROM 'C:\...\Visual Studio 2008\Projects\VE-SQL-01-Trigger\VE-SQL-01-Trigger\bin\VE-SQL-01-Trigger.dll' 
 WITH PERMISSION_SET = EXTERNAL_ACCESS;

Next we have to provide another assembly for XML Serialization. Why do we need to do this? Calling web service methods requires serializing all the types being used in xml. Outside SQL Server this serialization code is generated at runtime, compiled and loaded in the application. However, for security and reliability reasons, SQL Server does not allow you to dynamically load assemblies. The Visual Studio automatically recognized that we need this assembly and it compiled it in the project’s bin folder. However, SQL Server 2005 did not allow the automated provisioning, we have to load this assembly as follows:

CREATE ASSEMBLY [Geocode.XmlSerializers] 
 FROM 'C:\...\Visual Studio 2008\Projects\VE-SQL-01-Trigger\VE-SQL-01-Trigger\bin\VE-SQL-01-Trigger.XmlSerializers.dll' 
 WITH PERMISSION_SET = SAFE;

And Finally we create the trigger on the database table

CREATE TRIGGER trig_Geocode 
 ON FMD 

  INSTEAD OF INSERT 

  AS EXTERNAL NAME [Geocode].[VE_SQL_01_Trigger.Triggers].[Geocode];

Let's insert a few records and check the results. Note: to easily review the geometry or geography data types use the method, STAsText().

(to be continued)

*