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

Geocoding with SQL Server 2005 Expand / Collapse
Author
Message
Posted Monday, May 1, 2006 10:27 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
Thanks for the feedback. Good advice on the warning column length.
Post #276607
Posted Monday, May 1, 2006 11:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 3, 2006 5:51 AM
Points: 3, Visits: 1
From what I understand from the Yahoo page, this is only for US addresses ? Seem to require "US State"and there is no Country parameter when sending the query. 
Post #276620
Posted Monday, May 1, 2006 11:31 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
I took a quick look on their yahoo developer group, and looks like they have some city level geocoding available for some locations. But unfortunately, you're right, the really precise geocodes may only be US. Trying this link returns a city centre for London.

http://api.local.yahoo.com/MapsService/V1/geocode?appid=YahooDemo&city=London

Search "internation" on the yahoo group, and you may have some more luck. Not sure...
http://groups.yahoo.com/group/yws-maps/
Post #276623
Posted Monday, May 1, 2006 11:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, February 9, 2008 9:28 AM
Points: 4, Visits: 2

Addresses with apartment numbers have some issues...

The following work: Apartment B32 or apt. B32

The following don't work: Apartment #B32, apt B32, apt #B32 (in fact, the pound (#) sign causes no results to be returned)

It seems like I will have to scan each address for an apartment number before sending it to the stored procedure, but there are lots of variations that people use 

PO Boxes are also causing some issues.

Any thoughts?

Allan

