Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Geocode Addresses in T-SQL Expand / Collapse
Author
Message
Posted Wednesday, May 19, 2010 9:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 11:04 AM
Points: 14, Visits: 264
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
Posted Wednesday, May 19, 2010 9:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 1, 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
Posted Wednesday, May 19, 2010 9:57 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 4:22 PM
Points: 52, Visits: 405
@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
Posted Wednesday, May 19, 2010 10:01 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 4:22 PM
Points: 52, Visits: 405
@chouse: Yes, that is my recollection about Yahoo as well, though I haven't reviewed their policy lately.
Post #924442
Posted Wednesday, May 19, 2010 10:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 11:04 AM
Points: 14, Visits: 264
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
Posted Wednesday, May 19, 2010 10:13 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 4:22 PM
Points: 52, Visits: 405
@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
Posted Wednesday, May 19, 2010 10:17 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 4:22 PM
Points: 52, Visits: 405
@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
Posted Wednesday, May 19, 2010 11:57 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:29 PM
Points: 21,209, Visits: 14,901
Nice article and great discussion.



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


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
Posted Wednesday, May 19, 2010 1:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 1, 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
Posted Wednesday, May 19, 2010 1:56 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 11:04 AM
Points: 14, Visits: 264
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 »

Add to briefcase ««12345»»»

Permissions Expand / Collapse