SQLServerCentral / Discuss Content Posted by Corey Bunch / Article Discussions / Article Discussions by Author / Geocoding with SQL Server 2005 / Latest Posts

RE: Geocoding with SQL Server 2005

proleget — Fri, 30 Dec 2011 06:42:20 GMT

[quote][b]Allen McGuire (6/26/2008)[/b][hr]I personally would not use this method to do what you are trying to do. If you have a web server that can run ASP, I would simply make a database call to get the customers and their address data, then call the following function with as much or little address information as you wish:Function GetGoogleCoordsCSV(Query) GoogleMapKey = "your Google key" url = "http://maps.google.com/maps/geo?output=csv&key=" & GoogleMapKey & "&q=" & Replace(Query,"#","") set csvhttp = Server.CreateObject("Msxml2.ServerXMLHTTP") csvhttp.open "GET", url, false csvhttp.send "" csvResult = Split(csvhttp.responseText, ",") ' The first number is the status code, ' the second is the accuracy, ' the third is the latitude, ' the fourth one is the longitude. ' 0 Unknown location. ' 1 Country level accuracy. ' 2 Region (state, province, prefecture, etc.) level accuracy. ' 3 Sub-region (county, municipality, etc.) level accuracy. ' 4 Town (city, village) level accuracy. ' 5 Post code (zip code) level accuracy. ' 6 Street level accuracy. ' 7 Intersection level accuracy. ' 8 Address level accuracy. Precision = csvResult(1) Longitude = csvResult(3) Latitude = csvResult(2)End FunctionThen just run an update statement in the ASP to update the customer coordinates. If you need more information let me know. Here is the code for the main ASP page:<%strSQL = "SELECT CustomerID, Address1, City, State, Zip FROM Customer"Set oRs = oConn.Execute(strSQL)Do While Not oRs.EOF CustomerID = orS("CustomerID") Address1 = oRs("Address1") City = oRs("City") State = oRs("State") Zip = oRs("Zip") If Address1 <> "" and City <> "" Then ' no sense in geocoding blank address Call GetGoogleCoordsCSV(Replace(Address1,"#","") & " " & City & " " & State) oConn.Execute("UPDATE Customer SET Latitude = '" & Latitude & "', Longitude = '" & Longitude & "', Modified = GETDATE() WHERE CustomerID = " & CustomerID & "") End If oRs.MoveNextLoop%>[/quote]thanks for the code! works great! is it possible to do the same in aspx, c#? i'm not really familiar with .net.

RE: Geocoding with SQL Server 2005

juanvi — Tue, 12 May 2009 03:25:46 GMT

Hi,How can I store the Latitude and Longitude results in a table, the stored procedure returns any value ?Thanks

RE: Geocoding with SQL Server 2005

mklausmeyer3 — Sun, 18 Jan 2009 11:09:53 GMT

Thank you so much for the help. I'll give it a try.

RE: Geocoding with SQL Server 2005

scb — Sun, 18 Jan 2009 10:52:54 GMT

If you go to File, New Project, then you should have various options, including database projects, business intelligence projects, etc. If you do not see these options, stick in the SQL Server 2008 CD, and install Workstation Components, and make sure Business Intelligence Development Studio (BIDS) or whatever they're calling it now is installed. This will install the appropriate project types. Then Start, All Programs, Microsoft SQL Server, BIDS.

RE: Geocoding with SQL Server 2005

mklausmeyer3 — Sat, 17 Jan 2009 14:00:39 GMT

Yes, I have tried but when I open a new solution there are no templates except "blank solution". I was hoping someone has created this stored procedure with visual studio 2008 and knew what to do next.Thank you for any help.

RE: Geocoding with SQL Server 2005

scb — Sat, 17 Jan 2009 10:54:01 GMT

Have you tried the steps in the article? I haven't tried with 2008 but I cannot imagine it would be much different.

RE: Geocoding with SQL Server 2005

mklausmeyer3 — Sat, 17 Jan 2009 10:11:42 GMT

Does anyone know how to add this stored procedure using visual studio 2008?any help would be appreciated.

RE: Geocoding with SQL Server 2005

mklausmeyer3 — Sun, 11 Jan 2009 20:49:42 GMT

Can I compile this using visual studio 2008 express? If not does any one know what I can use if I don't have visual studio 2005?

RE: Geocoding with SQL Server 2005

mklausmeyer3 — Sun, 11 Jan 2009 10:38:55 GMT

Thank you for the help. I only have vwd 2008 and it wont work by following the article. I guess I'll have to purchase visual studio to get the stored procedure in my database unless someone knows of another way.Thank you for the help.

RE: Geocoding with SQL Server 2005

scb — Sun, 28 Dec 2008 18:25:16 GMT

Mikek - unfortunately it is not as simple as copy/paste. If you have visual studio you can just follow the directions in the article, and check out everyone's feedback on the forums, as others have found better ways to do things. If you do not have visual studio, you can do a command line compile if you wish, but I think you will still need some parts of visual studio or .net to at least compile the c#.After it's compiled is when you load it into sql server - which you can do with sql server management studio.

