Hi,

VBA function :

Option Compare Database
Option Explicit

'Public Type containing the geocoding of the postal address
Public Type tGeocodeResult
dLatitude As Double
dLongitude As Double
sRetAddress As String
sAccuracy As String
sStatus As String
End Type

'---------------------------------------------------------------------------------------
' Procedure : Geocode with Google Geocoding API v3
' Version : 1.01
' DateTime : 03/03/2011
' Author : Philben
' Purpose : converting addresses into geographic coordinates
' Parameter : No mandatory. string format or NULL
' Reference : http://code.google.com/intl/fr-FR/apis/maps/documentation/geocoding/index.html
' Remark : Query limit of 2,500 geolocation requests per day
' : A good accuracy is different of a good geocoding !!!
' : Minimum delay between two queries : >= 200 ms
'---------------------------------------------------------------------------------------
Public Function Geocode(Optional ByVal vAddress As Variant = Null, _
Optional ByVal vTown As Variant = Null, _
Optional ByVal vPostCode As Variant = Null, _
Optional ByVal vRegion As Variant = Null, _
Optional ByVal sCountry As String = "UNITED KINGDOM") As tGeocodeResult
On Error GoTo catch
Dim oXmlDoc As Object
Dim sUrl As String, sFormatAddress As String
If Not IsNull(vAddress) Then vAddress = Replace(vAddress, ",", " ")
sFormatAddress = (vAddress + ",") & _
(vTown + ",") & _
(vRegion + ",") & _
(vPostCode + ",") & _
sCountry
'To create the URL
sUrl = "http://maps.googleapis.com/maps/api/geocode/xml?address=" & sFormatAddress & "&sensor=false"
''XMLDOM to get the XML response
Set oXmlDoc = CreateObject("Microsoft.XMLDOM")
With oXmlDoc
.Async = False
If .Load(sUrl) And Not .selectSingleNode("GeocodeResponse/status") Is Nothing Then
'Status code
Geocode.sStatus = .selectSingleNode("GeocodeResponse/status").Text
'If a result is returned
If Not .selectSingleNode("GeocodeResponse/result") Is Nothing Then
'formatted_address
Geocode.sRetAddress = .selectSingleNode("//formatted_address").Text
'Accuracy
Geocode.sAccuracy = .selectSingleNode("//location_type").Text
'Latitude and longitude
Geocode.dLatitude = Val(.selectSingleNode("//location/lat").Text)
Geocode.dLongitude = Val(.selectSingleNode("//location/lng").Text)
End If
End If
End With
Set oXmlDoc = Nothing
Exit Function
catch:
Set oXmlDoc = Nothing
Err.Raise Err.Number, , Err.Description
End Function


Example of use :

Public Function test()
Dim tGeo As tGeocodeResult
Dim sPrompt As String
tGeo = Geocode("49 Featherstone Street", "LONDON", "EC1Y 8")
With tGeo
sPrompt = "Returned address:" & vbCrLf & "'" & .sRetAddress & "'" & vbCrLf & _
"Latitude:" & String(2, vbTab) & .dLatitude & vbCrLf & _
"Longitude:" & vbTab & .dLongitude & vbCrLf & _
"Accuracy:" & String(2, vbTab) & .sAccuracy & vbCrLf & _
"Status:" & String(2, vbTab) & .sStatus
MsgBox sPrompt, vbInformation, "Geocoding results"
End With
End Function


Good geocoding,

Philippe

Tried it with my adr but how do I validate the coords?

Hi,

In fact, it's the problem with geocoding.

First level of verification :
- If accuracy is not good ie "GEOMETRIC_CENTER" or "APPROXIMATE" then it's necessary to go one step further in the verification.
- But remember that a good accuracy is not synonymous of a good geocoding...

Second level of verification :
- Manual step : Add in google map (Web control in a form) the geocoding result to visually control the position
- Manual step (easily automatised) : Check that the postal address returned equals the postal address given in the function parameters
- Automatic step : If you have a table with the towns limits (latitudes and longitudes), it's easy to check if the geocoding is correct or not.

Hope this post helps you.

Feel free to send me your ideas.

Philippe

Hi,

please see attached a quick example of how to display the geocoding result on a map.

Philippe

I hope it is ok to post a comment here.

Tried it with my adr but how do I validate the coords?
That is to be expected from this API (non javascripted)

Google offers two methods of geocoding
1. Through the google maps Javascript (you need a website) API http://code.google.com/apis/maps/documentation/javascript/v2/services.html and

2. geocoding service getting an expected response. http://code.google.com/apis/maps/documentation/geocoding/index.html

a quote from No.2

This service is generally designed for geocoding static (known in advance) addresses for placement of application content on a map; this service is not designed to respond in real time to user input, for example. For dynamic geocoding (for example, within a user interface element), consult the documentation for the JavaScript API V2 Client Geocoder (http://code.google.com/apis/maps/documentation/services.html#Geocoding), the JavaScript API V3 Client Geocoder (http://code.google.com/apis/maps/documentation/javascript/services.html#Geocoding), or the Maps API for Flash Client Geocoder (http://code.google.com/apis/maps/documentation/flash/services.html#Geocoding).

In the google playground you can try No.1 here - I am guessing this will resolve your address and if I was a betting man I would bet a beer on it.
http://code.google.com/apis/maps/documentation/javascript/v2/examples/geocoding-simple.html

Philben,

I love the sample database you've kindly attached, in fact it's so nice that I want to use it in my own database (of course I'll leave credits and comments intact). However, this is a bit over my head as I know nothing about HTML coding. Would you be kind enough to place some comments on the Geocoding.html so I could amend it for my use.

in particular I was wondering why this needs references to:

http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd
and
http://www.w3.org/1999/xhtml


Many thanks
SmallTime

Hi,

Thanks for your kind message.

This need references to this links because they are doctype declarations.
You will find an explanation here (http://www.w3.org/QA/2002/04/valid-dtd-list.html)

hope this helps.
Best regards,

Philippe