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 Eights!
SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)

Group: General Forum Members
Points: 821 Visits: 175
Thanks for the feedback. Good advice on the warning column length.
pl-272086
pl-272086
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 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.
scb
scb
SSC Eights!
SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)

Group: General Forum Members
Points: 821 Visits: 175
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/
Allan M.
Allan M.
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 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


scb
scb
SSC Eights!
SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)

Group: General Forum Members
Points: 821 Visits: 175
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.
zootie
zootie
SSC Veteran
SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)

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



ALZDBA
ALZDBA
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47851 Visits: 9075

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


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
scb
scb
SSC Eights!
SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)

Group: General Forum Members
Points: 821 Visits: 175
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?
ALZDBA
ALZDBA
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47851 Visits: 9075

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


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
scb
scb
SSC Eights!
SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)

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