RE: Geocoding with SQL Server 2005

mklausmeyer3 — Sun, 28 Dec 2008 11:53:41 GMT

How do I make this work for me? Do I just paste the code as a stored procedure in my sql 2005 database?Thank you for any help in advance.Mikek

RE: Geocoding with SQL Server 2005

mklausmeyer3 — Sun, 12 Oct 2008 09:07:18 GMT

Can I do this in sql server management express instead of visual studio?

RE: Geocoding with SQL Server 2005

lima_2001 — Fri, 01 Aug 2008 06:00:46 GMT

Hello Thanks for your mailI did have an address and found a workaround, namely calling my procedure from another one. I can now create Google maps based on the latitude/longitud data. I have created a reporting service report and it works fine from visual studio, But when I deploy it to my report server and call it from a browser I get an error from Google saying "Bad RequestYour client has issued a malformed or illegal request. "Have tried calling the google static function from reporting services? How can I avoid this error?

RE: Geocoding with SQL Server 2005

Allen McGuire — Thu, 31 Jul 2008 19:21:14 GMT

Hopefully I understood you correctly - if not let me know. Maybe even post the code of whatever isn't working.Allenhttp://www.madtownlounge.comLocate Live Music

RE: Geocoding with SQL Server 2005

Allen McGuire — Thu, 31 Jul 2008 19:19:57 GMT

If you don't have an address, don't send anything - not even a question mark. Google's API will return lat/long still. I've done it many times with just city/state pairs.

RE: Geocoding with SQL Server 2005

lima_2001 — Thu, 31 Jul 2008 18:57:29 GMT

Hello I also enjoyed using this article and all works except that I get an error in Visual studio when I try to use the store procedure with a dynamic addressm(it just refuses to let me use the ?-mark and says I have a syntax error)this is the sentence i useEXEC [dbo].[SPGeocode] @Address = ?, @City = NULL, @State = NULL, @appid = N'MyyahooId'I am trying to get the latitud/longitude for a number of addresses. I call the procedure from within a FOR each LOOP. If I use a given address it works. I also worte a standard procedure that takes a text and inserts a record. It also works with my ?-mark.What do I need to do to be able to use a dynamic input for the Address parameter?regards lima

RE: Geocoding with SQL Server 2005

nik.hall — Thu, 26 Jun 2008 09:24:10 GMT

Hmm going to try all reccomendations and see what I can understand / get working.To be honest I'm used to doing fairly simple stuff with SQL2000 and vb.net so this is all a bit scary atm and my first venture into SQL2005I cant thank you guys enough for helping though, I will let you know how I get on...Cheers,Nik

RE: Geocoding with SQL Server 2005

Allen McGuire — Thu, 26 Jun 2008 09:13:42 GMT

I personally would not use this method to do what you are trying to do. If you have a web server that can run ASP, I would simply make a database call to get the customers and their address data, then call the following function with as much or little address information as you wish:Function GetGoogleCoordsCSV(Query) GoogleMapKey = "your Google key" url = "http://maps.google.com/maps/geo?output=csv&key=" & GoogleMapKey & "&q=" & Replace(Query,"#","") set csvhttp = Server.CreateObject("Msxml2.ServerXMLHTTP") csvhttp.open "GET", url, false csvhttp.send "" csvResult = Split(csvhttp.responseText, ",") ' The first number is the status code, ' the second is the accuracy, ' the third is the latitude, ' the fourth one is the longitude. ' 0 Unknown location. ' 1 Country level accuracy. ' 2 Region (state, province, prefecture, etc.) level accuracy. ' 3 Sub-region (county, municipality, etc.) level accuracy. ' 4 Town (city, village) level accuracy. ' 5 Post code (zip code) level accuracy. ' 6 Street level accuracy. ' 7 Intersection level accuracy. ' 8 Address level accuracy. Precision = csvResult(1) Longitude = csvResult(3) Latitude = csvResult(2)End FunctionThen just run an update statement in the ASP to update the customer coordinates. If you need more information let me know. Here is the code for the main ASP page:<%strSQL = "SELECT CustomerID, Address1, City, State, Zip FROM Customer"Set oRs = oConn.Execute(strSQL)Do While Not oRs.EOF CustomerID = orS("CustomerID") Address1 = oRs("Address1") City = oRs("City") State = oRs("State") Zip = oRs("Zip") If Address1 <> "" and City <> "" Then ' no sense in geocoding blank address Call GetGoogleCoordsCSV(Replace(Address1,"#","") & " " & City & " " & State) oConn.Execute("UPDATE Customer SET Latitude = '" & Latitude & "', Longitude = '" & Longitude & "', Modified = GETDATE() WHERE CustomerID = " & CustomerID & "") End If oRs.MoveNextLoop%>

