How to geocode an address ? (1 Viewer)

Status
Not open for further replies.

philben

Registered User.
Local time
Today, 09:20
Joined
Jan 30, 2011
Messages
23
Hi,

VBA function :
Code:
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 :
Code:
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
 

DCrake

Remembered
Local time
Today, 08:20
Joined
Jun 8, 2005
Messages
8,632
Tried it with my adr but how do I validate the coords?
 

philben

Registered User.
Local time
Today, 09:20
Joined
Jan 30, 2011
Messages
23
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
 

philben

Registered User.
Local time
Today, 09:20
Joined
Jan 30, 2011
Messages
23
Hi,

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

Philippe
 

Attachments

  • MapGeocoding_A2003.zip
    34.9 KB · Views: 59,079

darbid

Registered User.
Local time
Today, 09:20
Joined
Jun 26, 2008
Messages
1,428
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, the JavaScript API V3 Client Geocoder, or the Maps API for Flash Client Geocoder.

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
 

SmallTime

Registered User.
Local time
Today, 01:20
Joined
Mar 24, 2011
Messages
246
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
 

cavo789

New member
Local time
Today, 01:20
Joined
Jan 14, 2013
Messages
1
Good evening

Just found this post thanks to Google and wanted to say : THANK YOU.

I was searching for tutorials and here, more than this, I've found a ready-to-use code.

Thanks for the sharing.
 

LarryY

New member
Local time
Today, 04:20
Joined
Jul 8, 2010
Messages
4
Hello Could someone give me the correct syntax coding to put in an update query? For example:
I want to update the field [Latitude] to = ".dLatitude" as referred to in this function where
=Geocode(Me!Address1 & " " & Me!City & " " & Me!State & " " & Me!Zip & " USA")

So on the access GUI query -
I select the field [Latitude]
update to ",dlatitude"
Criteria = =Geocode(Me!Address1 & " " & Me!City & " " & Me!State & " " & Me!Zip & " USA")
- I get an error undefined function?
I already copied the module gecoding in my accdb.
I am a newbie - any suggestions to make this work?
THANKS I REALLY NEED SOMEONES HELP HERE!!!
 
Status
Not open for further replies.

Users who are viewing this thread

Top Bottom