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


Geocoding with SQL Server 2005


Geocoding with SQL Server 2005

Author
Message
scb
scb
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 175
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.
zootie
zootie
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: 48
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...



Crasz
Crasz
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 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);
}

}
};
Allen McGuire
Allen McGuire
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 560
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
Sam Stange
Sam Stange
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 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
{
}
}
}
}
Sam Stange
Sam Stange
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 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?
ALZDBA
ALZDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12236 Visits: 8925
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


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


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
zootie
zootie
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: 48
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.



Allen McGuire
Allen McGuire
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 560
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
nik.hall
nik.hall
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 84
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
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