February 21, 2007 at 11:37 am
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.
February 21, 2007 at 12:32 pm
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...
April 19, 2007 at 11:50 am
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);
}
}
};
September 11, 2007 at 3:18 pm
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.
December 12, 2007 at 1:02 pm
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
{
}
}
}
}
February 8, 2008 at 4:03 pm
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?
February 11, 2008 at 7:41 am
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.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 20, 2008 at 10:35 am
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.
February 20, 2008 at 10:49 am
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.
June 26, 2008 at 3:12 am
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
June 26, 2008 at 8:53 am
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.
June 26, 2008 at 9:13 am
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 Function
Then 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.MoveNext
Loop
%>
June 26, 2008 at 9:24 am
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 SQL2005
I cant thank you guys enough for helping though, I will let you know how I get on...
Cheers,
Nik
July 31, 2008 at 6:57 pm
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 use
EXEC[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
July 31, 2008 at 7:19 pm
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.
Viewing 15 posts - 31 through 45 (of 58 total)
You must be logged in to reply to this topic. Login to reply