Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Geocode Addresses in T-SQL


Geocode Addresses in T-SQL

Author
Message
David Rueter
David Rueter
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 573
Sorry you are having problems. I just re-tested by downloading the unmodified Geocode.sql from the resources link at the end of the article and executed:

EXEC spGeocode '1234 N. Main Street, Santa Ana, CA'



I got valid results back:

33.755319 -117.867595 Santa Ana CA 92701 1234 N Main St ...

(FYI, I just re-tested on SQL 2005 and 2008 R2)

I suspect your database is not set to allow calls to OLE Automation procedures. (I tested this--if this is disabled, I do get a one-row resultset with nulls...along with an error message.)

To enable, execute the following:

EXEC sp_configure 'show advanced options';
RECONFIGURE;
EXEC sp_configure 'Ole Automation Procedures', 1;
RECONFIGURE;

If that is not the problem, you are likely dealing with some network issue--involving a proxy or filtering.
Naresh Parmar
Naresh Parmar
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 21
Thanks,

It works now.

I just execute below things:

EXEC sp_configure 'show advanced options';
RECONFIGURE;
EXEC sp_configure 'Ole Automation Procedures', 1;
RECONFIGURE;

Thanks a lot. Smile
SQLServerForum.nl
SQLServerForum.nl
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 129
I really loved the article!

So I tried the same with the NS api (www.ns.nl/api), but when I put the url in firefox, I get results, but within my stored procedure I get @XML = null.

I do have to send parameters with my login details, but I don't think that would be the problem.

Here's my code (without username and password):
CREATE PROCEDURE [dbo].[spNS]

AS
BEGIN
SET NOCOUNT ON

DECLARE @URL varchar(MAX)
-- SET @URL = 'http://webservices.ns.nl/ns-api-treinplanner?fromStation=Utrecht+Centraal&toStation=Wierden&departure=true'
SET @URL = 'https://username:password@webservices.ns.nl/ns-api-treinplanner?fromStation=Arnhem&toStation=Wierden&departure=true'
DECLARE @Response varchar(8000)
DECLARE @XML xml
DECLARE @Obj int
DECLARE @Result int
DECLARE @HTTPStatus int
DECLARE @ErrorMsg varchar(MAX)

EXEC @Result = sp_OACreate 'MSXML2.ServerXMLHttp', @Obj OUT

BEGIN TRY
EXEC @Result = sp_OAMethod @Obj, 'open', NULL, 'GET', @URL, false
EXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
EXEC @Result = sp_OAMethod @Obj, send, NULL, ''
EXEC @Result = sp_OAGetProperty @Obj, 'status', @HTTPStatus OUT
EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml', @Response OUT
END TRY
BEGIN CATCH
SET @ErrorMsg = ERROR_MESSAGE()
END CATCH

EXEC @Result = sp_OADestroy @Obj

IF (@ErrorMsg IS NOT NULL) OR (@HTTPStatus <> 200) BEGIN
SET @ErrorMsg = 'Error in spNS: ' + ISNULL(@ErrorMsg, 'HTTP result is: ' + CAST(@HTTPStatus AS varchar(10)))
RAISERROR(@ErrorMsg, 16, 1, @HTTPStatus)
RETURN
END

SET @XML = CAST(@Response AS XML)

-- SET @GPSLatitude = @XML.value('(/GeocodeResponse/result/geometry/location/lat) [1]', 'numeric(9,6)')
-- SET @GPSLongitude = @XML.value('(/GeocodeResponse/result/geometry/location/lng) [1]', 'numeric(9,6)')

SELECT @Response AS XMLResults

END


SQLServerForum.nl
SQLServerForum.nl
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 129
Do you have an example how to do this with SQL CLR?
David Rueter
David Rueter
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 573
We're off-topic a bit (not talking about Geocoding any more, but rather calling an arbitrary web service). That's OK...

When I run your code (with either URL) I am getting:

Error in spNS: HTTP result is: 401

So the code is working, but the server is returning an HTTP 401 code (unauthorized)

