Tuesday, July 08, 2008
Call GeoNames Web Services from a SQL Stored Procedure
Posted on Tuesday, July 08, 2008 9:13:22 PM (Mountain Daylight Time, UTC-06:00)  Comments [2] | 
Categories: SQL Server

Today I needed to get a country name from the lat, long of a point, ideally in a stored procedure.

Getting the country name is just a matter of calling the geonames web service via a handy dandy GET...
http://ws.geonames.org/countryCode?lat=47.03&lng=10.2&style=full&type=XML

which returns the following xml...

<geonames>
<country>
<countryCode>AT</countryCode>
<countryName>Austria</countryName>
<distance>0.0</distance>
</country>
</geonames>

Great, but how do we call this from inside a stored procedure? Initially I thought of looking at using .NET in the SQL CLR, but found some other code snippets that use OLE Automation in T-SQL to create an instance of 'MSXML2.XMLHTTP' to pull data. I thought I'd share since it could be pretty handy. Here's the stored proc.

CREATE PROCEDURE GetCountry
    -- Add the parameters for the stored procedure here
     @lat as float,
     @lon as float
AS
BEGIN

    Declare @Object as Int;
    Declare @ResponseText as Varchar(8000);
    Declare @Url as Varchar(MAX);

    select @Url = 'http://ws.geonames.org/countryCode?lat=' + CAST(@lat as varchar) + '&lng='+ cast(@lon as varchar) +'&type=xml'

    Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
    Exec sp_OAMethod @Object, 'open', NULL, 'get', @Url, 'false'
    Exec sp_OAMethod @Object, 'send'
    Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT     
    Exec sp_OADestroy @Object

    --load into Xml
    Declare @XmlResponse as xml;
    select @XmlResponse = CAST(@ResponseText as xml) 
    select @XmlResponse.value('(/geonames/country/countryName)[1]','varchar(50)') as CountryName

END

The basic idea is to construct the Url, then use the MSXML2.XMLHTTP object to make the request and get the response. From there, we then cast the response into Xml, and pull out the values of interest via xquery.

Since this sproc relies on OLE Automation, you will need to enable this on your SQL box:

On the SQL Server box go to…

  • Start-> Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Surface Area Configuration
  • Click "Surface Area Configuration for Features"
  • Click the DB -> Database Engine
  • Select OLE Automation & Check the checkbox Enable OLE Automation...

Close the tool and then the sproc should work.

As an example, running the stored procedure (i.e. exec dbo.GetCountry 47.03, 10.3) will return "Austria".

This could be easily extended to call any of the GeoNames web services, or any other REST service based on a GET.  Heck, if you really get after this, you could do anything you want - you are working with the full blown MSXML2.XMLHTTP object which does all the Ajaxy goodness for Internet Explorer. Have fun!

Wednesday, July 09, 2008 8:22:00 AM (Mountain Daylight Time, UTC-06:00)
Something about this makes me a bit paranoid.
Your basically opening up a 3rd tier application to the web, bypassing the first 2 security tiers. I guess this would be good if your pointing to your own web service but a little scary if your pointing to a 3rd party site. I'm sure geonames.org is safe but its still a little risky.
Donny V
Wednesday, July 09, 2008 9:07:43 AM (Mountain Daylight Time, UTC-06:00)
Neat, sure, but it seems to go against separation of concerns to have both your app server and your database server dealing in HTTP.
Sean Gillies
Comments are closed.