Looking everywhere for VBA to do reverse geocoding, not just for address

angomera · Feb 3rd 2017

Hello,

I have been looking everywhere for a macro that can help me do reverse geocoding: https://developers.google.com/…mentation/geocoding/intro Reverse Geocoding basically consists of getting the address of a place after providing latitude and longitude.

I need to get the next data after providing latitud and longitud:

neighborhood
sublocality
sublocality_level_1
locality

You can see a better example here: https://maps.googleapis.com/ma…TKgfJAeQSlETDadjoQHsXBF6Y

What I need is a macro that gets all this components for every latitude and longitude I provide.

Can someone help me with this?

bryce · Feb 3rd 2017

Re: Looking everywhere for VBA to do reverse geocoding, not just for address

This guys did just that.

http://www.myengineeringworld.…using-vba-google-api.html

angomera · Feb 4th 2017

Re: Looking everywhere for VBA to do reverse geocoding, not just for address

Quote from bryce;784839

This guys did just that.

http://www.myengineeringworld.…using-vba-google-api.html

That link is a solution for geocoding. Getting latitud and longitude from a given address.

What I need as explained in the title and post is Reverse Geocoding, wich is getting the address from a given latitude and longitude. But not only that. I need to go a bit further and get: neighborhood, sublocality, sublocality_level_1 and locality.

But thanks anyway.

bryce · Feb 4th 2017

Re: Looking everywhere for VBA to do reverse geocoding, not just for address

You are going to have to read up on the API.

https://developers.google.com/…ng/intro#ReverseGeocoding

The article I posted should get you most of the way there.

angomera · Feb 4th 2017

Re: Looking everywhere for VBA to do reverse geocoding, not just for address

Quote from bryce;784854

You are going to have to read up on the API.

https://developers.google.com/…ng/intro#ReverseGeocoding

The article I posted should get you most of the way there.

I have read that page 10 times. By doing it I can get the info im talking about by a url with parameters. The thing is I need to get the info for 500 different coordinates. I don't see any information on that page that tells you how to write a macro for getting address, neigborhood, locality, sublocality, etc for a given latitude and longitude. I think you don't understand what I need. Please read above to understand.

bryce · Feb 4th 2017

Re: Looking everywhere for VBA to do reverse geocoding, not just for address

Another quick search yeilds this

https://usefulgyaan.wordpress.…/07/05/reverse-geocoding/

for the 500 coordinates you can use a loop. If you post that book I will help to the extent that I can. I have never used the API but it seems interesting

angomera · Feb 4th 2017

Re: Looking everywhere for VBA to do reverse geocoding, not just for address

Quote from bryce;784858

Another quick search yeilds this

https://usefulgyaan.wordpress.…/07/05/reverse-geocoding/

for the 500 coordinates you can use a loop. If you posy that book I will help to the extent that I can. I have never used the API but it seems interesting

Check this out: http://oco-carbon.com/coding/google-maps-and-excel-download/

Please download the file, its a workbook with macros enabled. In that file you can see a very good example of a macro that allready does reverse geocoding. The address field is the result of the macro. The thing is I don't need the address cos I allready have it.

Now, what I need is one macro in one column that calls neigborhood; another macro in another column that calls sublocality. And that is it.

Thing is I dont know macros. If I would know, I would edit the macro that calls the address in that file and edit the part that calls the address and change it to call neigborhood and sublocality.

Can you do that?

bryce · Feb 4th 2017

Re: Looking everywhere for VBA to do reverse geocoding, not just for address

Yes, sir. Ill get back to you in a bit.

**Trebor76** · Feb 4th 2017

Re: Looking everywhere for VBA to do reverse geocoding, not just for address

angomera,