RE: Geocoding with SQL Server 2005

zootie — Thu, 26 Jun 2008 08:53:03 GMT

You have mostly 2 options. * Use a couple cursors (lookup how to use cursors in BOL) to iterate your source table and the return from the SP (you can dump the output of the SP into a temp table using an INSERT statement or you can use OpenRowSet)* Use the TVF I posted on 2006/05/10 in page 3. And see my 2007/02/09 post (same page) on how to use it for multiple hits.

RE: Geocoding with SQL Server 2005

nik.hall — Thu, 26 Jun 2008 03:12:57 GMT

Guys, This is all a bit advanced for me but I have gotten to the stage where I have SPGecode sproc working which is brill but...What I would ideally like to do is use it somehow to update a table of customer addresses with the lat/long returned from the sproc. Could someone be kind and put me out of my misery as to how I might be able to do this?I am thinking another sproc which updates the table with the lat/long returned from the SPGeocode on a row by row basis????Any help would really be appreciated.Nik

RE: Geocoding with SQL Server 2005

Allen McGuire — Wed, 20 Feb 2008 10:49:56 GMT

Both Yahoo and Google return "precision" values, so it's all about how you handle the returned data.The big difference for me in choosing my provider is that with Google you get like 50,000 hits per day; with Yahoo! last I heard was more like 5,000. I just use Yahoo! as a backup in case Google doesn't return me any data.

RE: Geocoding with SQL Server 2005

zootie — Wed, 20 Feb 2008 10:35:03 GMT

I haven't run data using both providers. My experiences with Google are from semi-manually running maps in Google Maps and Google Earth. Yahoo seems more forgiving and to try harder to return an approximate geocode. Also, since you get a "scope" out of the Yahoo API, it would return an area geocode (zip or city) when Google would just fail. The age of the data might also be a factor: It was funny getting Google maps with properties that were over hills or parking lots. The geocode (Yahoo and/or Google's) would be accurate, but the map would be incorrect because the road data and satellite imagery hadn't been updated.

RE: Geocoding with SQL Server 2005

ALZDBA — Mon, 11 Feb 2008 07:41:38 GMT

I've just used it for testing purposes to get a feeling with as well CLR as geocoding.I didn't compare the various providers. Maybe others did or are using it in a production environment.

RE: Geocoding with SQL Server 2005

Sam Stange — Fri, 08 Feb 2008 16:03:46 GMT

I've been geocoding assets using a variety of engines and I've been surprised that yahoo's engine has been more accurate than Google's geocoding service. Has anyone else noticed this?

RE: Geocoding with SQL Server 2005

Sam Stange — Wed, 12 Dec 2007 13:02:06 GMT

Here's what you need for google maps:using System;using System.IO;using System.Net;using System.Xml;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;using System.Globalization;namespace Geocoders{ public partial class GoogleGeocoder { [Microsoft.SqlServer.Server.SqlProcedure] public static void GetGoogleGeocode(string address, out double latitude, out double longitude, out double range) { Stream stream = null; XmlDocument doc; HttpWebRequest req; string text = ""; try { req = (HttpWebRequest)WebRequest.Create("http://maps.google.com/maps?output=kml&q=" + address); HttpWebResponse response = (HttpWebResponse)req.GetResponse(); stream = response.GetResponseStream(); StreamReader sr = new StreamReader(stream); text = sr.ReadToEnd(); sr.Close(); if (String.IsNullOrEmpty(text)) { latitude = 0; longitude = 0; range = 0; } else { doc = new XmlDocument(); doc.LoadXml(text); XmlNodeList longitudes = doc.GetElementsByTagName("longitude"); XmlNodeList latitudes = doc.GetElementsByTagName("latitude"); XmlNodeList ranges = doc.GetElementsByTagName("range"); NumberFormatInfo NFormat = new NumberFormatInfo(); NumberFormatInfo NFormatInt = new NumberFormatInfo(); NFormat.NumberDecimalSeparator = "."; double.TryParse(latitudes[0].InnerText, NumberStyles.Float, NFormat, out latitude); double.TryParse(longitudes[0].InnerText, NumberStyles.Float, NFormat, out longitude); double.TryParse(ranges[0].InnerText, NumberStyles.Float, NFormat, out range); } } catch { text = null; latitude = 0; longitude = 0; range = 0; } finally { } } }}

RE: Geocoding with SQL Server 2005

Allen McGuire — Tue, 11 Sep 2007 15:18:00 GMT

Great post - I was looking for this very thing. Question: how can I do the same using Google's API? Since Yahoo only allows 5K and Google allows 50K/day, I would like to see the same sort of example but using Google's API. I'm trying to find out how to do it myself, but I'm not real handy with C# just yet.

RE: Geocoding with SQL Server 2005

Crasz — Thu, 19 Apr 2007 11:50:00 GMT

