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 «««23456»»»

Geocoding with SQL Server 2005 Expand / Collapse
Author
Message
Posted Wednesday, February 21, 2007 11:37 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 17, 2012 2:21 PM
Points: 55, Visits: 168
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.
Post #346474
Posted Wednesday, February 21, 2007 12:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:51 AM
Points: 14, Visits: 43
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...


Post #346493
Posted Thursday, April 19, 2007 11:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 4, 2009 5:46 AM
Points: 1, Visits: 16
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);
}

}
};
Post #359742
Posted Tuesday, September 11, 2007 3:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 11:05 AM
Points: 23, Visits: 477
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.


Kindest Regards,

Allen McGuire
madtownlounge.com
Post #398272
Posted Wednesday, December 12, 2007 1:02 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 25, 2011 9:34 AM
Points: 17, Visits: 67
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
{
}
}
}
}
Post #432512
Posted Friday, February 8, 2008 4:03 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 25, 2011 9:34 AM
Points: 17, Visits: 67
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?
Post #453518
Posted Monday, February 11, 2008 7:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:09 AM
Points: 6,735, Visits: 8,495
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- 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
Post #453851
Posted Wednesday, February 20, 2008 10:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:51 AM
Points: 14, Visits: 43
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.



Post #458112
Posted Wednesday, February 20, 2008 10:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 11:05 AM
Points: 23, Visits: 477
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.



Kindest Regards,

Allen McGuire
madtownlounge.com
Post #458124
Posted Thursday, June 26, 2008 3:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 16, 2013 8:48 AM
Points: 2, Visits: 81
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
Post #523959
« Prev Topic | Next Topic »

Add to briefcase «««23456»»»

Permissions Expand / Collapse