Geocode Addresses in T-SQL

Question

Geocode Addresses in T-SQL

David Rueter

SSCrazy

Points: 2661
More actions
May 18, 2010 at 9:36 pm

#224789

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

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

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

sfriend SSC Journeyman Points: 89 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

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 More actions · Answer 5

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 More actions · Answer 6

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 More actions · Answer 7

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: 6193 More actions · Answer 8

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 More actions · Answer 9

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 More actions · Answer 10

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 More actions · Answer 11

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: 2661 More actions · Answer 12

@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: 2661 More actions · Answer 13

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

Idea Deadbeat SSC-Addicted Points: 416 More actions · Answer 14

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'