I'm also getting the message that the stored procedure ran successfully but I'm not getting any results. Could a firewall and/or proxy restrict this from truly running?Update:It was a proxy causing the problem. Here is a tweak to Corey's code that I found to work for my company's authenticated proxy. I found that trying to use the webproxy.getdefaultproxy() didn't work because this seems to run as a service, possibly, therefore tossing out any user credentials. Hopefully this helps someone out.using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;using System.Net;using System.Xml;using System.Xml.XPath;using System.Text;public partial class StoredProcedures{ [Microsoft.SqlServer.Server.SqlProcedure] public static void SPGeocode(string Address, string City, string State, string appid) { //Declare string for URL //string URL; WebRequest request; try { //Replace the spaces in your Address with the plus sign to build the URL needed for Yahoo Address = Address.Replace(" ", "+"); //Build the URL request = WebRequest.Create("http://api.local.yahoo.com/MapsService/V1/geocode?appid=" + appid + "&street=" + Address + "&city=" + City + "&state=" + State); request.Proxy = new WebProxy("ww.xx.yy.zz", 8080); request.Proxy.Credentials = new System.Net.NetworkCredential("username", "password", "domain"); request.Credentials = System.Net.CredentialCache.DefaultCredentials; // Create the record and specify the metadata for the columns. SqlDataRecord record = new SqlDataRecord( new SqlMetaData("Latitude", System.Data.SqlDbType.VarChar, 100), new SqlMetaData("Longitude", System.Data.SqlDbType.VarChar, 100), new SqlMetaData("GeoCodedAddress", System.Data.SqlDbType.VarChar, 100), new SqlMetaData("GeoCodedCity", System.Data.SqlDbType.VarChar, 100), new SqlMetaData("GeoCodedState", System.Data.SqlDbType.VarChar, 2), new SqlMetaData("GeoCodedZip", System.Data.SqlDbType.VarChar, 10), new SqlMetaData("GeoCodedCountry", System.Data.SqlDbType.VarChar, 10), new SqlMetaData("Precision", System.Data.SqlDbType.VarChar, 100), new SqlMetaData("Warning", System.Data.SqlDbType.VarChar, 100)); using (HttpWebResponse response = request.GetResponse() as HttpWebResponse) { DataSet ds = new DataSet(); ds.ReadXml(response.GetResponseStream()); //If the response included an error, then the array length is different, 0-7 if (ds.Tables[0].Rows[0].ItemArray.Length.Equals(9)) { record.SetString(0, ds.Tables[0].Rows[0].ItemArray[0].ToString()); record.SetString(1, ds.Tables[0].Rows[0].ItemArray[1].ToString()); record.SetString(2, ds.Tables[0].Rows[0].ItemArray[2].ToString()); record.SetString(3, ds.Tables[0].Rows[0].ItemArray[3].ToString()); record.SetString(4, ds.Tables[0].Rows[0].ItemArray[4].ToString()); record.SetString(5, ds.Tables[0].Rows[0].ItemArray[5].ToString()); record.SetString(6, ds.Tables[0].Rows[0].ItemArray[6].ToString()); record.SetString(7, ds.Tables[0].Rows[0].ItemArray[7].ToString()); record.SetString(8, ds.Tables[0].Rows[0].ItemArray[8].ToString()); } else //If the response did not include an error, then the array length is 0-6 { record.SetString(0, ds.Tables[0].Rows[0].ItemArray[0].ToString()); record.SetString(1, ds.Tables[0].Rows[0].ItemArray[1].ToString()); record.SetString(2, ds.Tables[0].Rows[0].ItemArray[2].ToString()); record.SetString(3, ds.Tables[0].Rows[0].ItemArray[3].ToString()); record.SetString(4, ds.Tables[0].Rows[0].ItemArray[4].ToString()); record.SetString(5, ds.Tables[0].Rows[0].ItemArray[5].ToString()); record.SetString(6, ds.Tables[0].Rows[0].ItemArray[6].ToString()); record.SetString(7, "Precision Good"); record.SetString(8, "No Error"); } } // Mark the begining of the result-set. SqlContext.Pipe.SendResultsStart(record); SqlContext.Pipe.SendResultsRow(record); // Mark the end of the result-set. SqlContext.Pipe.SendResultsEnd(); } catch (Exception ex) { //Catch any errors here throw new Exception("mynewexception", ex); } }};

RE: Geocoding with SQL Server 2005

zootie — Wed, 21 Feb 2007 12:32:00 GMT

I probably have made small changes since then. But a quick tests, and the TVF function seems to be working ok. The SP also seems to be working, however, the precision is always null. I did modify the SP a bit, so there might be something in the logic that broke since then... I'll try and sit down later, and go over the code, see differences between the SP and TVF, and the original article code...

RE: Geocoding with SQL Server 2005

scb — Wed, 21 Feb 2007 11:37:00 GMT

That's odd - I haven't checked in with Yahoo Maps API lately, so I'm wondering if they changed any of their web service calls? Is the tutorial working out there for others? I'll give it a try from scratch on my new machine & check back in & see if mine is still working.

