Use Web Service to validate address 1

[MacroAlan]

I already have an Access 2010 DB that I have been using to input addresses for a couple years. I added a zipcode table a few months ago. See

http://www.http://alansofficespace.com/sAd0918122.htm

Now I want to go to the next step and verify the address/zipcodes on the fly.

Surely someone has already done this and I won’t have to re-invent the wheel.

Thanks


Alan

 

[kjv1611]

Your link got discombobulated or something... Here's what it should look like (for anyone else reading this post:

http://alansofficespace.com/sAd0918122.htm

As to doing what you as talking about, no, I've not tried.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[oharab]

Alan,
What validation are you trying to perform and what web service are you trying to perform?

To access web services, I usually use the WinHttpRequest object and build custom requests based on the service I'm accessing, but there are other libraries, such as the SOAP toolkit (

http://www.webcontinuum.net/ws_4.aspx)

which add an extra layer of abstraction you may find useful.

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[MacroAlan]

I want to pass in an address and get back the zipcode and the Lat/Long.

Right now, I am sending Zip to get City, State but would like to go the extra mile of address validation. B-)


Alan

 

[oharab]

Alan,
Here's a very quick example using the Yahoo PlaceFinder Geocoder.
It needs your AppID, you will need to tailor it to your requirements regarding the geocoding settings and you will need to tighten up the XML parsing routine, but it should be enough of a starter for 10 to get you going.

To use it:

Dim geocodedAddress as Address
geocodedAddress=Geocode("1600 Pennsylvania Avenue, Washington, DC")

'You can now save geocodedAddress.Line1, geocodedAddress.Latitude, etc in your database

You will need to check that you're complying with the T&C's of the service, you can't for example use the Google Maps geocoder for this purpose.


hth

Ben

Option Explicit
Private Const YAHOO_APPID As String = "[yourappidhere]"

Public Type Address
    Latitude As Double
    Longitude As Double
    Name As String
    Line1 As String
    Line2 As String
    Line3 As String
    Line4 As String
    Zip As String
End Type

Public Function Geocode(sAddress As String) As Address
    Dim responseXML As DOMDocument
    On Error GoTo Geocode_Error

    With New WinHttpRequest
        'Send our request to Yahoo
        .Open "GET", createYahooPlaceFinderURI(sAddress)
        .Send


        Set responseXML = New DOMDocument
        'load the response from Yahoo into an XML object
        responseXML.Load .ResponseBody

        Geocode = parseYahooResultXML(responseXML)

    End With
Geocode_Exit:
    On Error Resume Next
    Set responseXML = Nothing
    Exit Function

Geocode_Error:
    Select Case Err
    Case Else
        MsgBox "Unhandled Error in Module1.Geocode", Err.Number, Err.Description, Err.Source, Erl()
    End Select
    Resume Geocode_Exit
    Resume

End Function


Private Function createYahooPlaceFinderURI(sAddress As String) As String
'Build the requestURI as per [URL unfurl="true"]http://developer.yahoo.com/geo/placefinder/guide/requests.html[/URL]

    Const baseURI As String = "[URL unfurl="true"]http://where.yahooapis.com/geocode?"[/URL]
    Dim sLocation As String, sAppID As String

    sLocation = "location=" & URLEncode(sAddress)
    sAppID = "appid=" & sAppID
    createYahooPlaceFinderURI = baseURI & sLocation & sAppID
End Function

Private Function URLEncode(StringVal As String, Optional SpaceAsPlus As Boolean = False) As String
'From [URL unfurl="true"]http://stackoverflow.com/questions/218181/how-can-i-url-encode-a-string-in-excel-vba[/URL]

    Dim StringLen As Long: StringLen = Len(StringVal)
    If StringLen > 0 Then
        ReDim result(StringLen) As String
        Dim i As Long, CharCode As Integer
        Dim Char As String, Space As String
        If SpaceAsPlus Then Space = "+" Else Space = "%20"
        For i = 1 To StringLen
            Char = Mid$(StringVal, i, 1)
            CharCode = Asc(Char)
            Select Case CharCode
            Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
                result(i) = Char
            Case 32
                result(i) = Space
            Case 0 To 15
                result(i) = "%0" & Hex(CharCode)
            Case Else
                result(i) = "%" & Hex(CharCode)
            End Select
        Next i
        URLEncode = Join(result, "")
    End If
End Function

Private Function parseYahooResultXML(xml As DOMDocument) As Address
'Very basic parsing from [URL unfurl="true"]http://developer.yahoo.com/geo/placefinder/guide/responses.html[/URL]
'There is no error checking, no checking of quality etc.
    Dim a As Address
    With xml
        With .SelectSingleNode("//ResultSet/Result")
            a.Name = .SelectSingleNode("name").Text
            a.Line1 = .SelectSingleNode("line1").Text
            a.Line2 = .SelectSingleNode("line2").Text
            a.Line3 = .SelectSingleNode("line3").Text
            a.Line4 = .SelectSingleNode("line4").Text
            a.Zip = .SelectSingleNode("postal").Text
            a.Longitude = CDbl(.SelectSingleNode("longitude").Text)
            a.Latitude = CDbl(.SelectSingleNode("latitude").Text)
        End With
    End With

    parseYahooResultXML = a
End Function

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.