Geocode Addresses in T-SQL
-
@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...
-
@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.
-
Nice article and great discussion.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
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.?
-
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.
-
Great article. Works like a charm. I do have some reservations using OLE Automation due to security concerns and the ability to troubleshoot when something goes wrong.
Google will give you a status of 'OVER QUERY LIMIT' when you try to put this inside of some loop. The only way I was able to get around that was by putting in a 2 second pause and try that particular row again.
Others mentioned using the CLR to accomplish this task. The issue I came up with was when trying to do an HTTP call to Google it required me to use the System.Web class which SQL Server did not like unless I disabled all sorts of controls. Anybody been able to accomplish this using the CLR?
J.D.
-
I dont think it is a firewall issue.
-
David, really enjoyed seeing how you've implemented this and you couldn't have had better timing, as I am currently developing a GeoCoding solution; but for the example used (Google Maps API), a word of caution from the licensing perspective.
Using this implementation directly, the Google API (Free) only allows 2,500 requests from an IP address daily. So if you are corporate, and you max that out, you've potentially prevented everyone else in the building from making additional map requests for the day (Depending on your network setup, and whether or not you have anything else using the API).
If you have a Google Maps Premier API License, you can enhance spGeoCode to sign the URL with your encrypted ClientID, but you are in violation of your license agreement, unless you work with Google when you set up your account and they give you the OK to cache the data.
From Google Maps/Google Earth APIs Terms of Service (I put in the sections pertaining to data):
10. License Restrictions
10.3 pre-fetch, cache, or store any Content, except that you may store limited amounts of Content for the purpose of improving the performance of your Maps API Implementation if you do so temporarily, securely, and in a manner that does not permit use of the Content outside of the Service;
10.6 use the Service in a manner that gives you or any other person access to mass downloads or bulk feeds of any Content, including but not limited to numerical latitude or longitude coordinates, imagery, and visible map data;
Just wanted everyone to give everyone a heads up, as being Data professionals, we don't typically work with just one or two rows!:-)
Josh Lewis
-
Great article. Does anyone know if you can bring back the Accuracy of the address request in the stored procedure?
-
Hello David Rueter,
can u explain this Stored proceure in detail or can u help me to do reverse geocoding with sql server,
i m trying to do this but not able to get any results,,
Regards,
Saqib Nisar
-
Could anyone help me with the syntax for implementing this as a function rather than a stored procedure? I'd like to send a number of addresses to it at a time.
-
@Kim P:
From documentation we see that the precision (accuracy) information is stored in the location_type XML node.
You can define a parameter to store this information, such as:
@LocationType varchar(40) OUTPUT
You can populate that parameter from the XML result like this: SET
@LocationType = @XML.value('(/GeocodeResponse/result/geometry/location_type) [1]', 'varchar(40)')
Does this make sense?
-
@malik.s.nisar: see this Google documentation for reverse geocoding information.
The pattern should be similar: submit a request, receive the response, and then retrieve individual values via XQuery (i.e. @XML.value).
Do you have specific questions about the procedure I can answer for you?
-
what is the link to this article?
-
@keith-1057626: http://www.sqlservercentral.com/articles/geocode/70061/[/url]
Viewing 15 posts - 16 through 30 (of 70 total)
You must be logged in to reply to this topic. Login to reply