InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 23:56:12 GMT20http://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspxThanks,It works now.I just execute below things:EXEC sp_configure 'show advanced options';RECONFIGURE; EXEC sp_configure 'Ole Automation Procedures', 1;RECONFIGURE; Thanks a lot. :)Sun, 28 Oct 2012 22:44:36 GMTNaresh Parmarhttp://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspxSorry you are having problems. I just re-tested by downloading the unmodified Geocode.sql from the resources link at the end of the article and executed:[code="sql"]EXEC spGeocode '1234 N. Main Street, Santa Ana, CA'[/code]I got valid results back:33.755319 -117.867595 Santa Ana CA 92701 1234 N Main St ...(FYI, I just re-tested on SQL 2005 and 2008 R2)I suspect your database is not set to allow calls to OLE Automation procedures. (I tested this--if this is disabled, I do get a one-row resultset with nulls...along with an error message.)To enable, execute the following:EXEC sp_configure 'show advanced options';RECONFIGURE; EXEC sp_configure 'Ole Automation Procedures', 1;RECONFIGURE; If that is not the problem, you are likely dealing with some network issue--involving a proxy or filtering.Fri, 26 Oct 2012 01:32:17 GMTDavid Rueterhttp://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspxhi ,i have changed api url from v2 to v3.still getting null value after trying this : EXEC spGeocode '1234 N. Main Street, Santa Ana, CA'ThanksThu, 25 Oct 2012 22:32:30 GMTNaresh Parmarhttp://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspxthanx for the reply. I solved it with a little modification before seeing the reply... anywz, thnx again...:-)Wed, 24 Oct 2012 06:25:18 GMTbeetleshttp://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspxIt works on my system, I get:33.755319 -117.867595 Santa Ana CA 92701 1234 N Main Stbut I modified the code to use Google API ver. 3 instead of ver. 2 referred in the article, please see the post just above this one.Not sure though this is the reason, did you try to execute spGeocode with other parameter variations as described in the article?Regards,M.R.Tue, 23 Oct 2012 08:30:00 GMTmr-967650http://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspxHello,I would suggest to use Google API [b]ver. 3 [/b](the current one) rather than ver. 2 referred in this post. Therefore, the URL should be something like:[b]http://maps.googleapis.com/maps/api/geocode/[/b].....etc' - ver. 3instead of:[b]http://maps.google.com/maps/api/geocode/[/b] - ver. 2Tue, 23 Oct 2012 08:01:25 GMTmr-967650http://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspxHi.. I did this for reverse geocoding. I changed the url to: DECLARE @URL varchar(MAX) SET @URL = 'http://maps.google.com/maps/api/geocode/xml?sensor=false&latlng=' + CAST(@GPSLatitude AS VARCHAR(20)) + ',' + CAST(@GPSLongitude AS VARCHAR(20))But it shows null. What should I do to show the address for specific Lattitude and longitude using this techinique?Tue, 23 Oct 2012 01:27:44 GMTbeetleshttp://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspxhi all,i have created the SP, mention in this topic.when i execute the sp : EXEC spGeocode '1234 N. Main Street, Santa Ana, CA'i got NULL value in all the field.am i missed any previous step ???Thanks in advance.Mon, 22 Oct 2012 22:37:38 GMTNaresh Parmarhttp://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspxThank you!Mon, 23 Jul 2012 10:49:18 GMTcleonard 54659http://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspxThe link to the original script is under "Resources" at the bottom of the article ([url=http://www.sqlservercentral.com/articles/geocode/70061]http://www.sqlservercentral.com/articles/geocode/70061[/url])The direct link is: [url=http://www.sqlservercentral.com/Files/Geocode.sql/6085.sql]http://www.sqlservercentral.com/Files/Geocode.sql/6085.sql[/url]Fri, 20 Jul 2012 18:13:27 GMTDavid Rueterhttp://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspxDon't see the sp_geoCode script?Fri, 20 Jul 2012 16:08:51 GMTcleonard 54659http://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspxYeah, I've been through this POC before and what I've discovered is that even if you have the Google Maps Premier license, you still can't use it for this sort of potential "Batch Geo-Coding" without running the risk of getting your licenses disabled. That being said, if you have a funded project, there are companies like Melissa Data or Telogys (sp) that can provide web or local implementations for you to Geocode with. Personally my experience is with Melissa Data; they will provide SSIS wrappers around their GeoCoding API, with unlimited Geo Lookups, and at least Quarterly updates of their files. Being that we are .NET savvy based on this discussion, it should be easy enough to write a CLR wrapper around their API to deliver GeoCoding from T-SQL.Mon, 25 Jun 2012 10:17:52 GMTDeusExDatumhttp://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspxGreat article. very useful for geocoding data. Unfortunately we are limited in our use of the data. Yahoo appears the have the same "use of data" limitation as google.From Yahoo's terms of use page. (viii) store or allow end users to store map imagery, map data or geocoded location information from the Yahoo! Maps APIs for any future use; (ix) use the stand-alone geocoder for any use other than displaying Yahoo! Maps or displaying points on Yahoo! Maps; (x) publish or display, or allow other users to publish or display, any geocoded location information using any Yahoo! Maps APIs; or Oh well. Still a great article. . . .Fri, 08 Jun 2012 14:29:37 GMTjeff_ott_95132http://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspxI tried to change the "address" parameter in URL to "latlng", and pass a latitude, long value to stored procedure, but dont obtain nothing. anybody knows how to solve this?bsalazar@sksuministros.comSat, 03 Dec 2011 09:37:52 GMTpayasitofelizhttp://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspxI know I'm a little late in the conversation, but in case anyone stumbles on it and finds this helpful . . .First of all, thanks for the article. It was very helpful. It very much simplifies the task I was preparing for pulling latitude/longitude data. I ended up reworking your code into a function (for ease of multiple record updates without cursors), and chose to use Yahoo's Placefinder API instead of Google's API, for the reasons already mentioned by others in this thread. I also was having an issue with several records coming back with NULL values from google, even though I KNEW the urls were valid, as was the XML. The problem I was encountering had to do with the number of bytes returned in the XML (>4000). In these cases, the sp_OAGetProperty proc is not able to handle results >4000 bytes. This appears to be a hard limit, and no workaround or fix is available, at least for SQL 2008. The Yahoo XML file that's returned contains much less data than the Google API, and therefore processes all of my addresses just fine.The function code is attached. Note that you'll have to get a key from Yahoo (http://developer.yahoo.com/geo/placefinder/). Takes about 30 seconds to apply.Sample query using the function applied to an address table:select *from addr as a cross apply fnGeocode(a.addr,a.city, a.state, a.country,a.zip,null, null, null, null) as b --passes arguments to geocode functionTue, 23 Aug 2011 14:30:26 GMTbriandmorganhttp://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspxProcedure uses Geocoding API ver. 2 but can be updated in order to use Geocoding API ver. 3.Regards,M.R.Fri, 11 Feb 2011 09:47:53 GMTmr-967650http://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspxThe article is excellent. I appreciate the way it is explained.It work flawlessly for me.I want to mention that when I try this for bulk Geo coding passing the addresses by loop.Google block the IP for one day after the 2500 requestper day per IP or less.If the IPs are from same domain and if the per daylimit is reached from any perticular IP inside the that domain.it will be applicable to all the ip of that domain.Also we have we have incropoate a delay of 60 sec. after per request to get this 2500 Geocode per day .otherwise we will not get any data back.Thanks ,Raj ThakurFri, 22 Oct 2010 03:18:52 GMTraj.thakurhttp://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspxDear ShawnI've got the same result when running the sample of Geocoding. Have you got any update about this issue?ThxJesusWed, 13 Oct 2010 07:06:37 GMTChuskyhttp://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspxI always love to see alternatives ....But going back to use [i]the unstable [/i] sp_oa... is at least one bridge to far.There has been a nice article with a [b]CLR alternative on SQL2005[/b]: [url]http://www.sqlservercentral.com/articles/SS2K5+-+CLR+Integration/geocodingwithsqlserver2005/2373/[/url]It should still be a valid staring point for SQL2008 (R2) on this matter.Fri, 04 Jun 2010 08:01:50 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspxThank you for the info and the caveats.Mon, 24 May 2010 15:54:14 GMTrainermrilkehttp://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspx@rainermrilke:This geocoding web service can only process a single request at a time, so to process multiple rows you would need to loop through the rows to process (such as with a cursor): see below for a sample.Note that:1) you should check out Google's terms of use2) beware that there is a cap of 2,500 lookups per day from a single IP address (unless you make arangements with Google)3) you would want to modify spGeocode so that it did not return a resultset with each execution (i.e. remove the SELECT @GPSLatitude AS GPSLatitude.... statement)4) If you are thinking of updating your address data with the formatted address returned by the service, beware that[ul][li]the geocoding service may not succeed in encoding a specific address--and in such a case you may not want to overwrite your existing data, and[/li][li]you should probably review / enhance error handling to avoid any overwriting of data in the event of an error[/li][/ul][quote]SET NOCOUNT ONCREATE TABLE #temp ( RecordID int identity, Address varchar(50), City varchar(30), State varchar(5), GPSLat numeric(9,6), GPSLong numeric(9,6), MapURL varchar(255))INSERT INTO #temp (Address, City, State) VALUES ('1150 Magic Way', 'Anaheim', 'CA')INSERT INTO #temp (Address, City, State) VALUES ('8039 Beach Boulevard', 'Buena Park', 'CA')INSERT INTO #temp (Address, City, State) VALUES ('500 SeaWorld Drive', 'San Diego', 'CA')INSERT INTO #temp (Address, City, State) VALUES ('1 Legoland Drive', 'Carlsbad', 'CA')DECLARE curGeo CURSOR LOCAL STATIC FORSELECT RecordID, Address, City, StateFROM #tempDECLARE @RecordID intDECLARE @Address varchar(50)DECLARE @City varchar(30)DECLARE @State varchar(5)DECLARE @GPSLatitude numeric(9, 6)DECLARE @GPSLongitude numeric(9, 6)DECLARE @MapURL varchar(255)OPEN curGeoFETCH curGeo INTO @RecordID, @Address, @City, @StateWHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRY EXEC opsstream.sputilGeocode @Address = @Address OUTPUT, @City = @City OUTPUT, @State = @State OUTPUT, @GPSLatitude = @GPSLatitude OUTPUT, @GPSLongitude = @GPSLongitude OUTPUT, @MapURL = @MapURL OUTPUT UPDATE #temp SET GPSLat = @GPSLatitude, GPSLong = @GPSLongitude, MapURL = @MapURL WHERE RecordID = @RecordID END TRY BEGIN CATCH PRINT 'Warning: RecordID ' + CAST(@RecordID AS varchar(100)) + ' could not be geocoded.' END CATCH FETCH curGeo INTO @RecordID, @Address, @City, @StateENDSELECT * FROM #temp[/quote]Mon, 24 May 2010 15:17:25 GMTDavid Rueterhttp://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspxThanks for the article. How difficult would it be to pass table values to this stored procedure? I have been trying for a couple days and not getting it done. My attempt was to modify the procedure with a WHILE loop around a SELECT statement. Is there a way to check what the end result of the request looks like that was sent to the geocoder? This might help me with error checking.Mon, 24 May 2010 13:22:17 GMTrainermrilkehttp://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspx@keith-1057626: [url=http://www.sqlservercentral.com/articles/geocode/70061/]http://www.sqlservercentral.com/articles/geocode/70061/[/url]Sun, 23 May 2010 01:10:18 GMTsteve.darcyryanhttp://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspxwhat is the link to this article?Sat, 22 May 2010 18:08:45 GMTkeith-1057626http://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspx@malik.s.nisar: see this [b][url=http://code.google.com/apis/maps/documentation/geocoding/#ReverseGeocoding]Google documentation[/url][/b] for reverse geocoding information.The pattern should be similar: submit a request, receive the response, and then retrieve individual values via XQuery (i.e. @XML.value).Do you have specific questions about the procedure I can answer for you?Sat, 22 May 2010 11:14:13 GMTDavid Rueterhttp://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspx@Kim P: From [url=http://code.google.com/apis/maps/documentation/geocoding/#XML]documentation[/url] we see that the precision (accuracy) information is stored in the location_type XML node.You can define a parameter to store this information, such as:[quote]@LocationType varchar(40) OUTPUT[/quote]You can populate that parameter from the XML result like this: SET [quote]@LocationType = @XML.value('(/GeocodeResponse/result/geometry/location_type) [1]', 'varchar(40)')[/quote]Does this make sense?Sat, 22 May 2010 11:09:41 GMTDavid Rueterhttp://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspxCould anyone help me with the syntax for implementing this as a function rather than a stored procedure? I'd like to send a number of addresses to it at a time.Sat, 22 May 2010 03:14:24 GMTsteve.darcyryanhttp://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspxHello David Rueter,can u explain this Stored proceure in detail or can u help me to do reverse geocoding with sql server,i m trying to do this but not able to get any results,, Regards,Saqib NisarThu, 20 May 2010 22:47:49 GMTmalik.s.nisarhttp://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspxGreat article. Does anyone know if you can bring back the Accuracy of the address request in the stored procedure?Thu, 20 May 2010 10:38:33 GMTKim Phttp://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspxDavid, really enjoyed seeing how you've implemented this and you couldn't have had better timing, as I am currently developing a GeoCoding solution; but for the example used (Google Maps API), a word of caution from the licensing perspective.Using this implementation directly, the Google API (Free) only allows 2,500 requests from an IP address daily. So if you are corporate, and you max that out, you've potentially prevented everyone else in the building from making additional map requests for the day (Depending on your network setup, and whether or not you have anything else using the API).If you have a Google Maps Premier API License, you can enhance spGeoCode to sign the URL with your encrypted ClientID, but you are in violation of your license agreement, unless you work with Google when you set up your account and they give you the OK to cache the data.From Google Maps/Google Earth APIs Terms of Service (I put in the sections pertaining to data):[b]10. License Restrictions[/b]10.3 pre-fetch, cache, or store any Content, except that you may store limited amounts of Content for the purpose of improving the performance of your Maps API Implementation if you do so temporarily, securely, and in a manner that does not permit use of the Content outside of the Service;10.6 use the Service in a manner that gives you or any other person access to mass downloads or bulk feeds of any Content, including but not limited to numerical latitude or longitude coordinates, imagery, and visible map data;Just wanted everyone to give everyone a heads up, as being Data professionals, we don't typically work with just one or two rows!:-)Wed, 19 May 2010 14:48:21 GMTDeusExDatumhttp://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspxI dont think it is a firewall issue.[img]http://i1029.photobucket.com/albums/y356/shawnmccabewellsfargo/Image3.jpg[/img]Wed, 19 May 2010 14:07:14 GMTshawn mccabehttp://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspxGreat article. Works like a charm. I do have some reservations using OLE Automation due to security concerns and the ability to troubleshoot when something goes wrong.Google will give you a status of 'OVER QUERY LIMIT' when you try to put this inside of some loop. The only way I was able to get around that was by putting in a 2 second pause and try that particular row again.Others mentioned using the CLR to accomplish this task. The issue I came up with was when trying to do an HTTP call to Google it required me to use the System.Web class which SQL Server did not like unless I disabled all sorts of controls. Anybody been able to accomplish this using the CLR?J.D.Wed, 19 May 2010 14:01:36 GMTJ.D. Gonzalezhttp://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspxBack to the idea that it is a firewall issue - I tried adding this before the open and still no luck (still getting null back). EXEC @Result = sp_OAMethod @Obj, 'setProxy', 2, 'myProxyServer:8080','' EXEC @Result = sp_OAMethod @Obj, 'setProxyCredentials', 'myAccount, 'myPassword'I may pursue a C# path.Wed, 19 May 2010 13:56:05 GMTIdea Deadbeathttp://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspxGrasshooper,I down loaded the code as well and then executed it.EXEC spGeocode @Address = '1234 N. Main Street', @City = 'Santa Ana', @State = 'CA'And this is what I get back any ideas on how to fix it.?[img]http://i1029.photobucket.com/albums/y356/shawnmccabewellsfargo/Image2.jpg[/img]Wed, 19 May 2010 13:49:39 GMTshawn mccabehttp://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspxNice article and great discussion.Wed, 19 May 2010 11:57:27 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspx@Idea Deadbeat: Sorry you are having problems. I tried your examples, and they worked fine here.I would concur with your suspicions about a firewall blocking outbound HTTP connections from your server.Wed, 19 May 2010 10:17:36 GMTDavid Rueterhttp://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspx@thomas.schmidt: Thank you for the feedback.Your experience with "WinHTTP.WinHTTPRequest.5.1" is interesting. The information I can find suggests that "MSXML2.ServerXMLHTTP" is the right object for servers. See: [url=http://msdn.microsoft.com/en-us/library/ms762278(v=VS.85).aspx ]http://msdn.microsoft.com/en-us/library/ms762278(v=VS.85).aspx [/url]I'm not arguing your experience, and there could have been / could be a bug of which I am not aware.[quote]The ServerXMLHTTP object offers functionality similar to that of the XMLHTTP object. Unlike XMLHTTP, however, the ServerXMLHTTP object does not rely on the WinInet control for HTTP access to remote XML documents. ServerXMLHTTP uses a new HTTP client stack. Designed for server applications, this server-safe subset of WinInet offers the following advantages...[/quote] Wed, 19 May 2010 10:13:58 GMTDavid Rueterhttp://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspxWe 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'orEXEC spGeocode @PostalCode = '98405'Wed, 19 May 2010 10:05:19 GMTIdea Deadbeathttp://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspx@chouse: Yes, that is my recollection about Yahoo as well, though I haven't reviewed their policy lately.Wed, 19 May 2010 10:01:22 GMTDavid Rueterhttp://www.sqlservercentral.com/Forums/Topic924032-2700-1.aspx@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?Wed, 19 May 2010 09:57:50 GMTDavid Rueter