Call GeoNames Web Services from a SQL Stored Procedure

http://blog.davebouwman.net/2008/07/09/CallGeoNamesWebServicesFromASQLStoredProcedure.aspx
Call GeoNames Web Services from a SQL Stored Procedure

Posted on 8. juli 2008 21:13:22 (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://blog.davebouwman.net/ct.ashx?id=6b2d6ed4-cb5e-40e8-ab88-7477c2ce69d0&url=http%3a%2f%2fws.geonames.org%2fcountryCode%3flat%3d47.03%26lng%3d10.2%26style%3dfull%26type%3dXML
which returns the following xml...






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://blog.davebouwman.net/ct.ashx?id=6b2d6ed4-cb5e-40e8-ab88-7477c2ce69d0&url=http%3a%2f%2fws.geonames.org%2fcountryCode%3flat%3d + 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!