The problem is that the MSXML2.ServerXMLHttp supports providing username / password credentials via properties, but not embedded in the URL.

You can use an updated GetHTTP routine(see below) that accepts username and password. You can use it like this:


DECLARE @MyResults varchar(MAX)
DECLARE @HTTPStatus int

EXEC dbo.sputilGetHTTP
@URL = 'http://webservices.ns.nl/ns-api-treinplanner?fromStation=Utrecht+Centraal&toStation=Wierden&departure=true',
@User = 'username',
@Password = 'password',
@ResponseText = @MyResults OUTPUT,
@HTTPStatus = @HTTPStatus OUTPUT

SELECT
@HTTPStatus AS HTTPStatus,
@MyResults AS Response




Here is the code for the updated spGetHTTP procedure:


CREATE PROCEDURE [dbo].[sputilGetHTTP]
@URL varchar(MAX),
--URL to retrieve data from
@HTTPMethod varchar(40) = 'GET',
--can be either GET or POST
@ContentType varchar(80)= 'text/http',
--set to 'application/x-www-form-urlencoded' for POST, etc.
@DataToSend nvarchar(4000) = NULL,
--data to post, if @HTTPMethod = 'POST'
@HTTPStatus int = NULL OUTPUT,
--HTTP Status Code (200=OK, 404=Not Found, etc.)
@HTTPStatusText nvarchar(4000) = NULL OUTPUT,
@ResponseText nvarchar(MAX) = NULL OUTPUT,
--Full text returned by remote HTTP server (if @SuppressResponseText = 0)
@ErrorMsg varchar(MAX) = NULL OUTPUT,
--NULL unless an error message was encountered
@LastResultCode int = NULL OUTPUT,
--0 unless an error code was returned by MSXML2.ServerXMLHttp

@User varchar(512) = NULL,
--If provided, use this value for the HTTP authentication user name
@Password varchar(512) = NULL,
--If provided, use this value for the HTTP authentication password

@SuppressResponseText bit = 0,
--If 0, actual content is not returned from remote server (just status code)
@SuppressResultset bit = 1,
--If 0, result set is is not returned (just parameters)
@SilenceErrors bit = 0
--If 1, errors are not raised with RAISEERROR(), but caller can check @ErrorMsg.
--@ErrorMsg will be null if no error was raised.

--Written by David Rueter (drueter@assyst.com)

AS
BEGIN
--Retrieves data via HTTP

--http://msdn.microsoft.com/en-us/library/aa238861(v=sql.80).aspx

SET NOCOUNT ON

DECLARE @Debug bit
SET @Debug = 0

DECLARE @CRLF varchar(5)
SET @CRLF = CHAR(13) + CHAR(10)

DECLARE @Obj int

DECLARE @PerformedInit bit
SET @PerformedInit = 0

DECLARE @ErrSource varchar(512)
DECLARE @ErrMsg varchar(512)

DECLARE @tvResponse TABLE (Response nvarchar(MAX))

SET @ErrorMsg = NULL

IF @Debug = 1 PRINT 'About to call sp_OACreate for MSXML2.ServerXMLHttp'

BEGIN TRY
EXEC @LastResultCode = sp_OACreate 'MSXML2.ServerXMLHttp', @Obj OUT
IF @LastResultCode <> 0 BEGIN
EXEC sp_OAGetErrorInfo @obj, @ErrSource OUTPUT, @ErrMsg OUTPUT
END
ELSE BEGIN
SET @PerformedInit = 1
END
END TRY
BEGIN CATCH
SET @ErrorMsg = ERROR_MESSAGE()
END CATCH

BEGIN TRY

IF @LastResultCode = 0 BEGIN
IF @HTTPMethod = 'GET' BEGIN

IF @Debug = 1 PRINT 'About to call sp_OAMethod for open (GET)'
EXEC @LastResultCode = sp_OAMethod @Obj, 'open', NULL, 'GET', @URL, false, @User, @Password
IF @LastResultCode <> 0 BEGIN
EXEC sp_OAGetErrorInfo @obj, @ErrSource OUTPUT, @ErrMsg OUTPUT
END

