The Intellog Blog

Rather than design, implement and maintain logic for the conversion of UTM coordinates to lat/long, it makes sense to take advantage of the many web services that provide this functionality.  The one chosen for this application was from TerraServer, and the specific web wethod was ConvertUtmPtToLonLatPt.  The challenge was to make this web service accessible from within SQL Server as a stored procedure.  This implies the use of C# to implement the stored procedure, which Microsoft turbidly refers to as CLR Integration.  There’s a pretty good Microsoft article on the subject, and there is also an article by David Hayden that provides pretty good guidelines as well.  The latter, not only for the stored procedure part of it, but also, for calling a web service.  Co-incidentally, Mr. Hayden’s example uses TerraServer as well, making it particularly well suited to this work 

To start off, the .NET Framework Tools include a dandy tool, wsdl.exe, that generates a C# source file which is a proxy class for the web service itself.  In simple terms,  it creates code which emulates the behaviour of the web service which executes on the local host.  Borrowing heavily from Mr. Hayden’s example, modifying is slightly for this particular application, the command-line syntax is as follows;

wsdl.exe /o:TerraServer.cs /n:TerraServer http://www.terraserver-usa.com/TerraService2.asmx

which takes the definition of the web service found at the url, generates a .cs file as specified with the /o: option, and incorporating the  namespace of TerraServer, specified with the /n: option.  The resulting .cs file then needs to be turned into a .dll with the C# compiler, as follows;

csc.exe /target:library TerraServer.cs

The resulting  TerraServer.dll was added as a reference to the C# project within Visual Studio 2005, and the line

using TerraServer

was added to the C# source code. It’s now possible to build source referencing the web service, right down to getting all that good IntelliSense stuff, just as if it were a local class.  Here’s the source code which illustrates the mechanism to make the C# code accessible from SQL Server, and also, how the web service is called;

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using TerraServer;

public class HelloWorldProc
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void toLatLong(SqlDouble eastingAmt,
		out SqlDouble latitudeAmt, out SqlDouble longitudeAmt,
		SqlDouble northingAmt, SqlString zoneLbl)
    {
        TerraService ts = new TerraService();
        UtmPt utmPt = new UtmPt();
        utmPt.Zone = 14;
        utmPt.X = (Double)eastingAmt;
        utmPt.Y = (Double)northingAmt;
        LonLatPt lonLatPt = new LonLatPt();
        lonLatPt = ts.ConvertUtmPtToLonLatPt(utmPt);
        latitudeAmt = lonLatPt.Lat;
        longitudeAmt = lonLatPt.Lon;
    }
}

This source code was given the entirely cliché name HelloWorld.cs and then compiled with the following syntax;

csc.exe /target:library HelloWorld.cs /reference:TerraServer.dll

Which churns out HelloWorld.dll.  The final step, prior to going over to SQL Server, is to use the sgen.exe  utility (also provided as part of the .NET Framework Tools) which "creates an XML serialization assembly …in order to improve the startup performance…when it serializes or deserializes objects of the specified types"  Now that sounds kind of optional, but it seems as though it’s mandatory when accessing a web service from within SQL Server.  In any event, the syntax for the utility was;

sgen.exe /a:TerraServer.dll

which automatically creates TerraServer.XmlSerializers.dll.  It’s important to note all three .dll files created in the steps above were located in one folder.  It’s not clear whether that’s mandatory, convenient, or necessary at all.  But that’s the way this particular exercise was set up.  The Visual Studio/C# part of the exercise is now done.

There are a couple of things that need to be checked on the instance of SQL Server used.  CLR Integration is disabled by default, presumably for security reasons.  Microsoft has a good, simple article on how this is enabled.  Secondly, you have configure your database so that it’s deemed to be trustworthy, which is done with the Transact-SQL (T-SQL)  syntax;

ALTER DATABASE [myDatabase] SET TRUSTWORTHY ON

In a database which is accessible to the public, implementing the steps noted above may not be a good idea.  But, if the database is accessible, there’s a reasonable chance the management of the database is in the hands of a DBA, anyway, so the problem can simply be assigned to them to solve.  Now you have to issue the T-SQL commands to create the link between the stored procedure and the .dlls, as shown below;

USE [myDatabase]
CREATE ASSEMBLY HelloWorld
	FROM 'C:\HelloWorld.dll' WITH PERMISSION_SET = UNSAFE
CREATE ASSEMBLY [TerraServer.XmlSerializers]
	FROM 'C:\TerraServer.XmlSerializers.dll' WITH PERMISSION_SET = SAFE 

It’s looks really scary to have PERMISSION_SET=UNSAFE, but all it’s indicating is the stored procedure will be reaching outside of the local SQL Server to accomplish its objective, which again, appears to be for security reasons.  You can get around this with certificates and the like, but for the purposes of this development exercise, it was chosen to avoid this route.  The next step is to create the procedure object within SQL Server, as follows, using T-SQL;

CREATE PROCEDURE toLatLong
	(
	@eastingAmt FLOAT
	,@latitudeAmt FLOAT OUTPUT
	,@longitudeAmt FLOAT OUTPUT
	,@northingAmt FLOAT
	,@zoneLbl NVARCHAR(3)
	)  AS EXTERNAL NAME HelloWorld.HelloWorldProc.toLatLong

The setup steps required are now complete.  There should now be a stored procedure accessible like any other stored procedure within SQL Server.  The only differences, of course, are the fact the procedure is written in C#, and the heavy lifting of the stored procedure is actually implemented by an external web service.  Here’s a sample of how the stored procedure would be called with T-SQL;

DECLARE @longitudeAmt FLOAT
DECLARE @northingAmt FLOAT

EXEC toLatLong
	@eastingAmt = 368437.78
	,@latitudeAmt = @latitudeAmt OUTPUT
	,@longitudeAmt = @longitudeAmt OUTPUT
	,@northingAmt = 5430829.5
	,@zoneLbl = '14U'

SELECT
	@latitudeAmt
	,@longitudeAmt 

Sharp-eyed readers will notice the zone is being passed as an NVARCHAR, and yet, the C# code ignores it and supplies its own value for the UTM zone.  It’s simply an implementation detail that got overlooked prior to writing up this post. The C# code should, of course, parse out the numeric portion of the UTM label and pass that through to the web service, rather than hard-coding. 

Finally, if and when the time comes the stored procedure is no longer required, here’s how to remove it from the database.

USE [myDatabase]
DROP PROCEDURE toLatLong
DROP ASSEMBLY [TerraService.XmlSerializers]
DROP ASSEMBLY HelloWorld