Reverse geocode macro

[thekiwi]

I'm using an Excel version (from this source) of reverse geocoding a latitude & longitude to derive an address. The spreadsheet runs a macro which takes the latitude and longitude values and runs it through Nominatim. As an example, the following coordinates for Salzburg Airport would result in:
https://nominatim.openstreetmap.org/...42&lon=13.0031

The Excel macro actually pulls the xml file from this reverse geocode:
https://nominatim.openstreetmap.org/...42&lon=13.0031

I'd like to take the Excel macro and amend it to work in my Access database. Unfortunately for me, I am having trouble getting this to work as my knowledge of VBA and reading XML files is non-existent. The macro in the Excel version is as follows:

Function NominatimReverseGeocode(lat As Double, lng As Double) As String    On Error GoTo eh
    Dim xDoc As New MSXML2.DOMDocument
    xDoc.async = False
    Url = "https://nominatim.openstreetmap.org/reverse?lat=" & lat & "&lon=" & lng
    xDoc.Load (Url)
    If xDoc.parseError.ErrorCode <> 0 Then
        Application.Caller.Font.ColorIndex = vbErr
        NominatimReverseGeocode = xDoc.parseError.reason
    Else
        xDoc.SetProperty "SelectionLanguage", "XPath"
        Dim loc As MSXML2.IXMLDOMElement
        Set loc = xDoc.SelectSingleNode("/reversegeocode/result")
        If loc Is Nothing Then
            Application.Caller.Font.ColorIndex = vbErr
            NominatimReverseGeocode = xDoc.XML
        Else
            Application.Caller.Font.ColorIndex = vbOK
            NominatimReverseGeocode = loc.Text
        End If
    End If
    Exit Function
eh:
    Debug.Print err.Description
End Function

I*think* this should be an easy change to get it to work in Access, but it is beyond me. What I'd like to do is automate what I am doing manually, which is taking the reverse geocode results and splitting them into fields of Town, District, Region and Country. I've attached what I'm using at present. It has a map embedded in the form (note that browser emulation will likely be required - refer here for details), but that isn't required for the purposes of answering my question. At the moment I am manually doing the following:

1. Add the latitude/longitude from a map (e.g. 47.2603,11.3475) into the unbound field GMaps LatLong
2. Click the Split Lat/Long button to split this to latitude & longitude
3. Click a reverse geocode button which takes me to the link above (i.e.):
   https://nominatim.openstreetmap.org/...42&lon=13.0031
4. Cut and paste the address into the unbound RevGeocode field (e.g.):
   
   
   Burgfried, Maxglan, Salzburg, 5020, Austria
5. Click a final button (Split Reverse Geocode) to split everything into Town, District, Region and Country

Truly clunky. I know. I'm hoping someone might be able to point out how the macro above could be used to skip steps 3-5 by simply taking the lat/long and spitting out the address into the appropriate fields.

Thanks in advance!


JB

[Micron]