END
ELSE BEGIN
IF @Debug = 1 PRINT 'About to call sp_OAMethod for open (POST)'
EXEC @LastResultCode = sp_OAMethod @Obj, 'open', NULL, 'POST', @URL, false, @User, @Password
IF @LastResultCode <> 0 BEGIN
EXEC sp_OAGetErrorInfo @obj, @ErrSource OUTPUT, @ErrMsg OUTPUT
END

IF @Debug = 1 PRINT 'About to call sp_OAMethod for setRequestHeader'
IF @LastResultCode = 0 EXEC @LastResultCode = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', @ContentType
IF @LastResultCode <> 0 BEGIN
EXEC sp_OAGetErrorInfo @obj, @ErrSource OUTPUT, @ErrMsg OUTPUT
END

END
END

IF @Debug = 1 PRINT 'About to call sp_OAMethod for send'
IF @LastResultCode = 0 EXEC @LastResultCode = sp_OAMethod @Obj, 'send', NULL, @DataToSend
IF @LastResultCode <> 0 BEGIN
EXEC sp_OAGetErrorInfo @obj, @ErrSource OUTPUT, @ErrMsg OUTPUT
END

IF @LastResultCode = 0 EXEC @LastResultCode = sp_OAGetProperty @Obj, 'status', @HTTPStatus OUT
IF @LastResultCode <> 0 BEGIN
EXEC sp_OAGetErrorInfo @obj, @ErrSource OUTPUT, @ErrMsg OUTPUT
END


IF @LastResultCode = 0 EXEC @LastResultCode = sp_OAGetProperty @Obj, 'statusText', @HTTPStatusText OUT
IF @LastResultCode <> 0 BEGIN
EXEC sp_OAGetErrorInfo @obj, @ErrSource OUTPUT, @ErrMsg OUTPUT
END

IF (@LastResultCode = 0) AND (ISNULL(@SuppressResponseText, 0) = 0) BEGIN
INSERT INTO @tvResponse (Response)
EXEC @LastResultCode = sp_OAGetProperty @Obj, 'responseText' --, @Response OUT
--Note: sp_OAGetProperty (or any extended stored procedure parameter) does not support
--varchar(MAX), however returning as a resultset will return long results.
END
END TRY
BEGIN CATCH
SET @ErrorMsg = ERROR_MESSAGE()
END CATCH

DECLARE @DestroyResultCode int
EXEC @DestroyResultCode = sp_OADestroy @Obj

SELECT @ResponseText = Response FROM @tvResponse

SET @ErrorMsg =
NULLIF(RTRIM(
ISNULL(@ErrorMsg, '') +
ISNULL(' (' + @ErrMsg + ')', '') +
ISNULL(' [' + @ErrSource + ']', '')
), '')


IF @ErrorMsg IS NOT NULL BEGIN
SET @ErrorMsg = 'Error in sputilGetHTTP: ' + @ErrorMsg

IF @PerformedInit = 0 BEGIN
SET @ErrorMsg = @ErrorMsg + @CRLF +
'Remember that this stored procedure uses OLE. To work properly you may need to configure ' +
'your database to allow OLE, as follows: ' + @CRLF +
' EXEC sp_configure ''show advanced options'', 1;' + @CRLF +
' RECONFIGURE;' + @CRLF +
' EXEC sp_configure ''Ole Automation Procedures'', 1;' + @CRLF +
' RECONFIGURE;' + @CRLF +
'Also, your SQL user must have execute rights to the following stored procedures in master:' + @CRLF +
' sp_OACreate' + @CRLF +
' sp_OAGetProperty' + @CRLF +
' sp_OASetProperty' + @CRLF +
' sp_OAMethod' + @CRLF +
' sp_OAGetErrorInfo' + @CRLF +
' sp_OADestroy' + @CRLF +
'You can grant rights for each of these as follows:' + @CRLF +
' USE master' + @CRLF +
' GRANT EXEC ON sp_OACreate TO myuser' + @CRLF +
' GRANT EXEC etc. ...'