If you must cross-post please provide the link(s) the other site(s) where you have do so as I have done (this time as it's your first post) with these links:

http://www.excelforum.com/showthread.php?t=1172412
https://www.mrexcel.com/forum/…cel-not-just-address.html
http://stackoverflow.com/quest…-not-only-for-the-address

Failure to do so is in breach of our rule "4b. Do not cross-post without supplying a link to the duplicate question on the other Forum" which you agreed to abide by when you joined. Please familiarise yourself with these rules (click here)when you get a moment.

Though I doubt I can help - how would you manually find the data? Knowing this may help in providing an automated solution.

Robert

angomera · Feb 4th 2017

Re: Looking everywhere for VBA to do reverse geocoding, not just for address

Sorry for not cross linking, im very new.
I don't understand your question.
Lets say I have 100 spots in thr world and its correspondant latitude and longitude in a database of mine. On the excel workbook I will imput the latitude in column 1A, the longitude in column B1; and column C1 will have a macro that gives me the neigborhood, and in Column D1 a kacro that gives me the sublocality. Thats it. I need the macros.

bryce · Feb 4th 2017

Re: Looking everywhere for VBA to do reverse geocoding, not just for address

Robert just means how would you find the data you want through google maps. I.E. how would you take a set or coordinates and have google maps return a neighborhood etc.

I think the answer is here...just need ot figure out how to get it written in VBA

http://maps.googleapis.com/map…24,-73.961452&sensor=true

angomera · Feb 4th 2017

Re: Looking everywhere for VBA to do reverse geocoding, not just for address

Quote from bryce;784865

Robert just means how would you find the data you want through google maps. I.E. how would you take a set or coordinates and have google maps return a neighborhood etc.

I think the answer is here...just need ot figure out how to get it written in VBA

http://maps.googleapis.com/map…24,-73.961452&sensor=true

Apparently google maps api only allows to post one lafitude and one longitude into the url for getting the json file or the xml file.

I thing the process of the macro would be to take the latitude in column A1 and the longitude in column B1, place it in the google maps url just like this: https://maps.googleapis.com/ma…TKgfJAeQSlETDadjoQHsXBF6Y
Then into that file look for the values neigborhood and sublocality.

And repeat de proces for a2, b2; a3, b3, etc

**Trebor76** · Feb 4th 2017

Re: Looking everywhere for VBA to do reverse geocoding, not just for address

Quote

Then into that file look for the values neighborhood and sublocality.

So from that xml file what are the neighborhood and sublocality names you need?

The User Defined Function (UDF) bryce mentions in thread 6 is using that URL so that is part of the solution I would imagine. This thread might also help.

angomera · Feb 4th 2017

Re: Looking everywhere for VBA to do reverse geocoding, not just for address

Quote from Trebor76;784869

So from that xml file what are the neighborhood and sublocality names you need?

The User Defined Function (UDF) bryce mentions in thread 6 is using that URL so that is part of the solution I would imagine. This thread might also help.

Trebor76, in the next xml: https://maps.googleapis.com/ma…TKgfJAeQSlETDadjoQHsXBF6Y

You can find neigborhood and sublocality_level_1 marked in green here

<address_component>
<long_name>Emaus</long_name>
<short_name>Emaus</short_name>
<type>neighborhood</type>
<type>political</type>
</address_component>

<address_component>
<long_name>Chapinero</long_name>
<short_name>Chapinero</short_name>
<type>political</type>
<type>sublocality</type>
<type>sublocality_level_1</type>
</address_component>

But in the link you shared which can be really helpfull I found that sublocality_level can go through one to 5. "sublocality indicates a first-order civil entity below a locality. For some locations may receive one of the additional types: sublocality_level_1 through to sublocality_level_5. Each sublocality level is a civil entity. Larger numbers indicate a smaller geographic area."

So I know now my need is: Neigborhood, locality, sublocality_level_1, sublocality_level_2, sublocality_level_3, sublocality_level_4 and sublocality_level_5, administrative_area_level_1, administrative_area_level_2, administrative_area_level_3, administrative_area_level_4 and administrative_area_level_5

In the next code you will see an example of sublocality_level_1, locality, administrative_area_level_3 and administrative_area_level_1 From a Hotel in Mexico City: https://maps.googleapis.com/ma…TKgfJAeQSlETDadjoQHsXBF6Y

<address_component>
<long_name>Portales Norte</long_name>
<short_name>Portales Nte</short_name>
<type>political</type>
<type>sublocality</type>
<type>sublocality_level_1</type>
</address_component>

<address_component>
<long_name>Ciudad de México</long_name>
<short_name>México D.F.</short_name>
<type>locality</type>
<type>political</type>
</address_component>

<address_component>
<long_name>Benito Juárez</long_name>
<short_name>Benito Juárez</short_name>
<type>administrative_area_level_3</type>
<type>political</type>
</address_component>

<address_component>
<long_name>Ciudad de México</long_name>
<short_name>CDMX</short_name>
<type>administrative_area_level_1</type>
<type>political</type>
</address_component>

So conslusion:

1. What I needed before got bigger, sorry for that but your link really helped
2. Not all cities shows the same data so the macro must be good to ignore missing information.
3. Google takes up to 2,500 free requests per day which is fine by me.

Check out this nice url: https://developers.google.com/…cript/geocoding?hl=es-419

Appreciate a lot your help.

**Trebor76** · Feb 5th 2017

Re: Looking everywhere for VBA to do reverse geocoding, not just for address

Hi angomera,

Have a look at the attached where you enter your latitude and longitude coordinates in column A and B (respectively) and then run the ReverseGeocoding macro. I also included the formatted address so you know what the coordinates have returned for each entry.

It works for me - the only "issue" being that it keeps translating the "administrative_area_level_1" from Spanish (Ciudad de México) to English (Mexico City) for which I have no idea why :confused:

I have spent way too much time on this so if it's not correct someone else will have to jump and help.

For anyone who can't download the macro is below - just have the latitude and longitude in columns A and B starting from Row 2 (or change the macro to suit) and run the macro while on that tab. Note the reference to Microsoft XML, v6.0 (or whatever your latest is) is required:

Code

Option Explicit
Sub XMLerverRead(dblLatitude As Double, dblLongitude As Double, lngRowNumber As Long)


    '//Note - this codes uses Early Binding!!
    'As such a reference to Microsoft XML, v6.0 (or whatever your latest is) is required.
    'This will give you access to the XML Object Library//.
    
    'This code is based on the code given from these sources:
        'http://stackoverflow.com/questions/13232613/automatic-geocoding-using-vba
        'http://stackoverflow.com/questions/20020990/looping-through-xml-using-vba
   
    Dim odc       As DOMDocument
    Dim node      As IXMLDOMNode
    Dim list      As IXMLDOMNodeList
    Dim attr      As IXMLDOMAttribute
    Dim childNode As IXMLDOMNode
    Dim strURL    As String
    Dim i         As Integer
    
    strURL = "https://maps.googleapis.com/maps/api/geocode/xml?latlng=" & dblLatitude & "," & dblLongitude
    
    Set odc = New MSXML2.DOMDocument
    odc.async = False
    odc.Load (strURL)
   
    'The following looks for the desired field name within the node
    'and once found returns its 'long name' to the desired cell
         
    'Formatted address
    Set list = odc.SelectNodes("GeocodeResponse/result/formatted_address")
    For Each node In list
        Range("C" & lngRowNumber) = node.ChildNodes(0).Text
        Exit For
    Next node
    
    'Address components
    Set list = odc.SelectNodes("GeocodeResponse/result/address_component")
    For Each node In list
        For i = node.ChildNodes.Length - 1 To 0 Step -1
            If node.ChildNodes(i).BaseName = "type" And node.ChildNodes(i).Text = "neighborhood" Then
                Range("D" & lngRowNumber) = node.ChildNodes(0).Text
                Exit For
            ElseIf node.ChildNodes(i).BaseName = "type" And node.ChildNodes(i).Text = "locality" Then
                Range("E" & lngRowNumber) = node.ChildNodes(0).Text
                Exit For
            ElseIf node.ChildNodes(i).BaseName = "type" And node.ChildNodes(i).Text = "sublocality_level_1" Then
                Range("F" & lngRowNumber) = node.ChildNodes(0).Text
                Exit For
            ElseIf node.ChildNodes(i).BaseName = "type" And node.ChildNodes(i).Text = "sublocality_level_2" Then
                Range("G" & lngRowNumber) = node.ChildNodes(0).Text
                Exit For
            ElseIf node.ChildNodes(i).BaseName = "type" And node.ChildNodes(i).Text = "sublocality_level_3" Then
                Range("H" & lngRowNumber) = node.ChildNodes(0).Text
                Exit For
            ElseIf node.ChildNodes(i).BaseName = "type" And node.ChildNodes(i).Text = "sublocality_level_4" Then
                Range("I" & lngRowNumber) = node.ChildNodes(0).Text
                Exit For
            ElseIf node.ChildNodes(i).BaseName = "type" And node.ChildNodes(i).Text = "sublocality_level_5" Then
                Range("J" & lngRowNumber) = node.ChildNodes(0).Text
                Exit For
            ElseIf node.ChildNodes(i).BaseName = "type" And node.ChildNodes(i).Text = "administrative_area_level_1" Then
                Range("K" & lngRowNumber) = node.ChildNodes(0).Text
                Exit For
            ElseIf node.ChildNodes(i).BaseName = "type" And node.ChildNodes(i).Text = "administrative_area_level_2" Then
                Range("L" & lngRowNumber) = node.ChildNodes(0).Text
                Exit For
            ElseIf node.ChildNodes(i).BaseName = "type" And node.ChildNodes(i).Text = "administrative_area_level_3" Then
                Range("M" & lngRowNumber) = node.ChildNodes(0).Text
                Exit For
            ElseIf node.ChildNodes(i).BaseName = "type" And node.ChildNodes(i).Text = "administrative_area_level_4" Then
                Range("N" & lngRowNumber) = node.ChildNodes(0).Text
                Exit For
            ElseIf node.ChildNodes(i).BaseName = "type" And node.ChildNodes(i).Text = "administrative_area_level_5" Then
                Range("O" & lngRowNumber) = node.ChildNodes(0).Text
                Exit For
            End If
        Next i
    Next node
End Sub
Sub ReverseGeocoding()
    Dim lngLastRow As Long
    Dim lngMyRow As Long
    
    On Error Resume Next
        lngLastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        If lngLastRow = 0 Then
            MsgBox "There is no data on the tab to work with!!", vbExclamation
            Exit Sub
        End If
    On Error GoTo 0
    
    Application.ScreenUpdating = False
    
    For lngMyRow = 2 To lngLastRow
        Call XMLerverRead(CDbl(Range("A" & lngMyRow)), CDbl(Range("B" & lngMyRow)), lngMyRow)
    Next lngMyRow
    
    Application.ScreenUpdating = True


End Sub

Display More

angomera · Feb 5th 2017

Re: Looking everywhere for VBA to do reverse geocoding, not just for address

Quote from Trebor76;784933

Hi angomera,

Have a look at the attached where you enter your latitude and longitude coordinates in column A and B (respectively) and then run the ReverseGeocoding macro. I also included the formatted address so you know what the coordinates have returned for each entry.

It works for me - the only "issue" being that it keeps translating the "administrative_area_level_1" from Spanish (Ciudad de México) to English (Mexico City) for which I have no idea why :confused:

I have spent way too much time on this so if it's not correct someone else will have to jump and help.

For anyone who can't download the macro is below - just have the latitude and longitude in columns A and B starting from Row 2 (or change the macro to suit) and run the macro while on that tab. Note the reference to Microsoft XML, v6.0 (or whatever your latest is) is required:

Display More

Its amazing what you have done, thanks for taking the time to help.

I do have a problem. I downloaded your file XML Extract and I go to the macros list and run the macro "ReverseGeocoding" and then I get an error popup windows that says: "Excel stoped working" and then excel closes. I checked out the reference to Microsoft XML, v6.0 and its correct.

I also tried opening a new excel file, I opened the vba window, copied your code as a new module, added the reference to Microsoft XML, v6.0, added latitude and longitude in row two and run the macro. Now I get an error that says: "Compilation error, the type defined by the user has not been defined." Then I added a reference to Microsoft XMl, v3.0 and the error disapeared when running the macro. But nothing happenes when running it, info doesn't get populated.

I am really new to macros so I might be making a very little mistake. I feel so frustrated because after what you have done I feel like one step away from making it.

BTW what do you mean by "run the macro while on that tab" ?

Appreciate any help.
Thanks.

angomera · Feb 5th 2017

Re: Looking everywhere for VBA to do reverse geocoding, not just for address

I just changed the reference from Microsoft XML, v6.0 to Microsoft XML, v3.0 in your file XML Extract. I run the macro "ReverseGeocoding" and now excel does not close, but again data does not get populated. Nothing happens.

Any ideas?

**Trebor76** · Feb 5th 2017

Re: Looking everywhere for VBA to do reverse geocoding, not just for address

It's my guess there's an issue with using early binding as the *.dll library on my machine is obviously different than on yours. As such, see if this late binding method does the trick:

Code

Option Explicit
Sub XMLerverReadLB(dblLatitude As Double, dblLongitude As Double, lngRowNumber As Long)    
    'Original thread: [URL]http://www.ozgrid.com/forum/newreply.php?do=postreply&t=202704[/URL]
    
    'This code is based on the code given from these sources:
        'http://stackoverflow.com/questions/13232613/automatic-geocoding-using-vba
        'http://stackoverflow.com/questions/20020990/looping-through-xml-using-vba
        
    Dim httpReq   As Object
    Dim odc       As Object
    Dim node      As Object
    Dim list      As Object    
    Dim childNode As Object
    Dim strURL    As String
    Dim i         As Integer
    
    'Set httpReq = CreateObject("MSXML2.XMLHTTP.6.0")
    Set httpReq = CreateObject("MSXML2.XMLHTTP")
    Set odc = CreateObject("MSXML.DOMDocument")
    
    strURL = "https://maps.googleapis.com/maps/api/geocode/xml?latlng=" & dblLatitude & "," & dblLongitude
    
    odc.async = False
    odc.Load (strURL)
   
    'The following looks for the desired field name within the node
    'and once found returns its 'long name' to the desired cell
         
    'Formatted address
    Set list = odc.SelectNodes("GeocodeResponse/result/formatted_address")
    For Each node In list
        Range("C" & lngRowNumber) = node.ChildNodes(0).Text
        Exit For
    Next node
    
    'Address components
    Set list = odc.SelectNodes("GeocodeResponse/result/address_component")
    For Each node In list
        For i = node.ChildNodes.Length - 1 To 0 Step -1
            If node.ChildNodes(i).BaseName = "type" And node.ChildNodes(i).Text = "neighborhood" Then
                Range("D" & lngRowNumber) = node.ChildNodes(0).Text
                Exit For
            ElseIf node.ChildNodes(i).BaseName = "type" And node.ChildNodes(i).Text = "locality" Then
                Range("E" & lngRowNumber) = node.ChildNodes(0).Text
                Exit For
            ElseIf node.ChildNodes(i).BaseName = "type" And node.ChildNodes(i).Text = "sublocality_level_1" Then
                Range("F" & lngRowNumber) = node.ChildNodes(0).Text
                Exit For
            ElseIf node.ChildNodes(i).BaseName = "type" And node.ChildNodes(i).Text = "sublocality_level_2" Then
                Range("G" & lngRowNumber) = node.ChildNodes(0).Text
                Exit For
            ElseIf node.ChildNodes(i).BaseName = "type" And node.ChildNodes(i).Text = "sublocality_level_3" Then
                Range("H" & lngRowNumber) = node.ChildNodes(0).Text
                Exit For
            ElseIf node.ChildNodes(i).BaseName = "type" And node.ChildNodes(i).Text = "sublocality_level_4" Then
                Range("I" & lngRowNumber) = node.ChildNodes(0).Text
                Exit For
            ElseIf node.ChildNodes(i).BaseName = "type" And node.ChildNodes(i).Text = "sublocality_level_5" Then
                Range("J" & lngRowNumber) = node.ChildNodes(0).Text
                Exit For
            ElseIf node.ChildNodes(i).BaseName = "type" And node.ChildNodes(i).Text = "administrative_area_level_1" Then
                Range("K" & lngRowNumber) = node.ChildNodes(0).Text
                Exit For
            ElseIf node.ChildNodes(i).BaseName = "type" And node.ChildNodes(i).Text = "administrative_area_level_2" Then
                Range("L" & lngRowNumber) = node.ChildNodes(0).Text
                Exit For
            ElseIf node.ChildNodes(i).BaseName = "type" And node.ChildNodes(i).Text = "administrative_area_level_3" Then
                Range("M" & lngRowNumber) = node.ChildNodes(0).Text
                Exit For
            ElseIf node.ChildNodes(i).BaseName = "type" And node.ChildNodes(i).Text = "administrative_area_level_4" Then
                Range("N" & lngRowNumber) = node.ChildNodes(0).Text
                Exit For
            ElseIf node.ChildNodes(i).BaseName = "type" And node.ChildNodes(i).Text = "administrative_area_level_5" Then
                Range("O" & lngRowNumber) = node.ChildNodes(0).Text
                Exit For
            End If
        Next i
    Next node
End Sub
Sub ReverseGeocodingLB()
    Dim lngLastRow As Long
    Dim lngMyRow As Long
    
    On Error Resume Next
        lngLastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        If lngLastRow = 0 Then
            MsgBox "There is no data on the tab to work with!!", vbExclamation
            Exit Sub
        End If
    On Error GoTo 0
    
    Application.ScreenUpdating = False
    
    For lngMyRow = 2 To lngLastRow
        Call XMLerverReadLB(CDbl(Range("A" & lngMyRow)), CDbl(Range("B" & lngMyRow)), lngMyRow)
    Next lngMyRow
    
    Application.ScreenUpdating = True
    
    MsgBox "Done", vbInformation


End Sub

Display More

If this doesn't work I'm out of ideas I'm afraid as it's all good from my side :confused:

Quote

BTW what do you mean by "run the macro while on that tab" ?

I just meant to run the macro while on the tab that has the data (coordinates) in it (Sheet1 in my posted workbook).

Regards,

Robert

angomera · Feb 6th 2017

Re: Looking everywhere for VBA to do reverse geocoding, not just for address

Quote from Trebor76;784963

It's my guess there's an issue with using early binding as the *.dll library on my machine is obviously different than on yours. As such, see if this late binding method does the trick:

If this doesn't work I'm out of ideas I'm afraid as it's all good from my side :confused:

Regards,

Robert

Display More

Trebor76 I created a new VBA module with the new code you post, I executed the macro and I get a popup that says: "Done". But no data gets populated on the workbook. I appreciate a lot your effort.

Thanks a lot.

**Trebor76** · Feb 6th 2017

Re: Looking everywhere for VBA to do reverse geocoding, not just for address

The strURL variable in my last (late binding) macro had posted with URL tags for some strange reason (just like it's doing with the 'Original thread' string at the start of the macro) :confused:

I have removed them from the above macro and re-tested and all good. If you could copy the XMLerverReadLB macro back and over the existing XMLerverReadLB macro into your workbook and try again it would be appreciated.

I have also included the macro on the attached with some extra coordinates just to prove (to myself at least) that the macro is working.

Regards,

Robert

Looking everywhere for VBA to do reverse geocoding, not just for address

Participate now!

Share