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 Tuesday, May 18, 2010 9:36 PM
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
Comments posted to this topic are about the item Geocode Addresses in T-SQL
Post #924032
Posted Tuesday, May 18, 2010 11:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, May 18, 2010 11:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 4:26 AM
Points: 3, Visits: 208
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
Posted Wednesday, May 19, 2010 1:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 23, 2013 2:41 AM
Points: 1, Visits: 63
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
Posted Wednesday, May 19, 2010 2:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 4, 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
Posted Wednesday, May 19, 2010 2:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 2, 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
Posted Wednesday, May 19, 2010 8:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 19, 2014 2:32 PM
Points: 49, Visits: 178
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
Posted Wednesday, May 19, 2010 8:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Wednesday, May 19, 2010 8:21 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 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
Posted Wednesday, May 19, 2010 9:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 4:07 PM
Points: 31, Visits: 60
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 »

Add to briefcase 12345»»»

Permissions Expand / Collapse