RE: Geocoding with SQL Server 2005

Matt Rushton — Wed, 21 Feb 2007 09:04:00 GMT

I have followed the tutorial and everything went according to plan but when i execute the SP, it says Command Completed Successfully but I dont get any rows returned. I'm using the same test data as in the tutorial and I've tried other addresses too.Any ideas whats wrong or where I should start troubleshooting?Thanks

RE: Geocoding with SQL Server 2005

zootie — Fri, 09 Feb 2007 12:37:00 GMT

I also agree that making a CLR call from within a Trigger is a bit risky (your DBA probably won’t appreciate you doing this while he is away). With this assembly, I've encountered a sporadic error, presumably due to connectivity issues, that locks the assembly, and forces you to have to reinstall it. I haven't been able to reproduce it in a controlled environment to try and handle it within the assembly.

Msg 6260, Level 16, State 1, Line 1

An error occurred while getting new row from user defined Table Valued Function :

System.InvalidOperationException: Handle is not initialized.

System.InvalidOperationException:

at System.Runtime.InteropServices.GCHandle.FromIntPtr(IntPtr value)

The statement has been terminated.

That aside, to handle multiple matches by averaging the geocodes, using the TSV function above, the code would look like

select addr.AddressGuid, gc.*

into #tmpAddressGeocodefrom dbo.AddressTbl addr with (NoLock) cross apply dbo.fncGeocodeByAddress ( addr.StreetAddress1 + coalesce (' ' + StreetAddress2,''), addr.CityName, addr.State, addr.Zip, 'YahooDemo' ) gc

update addr set

LatitudeRad = LatAvg

, LongitudeRad = LongAvg

, LatestGeocodeAttemptDate = getutcdate()

, GeocodePrecision = Prec

, GeocodeMultiCnt = GeoCnt

from (

select

AddressGuid,

LatMin = Min (LatitudeRad),

LongMin = Min (LongitudeRad),

LatMax = Max (LatitudeRad),

LongMax = Max (LongitudeRad),

LatAvg = Avg (LatitudeRad),

LongAvg = Avg (LongitudeRad),

Prec = dbo.Concatenate (distinct geos.[Precision] ),

GeoCnt = count (*)

from #tmpAddressGeocode geos with (NoLock)

group by AddressGuid

) geos

join dbo.AddressTbl addr on addr.AddressGuid = geos.AddressGuid

where

-- 5 miles or less when multiple points

dbo.DistanceGreatArc ( LatMin, LongMin, LatMax, LongMax ) * dbo.EarthRadiusCalc ( 1, null ) <= 10.0

drop table #tmpAddressGeocode

I’m storing other information, like when the geocode was obtained, a list of the precision levels, and how many matches were found. I'm also using a couple other CLR functions. dbo.Concatenate is included in the SQL CLR programming samples, and dbo.DistanceGreatArc is used to calculate the distance (there are multiple implementations you can find on the Internet, either as a CLR or SQL function). The check for the radius to be within 5 miles is a bit redundant, and you could omit it.

If you’re geocoding a considerable number of records, you probably want to mange the temp table a bit more, and use a top to limit the number of geocodes you perform on each run (see earlier post).

RE: Geocoding with SQL Server 2005

scb — Fri, 09 Feb 2007 06:59:00 GMT

alzdba - This is exactly what we do on our production system. We have another table where a bit gets set on or off based on business rules, and then a sql job comes & picks up the bits=1 and runs the CLR process. This is what I would recommend as well.

RE: Geocoding with SQL Server 2005

ALZDBA — Fri, 09 Feb 2007 00:16:00 GMT

- it would look like bad code !

- keep in mind you have no control over the availability of the counterparty !

- If you put this clr-routine into a trigger, it will execute "in transaction", meaning if the clr-routine fails, your transaction will fail, so your actual insert statement will be rolled back !!!

- You might want to put this in an assynchrone process (e.g. a sqlagent job). maybe within the insert trigger just feedin the pk into a "trigger"-table on which your sqlagent job will start from to get all the geo-info and will report bad info.

RE: Geocoding with SQL Server 2005

Ken Nelson-405493 — Thu, 08 Feb 2007 21:19:00 GMT

I've just found this tutorial today, and it's awesome! I've contacted Corey directly, but I'm also throwing this one out there for anyone that might catch it. My DBA is out of town, and if I wanted to execute the Assembly and immediately stick the lat/lon into a table from within an Insert trigger for a newly created row, what would that look like?

RE: Geocoding with SQL Server 2005

scb — Thu, 11 May 2006 16:37:00 GMT

Great addition! Thanks!

RE: Geocoding with SQL Server 2005

zootie — Wed, 10 May 2006 16:13:00 GMT

I recoded it as a TVF function. I modified the logic to return multiple matches, and did some code changes.

