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 1 of 6

1 2 3 4 5 »»»

Geocode Addresses in T-SQL

Rate Topic

Display Mode

Topic Options

Author

Message

David Rueter

Posted Tuesday, May 18, 2010 9:36 PM

SSC Rookie

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

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

Post #924032

sfriend

Posted Tuesday, May 18, 2010 11:28 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 19, 2010 1:15 PM
Points: 1, Visits: 11

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.

Post #924059

thomas.schmidt

Posted Tuesday, May 18, 2010 11:52 PM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, January 20, 2013 11:49 PM
Points: 3, Visits: 206

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 :)

Post #924064

maciej.pilecki

Posted Wednesday, May 19, 2010 1:47 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 12, 2013 6:32 AM
Points: 1, Visits: 56

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

Post #924100

Laurent Michaud

Posted Wednesday, May 19, 2010 2:14 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 04, 2010 2:00 AM
Points: 44, Visits: 38

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

Post #924113

kll

Posted Wednesday, May 19, 2010 2:46 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, February 02, 2012 7:53 AM
Points: 10, Visits: 53

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.

Post #924125

SQLEE

Posted Wednesday, May 19, 2010 8:08 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 21, 2012 8:20 AM
Points: 46, Visits: 161

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.

Post #924319

Kevin-214685

Posted Wednesday, May 19, 2010 8:10 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 19, 2010 4:18 PM
Points: 32, Visits: 12

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.

Post #924320

Tom Garth

Posted Wednesday, May 19, 2010 8:21 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, February 04, 2011 7:20 AM
Points: 977, Visits: 1,499

An excellent article, and very well written.

Thanks

Tom Garth
Vertical Solutions

"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

Post #924334

arcain

Posted Wednesday, May 19, 2010 9:06 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, January 11, 2013 1:14 PM
Points: 31, Visits: 55

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.

Post #924378

« Prev Topic | Next Topic »

52 posts, Page 1 of 6

1 2 3 4 5 »»»

Permissions