Geocode Addresses in T-SQL

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by David Rueter » Geocode Addresses in T-SQL

52 posts, Page 2 of 6

««1 2 3 4 5 »»»

Geocode Addresses in T-SQL

Rate Topic

Display Mode

Topic Options

Author

Message

Idea Deadbeat

Posted Wednesday, May 19, 2010 9:50 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 17, 2013 9:53 AM
Points: 14, Visits: 247

I implemented the code (including reconfigure for OLE auto and show advanced) and the procedure returns nulls - I used example address from article. I took the URL from the procedure and passed an address (zip code) and it returned the XML.

Is there something not allowing server to get to google service.

Post #924423

chouse

Posted Wednesday, May 19, 2010 9:56 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, December 01, 2010 8:18 PM
Points: 13, Visits: 23

For what it's worth, Yahoo has a free REST based geocoding API that doesn't (or least didn't a few years ago when I was using it) have the restriction about displaying the results on a map. As I recall, Yahoo had a restriction of something like 5000 lookups per day per source IP address.

Post #924436

David Rueter

Posted Wednesday, May 19, 2010 9:57 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 11:22 PM
Points: 44, Visits: 357

@Idea Deadbeat: Thanks for your interest. I just downloaded and executed the attached code (in a new clean test database), and it worked as expected.

Google shouldn't block anything as long as there are no more than 2,400 requests from the same IP address in a 24-hour period. (I am speaking only to technical limits per the Google documentation. Understanding and complying with Google's usage policy is up to you.)

Can you provide the exact command you tried to execute so that I can test it here?

Post #924438

David Rueter

Posted Wednesday, May 19, 2010 10:01 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 11:22 PM
Points: 44, Visits: 357

@chouse: Yes, that is my recollection about Yahoo as well, though I haven't reviewed their policy lately.

Post #924442

Idea Deadbeat

Posted Wednesday, May 19, 2010 10:05 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 17, 2013 9:53 AM
Points: 14, Visits: 247

We think it might be some firewall rule - but here are some commands I tried:

EXEC spGeocode @Address = '1234 N. Main Street', @City = 'Santa Ana', @State = 'CA'
or
EXEC spGeocode @PostalCode = '98405'

Post #924452

David Rueter

Posted Wednesday, May 19, 2010 10:13 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 11:22 PM
Points: 44, Visits: 357

@thomas.schmidt: Thank you for the feedback.

Your experience with "WinHTTP.WinHTTPRequest.5.1" is interesting. The information I can find suggests that "MSXML2.ServerXMLHTTP" is the right object for servers. See: http://msdn.microsoft.com/en-us/library/ms762278(v=VS.85).aspx

I'm not arguing your experience, and there could have been / could be a bug of which I am not aware.

The ServerXMLHTTP object offers functionality similar to that of the XMLHTTP object. Unlike XMLHTTP, however, the ServerXMLHTTP object does not rely on the WinInet control for HTTP access to remote XML documents. ServerXMLHTTP uses a new HTTP client stack. Designed for server applications, this server-safe subset of WinInet offers the following advantages...

Post #924469

David Rueter

Posted Wednesday, May 19, 2010 10:17 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 11:22 PM
Points: 44, Visits: 357

@Idea Deadbeat: Sorry you are having problems. I tried your examples, and they worked fine here.

I would concur with your suspicions about a firewall blocking outbound HTTP connections from your server.

Post #924472

SQLRNNR

Posted Wednesday, May 19, 2010 11:57 AM

SSCoach

Group: General Forum Members
Last Login: Today @ 9:03 AM
Points: 18,736, Visits: 12,334

Nice article and great discussion.

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #924553

shawn mccabe

Posted Wednesday, May 19, 2010 1:49 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, November 01, 2010 4:49 PM
Points: 2, Visits: 28

Grasshooper,
I down loaded the code as well and then executed it.
EXEC spGeocode @Address = '1234 N. Main Street', @City = 'Santa Ana', @State = 'CA'

And this is what I get back any ideas on how to fix it.?

Post #924628

Idea Deadbeat

Posted Wednesday, May 19, 2010 1:56 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 17, 2013 9:53 AM
Points: 14, Visits: 247

Back to the idea that it is a firewall issue - I tried adding this before the open and still no luck (still getting null back).

EXEC @Result = sp_OAMethod @Obj, 'setProxy', 2, 'myProxyServer:8080',''
EXEC @Result = sp_OAMethod @Obj, 'setProxyCredentials', 'myAccount, 'myPassword'

I may pursue a C# path.

Post #924634

« Prev Topic | Next Topic »

52 posts, Page 2 of 6

««1 2 3 4 5 »»»

Permissions