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.Latitude & ", " & myFindResults.Results(0).FoundLocation.LatLong.Longitude & ", " & "geography::STPointFromText('POINT(" & myFindResults.Results(0).FoundLocation.LatLong.Latitude & " " & myFindResults.Results(0).FoundLocation.LatLong.Longitude & ")', 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)