IF ISNULL(@SilenceErrors, 0) = 0 BEGIN
RAISERROR(@ErrorMsg, 16, 1)
END
END
END

IF ISNULL(@SuppressResultset, 0) = 0 BEGIN
SELECT
@URL AS URL,
@ResponseText AS ResponseText,
@HTTPStatus AS HTTPStatus,
@LastResultCode AS LastResultCode,
@ErrorMsg AS ErrorMsg
END

END


SQLServerForum.nl
SQLServerForum.nl
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 129
Hi David,

Thank you so much for taking all this trouble to help me.

Works really good!!

Regards!
David Rueter
David Rueter
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 573
Yes, as a matter of fact I do. Here is the CLR source (see below).

See another article I wrote "Deploying CLR Assemblies with T-SQL" for one way to deploy CLR assemblies like this one.


//------start of CLR Source------
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Net;
using System.IO;

public partial class OpsStreamProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void HTTPGet(
SqlString URL,
SqlString HTTPMethod,
SqlString ContentType,
SqlString DataToSend,
SqlString User,
SqlString Password,

out SqlInt32 HTTPStatus,
out SqlString HTTPStatusText,
out SqlBinary ResponseBinary,
out SqlString ErrorMsg

)
{
string thisURL = Convert.ToString(URL);
string thisHTTPMethod = Convert.ToString(HTTPMethod);
string thisContentType = Convert.ToString(ContentType);
string thisDataToSend = Convert.ToString(DataToSend);
string thisUser = Convert.ToString(User);
string thisPassword = Convert.ToString(Password);
string thisErrorMsg = new string();


byte[] binData = new byte[1];
byte[] buffer = new byte[4096];

Int32 responseStatusCode = 0;
string responseStatusDescription = null;

try
{
HttpWebRequest request = null;
HttpWebResponse response = null;
Stream responseStream = null;

request = (HttpWebRequest)WebRequest.Create(thisURL);

request.UserAgent = "SQL CLR Client";

if (thisHTTPMethod.Length == 0) {
request.Method = "GET";
}
else {
request.Method = thisHTTPMethod; //PUT/POST/GET/DELETE
}

request.ContentType = thisContentType;

if (thisDataToSend.Length > 0) {
thisErrorMsg = "thisDataToSend.Length > 0";
//convert string thisDataToSend to byte array
byte[] binSendData = System.Text.Encoding.Default.GetBytes(thisDataToSend);

//set ContentLength
request.ContentLength = binSendData.Length;

//get stream object for the request
Stream dataStream = request.GetRequestStream();

//write byte array to the stream
dataStream.Write (binSendData, 0, binSendData.Length);

//close the stream
dataStream.Close();
}
else {
request.ContentLength = 0;
}

response = (HttpWebResponse)request.GetResponse();

responseStream = response.GetResponseStream();

using(MemoryStream memoryStream = new MemoryStream())
{
int count = 0;
do
{
count = responseStream.Read(buffer, 0, buffer.Length);
memoryStream.Write(buffer, 0, count);

} while(count != 0);

binData = memoryStream.ToArray();

}

responseStatusCode = Convert.ToInt32(response.StatusCode);
responseStatusDescription = response.StatusDescription;

response.Close();
responseStream.Dispose();
}

catch (Exception ex)
{
SqlContext.Pipe.Send(ex.Message.ToString());
}

ResponseBinary = binData;

thisErrorMsg = "Hello World";

HTTPStatus = new SqlInt32(responseStatusCode);
HTTPStatusText = new SqlString(responseStatusDescription);
ErrorMsg = new SqlString(thisErrorMsg);

}
};
//------end of CLR Source------


johnmaynardjr
johnmaynardjr
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 14
Thank you, Great Article.
Do you have thoughts on if this would work with Bing Maps too ?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search