Well the whole west coast actually, Im pretty sure its already tomorrow in Asia.

I recently inherited a database that, at one time had a bad input translation formula (no relation to previous post), but, as the result, all the zip codes on all the documents were zapped, proir to my comming on board..

There really was not that much data, but, I figured it was time to dig into google maps api and come up with a programatic solution.

I know that I get pretty good results pasteing partial addresses into the search bar, it comes back with whatever it can, formatted as well as it can.

I was quite amazed at how much data I could forget, and still come back with valid results.

I bookmarked this one a while ago, I new that I would come back to it at some time and it ended up being today.

http://econym.googlepages.com/example_geo.htm

Most importanly when I click the "Go!" button, while fiddler is running it shows me the data that goes back and forth, its pretty clear what the URL syntax is and what the parsing method is.

In the case of the example, they use, what years ago, I called dynamic javascript. Now that this sort of thing is in vogue, Im sure its got a flashy name, but I don't know what it is. I'm pretty sure that even though it has JSON in the URL, its not "real" JSON because its directly executed, but, none the less, I digress. I'm really much less interested in the name than how it works.

On to the code!

The code I wrote is a forground agent that runs on selected documents and places a value in the "zip" field.

I intended this to be reused so, I built it a little more robust than that, but, thats all the further I got with it today.


Sub Initialize
Dim Address As String
Dim AllVals As Variant
Dim Session As New NotesSession
Dim CurrDB As NotesDatabase
Dim UPD As NotesDocumentCollection
Dim CurrDoc As NotesDocument
Set CurrDB = Session.CurrentDatabase
Set UPD = CurrDB.UnprocessedDocuments
Set CurrDoc = UPD.getFirstdocument
Do Until CurrDoc Is Nothing
Address = CurrDoc.StreetAddress(0)
Address = Address + "," + CurrDoc.City(0)
Address = Address + "," + CurrDoc.State(0)
allvals = AddressToList( Address)
CurrDoc.Zip = allvals("zipcode")
Call CurrDoc.save(1,1)
Set CurrDoc = UPD.GetNExtDocument( CurrDoc)
Loop
End Sub

I've looked through this code and I see nothing that I can add to describe it better than it describes its self.

Function AddressToList( Byval address As String) As Variant
Dim GoogleKey As String
Dim URL As String
Dim ReturnVal List As String
GoogleKey = "ABQIAAAAtOjLpIVcO8im8KJFR8pcMhQjskl1-YgiA_BGX2yRrf7htVrbmBTWZt39_v1rJ4xxwZZCEomegYBo1w"
'Nope, its not my key, its googles key. I found that when you don't have a referer, it does not check that your key is yours, it just checks that you have one.
'Non the less, keys are free, so, go get one.
'This is where I planned to use a centrally stored value pair so, code will be portable between customers. Just configure your own key.

address = Replace( address, "%","%25")
address = Replace( address, " ","%20")
address = Replace( address, "&","%26")
address = Replace( address, ",","%2C")
address = Replace( address, "#","%23")
'Yes, I agree, that would have been an excellent place for a generic replace escape, but, this a a disposable agent for one shot use.
'There is stuff that you can't have in URL's so, replace some of the common ones with the equivalent escaped values.
URL = "http://maps.google.com/maps/geo?output=xml"
'This was a stab in the dark, but the example script said output=json . It didn't take too many guesses to figure this out.
'I never was able to find docs that explained this, just a littel trial and error.
URL = URL + "&q=" + Address
URL = URL + "&key=" + GoogleKey
'remember to use your own key instead of possibly causing google to consider this one poisoned.
' Dim fetcher As New XMLHTTP
Static fetcher As XMLHTTP
If fetcher Is Nothing Then
Set fetcher = New XMLHTTP
'OK, I admit it when I wrote this originally I was re-instantiating and destroying this for every document that gets processed.
'I only changed it to a static so I don't have to answer the question, "why didn't you use a static here?"
End If



'this is my class. WARNING! MICRSOFT ONLY, bail now if that offends you.
'anyone who wishes can build their own class for operating systems that I can't test with.
If Fetcher.GetXML( URL) Then
'if it does get a value, then do this stuff otherwise skip
ReturnVal( "streetaddress") = Fetcher.GetFirstNodeValue( "ThoroughfareName")
ReturnVal( "city") = Fetcher.GetFirstNodeValue( "LocalityName")
ReturnVal( "state") = Fetcher.GetFirstNodeValue( "AdministrativeAreaName")
ReturnVal( "county") = Fetcher.GetFirstNodeValue( "SubAdministrativeAreaName")
ReturnVal( "zipcode") = Fetcher.GetFirstNodeValue( "PostalCodeNumber")
ReturnVal( "geocode") = Fetcher.GetFirstNodeValue( "coordinates")
'all pretty boring stuff, I assume that the naming convention used is politically correct or something. It's not often you hear people talk about,
'my LocalityName is Irving and My AdministrativeAreaName is Texas, but, whatever!
AddressToList = ReturnVal
End If
End Function


'yes, the class really does come first, but, Im putting it in order of execution.
Class XMLHTTP
Private HTTPOBJ As Variant
Sub New
Set HTTPOBJ = CreateObject("Msxml2.ServerXMLHTTP")
'yup, thats the dreaded Microsoft part.
'maybe if they stopped giving developer tools away for free people would stop using them??.
End Sub
Function GetXML( URL As String) As Integer
'ok, I didn't waste a bunch of time in here with unused error handling
Call HTTPOBJ.Open("GET", URL, False)
Call HTTPOBJ.Send
If HTTPOBJ.readystate = 4 Then
'I suspect in an object that I would "really" reuse, I would want to do a bit more checking. before concidering the data good,
'but, remember this is an agent to be run by the developer (me) One time.
GetXML = True
Else
GetXML = False
End If
End Function
Sub Terminate
Delete HTTPOBJ
'clean up.
End Sub
Function GetFirstNodeValue( NodeName As String) As String
'Although the data comes through hierarchical, theres is a one to one relationship of data to data element, so, plucking the data out of the XML is very straight forward.
Dim ChildSelection As Variant
Dim ChildItem As Variant
Set ChildSelection = HTTPOBJ.responseXML.getElementsByTagName(NodeName)
If ChildSelection.Length = 1 Then
Set childitem = ChildSelection.Item(0)
GetFirstNodeValue = ChildItem.nodeTypedValue
Else
'Maybe this should throw an error?
'I've not been really evaluating the result set to see if it will every give more than 1 result.
GetFirstNodeValue = ""
End If
End Function
End Class

In reality there were quite a few addresses that google could not find zip codes for, (or even cities) For those I manually looked them up in USPS.gov

I've been meaning to look into their API, but, its exclusivly US centric and last time I did look, it was pretty complicated.

To anyone that is wondering, yes, you could perform client side validation in javascript before sending to the server, thats really what the API is for and actaully quite a bit easier.

That concludes our freebie for today, err, yesterday. Whatever.