SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Geocode Addresses in T-SQL


Geocode Addresses in T-SQL

Author
Message
David Rueter
David Rueter
SSChasing Mays
SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)

Group: General Forum Members
Points: 626 Visits: 574
Comments posted to this topic are about the item Geocode Addresses in T-SQL
sfriend
sfriend
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 13
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
thomas.schmidt
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 212
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 Smile
maciej.pilecki
maciej.pilecki
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 80
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
Laurent Michaud
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 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
kll
kll
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 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.
SQLEE
SQLEE
SSC Veteran
SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)

Group: General Forum Members
Points: 279 Visits: 207
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
Kevin-214685
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 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.
Tom Garth
Tom Garth
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2039 Visits: 1499
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

arcain
arcain
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 61
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search