I used it to geocode 500 addresses, and it found 4 that returned multiple matches (3 of them at the address level). While it doesn't happen often, and the accuracy of the first match is probably good enough for most applications, if you want high accuracy, you should analyze all the matches returned.

Some examples of how to call it (the first 3 return multiple matches)

select * from dbo.fncGeocodeByAddress ( '245 Union Ave', 'Pueblo', 'CO', '81003', 'YahooDemo' )select * from dbo.fncGeocodeByAddress ( '7920 Stone Creek Dr.', 'Chanhassen', 'MN', '55317', 'YahooDemo' )select * from dbo.fncGeocodeByLocation ( 'Springfield', 'YahooDemo' )select * from dbo.fncGeocodeByAddress ( '100 East 42nd Street', 'new york', 'NY', Null, 'YahooDemo' )select * from dbo.fncGeocodeByAddress ( '701 First Street', 'Sunnyvale', 'CA', Null, 'YahooDemo' )

By making it a TVF, it is more flexible, and you can call it in a select using CROSS APPLY

insert tmpAddressGeocode ( [AddressGuid] ,[Latitude] ,[Longitude] ,[Address] ,[City] ,[State] ,[Zip] ,[Country] ,[Precision] ,[Warning] )select top 100 addr.AddressGuid, gc.*from dbo.AddressTbl addr with (NoLock) left join tmpAddressGeocode alldone with (NoLock) on alldone.AddressGuid = addr.AddressGuid cross apply dbo.fncGeocodeByAddress ( addr.StreetAddress1 + coalesce (' ' + StreetAddress2,''), addr.CityName, addr.State, addr.Zip, 'YahooDemo' ) gcwhere alldone.AddressGuid is Null

SQL Installation

CREATE FUNCTION [dbo].[fncGeocodeByLocation](@Location nvarchar(4000), @appid nvarchar(100))RETURNS TABLE ( [Latitude] float Null, [Longitude] float Null, [Address] nvarchar(80) Null, [City] nvarchar(35) Null, [State] nchar(2) Null, [Zip] nvarchar(10) Null, [Country] nvarchar(35) Null, [Precision] nvarchar(10) Null, [Warning] nvarchar(1024) Null)AS EXTERNAL NAME [YahooGCSQL].[sqlservercentral.Geocoding.YGeocode].[GeocodeByLocation]GO

CREATE FUNCTION [dbo].[fncGeocodeByAddress]( @Address [nvarchar](4000), @City [nvarchar](4000), @State [nvarchar](4000), @Zip [nvarchar](10), @appid nvarchar(100))RETURNS TABLE ( [Latitude] float Null, [Longitude] float Null, [Address] nvarchar(80) Null, [City] nvarchar(35) Null, [State] nchar(2) Null, [Zip] nvarchar(10) Null, [Country] nvarchar(35) Null, [Precision] nvarchar(10) Null, [Warning] nvarchar(1024) Null)AS EXTERNAL NAME [YahooGCSQL].[sqlservercentral.Geocoding.YGeocode].[GeocodeByAddress]GO

Function Assembly Code

using System;using System.Collections;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using System.Web;using System.Xml;using System.Xml.XPath;using System.Text;using Microsoft.SqlServer.Server;

