Geocode Addresses in T-SQL

  • David Rueter

    SSCrazy

    Points: 2632

    Comments posted to this topic are about the item Geocode Addresses in T-SQL

  • sfriend

    SSC Journeyman

    Points: 89

    Great article, in theory. Loved the creative use of the ActiveX HTTPRequest object and parsing of XML.

    I have not really done any work in SQL Server in over a year, almost 2 years I guess. But about 8 years ago (with 2000) I had made HTTP calls through a shell to goecode, but it was very heavy.

    Now, I said "in theory" for two reasons. It is rare that a DBA will grant a developer access to embed ActiveX links to a production database. (I agree that shelling is just as risky). The other reason is it assumes that the SQL box has outbound internet access. This assumption is against any security best practices that I am aware of. Granted, it can be secured by firewall, but the SQL box should be on the LAN side, and if a webserver is involved it should have external and internal NICs.

    Personally, for security reasons I would recommend the geocoder in a seperate app that walks a filtered recordset where geocode_lat is null and geocode_long is null. Also, this will allow for error handling in a seperate program without bogging down the SQL box. (also be cautious of locks during the update process)

    Just my 2 cents - but again, loved the creative usage.

  • thomas.schmidt

    SSC Journeyman

    Points: 91

    Good article, i have done something similar a few years back but got burned badly by MSXML as using it killed off several SQL Servers over time with a tiny memory leak/handle leak. We got MS support on the case and after heavy debugging of live production servers the culprit was found to be MSXML, even when using it in ServerXMLHttp "mode" which is supposedly the way to use it properly.

    I would very much suggest switching to "WinHTTP.WinHTTPRequest.5.1" instead if it needs to be done in pure sql as that fixed our problem and was what MS recommended for this kind of use. Or you could switch to a custom C# CLR based stored procedure for this 🙂

  • maciej.pilecki

    Valued Member

    Points: 71

    Good idea, bad implementation.

    Don't use sp_OA, make Web calls from SQLCLR instead - everything will be better, from security to performance.

    I have implemented something like this myself and the idea of using OLE has never even crossed my mind - it was done purely in C#.

    Regards,

    Maciej

  • Laurent Michaud

    SSC Enthusiast

    Points: 190

    Hi !

    I agree with Maciej, this should be done with SQL CLR. Personally I have made something similar for my company, except that it calls Navteq Web Services and stores geocoding results in a column of type "geography".

    Regards,

    Laurent

  • kll

    SSC Veteran

    Points: 250

    Nice little article.

    Implementers must be aware that if you ask for too many addresses in this manner, Google will come after you, asking for money. The only way to get around this is if your service is public and unpaid.

  • SQLEE

    Ten Centuries

    Points: 1077

    I agree with kll but I don't think microsoft would care if instead you made the call to bing.com.

    One friend made a comment to me that with all the work that is been done by SQL Server & Oracle in spatial data storage he wonders how long ESRI SDE technology is going to be around.

  • Kevin-214685

    SSC Veteran

    Points: 230

    Yes, you definitely want to check the terms and conditions of the service - including the part where it says you can't store that information you get back in any form.

  • Tom Garth

    SSCertifiable

    Points: 6173

    An excellent article, and very well written.

    Thanks

    Tom Garth
    Vertical Solutions[/url]

    "There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
  • arcain

    SSC Journeyman

    Points: 99

    I just wanted to mention the fine print that Google includes at http://code.google.com/apis/maps/documentation/geocoding/. As it stands, what this article does would breach Google license restrictions that state that use of the geocoding service is only allowed if you are also displaying a map. So, unless the map URL is used and the map view is rendered, Google may ban you from using the API. Of course, Google was only used as an example here, but if someone implemented this exactly, they might find their API call suddenly doesn't work.

  • Idea Deadbeat

    SSC-Addicted

    Points: 416

    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.

  • chouse

    SSC Rookie

    Points: 47

    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.

  • David Rueter

    SSCrazy

    Points: 2632

    @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?

  • David Rueter

    SSCrazy

    Points: 2632

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

  • Idea Deadbeat

    SSC-Addicted

    Points: 416

    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'

Viewing 15 posts - 1 through 15 (of 70 total)

You must be logged in to reply to this topic. Login to reply