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

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.

image

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.

image

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”.

image

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

image

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.

image

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 2008ProjectsVE-SQL-01-TriggerVE-SQL-01-TriggerbinVE-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 2008ProjectsVE-SQL-01-TriggerVE-SQL-01-TriggerbinVE-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().

image

(to be continued)

 

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

5 Responses to Virtual Earth & SQL Server 2008 – Part 4: Integrating SQL Server 2008 and Virtual Earth (1/6)

  1. podlipensky says:

    I can’t find full source code of your great sample. Please help to find it.

  2. Johannes says:

    The embedded links to my SkyDrive are broken. I have now created a href.

  3. Wyndham says:

    Hi there Johannes,

    I am new to the geospacial game and loving it. I am struggling with your site a bit, I cant seem to see your pictures (Or most of them anyway). Any thoughts?

    Kind regards and many thanks,
    Wyndham

    • Hi there. Unfortunately I made a stupid mistake which lead to the broken images. My blog has been migrated from Windows Live Spaces to WordPress and after the migration I deleted all image files from Windows Live. Unfortunately I noticed only then that the older blog postings were refrencing the original locations on Windows Live. Even more unfortunate is that the URLs were using some cryptic tokens so it won’t be just as simple as uploading the files to Windows Live again. I will fix the problem in the comming days but it might take a while. Sorry for the inconvenience.

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