You might need to post a copy of the Excel file with instructions if anyone wants to take this on. As for me, I'm not seeing the purpose of Application.Caller since AFAIK, that doesn't return a number (at least that's what I expect vbErr would be). Rather it would return an object, object name, or #REF error value, so I can't see the point of formatting text for that. You'd need a reference to Microsoft XML library (I think version 3) and maybe eliminate any line with this in it: Application.Caller - or explain what it's for and perhaps an Access equivalent can be determined.

Using the numbers you provided, I get this in Access:
Flughafen Innsbruck, Fürstenweg, Höttinger Au, Hötting, Innsbruck, Tirol, 6020, Österreich

but I suspect you want what you posted and not that. So I reversed the numbers (figuring that might be why) and got this
Ceerigaabo ???????, ???? Sanaag, Soomaaliland ??? ???????, Soomaaliya ???????

so probably not right either.

As for your list, if you have lat / long in separate form fields there's no need to split, manually or otherwise. If it's one string, still maybe not a Split function case if that's what you meant by split, but I'm on the fence with that at the moment. I'm seeing this as a one click operation so far. I haven't looked at your file yet - busy watching my team lose again (Blue Jays).

[thekiwi]

Regarding the result you got - that'd be my fault. The lat-long is correct - it's just the second example I included in my database table. Doh!

Let me post the Excel spreadsheet as well.

The reason I split the lat longs is because I paste them from Google maps as "lat, long".

Thanks micron

[thekiwi]

Attaching excel spreadsheet with full attribution to #sanand at this source.

Reverse geocode example in cell A32.

[Micron]

The reason I split the lat longs is because I paste them from Google maps as "lat, long".

I put this in 43.65493, -79.37874 and got

Li Ka Shing Knowledge Institute, Victoria Street, Downtown Yonge, Toronto Centre, Old Toronto, Toronto, Golden Horseshoe, Ontario, M5B 1T8, Canada

which looks like the right value. It's looking like this is a fairly straight forward conversion, albeit your example has 8 levels and that one is 10. Probably not an issue if you're happy with the entire string as is. I'm still fuzzy on the reversing thing though.

[davegri]

Here you go. Requires reference to Microsoft xml v3.0

Function NominatimReverseGeocode(lat As Double, lng As Double) As String
    On Error GoTo eh
    Dim xDoc As New MSXML2.DOMDocument, Url As String
    xDoc.async = False
    Url = "https://nominatim.openstreetmap.org/reverse?lat=" & lat & "&lon=" & lng
    xDoc.Load (Url)
    If xDoc.parseError.ErrorCode <> 0 Then
        NominatimReverseGeocode = xDoc.parseError.reason
    Else
        xDoc.SetProperty "SelectionLanguage", "XPath"
        Dim loc As MSXML2.IXMLDOMElement
        Set loc = xDoc.SelectSingleNode("/reversegeocode/result")
        If loc Is Nothing Then
            NominatimReverseGeocode = xDoc.XML
        Else
            NominatimReverseGeocode = loc.Text
        End If
    End If
    Exit Function
eh:
    Debug.Print Err.Description
End Function

Immediate window:

?NominatimReverseGeocode(47.7942,13.0031)
Flughafen Salzburg, Laschenskystraße, Viehhausen, Wals-Siezenheim, Bezirk Salzburg-Umgebung, Salzburg, 5071, Österreich

[Micron]

I already had pretty much that version. Was just trying to validate the desired results.
Carry on.

[thekiwi]

Just finding time to come back to this now. Thank you both for the replies. I've checked and have MSXML3.

Dave - when I run this in the immediate window I encounter a compile error "User-defined type not defined". This relates to this line in the macro:

Dim xDoc As New MSXML2.DOMDocument, Url As String

This is probably a really simple issue to resolve, but per my initial post, I have very limited knowledge of VBA.

I am assuming when I call the function from a button in my form it should look something like:

Private Sub ReverseGeocode_Click()
  NominatimReverseGeocode([Latitude],[Longitude])
End Sub

At which point I'd can add my code which splits out the town, region etc from loc.Text in the Nominatin function.

How far off am I?

Thanks

[June7]

A function that returns a value must be called so that something is done with the value.

1) call from query: SELECT *, NominatimReverseGeocode([Latitude],[Longitude]) AS Loc FROM tablename;

2) set a VBA variable: strLoc = NominatimReverseGeocode([Latitude],[Longitude])

3) expression in textbox: =NominatimReverseGeocode([Latitude],[Longitude])

[davegri]

Dave - when I run this in the immediate window I encounter a compile error "User-defined type not defined". This relates to this line in the macro:

I think setting a reference to Microsoft XML V3.0 will fix that.



As June7 states, a function returns a value, so you need to define a string to hold the value:

Private Sub ReverseGeocode_Click()
Dim sLocations as string
sLocations = NominatimReverseGeocode([Latitude],[Longitude])
End Sub

[thekiwi]

Excellent!

Thank you both very much for the replies, trouble-shooting and VBA education. With luck some of this might actually stick going forward! I've got it all working with just a single button click now. Much appreciated