namespace sqlservercentral.Geocoding{ public sealed class YGeocode { /// <summary> /// Prevent instances by making constructor private /// </summary> private YGeocode() { }

/// <summary> /// Query Yahoo Geocode API, and return an enumeration of results. Have to provide City and State or Zip or Location. Data is extracted for SQL by FillRow /// </summary> /// <param name="AppId">Yahoo application ID</param> /// <param name="StreetAddress">Street Address</param> /// <param name="City">City name</param> /// <param name="State">US State 2 letter abbreviation</param> /// <param name="Zip">Zip Code</param> /// <param name="Location">Free form address/location</param> /// <returns>Enumeration of addresses found</returns> private static IEnumerable GeocodeStart(SqlString AppId, SqlString StreetAddress, SqlString City, SqlString State, SqlString Zip, SqlString Location) { // Based on http://www.sqlservercentral.com/columnists/cBunch/geocodingwithsqlserver2005.asp try { // Build the URL StringBuilder sb = new StringBuilder(); sb.Append(AppId); if (!StreetAddress.IsNull) { sb.Append("&street="); sb.Append(StreetAddress); } if (!City.IsNull) { sb.Append("&city="); sb.Append(City); } if (!State.IsNull) { sb.Append("&state="); sb.Append(State); } if (!Zip.IsNull) { sb.Append("&zip="); sb.Append(Zip); } if (!Location.IsNull) { sb.Append("&location="); sb.Append(Location); } // Replace spaces and # with + // string Address = System.Web.HttpUtility.UrlEncode(StreetAddress); // It seems that HttpUtility.UrlEncode is not available here (Compact Framework?), so we have to do it "manually" // If need be, could use the implementation available at http://www.devx.com/vb2themax/Tip/19352 (just check for the = and & ) sb.Replace('#', '+'); sb.Replace(' ', '+'); // Prepend the API URL sb.Insert(0, "http://api.local.yahoo.com/MapsService/V1/geocode?appid=");

String URL = sb.ToString();

// Get the XML response XmlTextReader xmlread = new XmlTextReader(URL);

// Create a Dataset to hold the results DataSet ds = new DataSet();

// Fill the DS ds.ReadXml(xmlread);

// Return rows of data return ds.Tables[0].Rows; } catch (Exception ex) { // Catch any errors here ; } return null; }

[SqlFunction(FillRowMethodName = "FillRow", TableDefinition="[Latitude] float Null, [Longitude] float Null, [Address] nvarchar(80) Null, [City] nvarchar(35) Null, [State] nchar(2) Null, [Zip] nvarchar(10) Null, [Country] nvarchar(35) Null, [Precision] nvarchar(15) Null, [Warning] nvarchar(1024) Null")] public static IEnumerable GeocodeByAddress(SqlString StreetAddress, SqlString City, SqlString State, SqlString Zip, SqlString AppId) { return GeocodeStart(AppId, StreetAddress, City, State, Zip, null); }

[SqlFunction(FillRowMethodName = "FillRow", TableDefinition = "[Latitude] float Null, [Longitude] float Null, [Address] nvarchar(80) Null, [City] nvarchar(35) Null, [State] nchar(2) Null, [Zip] nvarchar(10) Null, [Country] nvarchar(35) Null, [Precision] nvarchar(15) Null, [Warning] nvarchar(1024) Null")] public static IEnumerable GeocodeByLocation(SqlString Location, SqlString AppId) { return GeocodeStart(AppId, null, null, null, null, Location); }

[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1021:AvoidOutParameters"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1021:AvoidOutParameters"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1021:AvoidOutParameters"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1021:AvoidOutParameters"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1021:AvoidOutParameters"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1021:AvoidOutParameters"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1021:AvoidOutParameters"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1021:AvoidOutParameters")] public static void FillRow(object obj, out SqlDouble Latitude, out SqlDouble Longitude, out string Address, out string City, out string State, out string Zip, out string Country, out string Precision, out string Warning ) { // XML elements and attributes are case sensitive. // Access by name to protect against API changes. It is slower, but not so bad compared with the cost of calling an Internet API DataRow row = (DataRow)obj; if (!String.IsNullOrEmpty(row["Latitude"].ToString())) Latitude = Convert.ToDouble(row["Latitude"].ToString()); else Latitude = SqlDouble.Null; if (!String.IsNullOrEmpty(row["Longitude"].ToString())) Longitude = Convert.ToDouble(row["Longitude"].ToString()); else Longitude = SqlDouble.Null; Address = (!String.IsNullOrEmpty(row["Address"].ToString())) ? row["Address"].ToString() : null; City = (!String.IsNullOrEmpty(row["City"].ToString())) ? row["City"].ToString() : null; State = (!String.IsNullOrEmpty(row["State"].ToString())) ? row["State"].ToString() : null; Zip = (!String.IsNullOrEmpty(row["Zip"].ToString())) ? row["Zip"].ToString() : null; Country = (!String.IsNullOrEmpty(row["Country"].ToString())) ? row["Country"].ToString() : null; // Better way to detect precision/warning columns w/o going back up to the columns collection? w/o resorting to exception trapping? w/o assuming indexes? // Alternate way = Precision = (row.ItemArray.Length >= 8) && !String.IsNullOrEmpty(row["precision"].ToString()) ? row["precision"].ToString() : null; Precision = (row.Table.Columns.Contains("precision")) && !String.IsNullOrEmpty(row["precision"].ToString()) ? row["precision"].ToString() : null; Warning = (row.Table.Columns.Contains("warning")) && !String.IsNullOrEmpty(row["warning"].ToString()) ? row["warning"].ToString() : null; } }};

RE: Geocoding with SQL Server 2005

ALZDBA — Wed, 03 May 2006 08:08:00 GMT

It works just fine now !!

Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Imports System.Net

Imports System.Xml

Imports System.Xml.XPath

Imports System.Text

'Zie http://www.sqlservercentral.com/columnists/cBunch/geocodingwithsqlserver2005_printversion.asp

Partial Public Class StoredProcedures

<Microsoft.SqlServer.Server.SqlProcedure()> _

Public Shared Sub SPCLR_Geocode(ByVal Address As String, ByVal City As String, ByVal State As String, ByVal appid As String)

'Declare string for URL

Dim URL As String = "_empty_"

'Yahoo test appid ""

Try

'Replace the spaces in your Address with the plus sign to build the URL needed for Yahoo

Address = Address.Replace(" ", "+")

'Provide webservice and proxy and credentials

Dim myWebSvc As New System.Net.WebClient

Dim myWebProxy As New System.Net.WebProxy("xxx.yyy.zzz.aaa", myport)

'use current users credentials

myWebProxy.Credentials = System.Net.CredentialCache.DefaultCredentials

myWebSvc.Proxy = myWebProxy

'Build the URL

URL = "http://api.local.yahoo.com/MapsService/V1/geocode?appid=" + appid + "&street=" + Address + "&city=" + City + "&state=" + State

' http://api.local.yahoo.com/MapsService/V1/geocode?appid=myyahooappid&street=8&city=seattle&state=wa

'Get the XML response

Dim xmlread As New XmlTextReader(myWebSvc.OpenRead(URL))

' Create the record and specify the metadata for the columns.

Dim record As New SqlDataRecord(New SqlMetaData("Latitude", System.Data.SqlDbType.VarChar, 100), New SqlMetaData("Longitude", System.Data.SqlDbType.VarChar, 100), New SqlMetaData("GeoCodedAddress", System.Data.SqlDbType.VarChar, 100), New SqlMetaData("GeoCodedCity", System.Data.SqlDbType.VarChar, 100), New SqlMetaData("GeoCodedState", System.Data.SqlDbType.VarChar, 2), New SqlMetaData("GeoCodedZip", System.Data.SqlDbType.VarChar, 10), New SqlMetaData("GeoCodedCountry", System.Data.SqlDbType.VarChar, 10), New SqlMetaData("Precision", System.Data.SqlDbType.VarChar, 100), New SqlMetaData("Warning", System.Data.SqlDbType.VarChar, 100))

'Create a Dataset to hold the results

Dim ds As New DataSet()

'Fill the DS

ds.ReadXml(xmlread)

' Mark the begining of the result-set.

SqlContext.Pipe.SendResultsStart(record)

For Each Rij As DataRow In ds.Tables(0).Rows

If Rij.ItemArray.Length.Equals(9) Then

record.SetString(0, Rij.ItemArray(0).ToString())

record.SetString(1, Rij.ItemArray(1).ToString())

record.SetString(2, Rij.ItemArray(2).ToString())

record.SetString(3, Rij.ItemArray(3).ToString())

record.SetString(4, Rij.ItemArray(4).ToString())

record.SetString(5, Rij.ItemArray(5).ToString())

record.SetString(6, Rij.ItemArray(6).ToString())

record.SetString(7, Rij.ItemArray(7).ToString())

record.SetString(8, Rij.ItemArray(8).ToString())

'If the response did not include an error, then the array length is 0-6

Else

record.SetString(0, Rij.ItemArray(0).ToString())

record.SetString(1, Rij.ItemArray(1).ToString())

record.SetString(2, Rij.ItemArray(2).ToString())

record.SetString(3, Rij.ItemArray(3).ToString())

record.SetString(4, Rij.ItemArray(4).ToString())

record.SetString(5, Rij.ItemArray(5).ToString())

record.SetString(6, Rij.ItemArray(6).ToString())

record.SetString(7, "Precision Good")

record.SetString(8, "No Error")

End If

SqlContext.Pipe.SendResultsRow(record)

' Mark the end of the result-set.

SqlContext.Pipe.SendResultsEnd()

Catch ex As Exception

Throw New Exception(String.Format("AT_Error with url [{0}]", URL.ToString), ex)

End Try

End Sub

End Class

Deploy and

EXECUTE SPCLR_Geocode '8','Seattle','WA','MyYahooAppId'

Thank your for your support !

RE: Geocoding with SQL Server 2005

scb — Wed, 03 May 2006 07:54:00 GMT

Theoretically.....we'll see. Give it a try.

RE: Geocoding with SQL Server 2005

ALZDBA — Wed, 03 May 2006 07:52:00 GMT

Thank you for your time.

It took me a while, but finaly I got the winform app to work ...

Address = Address.Replace(" ", "+")Dim myWebSvc As New System.Net.WebClientDim myWebProxy As New System.Net.WebProxy("xxx.yyy.zzz.aaa", myport)'use current users credentialsmyWebProxy.Credentials = System.Net.CredentialCache.DefaultCredentialsmyWebSvc.Proxy = myWebProxy'Build the URLURL = "http://api.local.yahoo.com/MapsService/V1/geocode?appid=" + appid + "&street=" + Address + "&city=" + City + "&state=" + State

'Get the XML responseDim xmlread As New XmlTextReader(myWebSvc.OpenRead(URL))

Now, can I just port this code into the CLR-proc for sql2005 ?

RE: Geocoding with SQL Server 2005

scb — Wed, 03 May 2006 06:53:00 GMT

Check in IE - go to Tools, Options, Connections tab, Lan Settings button..., and see if you have a proxy server specified. My guess is that you do. You may need to specify the same proxy server settings for this outgoing request from .NET. I did a quick search and found these....http://www.thescripts.com/forum/thread425824.htmlhttp://www.codeproject.com/vb/net/web_service_by_proxy.asp?df=100&forumid=16041&exp=0&select=1119964Let us know how it goes.