Post #276636
Posted Monday, May 1, 2006 12:04 PM
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
P.O. Boxes are of course not tied to a physical location, so there's nothing you can do about those. As far as the other dirty data, some of the other not-free geocoders that I've worked with, mainly Microsoft Mappoint Web Service, can handle a little more garbage (although I think they don't like # signs either). But to use the Yahoo one, you may have to do some string filtering, etc. as you mentioned. Sorry! That's a product of Yahoo's functionality/flexibility.
Post #276639
Posted Tuesday, May 2, 2006 6:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:51 AM
Points: 14, Visits: 43
This article came just in time. I used to work with address normalization and geocoding with a prior employer that had premium geocoding libraries available (like Group1 Centrus aka Sagent Centrus), so I know what is possible. Now I'm having to do similar work with less resources at a new place, and I'm also trying to give it a SQL 2005 spin. This article hits the mark, and saves me some work. Thank you.

Normally, for this type of service, you want to normalize/standarize the addresses before you geocode them (which fixes the apartment number issue). You can develop your own code to do this, or you can use an address normalization package. For the US and Canada, low cost alternatives include Semaphore Corp ZP4, and ZipInfo Perfect Address.

I found this site http://www.batchgeocode.com that allows you to geocode multiple addresses also using the Yahoo API, if you only need to do a one time run. You can optionally map the first 100 (using Google).

If you don't need high accuracy, need to geocode a large number of addresses quickly, and can live with zip level geocodes, a free alternative is to use the US Census ZCTAs (Zip Code Tabulation Areas). You can download the 2000 ZCTA file, and query it by zip code (or by major Zip Code). Not all zip codes are available, and some zip codes have changed since then, but it gets you pretty close. There is supposed to be a ZCTA 2003 file, but I haven't been able to find it.



Post #276760
Posted Wednesday, May 3, 2006 5:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:21 AM
Points: 6,743, Visits: 8,517

I like the article and the example.

I've tried to install it on my local sql2005STD instance wich has clr enabled and my testdb is trustworthy.

The vb.net version as well as the c#.net version do not work

I did add //Catch any errors here
            throw new Exception("mynewexception", ex);

When I execute the url in internet explorer I receive the correct results.

When executed with the sproc it says :

Msg 6522, Level 16, State 1, Procedure spclr_GeocodingC, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'spclr_GeocodingC':

System.Exception: mynewexception---> System.Net.WebException: The remote name could not be resolved: 'api.local.yahoo.com'

System.Net.WebException:

at System.Net.HttpWebRequest.GetResponse()

at System.Xml.XmlDownloadManager.GetNonFileStream(Uri uri, ICredentials credentials)

at System.Xml.XmlDownloadManager.GetStream(Uri uri, ICredentials credentials)

at System.Xml.XmlUrlResolver.GetEntity(Uri absoluteUri, String role, Type ofObjectToReturn)

at System.Xml.XmlTextReaderImpl.OpenUrlDelegate(Object xmlResolver)

at System.Threading.CompressedStack.runTryCode(Object userData)

at System.Runtime.CompilerServices.RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup(TryCode code, CleanupCode backoutCode, Object userData)

at System.Threading.CompressedStack.Run(CompressedStack compressedStack, ContextCallback callback, Object state)

at System.Xml.XmlTextReaderImpl.OpenUrl()

at System.Xml.XmlTextReaderImpl.Read()

at System.Xml.XmlTextReader.Read()

at System.Xml.XmlReader.MoveToContent()

at System.Data.DataSet.ReadXml(XmlReader reader, Boolean denyResolving)

at System.Data.DataSet.ReadXml(XmlReader reader)

at StoredProcedures.spclr_GeocodingC(String Address, String City, String State, String appid)

System.Exception:

at StoredProcedures.spclr_GeocodingC(String Address, String City, String State, String appid)

.

This server is running with the same account I am logged in

Any hints ?



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 #277077
Posted Wednesday, May 3, 2006 6:11 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
I haven't seen this before & I'm somewhat confused. The error would indicate that you can't resolve the URL, so maybe a DNS issue upstream from you. But then you said that it works in IE, which means your machine is indeed resolving the URL. Do you have any firewalls turned on on your local PC? Or anything that might be blocking http traffic coming from things other than IE? Not sure.......Could you try pulling some of the Yahoo API call out into a Windows form, and go into debug and watch as you step through?
Post #277097
Posted Wednesday, May 3, 2006 6:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:21 AM
Points: 6,743, Visits: 8,517

Here it is ... my vbform :

form fields : streettxt , citytxt, statetxt , datagridview Resultsdgv
form button : CallWebServicebtn

here's the code :

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

Public Class FormStartup

Private Sub CallWebServicebtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CallWebServicebtn.Click

Dim ds As New DataSet

Try

ds = SPCLR_Geocode(Streettxt.Text.ToString, Citytxt.Text.ToString, Statetxt.Text.ToString, "myyahootestappid")

If ds.Tables.Count > 0 Then

Resultdgv.DataSource = ds.Tables(0)

Else

Resultdgv.DataSource = Nothing

End If

 

Catch ex As Exception

MessageBox.Show(ex.ToString)

End Try

End Sub

Public Function SPCLR_Geocode(ByVal Address As String, ByVal City As String, ByVal State As String, ByVal appid As String) As DataSet

'Declare string for URL

Dim URL As String = "_empty_"

'Yahoo test appid "myyahootestappid"

Try

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

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

'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=myyahootestappid&street=8&city=seattle&state=wa

'

'Get the XML response

Dim xmlread As New XmlTextReader(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)

'If the response included an error, then the array length is different, 0-7

'If ds.Tables(0).Rows(0).ItemArray.Length.Equals(9) Then

' 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())

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

'Else

' 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")

'End If

 

'SqlContext.Pipe.SendResultsRow(record)

' Mark the end of the result-set.

'SqlContext.Pipe.SendResultsEnd()

Return ds

Catch ex As Exception

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

End Try

End Function

End Class

it also gives an error, but for some proxi thing ???

System.Net.WebException: The remote server returned an error: (407) Proxy Authentication Required.

at System.Net.HttpWebRequest.GetResponse()

at System.Xml.XmlDownloadManager.GetNonFileStream(Uri uri, ICredentials credentials)

at System.Xml.XmlDownloadManager.GetStream(Uri uri, ICredentials credentials)

at System.Xml.XmlUrlResolver.GetEntity(Uri absoluteUri, String role, Type ofObjectToReturn)

at System.Xml.XmlTextReaderImpl.OpenUrlDelegate(Object xmlResolver)

at System.Threading.CompressedStack.runTryCode(Object userData)

at System.Runtime.CompilerServices.RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup(TryCode code, CleanupCode backoutCode, Object userData)

at System.Threading.CompressedStack.Run(CompressedStack compressedStack, ContextCallback callback, Object state)

at System.Xml.XmlTextReaderImpl.OpenUrl()

at System.Xml.XmlTextReaderImpl.Read()

at System.Xml.XmlTextReader.Read()

at System.Xml.XmlReader.MoveToContent()

at System.Data.DataSet.ReadXml(XmlReader reader, Boolean denyResolving)

at System.Data.DataSet.ReadXml(XmlReader reader)

at GeocodingWIN.FormStartup.SPCLR_Geocode(String Address, String City, String State, String appid) in P:\Documents\Visual Studio 2005\Projects\Geocoding\GeocodingWIN\FormStartup.vb:line 56"

Being a dba that's trying to code a bit ... HEEELLLLPP

http://api.local.yahoo.com/MapsService/V1/geocode?appid=mytestcode&street=8&city=Seattle&state=WA

once again, internet exporer with the same url works fine



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 #277112
Posted Wednesday, May 3, 2006 6:53 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
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.html
http://www.codeproject.com/vb/net/web_service_by_proxy.asp?df=100&forumid=16041&exp=0&select=1119964

Let us know how it goes.
Post #277121
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse